SQL KIDS

What is .TUF file?

2013-11-15T01:08:00.002+05:30

What is .TUF file? What is the significance of the same? Any implications if the file is deleted?

.TUF file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.
When the database is in Standby mode the database recovery is done when the log is restored; and this mode also creates a file on destination server with .TUF extension which is the transaction undo file.

This file contains information on all the modifications performed at the time backup is taken.

The file plays a important role in Standby mode… the reason being very obvious while restoring the log backup all uncommited transactions are recorded to the undo file with only commited transactions written to disk which enables the users to read the database. So when we restore next transaction log backup; SQL server will fetch all the uncommited transactions from undo file and check with the new transaction log backup whether commited or not.

If found to be commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.

Query Plan (DMV)

2013-08-08T17:27:00.002+05:30

Here is a query that you can run to get the Total Elapsed time of queries and the number of times those queries were hit. It also give's you the query and the plan used for each of those.

select qs.execution_count
     , qs.total_elapsed_time, qs.last_elapsed_time
     , qs.min_elapsed_time, qs.max_elapsed_time
     , substring(st.text, (qs.statement_start_offset/2)+1
                        , ((case qs.statement_end_offset
                              when -1 then datalength(st.text)
                              else qs.statement_end_offset
                           end - qs.statement_start_offset)/2) + 1) as statement_text
      , qp.query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp

Possible Bad NC Indexes

2013-03-05T16:39:00.001+05:30

-- Possible Bad NC Indexes (writes > reads)

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name]

,i.name AS [Index Name]
,i.index_id
,user_updates AS [Total Writes]
,user_seeks + user_scans + user_lookups AS [Total Reads]
,user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC
, [Total Writes] DESC
, [Total Reads] ASC ;

Unused indexes

2013-03-05T16:26:00.003+05:30

-- List unused indexes

SELECT OBJECT_NAME(i.[object_id]) AS [Table Name]

,i.name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN ( SELECT s.index_id
           FROM sys.dm_db_index_usage_stats AS s
           WHERE s.[object_id] = i.[object_id]
           AND i.index_id = s.index_id
           AND database_id = DB_ID() )
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;

Index Read/Write stats

2013-03-05T16:19:00.001+05:30

--- Index Read/Write stats (all tables in current DB)

SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName]

,i.name AS [IndexName]
,i.index_id
,user_seeks + user_scans + user_lookups AS [Reads]
,user_updates AS [Writes]
,i.type_desc AS [IndexType]
,i.fill_factor AS [FillFactor]
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
ORDER BY OBJECT_NAME(s.[object_id]) , writes DESC ,reads DESC ;

Missing Indexes in current database

2013-03-05T16:10:00.000+05:30

-- Missing Indexes in current database by Index Advantage

SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )

AS [index_advantage]
    ,migs.last_user_seek
    ,mid.[statement] AS [Database.Schema.Table] ,mid.equality_columns
    ,mid.inequality_columns
    ,mid.included_columns
    ,migs.unique_compiles
    ,migs.user_seeks
    ,migs.avg_total_user_cost
    ,migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK ) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC ;

Sql Server All Database Used Space ,Size Info..... Using MSForeachdb

2012-10-30T13:18:00.001+05:30

Sql Server All Database Used Space ,Size Info.... Using MSForeachdb

EXEC sp_MSforeachdb 'USE [?]SELECT

