Learn T-SQL

SQL Basics?                                            SQL Server Interview Questions And Answers

 

Create Database

 

Table

View

Stored Procedure

Function

Trigger

SQL TOP

SQL LIKE

SQL Wildcards

SQL IN

SQL BETWEEN

SQL Alias

SQL JOINs

SQL UNION

SQL UNION ALL

SQL SELECT INTO

SQL CONSTRAINTS

Data Definition Language (DDL)

Data Manipulation Language (DML)

SQL Server Data Types

SQL NULLs

SQL ISNULL

SQL NULLIF

SQL Date and Time Functions

T-SQL Control of Flow

CURSOR

T-SQL Operators

PRINT

RAISERROR

Error Handling

SET Statements

Security Statements

BACKUP

RESTORE

TRANSACTIONS

 

 

 

 

 

 

 

 

 

 

SQL Server Interview Questions And Answers

How to Create Database using T-SQL command?

Explain Candidate Key, Alternate Key, and Composite Key

How to create Primary Key on existing table?

How to create Foreign Key on existing table?

How to create FOREIGN KEY constraint by using WITH NOCHECK

How to add a DEFAULT constraint to an existing column?

How to add CHECK CONSTRAINTS on existing table?

What's the difference between a primary key and a unique key constraints?

Explain SQL Server JOINs with examples?

What is the difference between TRUNCATE and DELETE?

Write T-SQL query to calculate Month End Date

How to get Month Number from Month Name

Write T-SQL Query to find Nth largest Number

Maximum Capacity Specifications for Database Objects in SQL Server 2008 R2

What is View in SQL Server?

What is Stored Procedure?

What is User Defined Functions (UDF)

What is User-Defined Function (UDF) in SQL Server?

Types of User Defined Functions (UDF) in SQL Server?

Difference between Function and Stored Procedure?

What is the difference between a local and a global temporary tables?

What is a NOLOCK?

What is lock escalation?

What is Log Shipping in SQL Server?

What is WITH TIES clause in SQL Server?

COUNT Number of Records for all the Tables in a Database?

Function to Split Multi-valued String

How to connect to SQL Server using RUNAS command from command prompt?

Regular Expression Problem in T-SQL

Concatenating Row Values using Transact-SQL

Which TCP/IP port does SQL Server run on? How can it be changed?

What is uniqueidentifier in SQL Server?

What is NEWSEQUENTIALID?

What is Trace Flag 610?

Sleep Command in T-SQL?

What is the use UPDATE_STATISTICS command?

How to Granting Execute permission on All the Stored Procedure?

How to get SQL Server Restore history using T-SQL?

Function to Convert Decimal Number into Binary, Ternary, and Octal

How to find all the IDENTITY columns in a database?

Difference between a WHERE clause and HAVING clause?

• A WHERE clause used to filter the physical rows in your table(s) whereas the HAVING clause operates on groups of a result-set (usually aggregate) and specifies which of the summary rows are returned in your resultset.  
• A WHERE clause is independent of GROUP BY clause but the HAVING clause does depend on this, although it can be used without this as well.
•  First SQL Server filters the physical rows using WHERE conditions and then performs GROUP BY on filtered data in memory and then filters the aggregated rows again with HAVING clause.

Example:

List of all the employee with basic greater than 3000.

Now I want to calculate the Dept codes for which total of basic is greater than 20,000 for those employees who are getting basic more than 3000.

Local temporary table VS Global temporary table

In this post I will describe the differences between a Local temporary table and global temporary table.
Below are the differences in tabular form:
 

