Interview Questions SQL Server

SQL Server

1. Index Type in SQL server

• Clustered 
• 1 Clustered Index is allowed per table
• Data in the table physically get arranged
• Index added on column that are searched more
• Leaf node of clustered index contains data pages
• Non Clustered
• Upto 249 per table
• Create a separate list of key values with pointer to the location of data
• Logical order of the index does not match the physical order of row
• Leaf Node does not contain data pages, instead it contains index rows

2. Difference between primary key and unique key.

• Primary key cannot have null value, where as unique key can have null value.

3. How to find which index is applied on table

• sp_helpundex table_name

4. Difference between truncate and delete

• Delete keep the lock on the row, where as truncate keep the lock on table not on the row.
• In truncate we cannot not roll back, where as in delete we can roll back

5. How to handle Multi Transaction SQL

    Begin Transaction One

    Begin Try

         Delete from tblUSPSMShipment

         

    End Try

   Begin Catch

        If @@Trancount > 0

           RollBack Transaction One

      Print 'Catch'

   End Catch

   If @@TranCount>0

           Commit Transaction One

           Print 'success'

           

           

   Begin Transaction Two

    Begin Try

         Delete from tblUSPSMShipper

         

    End Try

   Begin Catch

        If @@Trancount > 0

           RollBack Transaction Two

      Print 'Catch'

   End Catch

   If @@TranCount>0

           Commit Transaction Two

           Print 'success'

6 Error Handling in SQL Server

   The two most common mechanisms for error handling in SQL Server 2005 are:

• @@ERROR
• TRY-CATCH Block

7. Data Reader Vs DataSet

SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB");

con.Open();

SqlCommand cmd = new SqlCommand("select UserName,LastName,Location from UserInformation", con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

gvUserInfo.DataSource = ds;

gvUserInfo.DataBind();

using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))

{

con.Open();

SqlCommand cmd = new SqlCommand("Select UserName,LastName,Location FROM UserInformation", con);

SqlDataReader dr = cmd.ExecuteReader();

gvUserInfo.DataSource = dr;

gvUserInfo.DataBind();

• The ExecuteNonQuery() method executes a Transact-SQL statement against the connection and returns the number of rows affected.
• The ExecuteScalar() method returns a single value from a database query.
• The ExecuteReader() method returns a result set by using the DataReader object.

8. How can we achieve  Many to Many relationship

Yes, with the help of a 3rd table(junction table) where we will include primary keys from both table between which Many to Many relationship has to be maintained to from Composite key

9. Get a null and make it blank in sql server joiin