A.FILEID,
[FILE_SIZE_MB] =
CONVERT(DECIMAL(12,2),ROUND(A.size/128.000,2)),
[SPACE_USED_MB] =
CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(A.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
CONVERT(DECIMAL(12,2),ROUND((A.SIZE-FILEPROPERTY(A.name,''SpaceUsed''))/128.000,2)) ,
NAME = left(A.NAME,15),
FILENAME =RIGHT(A.FILENAME,CHARINDEX(''\'', REVERSE(A.FILENAME))-1)
FROM
dbo.SYSFILES A'

Configuring SQL Server

2012-02-20T17:10:00.001+05:30

Configuring Network Protocols from SQL Server configuration manager

SQL Server 2008 is a client-server application designed to efficiently exchange data and instructions over one or more network connections.

SQL Server 2008 Network Protocols

SQL Server 2008 provides support for four protocols:

· Shared Memory

· TCP/IP

· Named Pipes

· Virtual Interface Adapter (VIA)

By default, the only network protocols enabled for most editions of SQL Server are TCP/IP and Shared Memory. The Developer and Enterprise Evaluation editions are configured with all protocols except Shared Memory disabled during installation, but the remaining protocols can be enabled if required. If a protocol is not enabled, SQL Server will not listen on an endpoint that is configured to utilize that protocol.

The SQL Server Configuration Manager is used to configure server protocols.

Shared Memory

The Shared Memory protocol can only be used by local connections, because it is a shared memory and process space used for inter-server communication. It has only one configurable property: Enabled. The Enabled property can be set to Yes or No, resulting in a status of Enabled or Disabled.

Named Pipes

Named Pipes uses Inter-Process Communication (IPC) channels for efficient inter-server communication, as well as local area network (LAN) communication. The Named Pipes protocol has some enhancements in SQL Server 2008 including support for encrypted traffic, but because of the excessive overhead of Named Pipes when connecting across networks or firewalls, and the additional port that Named Pipes requires to be opened (445), it is generally a good idea to leave the Named Pipes protocol disabled. However, there are many applications that take advantage of the Named Pipes protocol because they were designed for local network implementations. Named Pipes provides easy access to Remote Procedure Calls (RPC) within a single security domain, and so is advantageous to these applications. If you need to support one of these applications, and the SQL Server is not exposed to external traffic, the risk of enabling the Named Pipes protocol and corresponding endpoint is minimal.

Named Pipes has two configurable properties: Enabled and Pipe Name. The Enabled property works the same as the Shared Memory protocol. The Pipe Name specifies the inter-process pipe that SQL Server will listen on. The default pipe is \\.\pipe\sql\query.

TCP/IP

The TCP/IP protocol is the primary and preferred protocol for most SQL Server installations. It is configured on two separate tabs on the TCP/IP Properties window: the Protocol tab and the IP Addresses tab, as shown

The Protocol tab has the following four configurable properties:

· Enabled — This works the same as the other protocols.

· Keep Alive — This specifies how many milliseconds SQL Server waits to verify an idle connection is still valid by sending a KEEPALIVE packet. The default is 30,000 milliseconds.

· Listen All — This specifies whether SQL Server will listen on all IP addresses configured on the server.

· No Delay — This option specifies whether the TCP protocol queues small packets to send out larger packets. This queuing is typically undesirable in transaction-based systems, and so it should be left in its default configuration of No.

Surface Area Configuration for services and Connections.

There are two ways to view the various services and connections on a given server. The first is by instance, and this is usually the easiest method.

However, if you have multiple components on the same server, such as multiple instances of the database engine, it may be easier to group by component, which is what the second tab (View by Component) is for:

Once you've settled on your view, the next step is to actually configure each component. Every component listed will have a Service option where you can configure the startup status for the service as well as control the current state of the service itself. For instance, the following image is of the MSSQLSERVER database engine service, which is set to Automatic, meaning it will start when the computer starts up.

You have the ability to set any of the following startup types:

· Automatic - The service will start when the computer starts up.

· Manual - The service is capable of being started, but must be started manually by an authorized user (Power Users or Administrators local group membership).

· Disabled - The service cannot be started unless the startup type is changed.

In addition to the service configurations, the Database Engine and Analysis Services have additional options. For the database engine, there is the Remote Connections configuration.

As the highlighted section shows, you can configure the database engine either to listen only for local connections (originating from the same computer as SQL Server) or to listen for both local and remote connections. In addition, if you choose for SQL Server to listen for remote connections as well, you'll have the choice whether to use TCP/IP, Named Pipes, or both. In general, for network connections, you'll want to use TCP/IP. In my experience, Named Pipes sometimes suffers from timeout issues that you don't get with TCP/IP.

The Dedicated Administrator Connection

In SQL Server 2005, Microsoft introduced a new feature called Dedicated Administrator Connection (DAC). Using this feature a SQL Server Database Administrator can connect to a SQL Server Instance when the database engine is not responding to regular connections. During such a scenario a DBA can connect to the SQL Server Instance to troubleshoot and to kill any of the SQL Server Processes which are causing the issues.

The DAC allows database administrators to connect to a SQL Server Instance and to execute T-SQL commands to troubleshoot and fix issues rather than rebooting the SQL Server which could lead to database corruption or other problems. By default, the remote Dedicated Administrator Connection feature is disabled in SQL Server 2005 and later versions. It’s a good practice to enable the DAC feature once the SQL Server 2005 or SQL Server 2008 is installed on every instance as this will help you troubleshoot issues when regular connections are not responding. However, only one dedicated administrator connection is allowed at a time on SQL Server 2005 and later versions.

Enable Dedicated Administrator Connection in SQL Server 2008 Using TSQL

Execute the below T-SQL to enable remote clients to utilize the Dedicated Administrator Connection.

Use master

sp_configure 'show advanced options' , 1

/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/

sp_configure 'remote admin connections', 1

RECONFIGURE

Enable Dedicated Administrator Connection in SQL Server 2008 Using SQL Server 2008 Management Studio

Database Administrators can also enable Dedicated Administrator Connection Feature using SQL Server 2008 Management Studio. This can be done by right clicking the SQL Server Instance and selecting the Facets option from the drop down list as shown in the snippet below.

This will open up View Facets window as shown in the snippet below. Here you need to select Surface Area Configuration facet as highlighted and then select the option as “True” for RemoteDacEnabled.

Finally, click OK to save the configuration changes in the View Facets window.

You can also enable other database engine features like AsHocRemoteQueriesEnabled, ClrIntegrationEnabled, DatabaseMailEnabled, OleAutomationEnabled, ServiceBrokerEndpointActive, SoapEndpointsEnabled, SQLMailEnabled, WebAssistanceEnabled, XPCmdShellEnabled etc when required using the Surface Server Configuration Facet which is available in SQL Server 2008 Management Studio.

Once the Dedicated Administrator Connection is enabled you can connect to SQL Server 2008 using either SQL Server Management Studio or using SQLCMD.

Using DAC with SQL Server Management Studio

You need to specify “ADMIN:” before the SQL Server Instance name when trying to connect to an SQL Server Instance to using DAC feature as shown in the snippet below.

Once you are connected to SQL Server Instance using DAC, then you can execute code such as the code below to check the SQL Server health.

-- Locking Information

SELECT * FROM sys.dm_tran_locks

-- Cache Status

SELECT * FROM sys.dm_os_memory_cache_counters

-- Active Sessions

SELECT * FROM sys.dm_exec_sessions

-- Requests Status

SELECT * FROM sys.dm_exec_requests
GO

Null value is eliminated by an aggregate or other SET operation

2012-02-15T17:54:00.000+05:30

The Problem occurs when an aggregate function(max,sum,avg..) exists on null values. Trying to get rid of the null values may be the solution but in certain cases..you may need the extra data relying on the null values.

So whats the solution?

Append set ANSI_WARNINGS OFF on the beginning of the sql statement.

How to Enable the Xp_CmdShell

2012-02-01T17:08:00.000+05:30

You can enable the Xp_CmdShell using 2 ways, either by executing T-sql statements or from
"Surface Area Configuration Manager". We'll see both of them.

A. Enable Xp_Cmdshell from Management Studio.
For enabling Xp_CmdShell from Management Studio you need to execute following code.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

B. Enable the Xp_CmdShell from "Surface Area Configuration Manager."

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Features.
3. Click on xp_cmdshell and tick on checkbox of "Enable the xp_cmdshell".

Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

2012-01-06T12:33:00.000+05:30

Sql server Collation Problem and Fix

What is collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying

case-sensitivity,
accent marks,
kana character types and
character width.

Case sensitivity
If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.
Accent sensitivity
If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.
Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.
Production server collation error(Log):

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Reproduce this error in local QA environment:

How to exploring collation?

Step 1 : What Collation is SQL installed local server identify below query

SELECT SERVERPROPERTY('COLLATION')

Step 2 : Identify databases level Collation

SELECT NAME,COLLATION_NAME FROM SYS.DATABASES

Step 4 : Show the collation for each column in selected database level

SELECT

C.TABLE_CATALOG AS DATABASE_NAME

,C.TABLE_SCHEMA

,C.TABLE_NAME

,C.COLUMN_NAME

,DATA_TYPE

,SERVERPROPERTY('COLLATION') AS SERVER_COLLATION

,CONVERT(SYSNAME,DATABASEPROPERTYEX(D.NAME,'COLLATION')) AS DATABASE_COLLATION

,C.COLLATION_NAME AS COLUMN_COLLATION

FROM INFORMATION_SCHEMA.COLUMNS C

INNER JOIN SYS.DATABASES D

ON DB_ID(C.TABLE_CATALOG) = DB_ID(D.NAME)

WHERE DATA_TYPE IN ('VARCHAR' ,'CHAR','NVARCHAR','NCHAR','TEXT','NTEXT')

In local server level collation ,database level collation and column level collation are same collation (“SQL_Latin1_General_CP1_CI_AS”)
How to Reproduce local environment?
Step 1:Change the database level collation using the below query

ALTER DATABASE FP_Yeti_QA2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE FP_Yeti_QA2 COLLATE Latin1_General_CI_AI

ALTER DATABASE FP_Yeti_QA2 SET MULTI_USER

Latin1_General_CI_AI This is not sql server collation ,This is Windows level collation existing in production server, That why I changed in local server also same collation.

Step 2 :Change table level collation means all column collation using the below script. ( The script will generate all table column collation querys )

DECLARE @Table_Name NVARCHAR(100)

SET @Table_Name = NULL--- THIS IS THE TableName that you want to change its collation columns

--- if null will set to all tables

DECLARE @TempTable AS TABLE

(

ID INT IDENTITY

,TableName NVARCHAR(100)

,ColumnName NVARCHAR(100)

,TypeName NVARCHAR(100)

,Max_length INT

,Collation_Name NVARCHAR(100)

,EnterDtm DATETIME DEFAULT GETDATE()

)

DECLARE @NewCollation NVARCHAR(100)

SET @NewCollation = 'database_default' --- THIS IS THE COLLATION NAME THAT YOU WANT TO CHANGE

INSERT INTO @TempTable(TableName,ColumnName,TypeName,Max_length,Collation_Name)

SELECT

QUOTENAME(SCHEMA_NAME(tables.schema_id)) + '.' + QUOTENAME(tables.name) AS TableName

,all_columns.name AS ColumnName

,type_name(all_columns.user_type_id)

,all_columns.max_length

,all_columns.collation_name

from sys.all_columns INNER JOIN sys.tables ON

tables.object_id = all_columns.object_id

AND collation_name IS NOT NULL

AND all_columns.collation_name != @NewCollation

WHERE tables.object_id = ISNULL(object_id(@Table_Name),all_columns.object_id)

DECLARE @TableID SMALLINT

SET @TableID = (SELECT MIN(ID) FROM @TempTable)

DECLARE @Query NVARCHAR(1000),@TableName NVARCHAR(100),@ColumnName NVARCHAR(100),@TypeName NVARCHAR(100)

,@Size INT

WHILE @TableID IS NOT NULL

BEGIN

SET @TableName = (SELECT TableName FROM @TempTable WHERE ID = @TableID)

SET @ColumnName = (SELECT QUOTENAME(ColumnName) FROM @TempTable WHERE ID = @TableID)

SET @TypeName = (SELECT TypeName FROM @TempTable WHERE ID = @TableID)

SET @Size = (SELECT Max_length FROM @TempTable WHERE ID = @TableID)

SET @Query='ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @TypeName+ ISNULL ('(' +CAST(@Size AS VARCHAR(200))+')', '') +' COLLATE '+ @NewCollation

PRINT (@Query)

--execute sp_executesql @Query

SET @TableID = (SELECT MIN(ID) FROM @TempTable WHERE ID > @TableID)

END

This is the way to generate script all table in select database level and execute single usermode

Ex:-

ALTER DATABASE FP_Yeti_QA2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Here column level generating scripts

ALTER DATABASE FP_Yeti_QA2 SET MULTI_USER

How to Error Fix:
Step 1:Change the database level collation using the below query

ALTER DATABASE FP_Yeti_QA2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE FP_Yeti_QA2 COLLATE SQL_Latin1_General_CP1_CI_AS

ALTER DATABASE FP_Yeti_QA2 SET MULTI_USER

SQL_Latin1_General_CP1_CI_AS this is sql server default collation all system databases follow the same collation better we should follow user databases also.

Step 2 :Change table level collation means all column collation inherit the database level collation (database_default) using the below script. ( The script will generate all table column collation querys )

DECLARE @Table_Name NVARCHAR(100)

SET @Table_Name = NULL--- THIS IS THE TableName that you want to change its collation columns

--- if null will set to all tables

DECLARE @TempTable AS TABLE

(

ID INT IDENTITY

,TableName NVARCHAR(100)

,ColumnName NVARCHAR(100)

,TypeName NVARCHAR(100)

,Max_length INT

,Collation_Name NVARCHAR(100)

,EnterDtm DATETIME DEFAULT GETDATE()

)

DECLARE @NewCollation NVARCHAR(100)

SET @NewCollation = 'database_default' --- THIS IS THE COLLATION NAME THAT YOU WANT TO CHANGE

INSERT INTO @TempTable(TableName,ColumnName,TypeName,Max_length,Collation_Name)

SELECT

QUOTENAME(SCHEMA_NAME(tables.schema_id)) + '.' + QUOTENAME(tables.name) AS TableName

,all_columns.name AS ColumnName

,type_name(all_columns.user_type_id)

,all_columns.max_length

,all_columns.collation_name

from sys.all_columns INNER JOIN sys.tables ON

tables.object_id = all_columns.object_id

AND collation_name IS NOT NULL

AND all_columns.collation_name != @NewCollation

WHERE tables.object_id = ISNULL(object_id(@Table_Name),all_columns.object_id)

DECLARE @TableID SMALLINT

SET @TableID = (SELECT MIN(ID) FROM @TempTable)

DECLARE @Query NVARCHAR(1000),@TableName NVARCHAR(100),@ColumnName NVARCHAR(100),@TypeName NVARCHAR(100)

,@Size INT

WHILE @TableID IS NOT NULL

BEGIN

SET @TableName = (SELECT TableName FROM @TempTable WHERE ID = @TableID)

SET @ColumnName = (SELECT QUOTENAME(ColumnName) FROM @TempTable WHERE ID = @TableID)

SET @TypeName = (SELECT TypeName FROM @TempTable WHERE ID = @TableID)

SET @Size = (SELECT Max_length FROM @TempTable WHERE ID = @TableID)

SET @Query='ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @TypeName+ ISNULL ('(' +CAST(@Size AS VARCHAR(200))+')', '') +' COLLATE '+ @NewCollation

PRINT (@Query)

--execute sp_executesql @Query

SET @TableID = (SELECT MIN(ID) FROM @TempTable WHERE ID > @TableID)

END

Ex:-

ALTER DATABASE FP_Yeti_QA2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Here column level generating scripts

ALTER DATABASE FP_Yeti_QA2 SET MULTI_USER

Restart the database server.

Msg 1807, Level 16, State 3, Line 1 Could not obtain exclusive lock on database ‘model’. Retry the operation later. Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

2011-12-01T17:51:00.001+05:30

Problem:

Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database ‘model’. Retry the operation later.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Solution:

Option 1:

Disconnect and Reconnect your SQL Server Management Studio’s session. Your error will go away.

Option 2:

Root Cause: Root cause of this error may be one of the following reason

1. Somebody exclusively open the Model database in different session

2. If more than one create database statements attempt to run simultaneously

How to identity:

Use master 
GO
IF EXISTS(SELECT request_session_id  FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model'))
 PRINT 'Model Database being used by some other session'
ELSE
 PRINT 'Model Database not used by other session'

So we can identify that whether the Model database being execlusively used by any session.., If you found anybody using the Model database execlusively, You can identify what kind of statement being used by the session...using the script given below

SELECT request_session_id  FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model')

The script will return the session ID (i.e: 53)

We have to identity what kind of query/activity being performed by this session(53).

DBCC InputBuffer(53)

    EventInfo column returns the query performed, Based on that, you have to decide whether the Session ID(53) can be Aborted/ Not. If you want to abort the session(53),

run the command

    Kill 53

    Now you can try to create a new Database..!

Renaming database and physical database files

2011-11-10T14:50:00.000+05:30

1.Rename a Database using Management Studio:

Renaming Database:

Right Click the Database Name in Object Explorer and Select "Rename", now you can specify new name:

However, this does not change the database file names:

SQL Query: SELECT name, physical_name

FROM [SqlAndMe].sys.database_files

Result Set:

name            physical_name

--------        ----------------

MyDatabase     C:\…\MyDatabase.mdf

MyDatabase_log C:\…\MyDatabase_log.LDF

(2 row(s) affected)

 Renaming Files: 

To change filenames, Right Click on Database in Object Explorer and Select "Properties", Then, go to "Files" Tab:

Here, you can change the logical filenames for DATA and LOG files.

2. Renaming a Database using T-SQL:

Renaming Database:

To Rename a database using T-SQL, use below script:

USE [master]

--Set Database to Single-User Mode

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Rename Database

ALTER DATABASE [MyDatabase] MODIFY Name = [SqlAndMe]

--Set Database to Multi-User Mode

ALTER DATABASE [SqlAndMe] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Result Set:

The database name 'SqlAndMe' has been set.

Renaming Files:

--Rename Logical File Names

ALTER DATABASE [SqlAndMe]

MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')

ALTER DATABASE [SqlAndMe]

MODIFY FILE (NAME=N'MyDatabase_log',NEWNAME=N'SqlAndMe_log')

Result Set:

The file name 'SqlAndMe' has been set.

The file name 'SqlAndMe_log' has been set.

However, none of these two methods can change the physical database file name, you can check this using sys.database_files:

SQL Query:SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

Result Set:

name          physical_name

——---        —————------------

SqlAndMe      C:\…\MyDatabase.mdf

SqlAndMe_log  C:\…\MyDatabase_log.LDF

(2 row(s) affected)

Sometimes we also need to rename the physical database files, this cannot be done via two methods described above.

3. Renaming a Database using detach/attach:

1. Detach database:

You can detach a database either using Management Studio or T-SQL, To
 detach a database using Management Studio, right-click on database >
 go to "Tasks" > "Detach", click OK to detach a database,

To detach a database using T-SQL, you can use master..sp_detach_db procedure:

--Detach Database using sp_detach_db

SQL Query:USE [master]

          ALTER DATABASE [MyDatabase]

          SET SINGLE_USER WITH ROLLBACK IMMEDIATE

        EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'

 2.Rename Physical files:

Once the database is detached, SQL Server releases the locks on 
physical files, now you can rename the physical files using Windows 
Explorer, or command prompt:

3. Attaching database with New Name:
Once, the physical files have been renamed, you can attach the database using new database and file names:

-– Attach Database

SQL Query:USE [master]

          CREATE DATABASE [SqlAndMe] ON

          ( FILENAME = N'C:\…\SqlAndMe.mdf'),

          ( FILENAME = N'C:\…\SqlAndMe_log.LDF')

          FOR ATTACH

--Rename Logical file names

SQL Query: USE [SqlAndMe]

           ALTER DATABASE [SqlAndMe]

         MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')

          ALTER DATABASE [SqlAndMe]

         MODIFY FILE (NAME=N'MyDatabase_log', NEWNAME=N'SqlAndMe_log')

SQL Query: SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

Result Set:

name          physical_name

————  —————–

SqlAndMe      C:\…\SqlAndMe.mdf

SqlAndMe_log  C:\…\SqlAndMe_log.LDF

(2 row(s) affected)

SPACE function will return maximum of 8000 spaces only

2011-11-10T14:16:00.000+05:30

We can use SPACE function to replicate spaces for a string. Consider the following statement

SQL Query: select 'A'+space(10)+'B'
The result is: A B

As you see space function adds 10 spaces between the string A and B. Note that the space function though accepts any positive number, it will return maximum of 8000 spaces only which you can confirm from the following example

SQL Query: select datalength(space(10000))

The above returns 8000. So you should be aware of this. The alternate solution to have more spaces is to use replicate function as shown below

SQL Query: select datalength(replicate(cast(' ' as varchar(max)),10000))

Which returns 10000. Also note that replicate returns maximum 8000 characters so we need to convert the string to varchar(max) datatype to have more chanracters.
Varchar(max) datatype and Replicate function

SQL Query: declare @v varchar(max)

set @v=replicate('a',50000)

select len(@v),datalength(@v)
- Note that the result is not 50000 but 8000 because by default the result is limited to the maximum size of 8000 for varchar/char datatype
```
To get a correct result, you need to convert the expression to the type of varchar(max)
```
SQL Query:declare @v varchar(max)

set @v=replicate(convert(varchar(max),'a'),50000)

select len(@v),datalength(@v)
- Now the result is 50000 as expected.
```
So you need to be aware of this implicit convertion when using Replicate function to assingn value to the column of varchar(max) datat
```

Deterministic Functions and Nondeterministic Functions

2011-11-10T14:02:00.000+05:30

Deterministic functions: always returns the same output result all the time it is executed for same input values.

Exp: ABS, DATEDIFF, ISNULL etc.

Nondeterministic functions: may return different results each time they are executed.

Exp: NEWID, RAND, @@CPU_BUSY etc.

Functions that call extended stored procedures are nondeterministic. User-defined functions that create side effects on the database are not recommended.

Oracle DataTypes

2011-10-18T13:44:00.000+05:30

Data types for Oracle 8 - Oracle 11g + PL/SQL

Datatype	Description	Max Size: Oracle 8	Max Size: Oracle 9i/10g	Max Size: Oracle 11g	Max Size: PL/SQL	PL/SQL Subtypes/ Synonyms
VARCHAR2(size)	Variable length character string having maximum lengthsize bytes. You must specify size	4000 bytes minimum is 1	4000 bytes minimum is 1	4000 bytes minimum is 1	32767 bytes minimum is 1	STRING VARCHAR
NVARCHAR2(size)	Variable length national character set string having maximum length size bytes. You must specify size	4000 bytes minimum is 1	4000 bytes minimum is 1	4000 bytes minimum is 1	32767 bytes minimum is 1	STRING VARCHAR
VARCHAR	Now deprecated (provided for backward compatibility only) VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions.	-	-	-
CHAR(size)	Fixed length character data of length size bytes. This should be used for fixed length data. Such as codes A100, B102...	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	32767 bytes Default and minimum size is 1 byte.	CHARACTER
NCHAR(size)	Fixed length national character set data of length size bytes. This should be used for fixed length data. Such as codes A100, B102...	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	32767 bytes Default and minimum size is 1 byte.
NUMBER(p,s)	Number having precision p and scale s.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.	Magnitude 1E-130 .. 10E125 maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits The scale s can range from -84 to 127. For floating point don't specify p,s REAL has a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits	fixed-point numbers: DEC DECIMAL NUMERIC floating-point: DOUBLE PRECISION FLOAT binary_float (32 bit) binary_double (64 bit) integers: INTEGER INT SMALLINT simple_integer(10g) BOOLEAN REAL
PLS_INTEGER	signed integers PLS_INTEGER values require less storage and provide better performance than NUMBER values. So use PLS_INTEGER where you can!	PL/SQL only	PL/SQL only	PL/SQL only	magnitude range is -2147483647 .. 2147483647
BINARY_INTEGER	signed integers (older slower version of PLS_INTEGER)				magnitude range is -2147483647 .. 2147483647	NATURAL NATURALN POSITIVE POSITIVEN SIGNTYPE
LONG	Character data of variable length (A bigger version the VARCHAR2 datatype)	2 Gigabytes	2 Gigabytes - but now deprecated (provided for backward compatibility only).	2 Gigabytes - but now deprecated (provided for backward compatibility only).	32760 bytes Note this is smalller than the maximum width of a LONG column
DATE	Valid date range	from January 1, 4712 BC to December 31, 9999AD.	from January 1, 4712 BC to December 31, 9999 AD.	from January 1, 4712 BC to December 31, 9999 AD.	from January 1, 4712 BC to December 31,9999 AD. (in Oracle7 = 4712 AD)
TIMESTAMP (fractional_seconds_precision)	the number of digits in the fractional part of the SECOND datetime field.	-	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)
TIMESTAMP (fractional_seconds_precision) WITH {LOCAL} TIMEZONE	As above with time zone displacement value	-	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)
INTERVAL YEAR (year_precision) TO MONTH	Time in years and months, where year_precision is the number of digits in the YEAR datetime field.	-	Accepted values are 0 to 9. (default = 2)	Accepted values are 0 to 9. (default = 2)
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)	Time in days, hours, minutes, and seconds. day_precision is the maximum number of digits in 'DAY' fractional_seconds_precisionis the max number of fractional digits in the SECOND field.	-	day_precision may be 0 to 9. (default = 2) fractional_seconds_precisionmay be 0 to 9. (default = 6)	day_precision may be 0 to 9. (default = 2) fractional_seconds_precisionmay be 0 to 9. (default = 6)
RAW(size)	Raw binary data of length size bytes. You must specify size for a RAW value.	Maximum size is 2000bytes	Maximum size is 2000 bytes	Maximum size is 2000 bytes	32767 bytes
LONG RAW	Raw binary data of variable length. (not intrepreted by PL/SQL)	2 Gigabytes.	2 Gigabytes - but now deprecated (provided for backward compatibility only)	2 Gigabytes - but now deprecated (provided for backward compatibility only)	32760 bytes Note this is smalller than the maximum width of a LONG RAW column
ROWID	Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)	10 bytes	10 bytes	10 bytes	Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)
UROWID	Hex string representing the logical address of a row of an index-organized table	The maximum size and default is 4000 bytes	The maximum size and default is 4000 bytes	The maximum size and default is 4000 bytes	universal rowid - Hex string representing the logical address of a row of an index-organized table, either physical, logical, or foreign (non-Oracle)	SeeCHARTOROWIDand the package:DBMS_ROWID
MLSLABEL	Binary format of an operating system label.This datatype is used with Trusted Oracle7.
CLOB	Character Large Object	4Gigabytes	8 TB	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
NCLOB	National Character Large Object	4Gigabytes	8 TB	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
BLOB	Binary Large Object	4Gigabytes	8 TB	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
BFILE	pointer to binary file on disk	4Gigabytes	8 TB	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
XMLType	XML data	-	4 Gigabytes	4 Gigabytes	Populate with XML from a CLOB or VARCHAR2. or query from another XMLType column.

