What is a temporary table?

A temporary table is an object that is created and stored temporarily by SQL Server in the tempdb database. The table can be created by any user by using either the CREATE TABLE statement or SELECT…INTO statement. Temporary tables are of the following two types:


1. Local: A local temporary table is created by prefixing the table name with a single hash(#) sign. The table is visible only to the user who created it.

2. Global: A global temporary table is created by prefixing the table name with a double hash(##) sign. The table is visible to all the users who are connected to the database engine.

The syntax of creating a temporary table is as follows:
To create a local temporary table.

•  CREATE TABLE #temporary_table ( column1 datatype, column2 datatype)
•  SELECT * INTO #temporary_table FROM original_table

To create a global temporary table.

• CREATE TABLE ##temporary_table (column1 datatype, column2 datatype)
• SELECT * INTO ##temporary_table FROM original_table

What is an endpoint?

In SQL Server 2005, a connection between servers is managed through an endpoint. The endpoint is an object in SQL Server 2005 through which SQL Server computers communicate with each other over the network. For database mirroring, a SQL Server computer requires a dedicated database mirroring endpoint. The database mirroring endpoint is used to communicate between the servers. It uses the TCP protocol to send and receive messages between the servers in the database mirroring sessions. Each endpoint listens on a unique TCP port number. Clients do not use the database mirroring endpoint to connect to the principal server.   

What is synonym in SQL Server?

A synonym is a database object used to create aliases for database objects. It is used to hide the actual name of an object from an application connected to the database. If the object is moved to another schema or server, it does not affect the application. After the object is moved, the path of the synonym should be changed according to the path of the object to which the synonym is associated.

Syntex to create and use synonym:

• CREATE SYNONYM synonym_name FOR object name
• SELECT * FROM synonym_name

Implementing Aggregate Queries

AGGREGATE FUNCTIONS
Aggregate functions perform calculations on a set of data and return a scalar value. Following aggregate functions are available in SQL Server 2008,

AVG : Returns the average of the values in a data set
CHECKSUM_AGG : Returns the checksum of the values in a data set
COUNT : Returns the number of the values in a data set COUNT(*) returns the number of rows in a set. COUNT(Column Name) Returns number of rows which contain data in specified column. COUNT(DISTINCT ) returns number of unique not null values in data set
COUNT_BIG : it work same as "COUNT" but it returns Bigint data type, while "COUNT" return Int data type.
GROUPING : It returns 0 or 1, the value 1 indicate aggregate while 0 indicate detail result
MAX : Returns the highest value in a data set
MIN : Returns the lowest value in a data set
SUM : Returns the total of the values in the data set
STDEV : Returns the statistical standard deviation of all values in given data set
STDEVP : Returns the statistical standard deviation for the population for all values in data set
VAR : Returns the statistical variance of all values in the specified expression
VARP : Returns the statistical variance for the population for all values in the specified expression

GROUP BY CLAUSE
The "Group By" clause is used in the query with Aggregate functions. When an Aggregate function is used in Select, all other expression in Select must be either Aggregate function or included in Group By clause. The "Group By" allow to find out subtotal of the aggregate data. For example, I have a table like given below

Table A : MonthlySalary

Now look at the following query, it will return Average, Minimum and Maximum salary of each employee

SELECT Employee, AVG(Salary) Average,
MIN(Salary) Minimum, MAX(Salary)Maximum
FROM MonthlySalary
GROUP BY Employee

WITH ROLLUP OPERATOR
The ROLLUP operator is useful in generating quick result set that contain subtotals and total. This operators provides grand total and subtotal based on the column included in GROUP BY clause. On "MonthlySalary" table execute the given query that uses WITH ROLLUP Operator and check the output,

SELECT Employee, Month, SUM(Salary) AS Salary
FROM MonthlySalary
GROUP BY Employee, Month WITH ROLLUP
OR
SELECT Employee, Month, SUM(Salary) as Salary
FROM MonthlySalary
GROUP BY ROLLUP(Employee,Month)

The result set contain subtotal of the salary for all the employees, and the grand total.

WITH CUBE OPERATOR
The CUBE operator should be used when the summary information needed for more then one column. It generates a result set that contain subtotal for all the column specified in GROUP BY clause. Look at the query and the result given below

SELECT Employee, Month, SUM(Salary) AS Salary
FROM MonthlySalary
GROUP BY Employee, Month WITH CUBE
OR
SELECT Employee, Month, SUM(Salary) as Salary
FROM MonthlySalary
GROUP BY CUBE(Employee,Month)

Don't miss the row number 3, 7 and 11 which shows subtotal by month as well, when using WITH CUBE operator. While using WITH ROLLUP operator this rows will not be displayed.

Things to remember while using WITH ROLLUP and WITH CUBE operator

• CHECKSUM_AGG aggregate function is not compatible with ROLLUP, CUBE and GROUPING SETS
• Avoid using WITH CUBE operator on large table where their are more then 3 columns in GROUP BY clause, for batter performance.

ROLLUP() AND CUBE()
ROLLUP() and CUBE() are equivalent to WITH ROLLUP and WITH CUBE and use the same query plans. But note that the new ROLLUP and CUBE syntax is only available in compatibility level 100.

GROUPING AGGREGATE FUNCTION
How can a NULL generated by the CUBE operation be distinguished from a NULL returned in the actual data? This can be done by using the GROUPING function. The GROUPING function returns 0 if the column value NULL came from the fact data, and 1 if the column value NULL is generated by the CUBE operation. It will be useful when we have to further use the result set generated, as it is tough to find out which data is summary data and for which column, so using GROUPING function we can easily find it out. Look at the query and the result given below

SELECT Employee, Month, SUM(Salary) AS Salary,
GROUPING(Employee)ForEmployee, GROUPING(Month)ForMonth
FROM MonthlySalary
GROUP BY Employee, Month WITH Cube

GROUPING SETS
It allows us to specify which aggregations do we want in our result set.
For Example:
1 - Following query will give us same output as we get with ROLLUP() or WITH ROLLUP operator

SELECT Employee, Month, SUM(Salary) as Salary
FROM MonthlySalary
GROUP BY GROUPING SETS((Employee,Month), (Employee), 0)

This query aggregates the Salary by Employee and Month, then it aggregate by Employee only, at last it compute the total for all Employees for all Months. The () syntax with no GROUP BY columns display the total.

2 - Following query will give us the same output as we get with CUBE() or WITH CUBE operator

SELECT Employee, Month, SUM(Salary) as Salary
FROM MonthlySalary
GROUP BY GROUPING SETS((Employee,Month), (Employee), (Month), 0)

This query aggregates the Salary by Employee and Month, then it aggregate by Employee only, then it aggregate by Month only, at last it compute the total for all Employees for all Months.

SQL Server Database Recovery Models in Brief

SQL Server database recovery models gives backup and restore flexibility. The model used to determine how great your risk of data loss will be when a breakdown occurs. There are three types of database recovery models available

1. Full Recovery
2. Bulk_Logged Recovery
3. Simple Recovery

Full Recovery Model :
In the Full recovery model, the database engine logs all operations onto the transaction log, and the database engine never truncates the log. The Full recovery model lets you restore a database to the point of failure
Simple Recovery Model :
In the Simple recovery model, the database engine minimally logs most operations and truncates the transaction log after each checkpoint. In the Simple recovery model, you cannot back up or restore the transaction log. Furthermore, you cannot restore individual data pages.
Bulk-Logged Recovery Model:
In the Bulk-Logged recovery model, the database engine minimally logs bulk operations such as SELECT INTO and BULK INSERT. In this recovery model, if a log backup contains any bulk operation, you can restore the database to the end of the log backup, not to a point in time. The Bulk-Logged recovery model is  intended to be used only during large bulk operations.

Important: The Simple recovery model is not appropriate for databases in which the loss of recent changes is unacceptable.

What Deterministic and Nondeterministic Functions are?

Deterministic Function: Functions which always return the same result any time they are called with a specific set of input values. For example square(2) will always return 4.


Nondeterministic Function: Functions that may return different results each time they are called with a specific set of input values. For example getdate() function will return one result today, but the same function will return different result tomorrow.

What is a FILESTREAM in SQL Server 2008?

A FILESTREAM is a new feature in the SQL Server 2008. It allows structured data to be stored in the database and associated unstructured data to be stored directly in the NTFS file system. Transactional consistency is maintained at all times between the structured and unstructured data. FILESTREAM combines the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system.
It is recommended to use the FILESTREAM when any of the following conditions are met:

• When the objects that are being stored in the FILESTREAM have a size greater than 1 MB.
• When fast read access is an essential requirement.
• When an application is developed that uses a middle tier for application logic.

Important date queries in SQL Server 2005

Query to fetch current date in '2010-02-23 00:00:00.000' format
SELECT DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
OR
SELECT DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()) )

