New Features in SQL Server 2008

IntelliSense for Query Editing IntelliSense offers a few additional features besides just completing the world. You can see those options from SSMS List Members Parameter Info Quick Info Complete Word Refresh Local Cache Table-Value Parameter In many situations, it is necessary to pass a set of table structured values to a stored procedure or function. These values may be used for updating/population a table. CREATE TYPE Customer AS TABLE ( CustomerId INT IDENTITY(1,1), CustomerName VARCHAR(30) ); GO DECLARE @T as Customer INSERT INTO @T(CustomerName) VALUES ('a'),('b'),('c'),('d'),('e') SELECT * FROM @T Grouping Sets Grouping Sets is an extension to the GROUP BY clause that lets users define multiple grouping in the same query. Grouping Sets produce a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster. Example SELECT year (order_date) AS Year, quarter (order_date) AS Quarter, COUNT (*) AS Orders FROM sales_order GROUP BY GROUPING SETS ((Year, Quarter), (Year)) ORDER BY Year, Quarter File Stream data

Difference between count(*) and count(columnname) in Sql

COUNT(*) returns the number of items in a group, including NULL values and duplicates.COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values. COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values. COUNT(*) : return total number of rows in a table COUNT(ColumnName) : return total number of rows from a table WHERE ColumnName IS NOT NULL COUNT(DISTINCT ColumnName): return total number of rows from table but ignores duplicates values and null values. COUNT(1) : return total number of rows in a table

Check Constraint in SQL-Server

A check constraint is a rule that identifies acceptable column values for data in a row within a SQL Server table. The CHECK constraint ensures that all values in a column satisfy certain conditions. Example We will create an Employee table with Check constraint, Employee Table contain Employee ID, Name and Age. We set Check constraint property on Age column. Age should not be less than 18 Create table of Employee as follows.

Rank function in SQL Server

The ROW_NUMBER () function in SQL Server returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. The RANK() function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties. The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties. The NTILE() function in SQL Server return distributes the rows in an ordered partition into a specified number of groups.

Date and Time Formatting Scripts in Management Studio Query Editor (SQL Server)

-- Get date only from datetime - QUICK SYNTAX SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, CURRENT_TIMESTAMP)) -- 2016-10-23 00:00:00.000 ------------ -- SQL Server T-SQL date & datetime formats - Gregorian calendar - Christian calendar -- getdate() / CURRENT_TIMESTAMP(ANSI) returns system date & time in standard format -- SQL datetime formats with century (YYYY or CCYY format)- sql date & time format SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM) -- Oct 2 2010 11:01AM SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010 SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.10.02 SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy