SQL SERVER :Question & Answer Part 1

1)What are the System database in Sql Server ?
   a)master
   b)model
   c) msdb
   d)tempdb

2)What are the purpose of System Databases?

   a)master :  The master database records all the system-level information for a SQL Server system.It store master is the database that records the existence of all other databases and the location of those database files and records the initialisation information for SQL Server.

   b)model :The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system

 c)msdb:Yes it does. It holds all Job and Maintenance plans and any DTS packages that were stored on the server too. If you need this data then restore from your backup 

 d)tempdb
* Temporary tables, table variables, and table-valued functions
* Query, Cursors, Triggers
* Snapshot isolation and read committed snapshot
*User-defined functions

 3)How to view contents of a store procedure, function by sql

   sp_helptext *your function or procedure name*

4) How to return XML format data from Sql Server ?

Just add the string  "FOR XML AUTO" , at the last of sql query, for example say , a table name tbl_student.
I want XML format data from that table .
Select Query is : select * from tbl_student
Select Query for XML is :select * from tbl_student  FOR XML AUTO

Output is like
<tbl_student student_id="1" name="John,Krow" year="2009...


5) How to return XML format data from Sql Server in Binary format ?

follow the question number 4 and then just add "BINARY BASE64" at the end of statement
for example 
      select * from tbl_student  FOR XML  RAW, BINARY BASE64


Output is like
<tbl_student student_id="1" name="John,Krow" year="2009...
 This is binary base 64 format

6)What is the difference between ISNULL & COALESCE  in sql server

ISNULL & COALESCE  are both used to check is value is null or not .but
generally engineer preferred ISNULL.because there are several reason.
The main difference between  ISNULL & COALESCE  are

a)ISNULL takes only two arguments , but COALESCE takes unlimited arguments
      declare @s varchar(5)
      set @s=null
      select ISNULL( @s,'hello ! this is sql blog')
      select COALESCE ( @s,'hello ! this is sql blog','we will discuss some tuff questions','let ,hope for the     best')





Out put will be
-----------------------------------
hello
----------------------------------
hello ! this is sql blog

Notice variable length is only 5,ISNULL return only 5 digit , but COALESCE will return whole string


7)Why stored procedure is faster than query ?

Stored procedures are precompiled and optimised, which means that the query engine can execute them more rapidly. By contrast, queries in code must be parsed, compiled, and optimised at run time. This all costs time.
More over , the execution plan for pre-defined  and stored


8) What is File Group Concept in Sql Server ?

SQL Server databases have three types of files:

a) Primary data files 

         Extension: .mdf .Every database have at least one Primary Data Files.For student database the files

         may be student.mdf .Data  are mainly stored here

b) Log files
       Extension: .ldf . Log files hold all the log information of the database.Help to recover data.

Now comes the Files Group
       Filegroups can be created when the database is first created or created later when more files are added to the database. However, you cannot move files to a different filegroup after the files have been added to the database

CREATE DATABASE student_data
ON PRIMARY
(NAME = student, FILENAME = 'C:\SQL\student.mdf'),
FILEGROUP [studentHist]
(NAME = student_Hist1, FILENAME = 'D:\SQL\student1.ndf'),
(NAME = student_Hist2, FILENAME = 'D:\SQL\student2.ndf')
LOG ON
(NAME = student_Log, FILENAME = 'E:\SQL\studentLog.ldf')
 
GO

Notice that first is a .mdf file which is primary data files.Last is .ldf file, which is a log files.
.ndf files , can be added more while creating and altering database



9) Compare  TRUNCATE & DELETE

Both truncate & delete command  remove rows from a table.
For Examples

  delete from tbl_student

 TRUNCATE TABLE tbl_student 

Both will remove all rows from tables . But what are the different ?
a) Truncate are more faster than delete
b) Deleted data can be recover later but not truncated
c) Trigger does not fired in TRUNCATE , but in Delete it Fire
4) Truncate is an auto commit transaction; therefore as soon as this is executed the database is commuted.

What is the reason that    TRUNCATE is faster ?
While delete : data are copied in log , update data dictionary
While truncate : data are not copied in log , direct remove all.

That is the way ,   TRUNCATE is faster that Delete
That is the same reason Deleted data can be recovered  , but truncated data can not be recovered



10) What is way of exception handling in Sql Server

Sql Server have similar type of exception handling like C#, Java,C++,C etd.
Two block or region is there Try Block & Catch Block
All DML query written under the TRY Block  & if exception occurred, then it is
handle in CATCH

For Example :

BEGIN TRY

     SELECT * FROM tbl_student 

END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

another way is raiserror...that we will discuss later