Notes and Examples

VARCHAR2:
Storing character data as Varchar2 will save space:

Store 'SMITH' not 'SMITH     '

Oracle9i and above allow Varchar2 columns to be defined as a number of bytes VARCHAR2(50 BYTE) or a number of characters VARCHAR2(50 CHAR), the latter is useful if the database is ever converted to run a double-byte character set (such as Japanese), you won't have to edit the column sizes. The default measure, normally BYTE, is set withnls_length_semantics.

CHAR:
Over time, when varchar2 columns are updated they will sometimes create chained rows, CHAR columns are fixed width they are not affected by this, so less DBA effort is required to maintain performance.

PL/SQL
When retrieving data for a NUMBER column, consider using the PL/SQL datatype: PLS_INTEGER for better performance.

LONG
Use BLOB instead of LONG

INTEGER
This ANSI datatype will be accepted by Oracle - it is actually a synonym for NUMBER(38)

The FLOAT datatype
This ANSI datatype will be accepted by Oracle - Very similar to NUMBER it stores zero, positive, and negative floating-point numbers

The NUMBER datatype
Stores zero, positive, and negative numbers, fixed or floating-point numbers

Fixed-point NUMBER
NUMBER(p,s)
precision p = length of the number in digits
scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point.

Integer NUMBER
NUMBER(p)
This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0)

