SQL Server FAQs

What maximum size of row is allowed in SQL Server ?

8060 bytes

 

What is referential integrity in SQL Server ?

We can enforce such referential integrity through foreign key constraints.

For an example,…

CREATE TABLE Table_Referenced 

(

id INT

CONSTRAINT pk_id PRIMARY KEY(id)--Create Primary Key here

);

GO

CREATE TABLE Table_Referencing

(

RID Int

CONSTRAINT fk_RID FOREIGN KEY(RID)REFERENCES Table_Referenced(id)--Creates foreign key names 'RID' which is referring primary key of Table_Referenced.id

);

 

SET NOCOUNT ON and SET NOCOUNT OFF

This Statement turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements

 

How to pass table name as stored procedure parameter in SQL Server 2005 (Write stored procedure) ?

Create PROCEDURE Pass_TableName_As_Param --create procedure
@TableName varchar(50) -- Parameter of table name
AS
BEGIN
SET NOCOUNT ON;
SET @TableName= RTRIM(@TableName) --Trim the parameter of table name
DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM'+ @table_name
END

 

Maximum number of parameters you can pass in a Store procedure in SQL Server 2005?

2100  in sqlserver 2005

1024 in sqlserver 2000

 

What is the name of the Store procedure to get the listing of current users?

sp_who

 

Difference between clustered index and non clustered index

The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db. The Telephone Directory is a fine example of Clustered Index as data and index are at the same page, whereas index in the back side of the book is a fine example of non-clustered index and  non-clustered index is a fast B-tree structure as index just points to the data page. Also only one clustered index is possible per table and 249 non-clustered index per table.

 

What is trigger?

Triggers are special types of stored procedures allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table.

There are four types of triggers.

1. Insert  2. Delete  3. Update  4. Instead of

 

What is constraints?

SQL Server users constraints to enforce limitations on the data that can be entered into a particular column in table. There are following types of constraints.

Unique, Default, Check, Primary Key, Foreign Key, Not Null.

 

What is the difference between Truncate and Delete?

Delete command removes the rows from a table based on the

condition that we provide with a WHERE clause. Truncate will

actually remove all the rows from a table. Delete Command require Log file updation for each row of deleting process. But the Truncate command not. Truncate will fail if the concerned table has foreign key constraints while delete doesnt.

 

What's the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

 

ACID

Atomicity, Consistency, Isolation, Durability

 

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

 

Difference Between Implict Transaction And Explict Transaction

Implicit Transaction is the auto commit. There is no beginning or ending of the transaction.

Explicit Transaction has the beginning, ending and rollback of transactions with the command
Begin Transaction
Commit Transaction and
Rollback Transation
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.

 

How to change Database name in SQL Server?

Use following code

Supported in SQL Server 2000 and 2005
exec sp_renamedb "test", "test1"

Supported in SQL Server 2005 and later version
ALTER Database "test1" Modify Name="test"

 

What is #temp table and @table variable in SQL Server?

#temp - is created and populated on disk, in the system database tempdb. Generally, the table gets cleared up automatically when the current procedure goes out of scope, however, we should manually clean up the data when we are done with it.

-- create temporary table

CREATE TABLE #myTempTable (

AutoID int,

MyName char(50) )

-- populate temporary table

INSERT INTO #myTempTable (AutoID, MyName )

SELECT  AutoID, MyName 

FROM     myOriginalTable

WHERE   AutoID <= 50000

-- Drop temporary table

drop table #myTempTable 

 

@table variable
table variable is similar to temporary table except with more flexibility. It is not physically stored in the hard disk, it is stored in the memory. We should choose this when we need to store less 100 records.

Syntax:

DECLARE @myTable TABLE (

AutoID int,

myName char(50) )

INSERT INTO @myTable (AutoID, myName )

SELECT  YakID, YakName

FROM     myTable

WHERE   AutoID <= 50

We don't need to drop the @temp variable as this is created inside the memory and automatically disposed when scope finishes.

 

How to return XML in SQL Server?

We can use FOR XML statement at the end of the query to return xml data from the SQL Server.

select * from mytable for xml auto

There are three mode of returning XML and they are auto, raw and explicit

 

What is OPENXML in SQL Server?

OPENXML can parse the xml data in SQL server very efficiently in SQL Server. OpenXML primarily gives the ability to insert XML data to the relational database, however we can query the data too using OpenXML. We need to specify the path of the xml element using xpath.

Syntax:

DECLARE @index int

DECLARE @xmlString varchar(8000)

SET @xmlString ='<Persons>

    <Person id="1">

     <Name>Mohan</Name>

     <PhoneNo>34343</PhoneNo>

    </Person>

    <Person id="2">

     <Name>Sita</Name>

     <PhoneNo>23432</PhoneNo>

    </Person>
 
 

</Persons>'

EXEC sp_xml_preparedocument @index OUTPUT, @xmlString 

SELECT *

FROM OPENXML (@index, 'Persons/Person')

WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')

EXEC sp_xml_removedocument @index

The above code snippet will give following result.
---------------------------------
1 Mohan 34343
2 Sita 23432
---------------------------------

 

What is the use of COALESCE in SQL Server?

Coalesce returns the first non-null expression among its arguments.

Lets say we have to return a non-null from more than one column, then we can use COALESCE function.

SELECT COALESCE(hourly_wage, salary, commission) AS 'Total Salary' FROM wages

In this case,

If hourly_wage is not null and other two columns are null then hourly_wage will be returned.
If hourly_wage, commission are null and salary is not null then salary will be returned.
If commission is non-null and other two columns are null then commission will be returned.

 

