Sunday, April 7, 2013

Questionnaire - SQL Server

We go through many articles. Sometime we come to know many very useful aspects ,terms of the technology. But after some time, due to lack or revision, practice or use of the same time we do forgot the things.

Sometime we found many interesting points but could not remember for a long time. I kept many of them with me somewhere in trunk. Today as I started to clean my trunk I found Wow .. this was the thing I have been already noted but now It has gone from my memory or dump somewhere in the corner. Other things got overlapped.

Technology has been changed day by day ,year by year. But the concept and the core is more or less the same. In the era of the playing audio/video using "Pen Drive" it will always be an excited moment to know about the of cassettes.


So here today I started to prepare a questionnaire and to keep it online. This will never be completed. Some of the questions will not have the answers and some are need to improve. But today I am starting with some questions and I believe if I got your help It could convert into a library :)
I crossed my finger and begin to write from the point which I am saying as start ..


  1. What is the limit of traditional character datatypes like "Char" , "Varchar", "Nchar" and "Nvarchar"?

    Ans :-These traditional character datatypes are limited to 8000 characters if you need to store more characters then you need to use MAX. e.g Varchar(MAX) will store the data upto 2 GB.

  2. What is the size limit of XML datatype column ?

    Ans : - 2GB

  3. What is the size limit of varchar - VARCHAR(MAX) ?

    Ans : - 2GB

  4. What is "shredding" of data ?

    Ans :-"Shredding" means to break XML document in smaller pieces ( elements, attributes etc) so that the queries and partial updates can be effectively evaluated.


  5. What is the use of apply operator ?

    Ans :- This is new feature in SQL Server 2005 and later versions. It is used in the "FROM" clause of the query and it allows you to call a function returning table for each row in your outer table. For further details and example click here.


  6. What is TOP keyword ?

    Ans :- Top is an operator used to restrict the number of rows returned as number or percentage in select , update, delete and insert operations.

    Before SQL Server 2005 it was used for "Select" statement only. This enhancement feature replaces SET ROW COUNT which had performance issue.

    e.g.
    Select Top 10 * from dbo.Employee -- <-- Only top 10 rows will return from the result set
    Select Top 10 Percent from dbo.Employee -- <-- This query will return 10 percent out of total result set


  7. In Sql Server 2005 onwards Is text type is replaced by Varchar(Max)

    Ans:- Its our intension, You we can replace with MAX if we want. Microsoft recommends to replace the TEXT datatype with Varchar(MAX).


  8. Does varchar(max) use the full-text index option?

    Ans:- Yes, we can use full-text index on max datatype


  9. If I were to write SP in .NET language, will it have same performance as the one writen in T-SQL?

    Ans : It depends on the logic and the type of operations. There will be a whitepaper at RTM that describes various scenarios. One example where the .NET language SP will have better performance is computation intensive logic



  10. Are procedures created in CLR languages translated into TSQL?

    Ans:-   Procedures created in CLR languages use the Ado.Net API to execute batches of TSQL. The rest of the procedural code (non-Ado.net) is native CLR code.


  11. If I were to write SP in .NET language, will it have same performance as the one writen in T-SQL? 

    Ans:-  It depends on the logic and the type of operations. There will be a whitepaper at RTM that describes various scenarios. One example where the .NET language SP will have better performance is computation intensive logic .


  12. Can a varchar(max) column be indexed or, what is the index length limit for SQL 2005 and above? 

    Ans:-  vc(max) cannot be indexed as a key column. The key size limit in SQL Server 2005 is still 900 bytes. There is a new feature called "included columns in indexes" which allows vc(max) columns to be part of the index, though not part of the index key.


  13. How can we handle an exception in sql server ?

    Ans:- we can use TRY .. CATCH block to handle the exceptions. Also you can rethrow the exception using RAISERROR statement. Click here to know more details.


  14. What is event notifications and how it is different from DDL trigger ?

    Ans:- Event notifications enqueue a message in a service broker queue, that can be used to do processing based on the notification in an asynchronous way.

    DDL triggers executes synchronously.


  15. Do CTEs replace #Temp tables?

    Ans:-  No, CTE is a logical construct to write complex SELECT statements in a simplified manner and to perform recursive queries for example.


  16. What is the Max page size in SQL Server ?

    Ans:- 8K



  17. DDL Triggers comes with "After" "Before" and "Instead of" ?

    Ans:- No, this is always after.



  18. What will be the output of following

    Declare @name Varchar(20)
    SET @name=null

    Select Len(@name) ,Length(@name)


    Ans:- NULL.



Keep refreshing to know what's coming up next ....



Post a Comment