SQl Server Questions

1. Difference between delete and truncate

• TRUNCATE is a DDL command whereas DELETE is a DML command.
• Truncate faster in performance wise, because it doesn't keep any logs. Delete slower than truncate because, it keeps logs.

Reason: When you type DELETE. All the data get copied into the Rollback Table space first. Then delete operation get performed. That why when you type ROLLBACK after deleting a table, you can get back the data (The system get it for you from the Rollback Table space).All this process take time. But when you type TRUNCATE, it removes data directly without copying it into the Rollback Table space. That why TRUNCATE is faster. Once you truncate you can’t get back the data.

• You can’t rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes the record permanently.
• In case of TRUNCATE, Trigger doesn't get fired. But in DML commands like DELETE .Trigger get fired.
• You can’t use conditions (WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause
• DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
   

1. What is the difference between char, varchar, nvarchar in sql

CHAR data type:

It is a fixed length data type.

Uses static memory allocation.

Supports both Character & Numbers.

Used to store non-Unicode characters.

VARCHAR data type:

It is a variable length data type.

Uses dynamic memory allocation.

Supports both Character & Numbers.

NVARCHAR data type:

Nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database.

Char Datatype

Char datatype which is used to store fixed length of characters. Suppose if we declared char (50) it will allocates memory for 50 characters. Once we declare char (50) and insert only 10 characters of word then only 10 characters of memory will be used

And other 40 characters of memory will be wasted.

Varchar Datatype

Varchar means variable characters and it is used to store non-Unicode characters. It will allocate the memory based on number characters inserted. Suppose if we declared varchar (50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar (50) and insert only 10 characters of word it will allocate memory for only 10 characters.

Nvarchar Datatype

Nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. Nvarchar datatype will take twice as much space to store extended set of characters as required by other languages. So if we are not using other languages then it’s better to use varchar datatype instead of nvarchar.

 

1. Data Types Found in SQL Server 2008

Numeric Data Types
Data Type	Description	Length
int	Stores integer values ranging from -2,147,483,648 to 2,147,483,647	4 bytes
tinyint	Stores integer values ranging from 0 to 255	1 byte
smallint	Stores integer values ranging from -32,768 to 32,767	2 bytes
bigint	Stores integer values ranging from -253 to 253-1	8 bytes
money	Stores monetary values ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
smallmoney	Stores monetary values ranging from -214,748.3648 to 214,748.3647	4 bytes
decimal(p,s)	Stores decimal values of precision p and scale s. The maximum precision is 38 digits	5–17 bytes
numeric(p,s)	Functionally equivalent to decimal	5–17 bytes
float(n)	Stores floating point values with precision of 7 digits (when n=24) or 15 digits (when n=53)	4 bytes (when n=24) or 8 bytes (when n=53)
real	Functionally equivalent to float(24)	4 bytes

 

Date and Time Data Types
Data Type	Description	Length	Example
date	Stores dates between January 1, 0001, and December 31, 9999	3 bytes	2008-01-15
datetime	Stores dates and times between January 1, 1753, and December 31, 9999, with an accuracy of 3.33 milliseconds	8 bytes	2008-01-15 09:42:16.142
datetime2	Stores date and times between January 1, 0001, and December 31, 9999, with an accuracy of 100 nanoseconds	6–8 bytes	2008-01-15 09:42:16.1420221
datetimeoffset	Stores date and times with the same precision as datetime2 and also includes an offset from Universal Time Coordinated (UTC) (also known as Greenwich Mean Time)	8-10 bytes	2008-01-15 09:42:16.1420221 +05:00
smalldatetime	Stores dates and times between January 1, 1900, and June 6, 2079, with an accuracy of 1 minute (the seconds are always listed as “:00”)	4 bytes	2008-01-15 09:42:00
time	Stores times with an accuracy of 100 nanoseconds	3–5 bytes	09:42:16.1420221

 

Character String Data Types
Data Type	Description	Length
char(n)	Stores n characters	n bytes (where n is in the range of 1–8,000)
nchar(n)	Stores n Unicode characters	2n bytes (where n is in the range of 1–4,000)
varchar(n)	Stores approximately n characters	Actual string length +2 bytes (where n is in the range of 1–8,000)
varchar(max)	Stores up to 231–1 characters	Actual string length +2 bytes
nvarchar(n)	Stores approximately n characters	2n(actual string length) +2 bytes (where n is in the range of 1–4,000)
nvarchar(max)	Stores up to ((231–1)/2)–2 characters	2n(actual string length) +2 bytes

 

Binary Data Types
Data Type	Description	Length
bit	Stores a single bit of data	1 byte per 8 bit columns in a table
binary(n)	Stores n bytes of binary data	n bytes (where n is in the range of 1–8,000)
varbinary(n)	Stores approximately n bytes of binary data	Actual length +2 bytes (where n is in the range of 1–8,000)
varbinary(max)	Stores up to 231–1 bytes of binary data	Actual length +2 bytes

 

Other Data Types
Data Type	Description	Length
cursor	Stores a reference to a cursor	N/A (cannot be used in a table)
sql_variant	May store any data type other than sql_variant, text, ntext, image, and timestamp	Up to 8,000 bytes
table	Stores a temporary table (such as a query result)	N/A (cannot be used in a table)
rowversion	Stores a value of the database time (a relative number that increments each time you insert or update data in a database. It is not related to calendar/clock time)	8 bytes
uniqueidentifier	Stores a globally unique identifier	2 bytes
xml	Stores formatted XML documents	Up to 2GB

 

 

1. Difference between Stored Procedure and Function in SQL Server

Basic Difference

1.     Function must return a value but in Stored Procedure it is optional (Procedure can return zero or n values).

2.     Functions can have only input parameters for it whereas Procedures can have input/output parameters.

3.     Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1.     Procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

2.     Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

3.     Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

4.     Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

5.     Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

6.     Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

7.     We can go for Transaction Management in Procedure whereas we can't go in Function.

 

1. Difference between Local & Global Temporary Tables

• Local temporary tables are visible when there is a connection, and are deleted when the connection is closed.MySQL

1	CREATE TABLE #<table name>

• Global temporary tables are visible to all users, and are deleted when the connection that created it is closed.MySQL

1	CREATE TABLE ##<table name>

 

Temporary tables are like regular tables, except they are stored in the tempdb and automatically dropped after they have been used.

Local temporary table	Global temporary table
A Local temporary table is defined by giving it a prefix of # and is scoped to the session in which you created it.	Global temporary tables can be seen by all sessions connected to the server and are defined by a prefix of ##.
An example that creates a local temporary table, populates it with one row, and then selects from it: CREATE TABLE #TempTable ( SSN INT, NAME CHAR(3) ) ; INSERT INTO #TempTable ( SSN, NAME ) VALUES ( 1, ‘Sachin’ ) ; GO SELECT * FROM #TempTable ; GO DROP TABLE #TempTable;	An example that creates a Global temporary table, populates it with one row, and then selects from it: CREATE TABLE ##TempTable ( SSN INT, NAME CHAR(3) ) ; INSERT INTO ##TempTable ( SSN, NAME ) VALUES ( 1, ‘Sachin’ ) ; GO SELECT * FROM ##TempTable ; GO DROP TABLE ##TempTable;
Local temporary tables are dropped by using the DROP statement or are automatically removed from memory when the user connection is closed.	Global temporary tables are removed from SQL Server if explicitly dropped by DROP TABLE. They are also automatically removed after the connection that created it disconnects and the global temporary table is no longer referenced by other connections.

 

 

6.  Differences between temporary tables and table variables

Temporary Tables

CREATE TABLE #TempTable　(

　　 ID INT NOT NULL,　

　　 Name VARCHAR (10),　

　　 DOB DATETIME)

Table Variables

DECLARE @TableVariable TABLE (

　　 ID INT NOT NULL,　

　　 Name VARCHAR (10),　

　　 DOB DATETIME)

Table variables cannot have Non-Clustered Indexes


You cannot create constraints in table variables


You cannot create default values on table variable columns


Statistics cannot be created against table variables


The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism.


The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance

7. Different type of Constraints

Constraints are some rules that enforce on the data to be enter into the database table. Basically constraints are used to restrict the type of data that can insert into a database table. Constraints can be defined in two ways:

Column Level


The constraints can be specified immediately after the column definition with the CREATE TABLE statement. This is called column-level constraints.
Table Level

The constraints can be specified after all the columns are defined with the ALTER TABLE statement. This is called table-level constraints.

SQL Server contains the following 6 types of constraints:

Not Null Constraint


Check Constraint


Default Constraint


Unique Constraint


Primary Constraint


Foreign Constraint

Not Null Constraint

A Not null constraint restrict the insertion of null values into a column. If we are using a Not Null Constraint for a column then we cannot ignore the value of this column during an insert of data into the table.

Column Level

Syntax

1. CREATE　TABLE　Table_Name　　




2. (　　




3. 　　 Column_Name　Datatype　CONSTRAINT　Constraint_Name　NOT　NULL,　　


);　

Example

1. Create　Table　My_Constraint　　




2. (　　




3. 　　 IID　int　NOT　NULL,　　




4. 　　 Name　nvarchar(50)　CONSTRAINT Cons_NotNull not　null,　　




5. 　　 Age　int　Not　Null,　　


)　

Table Level

Syntax

1. ALTER　TABLE　Table_Name　　




2. ALTER　COLUMN　Column_Name　Datatype　NOT　NULL　

Example

1. Alter　Table　My_Constraint　　




2. Alter　Column　IId　int　Not　Null　

Without SQL Command

We can also create a Not Null constraint in Microsoft SQL Server without execution of a SQL query.

First right-click on the table and select and click on the design option. Now check all the columns in the "Allow Nulls" option that should have a Null Value.



Figure 1: Table

Check Constraint

A Check constraint checks for a specific condition before inserting data into a table. If the data passes all the Check constraints then the data will be inserted into the table otherwise the data for insertion will be discarded. The CHECK constraint ensures that all values in a column satisfies certain conditions.

Column Level

Syntax

1. Create　Table　Table_Name　　




2. (　　




3. 　　 Column_Name　Datatype　Constraint　Constraint_Name　Check(Condition)　　


)　

Example

1. Create　Table　Constraint_　




2. (　　




3. 　　 IId　int　Constraint　Constraint_Name　Check(IId>100)　　


)　

Table Level

Syntax

1. Alter　Table　Table_Name　　




2. Add　Constraint　Constraint_Name　Check(Condition)　

Example

1. Alter　table Constraint_　




2. Add　constraint　Cons_Name　Check(IId>150)　

Without SQL Command

First go to Table Design then right-click on the Column_Name that will contain a check constraint and select the "Check Constraint" option then a new window will be shown. In this window add a constraint and provide its definition in the Expression Field.



Figure 2: Check Constraint



Figure 3: Select Check Constraint

Default Constraint

Specifies a default value for when a value is not specified for this column. If in an insertion query any value is not specified for this column then the default value will be inserted into the column.

Column Level

Syntax

1. Create　Table　Table_Name　　




2. (　　




3. 　　 Column_Name　DataType　Constraint　Constraint_Name　Default(Value),　　


)　

Example

1. Create　Table　My_Table1　　




2. (　　




3. 　　 IId　int　default(1500),　　




4. 　　 Name　Nvarchar(50)Constraint　Name_Default　Default('Pankaj'),　　




5. 　　 Age　Int,　　




6. 　　 Salary　Int　Default(100)　　


)　

Table Level

Syntax

1. Alter　Table　Tabel_Name　　




2. Add　Constraint　Constraint_Name　Default(Value)　for[Column_Name]　

Example

1. Alter　Table　My_Table1　




2. Add　Constraint　cons_Default Default(40)　for[Age]　

Without SQL Command

Go to Table Design then click on the specific column name that should have a default value and go to the column Property and provide the default value.



Figure 4: Column Property

Unique Constraint

It ensures that each row for a column must have a unique value. It is like a Primary key but it can accept only one null value. In a table one or more column can contain a Unique Constraint.

Column Level

Syntax

1. Create　Table　Table_Name　　




2. (　　




3. 　　 Column_Name　Datatype　Constraint　Constraint_Name　Unique　　


)　

Example

1. Create　Table　MY_Tab　　




2. (　　




3. 　　 IId　int　constraint　Unique_Cons　Unique　,　　




4. 　　 Name　nvarchar(50)　　


)　

Table Level

Syntax

1. Alter　Table_Name　　




2. Add　Constraint　Constraint_Name　Unique(Column_Name)　

Example

1. Alter　Table My_Tab　




2. Add　Constraint　Unique_Cons_ Unique(Name)　

Without SQL Command

First go to Table definition and select a column and right-click on that column. Now select the option Index/Keys. Now a window will be shown. Add a constrint and mark its "Is Unique" option as True.

Figure 5: Indexes & Keys

Figure 6: Select Indexes

Primary Key Constraint

A Primary key uniquly identifies each row in a table. It cannot accept null and duplicate data. One or more of the columns of a table can contain a Primary key.

Column Level

Syntax

1. Create　Table　Table_Name　　




2. (　　




3. 　　 Column_Name　Datatype　Constraint　Constraint_Name　Primary　Key,　　


)　

Example

1. Create　Table　Employee　　




2. (　　




3. 　　 IId　int　constraint　Const_primary_IId　primary　key,　　




4. 　　 Name　nvarchar(50)　　


)　

Table Level

Syntax

1. Alter　Table　Table_Name　　




2. Add　constraint　Constraint_Name　Primary　Key(Column_Name)　

Example

1. Alter　Table Employee




2. Add　constraint　Constraint_Name　Primary　Key(IId)　

Without SqlQuery

First go to table design and right-click on Column and select the "Set Primary Key" Option.



Figure 7: Set Primary Key

Foreign Key Constraint

A Foreign Key is a field in a database table that is a Primary key in another table. A Foreign key creates a relation between two tables. The first table contains a primary key and the second table contains a foreign key.

Column Level

Syntax

1. Create　Table　Table_Name　　




2. (　　




3. 　　 Column_Name　Datatype　Constraint　Constraint_Name　References　Reference_Table_Name(Reference_Column_Name)　　


)　

Example

1. Create　Table　Employee_　　




2. (　　




3. 　　 IId　int　constraint　Cons_Reference　References　My_Constraint(IId),　　




4. 　　 Age　int,　　




5. 　　 Salary　int　　


)　

Table Level

Syntax

1. ALTER　TABLE　Table_Name　　




2. ADD　CONSTRAINT　Constraint_Name　FOREIGN　KEY(Column_Name)　　




3. REFERENCES　Reference_Table　(Column_Name)　

Example

1. ALTER　TABLE　Employee_　




2. ADD　CONSTRAINT　Cons_Emp_Foreign　FOREIGN　KEY(IId)　　




3. REFERENCES　My_Constraint(IId)　

Without SQL Command

First go to table design than right-click on the column and select the "Relationship" option. Now a window will be shown. In this window click on the "Table and Column Specificat" option and select Primary Key table, Column name and Column name for foreign key.

Figure 8: Column Relationships

Figure 9: Foreign Key Relationships

　

Difference between PRIMARY KEY and UNIQUE KEY


Below table lists out the major difference between PRIMARY KEY and UNIQUE KEY:

	PRIMARY KEY	UNIQUE KEY
NULL	It doesn’t allow Null values. Because of this we refer PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT	Allows Null value. But only one Null value.
INDEX	By default it adds a clustered index	By default it adds a UNIQUE non-clustered index
LIMIT	A table can have only one PRIMARY KEY Column[s]	A table can have more than one UNIQUE Key Column[s]
CREATE SYNTAX	Below is the sample example for defining a single column as a PRIMARY KEY column while creating a table:CREATE TABLE dbo.Customer ( Id INT NOT NULL PRIMARY KEY, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) ) Below is the Sample example for defining multiple columns as PRIMARY KEY. It also shows how we can give name for the PRIMARY KEY: CREATE TABLE dbo.Customer ( Id INT NOT NULL, FirstName VARCHAR(100) NOT NULL, LastName VARCHAR(100), City VARCHAR(50), CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id,FirstName) )	Below is the sample example for defining a single column as a UNIQUE KEY column while creating a table:CREATE TABLE dbo.Customer ( Id INT NOT NULL UNIQUE, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) ) Below is the Sample example for defining multiple columns as UNIQUE KEY. It also shows how we can give name for the UNIQUE KEY: CREATE TABLE dbo.Customer ( Id INT NOT NULL, FirstName VARCHAR(100) NOT NULL, LastName VARCHAR(100), City VARCHAR(50), CONSTRAINT UK_CUSTOMER UNIQUE (Id,FirstName) )
ALTER SYNTAX	Below is the Syntax for adding PRIMARY KEY CONSTRAINT on a column when the table is already created and doesn’t have any primary key:ALTER TABLE dbo.Customer ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id)	Below is the Syntax for adding UNIQUE KEY CONSTRAINT on a column when the table is already created:ALTER TABLE dbo.Customer ADD CONSTRAINT UK_CUSTOMER UNIQUE (Id)
DROP SYNTAX	Below is the Syntax for dropping a PRIMARY KEY:ALTER TABLE dbo.Customer DROP CONSTRAINT PK_CUSTOMER	Below is the Syntax for dropping a UNIQUE KEY:ALTER TABLE dbo.Customer DROP CONSTRAINT UK_CUSTOMER







　

　