11) What is database attachment & detachment ?

Database has mainly two kind of file 1)*.mdf --data file 2)*.ldf-logfile
At any stage admin can detach and attach database from Server.
When detached , database will no longer available

Suppose you have a database named "Student" .You detached database from sql server ,the data and the log file will remain same but database will remain unavailable.After detaching the file is now on your hand, now you can again attached/restored data in any Sql server or same server

a)How to detach ?

    There are two ways.First you can go to Microsoft SQL Server Management Studio ,Select Database

,right click -->>All Task -->>Detach Database

    The easiest way is by writing  Sql
   

   EXEC sp_detach_db 'database name ', 'true'

you will get file both file *.mdf & *.ldf

b)How to attach?

    There are two ways.First you can go to Microsoft SQL Server Management Studio ,Select Database

,right click -->>All Task -->>attach Database--select the metadata file (*..mdf)

    The easiest way is by writing  Sql
   

  EXEC sp_attach_db

      @dbname = N'database name',

      @filename1 = N'D:\DataFiles\database file name.mdf',

      @filename2 = N'E:\LogFiles\database file name.ldf';

It have been observed, that log file (*.ldf)

12) What is the difference Between Getdate and Sysdatetime

Getdate the precision till millisecond
Sysdatetime the precision nanosecond

13)STUFF And Replace

Stuff :stuff function is use to overwrite existing character
Replace:use to replace all the repetition

Select Stuff('This is my blog',start,lemgth, Replacement Character)

14) What are the magic table in Sql Server
Some times data are inserted /deleted/updated through trigger.When a insert/update/Delete is fired , two table are created

inserted table for inserted values
deleted table for deleted values
when update : both tables work

15) Difference between char/varchar/nvarchar

char : fixed length , 1-8000 character , non unicode character
varchar : variable length ,1-8000, non unicode,storage size is actual length

nvarchar : variable length ,1-4000, unicode,storage size is actual length/2

16)What is the default port number for SQL Server?
TCP port 1433.

17)Difference between a primary key and a unique key?
 Both  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.


 18) What is the maximum size of a row ?
8060 bytes

19)What is the maximum number of input & output parameter of SQL Server 2000
1024

20)What is the maximum limit of a Primary Key ?
900 bytes

21) What is the maximum number of column in Sql Server ?
1024

22)Column can not be index on this column
text,ntext,image

25) What is an execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server .execution plan is for both stored procedure and function

26) Two table not exists and not exists

SELECT col1, col2 FROM Table2
  WHERE NOT EXISTS (
    SELECT 1 FROM Table1 
    WHERE Table1.Col1 = Table2.Col1
  )

SELECT col1, col2 FROM Table2
  WHERE EXISTS (
    SELECT 1 FROM Table1 
    WHERE Table1.Col1 = Table2.Col1
  )

27) Database Lock
Database lock is a command name .Some times user update data when some other user query the same data.Which will result an non efficient output.

there are two type of lock

1) Shareed Lock
     Shared lock applied that data can be perform only read operation only.Can not write.
2) Exclusive Lock
     Exclusive lock applied that data can be perform  read and write operations.


 28)What are the disadvantages of VIEW 

  1)When the table is dropped view becomes irrlevant
  2)As view are created when a query requesting data from view is triggered, its bit slow.
  3)When views are created for large tables, it occupy more memory


29) How many types of joining are there in sql server

1. Inner join
2. Outer join
3. Left outer join
4. Right outer join
5. Cross join
6. Self join

30) How to get GUID in sql server ?
select newid().

31) ROW_NUMBER()
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition


SELECT  [ROW_NUM]= ROW_NUMBER()
OVER ( order by student_id desc ),* from tbl_student


32) Some Important Queries
       Return System Tables :
       Select * from Sys.Objects where Type='s'
   
       Return User Tables :
       Select * from Sys.Objects where Type='u'

       List of stored  procedures
       Select * from Sys.Objects where Type='p'

      List of function(Sclar value)
       Select * from Sys.Objects where Type='fn'
      
      List of function(Table Value)
      Select * from Sys.Objects where Type='tf'

      List of Trigger
      Select * from Sys.Objects where Type='tr'

      Table Defination (Alt+F1)
       Select column_name, data_type, character_maximum_length from information_schema.columns
       where table_name =[Table Name]

32) Use of Set NOCOUNT ON
      By Default When we execute any command it return us the number of record affected. if we don't want to return the number of records affected then we can use Set NOCOUNT ON

33)Sql Profiler
34)Diffence between stored procedure & functions

 a)Procedure can return zero or n values whereas function can return one value which is   mandatory.