Local Temporary Table	Permanent Temporary Table
They have a single number sign (#) as the first character of their names.	They have a two number sign (##) as the first character of their names.
They are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server.	They are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.
If local temp table is created inside a stored procedure then it will get deleted once the execution is completed.	If global temp table is created inside a stored procedure then it will exists once the execution is completed. This will dropped automatically when the last user session that references the table disconnects.
Multiple users can't share these tables.	Multiple users can share these tables; in fact, all other database users can access it.
You can't grant or revoke permissions on these tables because it is available only to the owner.	You can grant or revoke permissions on these tables because these are always available to everyone.
The local table we created in our procedure by using sp_executesql won't be accessible to either the procedure or its child procedures. Within its execution scope, an sp_executesql system stored procedure creates and drops a local table. By definition, when the sp_executesql procedure ends, the life of the table also ends—hence the need to create a global temporary table.	These tables will always available till the last user session that references the table disconnects.

System Databases in SQL Server

 When you install SQL Server, you will start with four system databases:

·         master

·         model

·         msdb

·         tempdb

All the above databases are essential to run your server properly.

 

The master database

Every SQL Server, regardless of the version, has master database and hold special set of tables called system tables. These system tables keeps track of the system as whole. For instance, when you create a new database then an entry goes to sys.databases or sysdatabases table.

SELECT * FROM sysdatabases

All the extended and system stored procedures are stored in this database regardless of which database they are intened to use with.

Note: everything that describes  your server is stored in master database so this is very critical database nd hence can not be deleted.

The model database

This is a database on which a copy can be based. Model database forms a template for any new database that we create.So you can modify model database if you want to change what standards, newly created database look like. For instance, you can add some set of tables, functions anmd procedures that need to be part of every newly created database. Also you can include some users that would be cloned into every new database.

Note: this database serves as a template for any other database, it is a required database and can not be deleted.

The msdb database

MSDB database stores all the tasks and processes related to SQL Agent. For instance, if you schedule backups to run on a databases nightly  then there will be an enrty in msdb database.

The tempdb database

One of the key workin area for the server is tempdb. SQl Server needs to create interm temporary tables to solve complex or large queries and these interim tables are created in tempdb.

tempdb is different from any other database. Not only are the objects within it temporary, the database itself is temporary. This is the only database in the system that is completely rebuilt from scratch everytime you start your SQL server.

Difference between Function and Stored Procedure?

• User Defined Function (UDF) can be used in the SQL statements anywhere in the SELECT, WHERE, and HAVING section where as Stored procedures cannot be used.
• Functions are designed to send their output to a query or T-SQL statement while Stored Procedures use EXECUTE or EXEC to run.
• We can not use EXECUTE and PRINT commands inside a function but in SPROC
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• UDFs can't change the server environment or your operating system environment, while a SPROC can.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
• Stored Procedures are stored in compiled format in the database where as Functions are compiled and excuted runtime.
• SPROC can be used with XML FOR Clause but Functions can not be.
• SPROC can have transaction but not Functions.
• Functions can be used in a SPROC but SPROC cann't be used in a Function. Only extended stored procedures can be called from a function.
• Of course there will be Syntax differences and here is a sample of that

CREATE PROCEDURE dbo.ProcedureName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype OUTPUT
)
AS 
BEGIN
   T-SQL statements
   RETURN
END
GO

CREATE FUNCTION dbo.FunctionName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype
)
RETURNS datatype
AS
BEGIN
   SQL Statement
   RETURN Value
END
GO

Explain SQL Server JOINs with examples?

I will explain types of SQL JOINs in in this article. JOINs in SQL Server can be classified as follows:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN

Each type is explained below with suitable examples:
Let’s consider two tables as Table1 & Table2 for our example.

– CREATE TEST TABLES
CREATE TABLE Table1(ID [int], Code [varchar](10));
GO
CREATE TABLE Table2(ID [int], Amount [int]);
GO


– INSERT DUMMY DATA
INSERT INTO Table1 VALUES
(1,’AAA’),(2,’BBB’),(3,’CCC’)
GO
INSERT INTO Table2 VALUES
(1,50),(2,30),(4,40)
GO

INNER JOIN:
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Below is the query for inner join:

SELECT *
FROM Table1 A
INNER JOIN Table2 B
   ON A.ID = B.ID
GO

LEFT OUTER JOIN
Left Outer joins return all rows from the left table referenced with a left outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Left Outer Join:

SELECT *
FROM Table1 A
LEFT OUTER JOIN Table2 B
   ON A.ID = B.ID
GO

RIGHT OUTER JOIN
Right Outer joins return all rows from the right table referenced with a right outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Right Outer Join:

SELECT *
FROM Table1 A
RIGHT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

FULL OUTER JOIN
Full Outer joins return all rows from both the tables. Unmatched records will be NULL. Below is the query for Full Outer Join:

SELECT * FROM Table1 A
FULL OUTER JOIN Table2 B
ON A.ID = B.ID
GO

CROSS JOIN
In cross joins, each row from first table joins with all the rows of another table. If there are m rows from Table1 and n rows from Table2 then result set of these tables will have m*n rows. Below is the query for

SELECT * FROM Table1 A
CROSS JOIN Table2 B
GO

Stored Procedure

What is Stored Procedure?

Difference Between Function and Stored Procedure?


 

How to find all the IDENTITY columns in a database?

Here is the easiest way to list all the IDENTITY Columns of any database:

SELECTOBJECT_NAME([object_id]) as TableName,

name as ColumnName

FROM [DatabaseName].sys.columns

WHERE is_identity = 1

How to get SQL Server Restore history using T-SQL?

How to find SQL Server databases Restore History?
How to find Last Restore Date od SQL Server database?

