SQL Server features/commands that most developers are unaware of [duplicate]

Question

FileStream in SQL Server 2008: FILESTREAM feature of SQL Server 2008 allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system.

Creating a Table for Storing FILESTREAM Data

Once the database has a FILESTREAM filegroup, tables can be created that contain FILESTREAM columns. As mentioned earlier, a FILESTREAM column is defined as a varbinary (max) column that has the FILESTREAM attribute. The following code creates a table with a single FILESTREAM column

USE Production;
GO
CREATE TABLE DocumentStore (
       DocumentID INT IDENTITY PRIMARY KEY,
       Document VARBINARY (MAX) FILESTREAM NULL,
       DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
              UNIQUE DEFAULT NEWID ())
FILESTREAM_ON FileStreamGroup1;
GO

• In SQL Server 2008 (and in Oracle 10g): MERGE.

  A single command to INSERT / UPDATE / DELETE into a table from a row source.

• To generate a list of numbers from 1 to 31 (say, for a calendary):

  WITH   cal AS
          (
          SELECT  1 AS day
          UNION ALL
          SELECT  day + 1
          FROM    cal
          WHERE   day <= 30
          ) 
  

• A single-column index with DESC clause in a clustered table can be used for sorting on column DESC, cluster_key ASC:

  CREATE INDEX ix_column_desc ON mytable (column DESC)
  
  SELECT  TOP 10 *
  FROM    mytable
  ORDER BY
          column DESC, pk
  -- Uses the index
  
  SELECT  TOP 10 *
  FROM    mytable
  ORDER BY
          column, pk
  -- Doesn't use the index
  

• CROSS APPLY and OUTER APPLY: enables to join rowsources which depend on the values of the tables being joined:

  SELECT  *
  FROM    mytable
  CROSS APPLY
          my_tvf(mytable.column1) tvf
  
  SELECT  *
  FROM    mytable
  CROSS APPLY
          (
          SELECT  TOP 5 *
          FROM    othertable
          WHERE   othertable.column2 = mytable.column1
          ) q
  

• EXCEPT and INTERSECT operators: allow selecting conditions that include NULLs

  DECLARE @var1 INT
  DECLARE @var2 INT
  DECLARE @var3 INT
  
  SET @var1 = 1
  SET @var2 = NULL
  SET @var2 = NULL
  
  SELECT  col1, col2, col3
  FROM    mytable
  INTERSECT 
  SELECT  @val1, @val2, @val3
  
  -- selects rows with `col1 = 1`, `col2 IS NULL` and `col3 IS NULL`
  
  SELECT  col1, col2, col3
  FROM    mytable
  EXCEPT 
  SELECT  @val1, @val2, @val3
  
  -- selects all other rows
  

• WITH ROLLUP clause: selects a grand total for all grouped rows

  SELECT  month, SUM(sale)
  FROM    mytable
  GROUP BY 
          month WITH ROLLUP
  
  Month   SUM(sale)
    ---        ---
    Jan        10,000
    Feb        20,000
    Mar        30,000
   NULL        60,000  -- a total due to `WITH ROLLUP`
  

It's amazing how many people work unprotected with SQL Server as they don't know about transactions!

BEGIN TRAN
...
COMMIT / ROLLBACK

After creating a #TempTable in a procedure, it is available in all stored procedures that are then called from from the original procedure. It is a nice way to share set data between procedures. see: http://www.sommarskog.se/share_data.html

COALESCE() , it accepts fields and a value to use incase the fields are null. For example if you have a table with city, State, Zipcode you can use COALESCE() to return the addresses as single strings, IE:

City | State | Zipcode

Houston | Texas | 77058

Beaumont | Texas | NULL

NULL | Ohio | NULL

if you were to run this query against the table:

select city + ‘  ‘ + COALESCE(State,’’)+ ‘  ‘+COALESCE(Zipcode, ‘’)

Would return:

Houston Texas 77058

Beaumont Texas

Ohio

You can also use it to pivot data, IE:

DECLARE @addresses VARCHAR(MAX)
SELECT @addresses = select city + ‘  ‘ + COALESCE(State,’’)+ ‘  ‘
+COALESCE(Zipcode, ‘’)             + ‘,’ FROM tb_addresses
SELECT @addresses 

Would return: Houston Texas 77058, Beaumont Texas, Ohio

A lot of SQL Server developers still don't seem to know about the OUTPUT clause (SQL Server 2005 and newer) on the DELETE, INSERT and UPDATE statement.

It can be extremely useful to know which rows have been INSERTed, UPDATEd, or DELETEd, and the OUTPUT clause allows to do this very easily - it allows access to the "virtual" tables called inserted and deleted (like in triggers):

DELETE FROM (table)
OUTPUT deleted.ID, deleted.Description
WHERE (condition)

If you're inserting values into a table which has an INT IDENTITY primary key field, with the OUTPUT clause, you can get the inserted new ID right away:

INSERT INTO MyTable(Field1, Field2)
OUTPUT inserted.ID
VALUES (Value1, Value2)

And if you're updating, it can be extremely useful to know what changed - in this case, inserted represents the new values (after the UPDATE), while deleted refers to the old values before the UPDATE:

UPDATE (table)
SET field1 = value1, field2 = value2
OUTPUT inserted.ID, deleted.field1, inserted.field1
WHERE (condition)

If a lot of info will be returned, the output of OUTPUT can also be redirected to a temporary table or a table variable (OUTPUT INTO @myInfoTable).

Extremely useful - and very little known!

Marc

There are a handful of ways to get a date without a time portion; here's one that is quite performant:

SELECT CAST(FLOOR(CAST(getdate() AS FLOAT))AS DATETIME) 

Indeed for SQL Server 2008:

SELECT CAST(getdate() AS DATE) AS TodaysDate