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

 

 

SQL Server Basics

DBMS vs RDBMS

 

RDBMS = DBMS + Referential Integrity

 

SQLServer 2000 vs SQLServer 2005

 

-       In SQL Server 2000 the Query Analyzer and Enterprise Manager are seperate,whereas in SQL Server 2005 both were combined as Management Studio.

-       In Sql 2005,the new datatype XML is used,whereas in Sql 2000 there is no such datatype

-       The most significant change is the .NET integration with SQL SERVER 2005. Stored procedures, user-defined functions, triggers, aggregates, and user-defined types can now be written using your own favorite .NET language (VB.NET, C#, J# etc.). This support was not there in SQL SERVER 2000 where the only language was T-SQL. In SQL 2005, you have support for two languages T-SQL and .NET.

-       SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000. It was a separate installation for SQL Server 2000

 

Normalization

 

Normalization is the process of efficiently organizing data in a database.

 

First Normal Form (1NF) - Eliminate duplicative columns from the same table.

Second Normal Form (2NF) - Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

Third Normal Form (3NF) - Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF) - A relation is in 4NF if it has no multi-valued dependencies.

 

 

 

All about Stored Procedures

Example

 

CREATE PROCEDURE PROC_NAME

(

          @MYNAME VARCHAR(10),

          @YOURNAME VARCHAR(10),

          @RESULT1 VARCHAR(10) OUTPUT,

          @RESULT2 VARCHAR(10) OUTPUT

)

AS

BEGIN

SET NOCOUNT ON;   

         INSERT INTO TABLE_NAME(MYNAME,YOURNAME) VALUES(@MYNAME,@YOURNAME);

           SET @RESULT1 = @@IDENTITY

          SET @RESULT2=SCOPE_IDENTITY()

END

 

DECLARE @RESULT1 INT

DELCARE @RESULT2 INT

EXEC PROC_NAME ‘ST1’,’ST2’,@RESULT1 OUTPUT,@RESULT2 OUTPUT

Code Access Security

.NET has two kinds of security:

1. Role Based Security 2. Code Access Security

CLR allows code to perform only those operations that the code has permission to perform. So CAS is the CLR's security system that enforces security policies by preventing unauthorized access to protected resources and operations. Using the Code Access Security, you can do the following:

• Restrict what your code can do
• Restrict which code can call your code
• Identify code

We can create code groups to meet our requirements based on the evidence provided by .NET Framework. Site, Strong Name, Zone, URL are some of the types of evidence.

 

 

Yield keyword in C#

Yield Keyword in .net

Yield keyword is used to return a value in between the iteration of a loop.

Ex:-

public static IEnumerable<int> Square(int min, int max)

{

    for (int i = min; i <= max; i++)

    {

        yield return i*i;

    }

}

Now each time this method is called it will return the square of current value within a given range and it’s also maintains the state between calls.

foreach (int i in Square(1, 10))

{

    Response.Write(i.ToString() + " ");

}

output will be like 1,4,9,16,25,36,49,64,81,100.

 

 

Few ADO.NET points for reference

FieldCount : is used to get number of colums in a row by using sqldatareader

IsClosed is used to find whether the sqldatareader is closed or not.

Good Example of stored procedure

//Create the Connection Object
SqlConnection ConnectionObject = new SqlConnection(ConnectionString);
//Create the Command Object
SqlCommand CommandObject = new SqlCommand("StoredProcedureName", ConnectionObject);
//Specify to CommandObject that you intend to execute a Stored Procedure
CommandObject.CommandType = CommandType.StoredProcedure;
//Create an SQL Parameter object
SqlParameter ParameterObject = new SqlParameter();
//Specify the name of the SQL Parameter
ParameterObject.ParameterName = "Parameter1";
//Assign the Parameter value
ParameterObject.Value = "Some Value";
//Specify the Database DataType of the Parameter
ParameterObject.DbType = DbType.String;
//Specify the type of parameter - input-only(default), output-only, bidirectional
ParameterObject.Direction = ParameterDirection.Input;
//Associate the Parameter to the Command Object
CommandObject.Parameters.Add(ParameterObject);
//Open the connection
ConnectionObject.Open();
//Execute the command
int Records_Affected = CommandObject.ExecuteNonQuery();
//Close the Connection
ConnectionObject.Close();


Executing and getting data from two queries using datareader

- By using NextResult() method
- Example

//Create the SQL Query with 2 Select statements
string SQLQuery = "Select * from Customers;Select * from Employees;";
//Create the Connection Object
SqlConnection ConnectionObject = new SqlConnection(ConnectionString);
//Create the Command Object
SqlCommand CommandObject = new SqlCommand(SQLQuery, ConnectionObject);
//Open the connection
ConnectionObject.Open();
//Execute the command. Now reader object will have 2 tables of data.
SqlDataReader ReaderObject = CommandObject.ExecuteReader();
//Loop thru the tables in the DataReader object
while (ReaderObject.NextResult())
{
while (ReaderObject.Read())
{
//Do Something
}
}
//Close the Reader
ReaderObject.Close();
//Close the Connection
ConnectionObject.Close();


Uses of using Stored procedures

1) Better Performance - as they are precompiled one's
2) Security - we can specify who has the rights to execute
3) Reduce Network traffic - Just we need to specify the name of SP instead of sending a big sql query

Differences between Datareader and Dataset

DataReader
1. DatReader works on a Connection oriented architecture.
2. DataReader is read only, forward only. It reads one record at atime. After DataReader finishes reading the current record, it moves to the next record. There is no way you can go back to the previous record. So using a DataReader you read in forward direction only.
3. Updations are not possible with DataReader.
4. As DataReader is read only, forward only it is much faster than a DataSet.
DataSet
1. DataSet works on disconnected architecture.
2. Using a DataSet you can move in both directions. DataSet is bi directional.
3. Database can be updated from a DataSet.
4. DataSet is slower than DataReader.

Access modifiers in C#

Access Modifiers
=================

Public
can be accessed by any other code in the same assembly or another assembly that references it.

Private
can only be accessed by code in the same class or struct.

Protected
can only be accessed by code in the same class or struct, or in a derived class.

Internal
can be accessed by any code in the same assembly, but not from another assembly.

Protected Internal
can be accessed by any code in the same assembly, or by any derived class in another assembly.

Some points

1) structs members cannot be declared as protected because they doesn't support inheritance.
2) destructors cannot have access modifiers.
3) internal is the default access modifier for class, struct, interface.
4) Interface members are always public.
5) Enumeration members are always public.