SQL Server features/commands that most developers are unaware of.

Question

Possible Duplicate:
Hidden Features of SQL Server

I believe that this question has incorrectly been closed as an exact duplicate. 19 people have this question favorited so I do not believe this is an incorrect assumption. In the question, I am not asking about hidden features as in the other question, I'm ask about features/commands that most developers should probably know, but are unaware of. Please vote to reopen this question. It is Community Wiki so there is no reason why it should be closed. Thanks

I've worked as a .NET developer for a while now, but predominantly against a SQL Server database for a little over 3 years now. I feel that I have a fairly decent grasp of SQL Server from a development standpoint, but I ashamed to admit that I just learned today about "WITH TIES" from this answer - http://stackoverflow.com/questions/1236394/top-5-with-most-friends/1236414#1236414.

It is humbling to see questions and answers like this on SO because it helps me realize that I really don't know as much as I think I do and helps re-energize my will to learn more, so I figured what better way than to ask the masses of experts for input on other handy commands/features.

What is the most useful feature/command that the average developer is probably unaware of?

BTW - if you are like I was and don't know what "WITH TIES" is for, here is a good explanation. You'll see quickly why I was ashamed I was unaware of it. I could see where it could be useful though. - http://harriyott.com/2007/06/with-ties-sql-server-tip.aspx

I realize that this is a subjective question so please allow for at least a few answers before you close it. :) I'll try to edit my question to keep up a list with your response. Thanks

[EDIT] - Here is a summary of the responses Please scroll down for more information. Thanks again guys/gals.

• MERGE - A single command to INSERT / UPDATE / DELETE into a table from a row source.
• 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
• CAST - get a date without a time portion
• Group By - I gotta say you should definitely know this already
• SQL Server Management Studio
• Transactions
• The sharing of local scope temp tables between nested procedure calls
• INSERT INTO
• MSDN
• JOINS
• PIVOT and UNPIVOT
• WITH(FORCESEEK) - forces the query optimizer to use only an index seek operation as the access path to the data in the table.
• FOR XML
• COALESCE
• How to shrink the database and log files
• Information_Schema
• SET IMPLICIT_TRANSACTIONS in Management Studio 2005
• Derived tables and common table expressions (CTEs)
• OUTPUT clause - allows access to the "virtual" tables called inserted and deleted (like in triggers)
• CTRL + 0 to insert null
• Spacial Data in SQL Server 2008

Answer 1

It sounds silly to say but I've looked a lot of queries where I just asked myself does the person just not know what GROUP BY is? I'm not sure if most developers are unaware of it but it comes up enough that I wonder sometimes.

Answer 2

• 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`
  

Answer 3

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

Answer 4

Duplicate of this.

Answer 5

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

Answer 6

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

BEGIN TRAN
...
COMMIT / ROLLBACK

Answer 7

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

Answer 8

use ctrl-0 to insert a null value in a cell

Answer 9

The documentation.

Sad to say, but I have come to the conclusion that the most hidden feature that developers are unaware of is the documentation on MSDN. Take for instance a Transact-SQL verb like RESTORE. The BOL will cover not only the syntax and arguments of RESTORE. But this is only the tip of the iceberg when it comes to documentation. The BOL covers:

• the in depth fundamentals of recovery: Understanding How Restore and Recovery of Backups Work in SQL Server.
• end-to-end scenarios on how to deploy a recovery strategy: Implementing Restore Scenarios for SQL Server Databases.
• the issues around system databases: Considerations for Backing Up and Restoring System Databases.
• optimizing the recovery procedures: Optimizing Backup and Restore Performance in SQL Server.
• understanding how to to a restore. Backing Up and Restoring How-to Topics (Transact-SQL).
• more corner cases and uncommon scenarios, there are examples like Example: Piecemeal Restore of Only Some Filegroups (Full Recovery Model).

The list goes on and on, and this is just one single topic (backup and restore). Every feature of SQL Server gets similar coverage. Reckon not everything will get the detail backup and recovery gets, but everything is documented and there are How To topics for every feature.

The amount of information available is just ludicrous. Yet the documentation is one of the most underused resources, hence my vote for it being a hidden feature.

Answer 10

Why am I tempted to say JOINS?

Derived tables are one of my favorites. They perform so much better than correlated subqueries but may people continue to use correlated subqueries instead.

Example of a derived table:

select f.FailureFieldName, f.RejectedValue, f.RejectionDate,
         ft.FailureDescription, f.DataTableLocation, f.RecordIdentifierFieldName,
         f.RecordIdentifier , fs.StatusDescription 
    from dataFailures f
    join(select max (dataFlowinstanceid) as dataFlowinstanceid 
      from dataFailures 
      where dataflowid = 13)a 
    on f.dataFlowinstanceid = a.dataFlowinstanceid
    join FailureType ft on f.FailureTypeID = ft.FailureTypeID
    join FailureStatus fs on f.FailureStatusID = fs.FailureStatusID

Answer 11

PIVOT and UNPIVOT

Answer 12

WITH (FORCESEEK) which forces the query optimizer to use only an index seek operation as the access path to the data in the table.

Answer 13

FOR XML

Answer 14

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

Answer 15

BACKUP LOG <DB_NAME> WITH TRUNCATE_ONLY

DBCC_SHRINKDATABASE(<DB_LOG_NAME>, <DESIRED_SIZE>)

When I started to manage very large databases on MS SQL Server and the log file had over 300 GB this statements saved my life. In most cases the shrink database will have no effect.

Before running them be sure to make full backup of LOG, and after running them to do a full backup of DB (restore sequence is no longer valid).

Answer 16

The "Information_Schema" gives me a lot of views that I can use to gather information about the SQL objects tables, procedures, views, etc.

Answer 17

If you are using Management Studio 2005 you can have it automatically execute your query as a transaction. In a new query window go to Query->Query Options. Then click on the ANSI "tab" (on the left). Check SET IMPLICIT_TRANSACTIONS. Click OK. Now if you run any query in this current query window it will run as a transaction and you must manually ROLLBACK or COMMIT it before continuing. Additionally, this only works for the current query window; pre-existing/new query windows will need to have the option set.

I've personally found it useful. However, it's not for the faint of heart. You must remember to ROLLBACK or COMMIT your query. It will NOT tell you that you have a pending transaction if you switch to a different query window (or even a new one). However, it will tell you if you try to close the query window.

Answer 18

Most SQL Server developers should know about and use derived tables and common table expressions (CTEs).

Answer 19

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

Answer 20

Spacial Data in SQL Server 2008 i.e. storing Lat/Long data in a geography datatype and being able to calculate/query using the functions that go along with it.

It supports both Planar and Geodetic data.

Answer 21

How about materialised views? Add a clustered index to a view and you effectively create a table containing duplicate data that is automatically updated. Slows down inserts and updates because you are doing the operation twice but you make selecting a specific subset faster. And apparently the database optimiser uses it without you having to call it explicitly.

http://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query

Answer 22

When I first started working as programmer, I started with using SQL Server 2000. I had been taught DB theory on Oracle and MySQL so I didn't know much about SQL Server 2000.

But, as it turned out nor did the development staff I joined because they didn't know that you could convert datetime (and related) data types to formatted strings with built in functions. They were using a very inefficient custom function they had developed. I was more than happy to show them the errors of their ways... (I'm not with that company anymore... :-D)

With that annotate:

So I wanted to add this to the list:

 select Convert(varchar, getdate(), 101) -- 08/06/2009
 select Convert(varchar, getdate(), 110) -- 08-06-2009

These are the two I use most often. There are a bunch more: CAST and CONVERT on MSDN