Few developers asked me how to find the last time a database was restored using T-SQL. I have formed the following query using MSDB..RestoreHistory table.

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'DatabaseName'
ORDER BY restore_date DESC

What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (For example row locks, and page locks) into higher level locks (e.g. table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

In the hierarchy of the objects in a SQL Server instance, at the top level we have Database, followed by Schema, Tables, Table Partitions, Pages and then finally the individual Rows. If you acquire a lock at higher level, it can cover more resources there by you consume fewer lock resources (each lock structure takes approximately 100 bytes) and the locking overhead but this comes at a price of lower concurrency. So for example, if you want to select all the rows of a table, if you acquire a lock at table level, you will not need to lock individual rows or pages but then it will block any concurrent update transaction. Similarly, if you lock individual rows, you will get higher concurrency but then you will incur the overhead of acquiring/releasing locks on each row and lot more locking resources depending upon the isolation level of your transaction, as you may need to hold the locks on all the rows till the end of transaction.

Depending upon the estimates during query compilation, the SQL Server recommends the locking granularity (i.e. row, page or table) appropriately and during query execution, depending on the concurrent work load, the appropriate locking granularity is applied. User can override the locking granularity option explicitly by providing locking hints and/or by executing sp_indexoption stored procedure. While locking granularity is chosen at the start of query execution but during the execution, the SQL Server may choose to escalate the lock to lower level of granularity depending on the number of locks acquired and the availability of memory at run time.

Granting Execute permission on All Stored Procedure

How to grant Execute permission on All Stored Procedure for a new User in SQL Server?


Most of the application have users who are not creator (owner) of the Stored Procedures. Hence the users don't have EXECUTE permission on any of the Stored Procedures by default, considering that they are not part of any existing Role that has execute permission.
 
I have created a small script which returns a string that grants permission to the new User to all the Stored Procedures. Here is the script:
 
SELECT 'GRANT EXECUTE ON ' + name + ' [UserName]'
FROM sysobjects WHERE xtype IN ('P')
 
You need to copy the script generated by above query and execute. Alternatively, you can use below script - where you need not to copy the script generated by above query. Just execute the below script in required database.

DECLARE @UserName [varchar](128), @SQL [varchar](1000)
SET @UserName = '[UserName]'


DECLARE Cur CURSOR FOR
   SELECT 'GRANT EXEC ON ' +
   '[' + u.[Name] + ']' + '.' +
   '[' + o.[Name] + ']' +
   ' TO ' + @UserName  AS SQLCommand
FROM dbo.sysobjects o
JOIN dbo.sysusers u
  ON o.[uid] = u.[uid]
WHERE o.[Type] = 'P'

OPEN Cur
FETCH NEXT FROM Cur INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @SQL
   EXEC (@SQL)
   FETCH NEXT FROM Cur INTO @SQL
END
CLOSE Cur
DEALLOCATE Cur

Cheers !!!

What is NEWSEQUENTIALID

NEWSEQUENTIALID() creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

What is the use UPDATE_STATISTICS command?

We add statistics on the columns that don't have statistics in order to boost query performance. UPDATE_STATISTICS Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan but in some cases you can improve query performance by using UPDATE_STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

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

Here is T-SQL Command:
1. Updating All Statistics with sp_updatestats
EXEC sp_updatestats

2. Update all statistics on a table
UPDATE STATISTICS TableName

3. Update the statistics for an index
UPDATE STATISTICS TableName, IndexName

For more information, click here

The SQL Server Query Optimizer has the ability to use statistics on columns that are not indexed in order to create more optimal execution plans. Doing so can increase performance without having the overhead of indexes. Column statistics are generated automatically by SQL Server when queries are being optimized by the Query Optimizer. For example, column statistics are automatically created when there are currently no statistics available for the column being used as a search argument in a WHERE clause or a JOIN clause. There are two cases when SQL Server will not automatically create column statistics, even when they fit the criteria described above. These include: when the costs needed to create the column statistics are larger than the costs of the query plan; and when SQL Server is too busy.

If you want to find out if an index has had its indexes updated or not, you can use the DBCC SHOW_STATISTICS command, like this:

DBCC SHOW_STATISTICS(table_name, index_name)

This command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for the index you are examining


Reference: MSDN

Sleep Command in T-SQL?

Is there to way write a T-SQL command to just make it sleep for a period of time?
WAITFOR command is the answer.

-- wait for 1 minute
WAITFOR DELAY '00:01:00'

-- wait for 1 second
WAITFOR DELAY '00:00:01'

This command allows you a high degree of precision but is only accurate within 10ms - 16ms for example, the call WAITFOR DELAY '00:00:00:001' is likely to result in no wait at all.

What is Trace Flag 610

What is Trace Flag 610? When should we use it?

Trace Flag 610 (TF610) can be used to have minimal logging into a b tree, i.e. clustered table or an index on a heap, that already has data. TF610 can be used to get minimal logging in a non-empty B-Tree. When you insert a large amount of data, you don't want to create a lot of transaction log. So, initially the idea was to automatically do this in the engine, but because of bunch of issues this was put under a TraceFlag.


There are a few things one shoud be aware of:

1. When a Transaction COMMITs, it writes all pages that were minimally logged to disk. But there is possibilty that not all the pages can be written in time in case of a slow I/O subsystem. This can make the operation slower than full logging because for full logging, SQL only needs to write the commit log record and it's done. 
2. There is an issue in SQL 2008 where the transaction log grows very big due to log reservation - even though the log is never used. This is due to how rollback works for minimally logged pages. However, this bug was fixed in SQL 2008 R2.
3. If you'hv a large buffer-pool and the I/O subsystem cannot keep up, the commit could take very long time.
4. Don't make transactions too big. Even though the features is build to be used when inserting a large number of rows, it does not work very well if you make the transactions too big. Inserting data around 1TB in a single transaction may caused some issues.
5. The feature is NOT designed to make inserts go faster. It is mainly written to reduce the size of the transaction log.

I would recommendation to test this feature in a test environment - very similar I/O characteristics than the production system - and see if this really helps.

I would only use it if I have fast enough I/O to keep up with creation of the minimally logged pages.

What is uniqueidentifier in SQL Server?

The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites.

A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:

1. By using the NEWID() function.
2. By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

Note: The only operations that can be performed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators can be used. All column constraints and properties, except IDENTITY, can be used on the uniqueidentifier data type.

Reference: MSDN

Which TCP/IP port does SQL Server run on? How can it be changed?

By default (unless changed) the default TCP Port for SQL Server is 1433.

There are a few options when trying to find out this information… one way would be to go right to the registry and look at the TCP settings:
SQL 2005
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.\MSSQLServer\SuperSocketNetLib\TCP\

SQL 2008
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\MSSQLServer\SuperSocketNetLib\TCP\

How to change TCP/IP port:
Open SQL Server Configuration Manager and expand the SQL Server Network Configuration, then select Protocols for MSSQLSERVER (default instance) in the right hand pane, you’ll see your protocols (Shared Memory, Named Pipes, TCP/IP, VIA).


If you double click on TCP/IP, the TCP/IP Properties dialog box will appear and by selecting the IP Addresses tab you will now see your IP addresses and which TCP port they are running under.


If you need to change this port number, make sure you restart the SQL Server service as this change will not take affect until you do.

Fun With TRANSACTION

What will be the output of the SELECT statement in the following query?

DECLARE @Table TABLE ([ID] INT IDENTITY(1,1), [Name] VARCHAR(10))
INSERT @Table ([Name]) VALUES ('Hari')
BEGIN TRANSACTION Test
INSERT  @Table ([Name]) VALUES ('Jon')
INSERT @Table ([Name]) VALUES ('Peter')
ROLLBACK TRANSACTION Test
INSERT @Table ([Name]) VALUES ('Max')
SELECT [ID],[Name] FROM @Table

Option1:

Option2:

Option3:

Option4:

None of the above.

Table variables do not participate in transactions so you can rollback a transaction without affecting the table variable. In our case, the insert inside the transaction is not rolled back.

Concatenating Row Values using Transact-SQL

Sometime SQL programmers come across a requirement to concatene a result-set using T-SQL query.

Below is an example of this scenarion:

SELECT Dept_Code,Emp_Name

FROM Employee (NOLOCK)

ORDER BY Dept_Code,Emp_Name

And user wants to see the data in below format:

You can do these using Transact SQL. Below is the T-SQL query to achieve this solution:

Method1: Concatenation that uses the FOR XML clause with PATH mode:

SELECT E1.Dept_Code,

(

   SELECT Emp_Name + ',' FROM Employee E2

   WHERE E2.Dept_Code = E1.Dept_Code

   ORDER BY Emp_Name

   FOR XML PATH('')

) AS Emp_Name

FROM Employee E1

GROUP BY Dept_Code

Method2: There is a similar approach using the CROSS APPLY operator.

SELECT DISTINCT E1.Dept_Code, Temp.Emp_Name

FROM Employee E1 CROSS APPLY

(

   SELECT Emp_Name + ',' FROM Employee E2

   WHERE E2.Dept_Code = E1.Dept_Code

   ORDER BY Emp_Name

   FOR XML PATH('')

) AS Temp (Emp_Name)