Query to fetch current date in '2010-02-23 11:59:59.000' format
SELECT DATEADD(ss, +43199, DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0))
OR
SELECT DATEADD(s, -1, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()) ) + 1)

Few days back I had a requirement that, I have to find out start date of the week, considering Monday as first day of the week. I have search on the internet for available solution and found the following query,
SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
Output is : 2010-10-04 00:00:00.000

Soon I have noticed a problem with above query that instead of using GETDATE() if I pass any date when the day is Sunday, for eg. I pass '2010-10-03' then it should return me ‘2010-09-27’ but it is returning me '2010-10-04'. With the following query, you will notice that output  is not meeting our requirement
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2010-10-03'), 0)
Output is : 2010-10-04 00:00:00.000

So, I have used the following query which will help to solve above mentioned problem
SELECT (CAST('2010-01-05' AS DATETIME) - DATEPART(dw, CAST('2010-01-05' AS DATETIME)-1)) +1
Output is : 2010-09-27 00:00:00.000

Writing a recursive query to find out all dates between two given date

--Declaring the required variables
DECLARE @fromdate DATETIME
DECLARE @todate DATETIME
SET @fromdate = '2010-01-01'
SET @todate = '2010-01-10'

--Query to fetch all the dates between two given date
;WITH tmpinfo(tmpdt) AS
(SELECT @fromdate
UNION ALL
SELECT tmpdt + 1
FROM tmpinfo
WHERE tmpdt < @todate)
SELECT * FROM tmpinfo OPTION (MAXRECURSION 0);