Floating-Point NUMBER
NUMBER
floating-point number with decimal precision 38

Confusingly the Units of measure for PRECISION vary according to the datatype.
For NUMBER data types: precision p = Number of Digits
For FLOAT data types: precision p = Binary Precision (multiply by 0.30103 to convert)

{So FLOAT = FLOAT (126) = 126 x 0.30103 = approx 37.9 digits of precision.}

Example

 The value 7,456,123.89 will display as follows
NUMBER(9)     7456124 
NUMBER(9,1)   7456123.9
NUMBER(*,1)   7456123.9    
NUMBER(9,2)   7456123.89 
NUMBER(6)    [not accepted exceeds precision] 
NUMBER(7,-2)  7456100 
NUMBER        7456123.89 
FLOAT         7456123.89 
FLOAT(12)     7456000.0

Storing Varchar2 Data

For VARCHAR2 variable whose maximum size is less than 2,000 bytes (or for a CHAR variable), PL/SQL allocates enough memory for the maximum size at compile time.

For a VARCHAR2 whose maximum size is 2,000 bytes or more, PL/SQL allocates enough memory to store the actual value at run time. In this way, PL/SQL optimizes smaller VARCHAR2 variables for performance and larger ones for efficient memory use.

For example, if you assign the same 500-byte value to VARCHAR2(1999 BYTE) and VARCHAR2(2000 BYTE) variables, PL/SQL allocates 1999 bytes for the former variable at compile time and 500 bytes for the latter variable at run time.