What is cursor in SQL Server?

A cursor is a set of rows together with a pointer that identifies a current row.

In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP and visual basic.

Typical syntax of cursor is

DECLARE @fName varchar(50), @lName varchar(50)

DECLARE cursorName CURSOR -- Declare cursor

LOCAL SCROLL STATIC

FOR

Select firstName, lastName FROM myTable

OPEN cursorName -- open the cursor

FETCH NEXT FROM cursorName

   INTO @fName, @lName

   PRINT @fName + ' ' + @lName -- print the name

WHILE @@FETCH_STATUS = 0

BEGIN

   FETCH NEXT FROM cursorName

   INTO @fName, @lName

   PRINT @fName + ' ' + @lName -- print the name

END

CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor

 

DESCRIBE is used to see table structure

 

How you can get the last identity value inserted in any table ?

@@IDENTITY

 

What is the maximum size of an SQL Server 2000 Database?

1,048,516 TB

 

how many database can be created on MSSQL Server 2000

32767

 

What is the use of @@TRANCOUNT in SQL Server?

Returns the number of active transactions for the current connection.

 

What is the maximum limit for Primary Key?

10 fields in MS Acces
900 Bytes in SQL Server

 

Where do you think the users names and passwords will be stored in sql server?

They get stored in master db in the sysxlogins table.

 

What is Log Shipping?

log shipping to feed transaction logs from one database to another

 

What Is DTS?

DTS is a set of tools you can use to import, export, and transform heterogeneous data between one or more data sources, such as Microsoft SQL Server, Microsoft Excel, or Microsoft Access. Connectivity is provided through OLE DB, an open-standard for data access. ODBC (Open Database Connectivity) data sources are supported through the OLE DB Provider for ODBC.

 

What is a tuple?

A tuple is an instance of data within a relational database.

 

What are three SQL keywords used to change or set someone’s permissions?

Grant, Deny and Revoke

 

How to get GUID in sql server?

select newid().

 

Can we create a Foreign Key with out Primary Key?

Yes. If the table has Unique Key then it is posible to create a Foreign key constraint

 

Difference between varchar and char:

varchar are variable length strings with a maximum length specified. If a string is less than the maximum length, then it is stored verbatim without any extra characters.
char are fixed length strings with a set length specified. If a string is less than the set length, then it is padded with extra characters so that it's length is the set length.

 

What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?

WITH ENCRYPTION keyword is used to encrypt the text of the Stored Procedure

 

Microsoft SQL Server's default protocol

Named Pipes

 

What are the disadvantages/limitation of the cursor?

Cursor requires a network roundtrip each time it fetches a record, thus consume network resources.
While data processing, it issues locks on part of the table, or on the whole table.

 

The maximum number of columns a table can have in SQL Server?

1024

 

38) How to Get SQL Server Version ?

@@VERSION

SELECT @@VERSION

 

39) What is the Max length of varchar variable in SQL Server?

8000

 

40) What is the Max Length of nVarchar in SQL Server?

4000

 

41) What is 'Self-referencing' tables in SQL Server ?

A FOREIGN KEY constraint can reference columns within the same table (a table itself).

 

42) DEFAULT definitions cannot be created on columns defined with the following DATA TYPE ?

Timestamp

 

43) What is the Max length of varchar variable in SQL Server?

8000

 

44) Number of PRIMARY KEY/FOREIGN KEY Constraints in a Table in SQL Server 2005/2008 ?

PRIMARY KEY :1, FOREIGN KEY :UNLIMITED (Recommended is 253)

 

45) How Can we Write the Transaction Block in SQL Server?

BEGIN TRANSACTION

Statement 1
Statement 2
..................
...............
IF(@ERROR>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

 

46) What is the page size in SQL Server?

8KB

 

47) How do you determine the maximum nested-level of Stored Procedure ?

The current nested level can be determine by : @@NESTLEVEL, The maximum nested level is 32 .

 

48) Which Function is used to count more than two billion rows in a table?

COUNT_BIG function - is identical to the COUNT function, but returns a BIGINT data type, whereas COUNT returns an INT

 

49) SQL Server allows for only 10 levels of nesting in CASE expressions.

 

50) Is it possible to create Cluster Index on Unique Key Column?

YES

 

51)How to Get nth Record in a Table?

First Get the n records fron the table using

Select Top n FROM UserTable
Now Reverse the Order using identity Column like:
Select Top n FROM UserTable Order By 1 DESC

Now we need nth record that can be get as
SELECT TOP 1 * FROM (Select Top n FROM UserTable Order By 1 DESC)AC

For Example i need to get 5th record From userTable then quey will be like this:

SELECT TOP 1 * FROM (SELECT TOP 5 * FROM UserTable Order By 1 DESC)AC

 

52) Can we use Truncate command on a table which is referenced by FOREIGN KEY?

No

 

53) What is a table called, if it has no Cluster Index?

Heap

 

54) How we can get the List of System Tables in DataBase?

select * from Sys.Objects where Type='s'

 

55) How we can generate Random number in SQL Server?

Select NEWID()

 

56) How We can Get List of Store Procedures?

select * from Sys.Objects where Type='p'

 

57) How we can Get List of Triggers?

select * from Sys.Objects where Type='tr'

 

58) Query to Select n Rendom Records From a Table in the Database?

SELECT TOP 3 * FROM Tbl_REGISTRATION ORDER By NEWID()

 

59) Query to Get List of Views?

select * from information_schema.views

 

61) What is the name of store procedure to send Email using SQL Server 2005/2008?

sp_send_dbmail in msdb database can be used to send email using SQL Server 2005/2008