Equivalent of Group_Concat function in MS-SQL

--Query to create the table given in the example
CREATE TABLE [dbo].[categoryproductlink](
[categoryproductlinkid] [int] IDENTITY(1,1) NOT NULL,
[categoryid] [int] NOT NULL,
[productid] [int] NOT NULL,
[endeffdt] [smalldatetime] NULL,
CONSTRAINT [PK_categoryproductlink] PRIMARY KEY CLUSTERED
(
[categoryproductlinkid] ASC
)) ON [PRIMARY]

GO
--Query to insert data in above table
SET IDENTITY_INSERT [dbo].[categoryproductlink] ON
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (1, 1, 1, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (2, 1, 2, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (3, 1, 3, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (4, 2, 4, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (5, 2, 5, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (6, 2, 6, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (7, 3, 2, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (8, 3, 6, NULL)
SET IDENTITY_INSERT [dbo].[categoryproductlink] OFF

Query 1 – Equivalent to Group_Concate function in MySQL
_________________________________________________________
SELECT CAST(productid AS varchar(20)) + ', ' AS [text()]
FROM categoryproductlink
WHERE categoryid = 2
FOR XML PATH('')
_________________________________________________________

Query 2 – Equivalent to Group_Concate function in MySQL
_________________________________________________________
DECLARE @prodidstr VARCHAR(MAX)

SELECT @prodidstr = COALESCE(@prodidstr+', ','')+CAST(productid AS varchar(8))
FROM categoryproductlink
WHERE categoryid = 2

SELECT @prodidstr
_________________________________________________________

Query to change collation of a column

ALTER TABLE categorydesc
ALTER COLUMN ldesc text COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Query to fetch description of all tables and columns in current database

SELECT major_id, minor_id, t.name AS [Table Name],
c.name AS [Column Name], value AS [Extended Property]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id
AND ep.minor_id = c.column_id
WHERE class = 1
ORDER BY t.name, c.name

Query to get Collation of database and columns of a table

To get dafault collation of database
1 - SELECT DATABASEPROPERTYEX('GoldenTours', 'Collation')
2 - SELECT collation_name FROM sys.databases WHERE name = 'GoldenTours'

Query to get Collation of all the columns of a table
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects
WHERE type = 'U'
AND name = 'categorydesc')

Query to generate create index statement for all the indexes in a perticular database. Here it will find only those index which start with 'IX_'

SELECT 'CREATE NONCLUSTERED INDEX [' + si.name + '] ON ' + so.name + '( [' + sc.name + '] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]' test
FROM sysobjects so, syscolumns sc , sysindexkeys sik , sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
AND si.name like 'IX_%'
ORDER BY si.name

To create index statement with "IF NOT EXIST" check

SELECT 'IF NOT EXISTS (SELECT * FROM sysindexes WHERE name = ''' + si.name + ''')
BEGIN CREATE NONCLUSTERED INDEX [' + si.name + '] ON ' + so.name + '( [' + sc.name + '] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] END' test
FROM sysobjects so, syscolumns sc , sysindexkeys sik , sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
AND si.name like 'IX_%'
ORDER BY si.name

Query to fetch all duplicate indexes in current database

SELECT si.name
FROM sysobjects so, syscolumns sc, sysindexkeys sik, sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
GROUP BY si.name
HAVING COUNT(DISTINCT(si.id)) > 1
ORDER BY si.name

Query to fetch all indexes on all user defined tables in current database

SELECT so.name TbName, sc.name ColName, si.name IxName
FROM sysobjects so, syscolumns sc, sysindexkeys sik, sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
ORDER BY TbName, ColName

Query to fetch all user defined tables in current database along with it's column name, datatype, length and collation

SELECT ts.TABLE_NAME, cs.COLUMN_NAME, cs.DATA_TYPE,
cs.CHARACTER_MAXIMUM_LENGTH, cs.COLLATION_NAME
FROM INFORMATION_SCHEMA.TABLES ts, INFORMATION_SCHEMA.COLUMNS cs
WHERE ts.TABLE_NAME != 'sysdiagrams'
AND cs.TABLE_NAME = ts.TABLE_NAME
AND cs.COLLATION_NAME IS NOT NULL
ORDER BY ts.TABLE_NAME, cs.COLUMN_NAME

Query to fetch all user defined tables in current database along with total number of rows in it

SELECT so.name, MAX(si.rows)
FROM sysobjects so, sysindexes si
WHERE so.xtype = 'U'
AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC

Query to fetch all user defined tables in current database

1 - SELECT * FROM sys.tables
2 - SELECT table_name FROM INFORMATION_SCHEMA.TABLES

Query to check wheather tamporary table exist or not?

IF
object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP
TABLE #MyTempTable
END