Storing Numeric Data

Oracle stores all numeric data in variable length format - storage space is therefore dependent on the length of all the individual values stored in the table. Precision and scale settings do not affect storage requirements. DATA_SCALE may appear to be truncating data, but Oracle still stores the exact values as input. DATA_PRECISION can be used to constrain input values.

It is possible to save storage space by having an application truncate a fractional value before inserting into a table, but you have to be very sure the business logic makes sense.

Select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE

From cols Where table_name = 'Your_Table';

A common space-saving trick is storing boolean values as an Oracle CHAR, rather than NUMBER:

Create TABLE my_demo (accountcode NUMBER, postableYN CHAR check (postableYN in (0,1)) );

-- Standard Boolean values: False=0 and True=1

Insert into my_demo values(525, '1');

Insert into my_demo values(526, '0');

Select accountcode, decode(postableYN,1,'True',0,'False') FROM my_demo;

-- or in French:

Select accountcode, decode(postableYN,1,'Vrai',0,'Faux') FROM my_demo;

Comparison with other RDBMS's

	int10	int6	int1	char(n)	blob	XML
Oracle 11	NUMBER(10)	NUMBER(6)	NUMBER(1)	VARCHAR2(n)	BLOB	XMLType
MS SQL Server 2005	NUMERIC(10)	NUMERIC(6)	TINYINT	VARCHAR(n)	IMAGE	XML
Sybase system 10	NUMERIC(10)	NUMERIC(6)	NUMERIC(1)	VARCHAR(n)	IMAGE
MS Access (Jet)	Long Int or Double	Single	Byte	TEXT(n)	LONGBINARY
TERADATA	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	VARBYTE(20480)
DB2	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	VARCHAR(255)
RDB	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	LONG VARCHAR
INFORMIX	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	BYTE
RedBrick	integer	int	int	char(n)	char(1024)
INGRES	INTEGER	INTEGER	INTEGER	VARCHAR(n)	VARCHAR(1500)