What is the difference between GETDATE and SYSDATETIME?




Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.

　

How data can be copied from one table to another table?




INSERT INTO SELECT

This command is used to insert data into a table which is already created.

SELECT INTO

This command is used to create a new table and its structure and data can be copied from existing table.

What is the difference between UNION and UNION ALL?




UNION statement is mainly used to combine the tables including the duplicate rows and UNION ALL combine but does not look for duplicate rows. With this, UNION ALL will be very faster than UNION statements.

What will be the maximum number of index per table?




ForSQLServer2005:
1 Clustered Index + 249 Nonclustered Index =250 Index
ForSQLServer2008:
1 Clustered Index + 999 Nonclustered Index = 1000 Index




　

　

Different joins in Sql Server

Inner Join


Outer Join (Left Outer Join, Right Outer Join, Full Outer Join)


Cross Join


Self Join

　

1. What is the difference between the WHERE and HAVING clauses?


The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).
What is the basic difference between clustered and a non-clustered index?

Clustered Index

Only one per table


Faster to read than non clustered as data is physically stored in index order


Cluster index exists on the physical level

Non Clustered Index

Can be used many times per table


Quicker for insert and update operations than a clustered index


They are not created on the physical level but at the logical level

What is VIEW

Views are virtual tables that are compiled at run time. The data associated with views are not physically stored in the view, but it is stored in the base tables of the view. A view can be made over one or more database tables. Generally we put those columns in view that we need to retrieve/query again and again. Once you have created the view, you can query view like as table. We can make index, trigger on view.