b)We can go for transaction management in procedure whereas we can't go in function.
c)Functions can be called from procedure whereas procedures cannot be called from function.
d)Functions can have only input parameters for it whereas Procedures can have input/output
parameters .
e)Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.


35)Print statement under function
No

36)PIVOT And UMPIVOT

37)Candidate Key

Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

38)Difference between Version of Sql Server

 Microsoft SQL Server 2008  released in August 2008, is the successor to SQL Server 2005.
Enhancements in SQL Server 2008 are:

• New data type FILESTREAM is introduced in SQL Server 2008.Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system.
•  Full-text search functionality is newly added in SQL Server 2008.This simplifies management and improves performance
• Flat Earth and Round Earth are newly introduced data types to represent Geometric and Geographic data.
• SQL Server includes better compression features, which also helps in improving scalability.
• SQL Server 2008 supports the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be built around the Entity Data Model.

Microsoft  SQL Server 2008 R2 or Microsoft  SQL Server 2010  was released on April 2010. New features in SQL Server 2008 R2 are :

• Master data management or Master Data Services : Its a central management of master data entities and hierarchies.
• Multi Server Management, a centralized console to manage multiple SQL Server 2008 instances and services including relational databases, Reporting Services, Analysis Services & Integration Services.
• SQL Server 2008 R2 includes a number of new services, including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, Report Builder 3.0, Reporting Services Add-in for SharePoint, a Data-tier function in Visual Studio that enables packaging of tiered databases as part of an application, and a SQL Server Utility named UC (Utility Control Point), part of AMSM (Application and Multi-Server Management) that is used to manage multiple SQL Servers.

39)What is CTE?
The CTE was introduced into standard SQL in order to simplify various classes of  SQL Queries for which a derived table just wasn't suitable.

A Common Table Expression contains three core parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword) 

Recursive Query Evaluation

1. Evaluate the non-recursive term. For UNION (but not UNION ALL), discard duplicate rows. Include all remaining rows in the result of the recursive query, and also place them in a temporary working table.
   
2. So long as the working table is not empty, repeat these steps:
   
   1. Evaluate the recursive term, substituting the current contents of the working table for the recursive self-reference. For UNION (but not UNION ALL), discard duplicate rows and rows that duplicate any previous result row. Include all remaining rows in the result of the recursive query, and also place them in a temporary intermediate table.
      
   2. Replace the contents of the working table with the contents of the intermediate table, then empty the intermediate table.
      

40)What is Scope identity operator ? What is the difference between Scope identity and identity ?
@@IDENTITY: givess the last identity value generated on your SQL connection (SPID) or session .

SCOPE_IDENTITY():Returns the last IDENTITY value inserted into an IDENTITY column in the same scop.here scope refer  stored procedure, trigger and other sql statement

IDENT_CURRENT(): returns the last identity value for a specific table

41)DBCC Reindex


42)Difference Between Implict Transaction And Explict Transaction

43)@@TRANCOUNT in SQL Server?
 Returns the number of BEGIN TRANSACTION statements that have occurred on the current   connection.

PRINT @@TRANCOUNT
--  The BEGIN TRAN statement will increment the
--  transaction count by 1.
BEGIN TRAN
    PRINT @@TRANCOUNT
    BEGIN TRAN
        PRINT @@TRANCOUNT
--  The COMMIT statement will decrement the transaction count by 1.
    COMMIT
    PRINT @@TRANCOUNT
COMMIT
PRINT @@TRANCOUNT
--Results
--0
--1
--2
--1
--0

44)What is the maximum limit for Primary Key?
10 fields in MS Acces
900 Bytes in SQL Server

45)What is an Index?

When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

46)What is a deadlock?
47)What is a tuple?

48)What are three SQL keywords used to change or set someone’s permissions?
49)WITH ENCRYPTION
50)Use of Bulk Copying ?

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)How we can implement Row lock explicitly in SQL Server?
53)What is referential integrity in SQL Server ?

54)What will you do if you need to create clustered index on more than one field? Is it possible?
55)Clustered Index vs NonClustered Index.

Clustered Index :
A Clustered index determines the physical order of data in a table and is particularly efficient on columns that are often searched for ranges of values.
The leaf nodes of a clustered index contain the data pages.
There can be only one clustered index per table.
"primary key" is the ideal column for a clustered index
Clustered indexes are good for range searches.

Nonclustered Index :
Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
There can be multiple non-clustered indexes per table.
"Unique Key" is the ideal column for a nonclustered index
Non-clustered indexes are good for random searches.

56)What is a Query Execution Plan?
57)What is the default port number for SQL Server?
Ans:1433