Also consider the maximum length of a table name (or column name) and the maximum size of an SQL statement - these limits vary considerably between products and versions.

Converts an int ASCII code to a character.

2011-09-30T13:48:00.000+05:30

Char will convert an int ASCII code to a character.

Syntax

CHAR ( integer_expression )

Integer_expression

Integer_expression is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.

Example

Char(1) . . . . . . . Char(255)

CHAR can be used to insert control characters into character string. The following table shows some frequently used control characters.

Control character	value
Tab	Char(9)
Line feed	Char(10)
Carriage return	Char(13)

CREATE TABLE #Temp (id INT IDENTITY,symd CHAR(10))

DECLARE @I INT =1

WHILE (@I<=255)

BEGIN

INSERT INTO #Temp SELECT CHAR(@I)

SET @I =@I+1

END

SELECT id 'Char Value',symd 'Control character' FROM #Temp

DROP TABLE #Temp

Constructing row values into comma-separate string

2011-09-21T10:56:00.000+05:30

---Create Table

CREATE TABLE #USER (USERNAME VARCHAR(25))

---Inserting Values

INSERT INTO #USER (USERNAME) VALUES ('Loki')

INSERT INTO #USER (USERNAME) VALUES ('Chinnu')

INSERT INTO #USER (USERNAME) VALUES ('Chinni')

INSERT INTO #USER (USERNAME) VALUES ('Laddu')

---Constricting comma-separate string