We make views for security purpose since it restricts the user to view some columns/fields of the table(s). Views show only those columns that are present in the query which is used to make view.


One more advantage of Views is, data abstraction since the end user is not aware of all the data present in database table.

Create View


CREATECREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued=No


Select View
SELECT * FROM [Current Product List]




Update View
CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued=No




Drop View
DROP VIEW [Current Product List]

Note:

1. We make views for security purpose since it restricts the user to view some columns/fields of the table(s).

2. One more advantage of Views is, data abstraction since the end user is not aware of all the data present in database table

　

What is FUNCTION

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, and Delete records in the database table.

Types of Function

1. System Defined Function

Scalar Function


Aggregate Function

2. User Defined Function

Scalar Function


Inline Table-Valued Function


Multi-Statement Table-Valued Function

　

　

　

　

　

　

Scalar Function

1. Select * from Employee

1. --Create function to get emp full name




2. Create function fnGetEmpFullName


(




3. @FirstName varchar(50),




4. @LastName varchar(50)


)




5. returns varchar(101)


As




6. Begin return (Select @FirstName + ' '+ @LastName);




7. end









8. --Calling the above created function




9. Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee

　

　

Inline Table-Valued Function

1. Create function fnGetEmployee()




2. returns Table


As




3. return (Select * from Employee)









4. --Now call the above created function




5. Select * from fnGetEmployee()

　

　

Multi-Statement Table-Valued Function

--Create function for EmpID,FirstName and Salary of Employee




1. Create function fnGetMulEmployee()




2. returns @Emp Table


(


EmpID int,




3. FirstName varchar(50),


Salary int


)


As


begin




4. Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;


--Now update salary of first employee




5. update @Emp set Salary=25000 where EmpID=1;


--It will update only in @Emp table not in Original Employee table


return




6. end

1. --Now call the above created function




2. Select * from fnGetMulEmployee()

1. --Now see the original table. This is not affected by above function update command




2. Select * from Employee

　

Note:

1. Unlike Stored Procedure, Function returns only single value.

2. Unlike Stored Procedure, Function accepts only input parameters.

3. Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).

4. Like Stored Procedure, Function can be nested up to 32 level.

5. User Defined Function can have up to 1023 input parameters while a Stored Procedure can have up to 2100 input parameters.

6. User Defined Function can't returns XML Data Type.

7. User Defined Function doesn't support Exception handling.

8. User Defined Function can call only Extended Stored Procedure.

9. User Defined Function doesn't support set options like set ROWCOUNT etc.