SELECT DISTINCT STUFF((SELECT ',' +USERNAME FROM #user FOR XML PATH ('')),1,1,'')AS LIST FROM #user

--- Drop table

DROP TABLE #USER

Finding Nth row

2011-08-11T13:45:00.000+05:30

--To get the nth row value

Select Type, Price from

(

Select TYPE, Price, RowNum , (z.RowNum % 5) as RowMod from

(

Select TYPE, Price, RowNum from

(

Select *, Row_Number() Over(Order By Model ASC) as RowNum from

(Select a.Model, a.TYPE, b.Price from Product a Left Join

(Select distinct p.Model, Avg(Price) Price from Product p inner join PC pc

on p.Model=pc.model Group by p.Model

Union

Select distinct p.Model, Avg(Price) Price from Product p inner join Laptop l

on p.Model=l.model Group by p.Model

Union

Select distinct p.Model, Avg(Price) Price from Product p inner join Printer pr

on p.Model=pr.model Group by p.Model

) b on a.Model=b.Model

) x

) y

) z

) FinalTable

Where RowMod=0

--------------------------------------------------------------------------

--To get the nth row value

Select TYPE, Price from

(

Select *, Row_Number() Over(Order By Model ASC) as RowNum from

(Select a.Model, a.TYPE, b.Price from Product a Left Join

(Select distinct p.Model, Avg(Price) Price from Product p inner join PC pc

on p.Model=pc.model Group by p.Model

Union

Select distinct p.Model, Avg(Price) Price from Product p inner join Laptop l

on p.Model=l.model Group by p.Model

Union

Select distinct p.Model, Avg(Price) Price from Product p inner join Printer pr

on p.Model=pr.model Group by p.Model

) b on a.Model=b.Model

) x

) z where RowNum=5

split function and cross apply

2011-08-11T13:30:00.000+05:30

create table product(name varchar(20),id varchar(20))

insert into product(name,id)values('sql','1,2,3')
insert into product(name,id)values('oracle','1')
insert into product(name,id)values('db2','2')
insert into product(name,id)values('sybase','3')
insert into product(name,id)values('mysql','4')
insert into product(name,id)values('php','5')
insert into product(name,id)values('dotnet','6')
insert into product(name,id)values('java','7,8')

select * from product

CREATE FUNCTION [dbo].[FPSplit]
(
       @List As Varchar(Max)
)
Returns @Items Table (Item Varchar(Max) NOT NULL)
AS

BEGIN
Declare @Item As Varchar(Max)
Declare @Pos As Int

WHILE DATALENGTH(@List)>0
               BEGIN
                       SET @Pos=CHARINDEX(',',@List)
                       IF @Pos=0
                           SET @Pos=DATALENGTH(@List)+1
                          SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
                       IF @Item<>''
                           INSERT INTO @Items SELECT @Item
                         SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
               END

RETURN

END

SELECT name,Item as id FROM product p CROSS APPLY [dbo].[FPSplit](p.id)

Nth Highest Salary Query

2011-08-10T17:59:00.000+05:30

Query to get the list of employees getting 2^nd highest salary (Set the value of @SalaryPosition to get Nth highest salary):

DECLARE @SalaryPosition INT
SET @SalaryPosition = 2
SELECT *
FROM Employee E1
WHERE @SalaryPosition =  
          (
            SELECT COUNT(DISTINCT E2.Salary) 
            FROM Employee E2
            WHERE E2.Salary >= E1.Salary
          )

Well the query looks simple but understanding it is little bit complex. Let’s explore the process behind it.
I have taken the variable @SalaryPosition in order to make it standardize while checking salaries in more than one level.
Before going further, let me tell you the concept behind the co-related query.

Co-Related Query: In short, it’s a type of nested sub query where the inner query is referenced to some value of the outer query. And the inner query is executed for each record in the outer query making it slowL. More appropriate example of a Co-Related query is the IN () operator.

In our query, to get the 2^nd highest salary, works in the same way too. Let’s dive a little deeper.
Sample data used in employee table:

slNo	empname	salary
101	Ram	5000
102	Abhishek	7500
101	kumar	5000
104	prasad	6570
102	Jumla	7500
101	Harkesh	12000
101	John	4000

Run the below query to create the table in your database and to insert data into it.

CREATE TABLE [dbo].[employee](
	[slNo] [INT] NULL,
	[empname] [VARCHAR](50) NULL,
	[salary] [FLOAT] NULL
)
INSERT INTO [dbo].[employee]([slNo],[empname],[salary])
SELECT '101', 'Ram', '5000' UNION All
SELECT '102', 'Abhishek', '7500' UNION All
SELECT '101', 'kumar', '5000' UNION All
SELECT '104', 'prasad', '6570' UNION All
SELECT '102', 'Jumla', '7500' UNION All
SELECT '101', 'Harkesh', '12000' UNION All
SELECT '101', 'John', '4000'

So we have taken two aliases for the employee table i.e. E1 and E2.

Now let’s see how to get the employee names getting 2^nd highest salary.
Query:

DECLARE @SalaryPosition INT
SET @SalaryPosition = 2
SELECT *
FROM Employee E1
WHERE @SalaryPosition =  
           (
             SELECT COUNT(DISTINCT E2.Salary) 
             FROM Employee E2
             WHERE E2.Salary >= E1.Salary
           )

Here E1 is the outer query and E2 is the inner query.

Corollary: Let’s take the first record in the table E1 where the salary is 5000 and assume it as the second highest salary. But how will you know if it is actually the second highest salary or not. If 5000 is the second highest salary, then the distinct count of all the salaries from table E2 which are greater than or equal to 5000 must be 2.

The first record from E1 (salary = 5000) will be compared to all the records in E2 (salary).

Table E1 (considering the first record only)	Table E2 (distinct salary)	Distinct count where salary is >= 5000
5000	4000 5000 6570 7500 12000	4

Hence 5000 is not the highest salary as we have 4 more salaries greater than equal to 5000. But it is concluded that 5000 is at the fourth position.
Now let’s take the second salary from table E1 and do the same comparison as in the earlier case.

The second salary is 7500 and as per rule the count of distinct salaries greater than equal to 7500 must be 2.

Table E1 (considering the second record only)	Table E2 (distinct salary)	Distinct count where salary is >= 5000
7500	4000 5000 6570 7500 12000	2

Here in this case our condition in the where clause of outer query is satisfying. Let’s dissect the query for this particular case.
Original Query:

DECLARE @SalaryPosition INT
SET @SalaryPosition = 2
SELECT *
FROM Employee E1
WHERE @SalaryPosition =  
           (
            SELECT COUNT(DISTINCT E2.Salary) 
            FROM Employee E2
            WHERE E2.Salary >= E1.Salary
           )

Step 1:

SELECT *
FROM Employee E1
WHERE 2 =  
           (
             SELECT COUNT(DISTINCT E2.Salary) 
             FROM Employee E2
             WHERE E2.Salary >= E1.Salary
           )

Step2: (where the salary is 7500, inner query will return 2 as only two salaries are there in table E2 that are greater than equal to 7500.)

SELECT *
FROM Employee E1
WHERE 2 =  
              (
                2
              )

Step3: (Now 2 = 2, the outer query condition satisfies, hence it will return the corresponding record in the result set.

Once it returns the record 101, Abhishek, 7500 it will iterate through other records in table E1 to check if any other record is satisfying the same condition. In our example two records satisfies the condition “where 2=2”, hence both the records returned to the result set.

Finally we got all the employee information with second highest salary.
If you want to check for highest salary or second highest salary, then just change the value of @SalaryPosition accordingly.

Other ways to get list of employees getting 2nd highest salary in the organization are:

--Method 1

SELECT * FROM (SELECT DENSE_RANK() OVER(ORDER BY salary DESC) AS RankID, * FROM dbo.Employee ) InnQ WHERE InnQ.RankID = 2

--Method 2
SELECT * FROM dbo.Employee WHERE salary = ( SELECT MAX(salary) FROM dbo.Employee WHERE salary < (SELECT MAX(salary) FROM dbo.Employee))

--Method 3
SELECT * FROM dbo.Employee WHERE salary = ( SELECT MAX(salary) FROM dbo.Employee WHERE salary NOT IN (SELECT MAX(salary) FROM dbo.Employee))

--Method 4
SELECT * FROM Employee E1 WHERE 2 = (SELECT COUNT(DISTINCT E2.Salary) FROM Employee E2 WHERE E2.Salary >= E1.Salary)

SQL Server Interview Questions and Answers

2011-08-10T11:36:00.000+05:30

SQL Server Interview Questions and Answers

What is RDBMS?Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What is normalization?Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
What are different normalization forms?

1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary keyBCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is Stored Procedure?A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What is View?A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Index?An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

What is the difference between clustered and a non-clustered index?A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A 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.

What are the different index configurations a table can have?A table can have one of the following index configurations:

No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes

What is cursors?Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order:

Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor

What is the use of DBCC commands?DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.
DBCC CHECKALLOC - To check that all pages in a db are correctly allocated.
DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

What is a Linked Server?Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.
Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is Collation?Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

What are different type of Collation Sensitivity?Case sensitivity
A and a, B and b, etc.

Accent sensitivity
a and á, o and ó, etc.

Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

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.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is a NOLOCK?Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

What is difference between DELETE & TRUNCATE commands?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 and there will be no data in the table after we run the truncate command.

TRUNCATETRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETEDELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.

Difference between Function and Stored Procedure?UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

When is the use of UPDATE_STATISTICS command?This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What types of Joins are possible with Sql Server?Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. HAVING criteria is applied after the the grouping of rows has occurred.

What is sub-query? Explain properties of sub-query.Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

Properties of Sub-QueryA subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.

What are types of sub-queries?Single-row subquery, where the subquery returns only one row.
Multiple-row subquery, where the subquery returns multiple rows,.and
Multiple column subquery, where the subquery returns multiple columns.

What is SQL Profiler?SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is User Defined Functions?User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

What kind of User-Defined Functions can be created?
There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.

Scalar User-Defined FunctionA Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

Inline Table-Value User-Defined FunctionAn Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined FunctionA Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Which TCP/IP port does SQL Server run on? How can it be changed?SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number.both on client and the server.

What are the authentication modes in SQL Server? How can it be changed?Windows mode and mixed mode (SQL & Windows).

To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

Where are SQL server users names and passwords are stored in sql server?They get stored in master db in the sysxlogins table.

Which command using Query Analyzer will give you the version of SQL server and operating system? SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’),
What is SQL server agent?SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is @@ERROR?The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is Raiseerror?
Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

What is log shipping?Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

What is the difference between a local and a global variable?A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What command do we use to rename a db? sp_renamedb ‘oldname’ , ‘newname’
If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

What is sp_configure commands and set commands?Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

What are the different types of replication? Explain.The SQL Server 2000-supported replication types are as follows:

Transactional
Snapshot
Merge

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.

What are the OS services that the SQL Server installation adds?MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)

What are three SQL keywords used to change or set someone’s permissions?GRANT, DENY, and REVOKE.

What does it mean to have quoted_identifier on? What are the implications of having it off?When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
What is the STUFF function and how does it differ from the REPLACE function? STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.
REPLACE function to replace existing characters of all occurance. Using this syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?SELECT *
FROM table1
SELECT COUNT(*)
FROM table1
SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID(table1)
AND indid < 2
How to rebuild Master Database?Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
In the Rebuild Master dialog box, click Browse.
In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.
To continue, you may need to stop a server that is running.
Source: http://msdn2.microsoft.com/en-us/library/aa197950(SQL.80).aspx

What is the basic functions for master, msdb, model, tempdb databases?The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.

What are primary keys and foreign keys? Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is data integrity? Explain constraints?Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What are the properties of the Relational tables?Relational tables have six properties:

Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.

What is De-normalization?De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

How to get @@error and @@rowcount at the same time?If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset.
And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

What is Identity?Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers, the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

What is a Scheduled Jobs or What is a Scheduled Tasks? Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that.

What is BCP? When does it used? BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

How do you load large data to the SQL server database?BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.

Can we rewrite subqueries into simple select statements or with joins? Subqueries can often be re-written to use a standard outer join, resulting in faster performance. As we may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching rows with NULL values. Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a sub-query.

Can SQL Servers linked to other servers like Oracle?SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

How to know which index a table is using? SELECT table_name,index_name FROM user_constraints

How to copy the tables, schema and views from one SQL server to another?Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets user extract, transform, and consolidate data from disparate sources into single or multiple destinations.

What is Self Join?This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.

What is Cross Join?A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Which virtual table does a trigger use?Inserted and Deleted.

List few advantages of Stored Procedure.

Stored procedure can reduced network traffic and latency, boosting application performance.
Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
Stored procedures help promote code reuse.
Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
Stored procedures provide better security to your data.

What is DataWarehousing?

Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting;
Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.

What is OLTP(OnLine Transaction Processing)?In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

How do SQL server 2000 and XML linked? Can XML be used to access data?
FOR XML (ROW, AUTO, EXPLICIT)You can execute SQL queries against existing relational databases to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures. To retrieve XML results, use the FOR XML clause of the SELECT statement and specify an XML mode of RAW, AUTO, or EXPLICIT.

OPENXMLOPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.

What is an execution plan? When would you use it? How would you view the execution plan?An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

Most Commonly Used DateTime Formats

2011-08-04T17:20:00.000+05:30

SELECT CONVERT(VARCHAR(20), GETDATE(), 108)

Format No	Output
101	U.S. mm/dd/yyyy
102	ANSI yy.mm.dd
103	British/French dd/mm/yyyy
104	German dd.mm.yy
105	Italian dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mi:ss
109	mon dd yyyy hh:mi:ss:mmmAM
110	USA mm-dd-yy
111	JAPAN yy/mm/dd
112	Yymmdd
113	dd mon yyyy hh:mi:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120	yyyy-mm-dd hh:mi:ss(24h)
121	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-ddThh:mi:ss.mmm
127	yyyy-mm-ddThh:mi:ss.mmmZ (With Time Zone)
130	dd mon yyyy hh:mi:ss:mmmAM (Hijri)
131	dd/mm/yy hh:mi:ss:mmmAM (Hijri)

Backup Failed for Server.

2011-07-22T10:33:00.000+05:30

Applies to: Microsoft SQL Server 2005 Express Edition.

Error Message

When we tried to backup a database using a logical backup device that represents a disk file, we received the following error:

Backup failed for Server 'servername'. (Microsoft.SqlServer.Smo)

Additional information:

System.Data.SqlClient.SqlError: Cannot open backup device.
'LogicalBackupDeviceName (C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\folder1\DB.bak'). Operating system error 5(Access is denied.).(Microsoft.SqlServer.Smo)

Cause

The service account for the MSSQLSERVER service does not have permissions over the physical file or folder on the hard drive that corresponds to the logical backup device that is being used to make the database backup.

Solution

Please make sure that the SQL Server Service account has modify permissions over the physical file or folder on the hard drive that corresponds to the logical backup device.

To find column_name,Type,Length in a Table

2011-07-19T13:05:00.000+05:30

select sst.name,c.name,st.name as datatype, c.max_length datatype_size
from sys.columns c
inner join sys.types st
on c.user_type_id=st.user_type_id
inner join sys.tables sst
on c.object_id = sst.object_id
where sst.type = 'u'and c.object_id=object_id('emp')

SQL KIDS

What is .TUF file?

Query Plan (DMV)

Possible Bad NC Indexes

Unused indexes

Index Read/Write stats

Missing Indexes in current database

Sql Server All Database Used Space ,Size Info..... Using MSForeachdb

Configuring SQL Server

SQL Server 2008 Network Protocols

Shared Memory

Named Pipes

TCP/IP

The Dedicated Administrator Connection

Null value is eliminated by an aggregate or other SET operation

How to Enable the Xp_CmdShell

Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Msg 1807, Level 16, State 3, Line 1 Could not obtain exclusive lock on database ‘model’. Retry the operation later. Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Renaming database and physical database files

SPACE function will return maximum of 8000 spaces only

Varchar(max) datatype and Replicate function

Deterministic Functions and Nondeterministic Functions

Oracle DataTypes

Converts an int ASCII code to a character.

Constructing row values into comma-separate string

Finding Nth row

split function and cross apply

Nth Highest Salary Query

SQL Server Interview Questions and Answers

List few advantages of Stored Procedure.

What is DataWarehousing?

Most Commonly Used DateTime Formats

Backup Failed for Server.

To find column_name,Type,Length in a Table