Continuing a series on table expressions, Itzik Ben-Gan explains recursive CTEs, features from the standard T-SQL is lacking, and workarounds.

Remember that the syntax of a query against a CTE is as follows:

WITH <CTE name> [ ( <with column list> ) ]
AS
(
<table expression>
)
<outer query>;

The BCP (Bulk Copy Program) utility is a command line that program that bulk-copies data between a SQL instance and a data file using a special format file.

Useful T-SQL queries for SQL Server to explore database schema.

select schema_name(tab.schema_id) as schema_name,
    tab.name as table_name, 
    col.column_id,
    col.name as column_name, 
    t.name as data_type,    
    col.max_length,
    col.precision
from sys.tables as tab
    inner join sys.columns as col
        on tab.object_id = col.object_id
    left join sys.types as t
    on col.user_type_id = t.user_type_id
order by schema_name,
    table_name, 
    column_id;

In this article, we briefly covered types of views in SQL Server and gave several examples of user defined views.

Creating a View Across Instances

In this article, you'll find the answers to the five most-asked questions about SQL COALESCE function in SQL Server. Explore useful examples.

The definition of COALESCE is an expression that returns the first non-null value from a list of values. The COALESCE syntax is:

COALESCE ( expression [ ,…n ] )

(4) SQL COALESCE is Converted to CASE. ISNULL Stays ISNULL

COALESCE returns the data type of the value with the highest precedence.

Locating the row to confirm it exists, only to have to locate it again in order to update it, is doing twice the work for nothing.

BEGIN TRANSACTION;

UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
END

COMMIT TRANSACTION;

SQL Server Date Formats

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format. Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERT function. Following the standard date formats are some extended date formats that are often asked by SQL Server developers.

It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type. With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Standard Date Formats

• Whenever you know that the column, or combination or columns, makes a unique key for a nonclustered index, I always recommend that you specify UNIQUE when you create the index. That information is great for the optimizer to have when generating query plans.

• Think carefully about the impact of your clustering key on your nonclustered indexes. Any non-unique nonclustered index will be larger (more pages, eventually more levels) because the entire clustering key will be in the tree level.

• If you have a clustering key with a column (or multiple columns) that are frequently modified, then those modifications have to be propagated to the tree levels of the nonclustered indexes as well.

You didn't say what version you were using, but in SQL 2005 and above, you can use a common table expression with the OVER Clause. It goes a little something like this:

WITH cte AS (
SELECT[foo], [bar],
row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn]
FROM TABLE
)
DELETE cte WHERE [rn] > 1

NULL values are special. For instance, a comparison between two NULL values, or even a comparison between a NULL value and a non-value, will always be false. This is because NULL values aren’t real values as such, but rather “unknowns”.

Say that I have a table of People, and these people have a Country column and a unique Primary Key. It's a demographics table, so these are the only things I care about; what Country and how many unique people are tied to that country.

I am thus only ever likely to SELECT WHERE or ORDER BY the Country column; a clustered index on the Primary Key doesn't do me any good, I'm not accessing this data by PK, I'm accessing it by this other column. Since I can only have one clustered index on a table, declaring my PK as Clustered would prevent me from using a Clustered Index on Country.

Therefore I thought I would give a quick explanation for anyone still unsure what a Key Lookup is actually doing and why it’s considered bad.

We all know that Non-Clustered indexes all include the “Clustered Key” of the table. If not, then you do now. And if you have no Clustered Key (ie. Your table is a Heap) then the non-clustered will contain a Row Identifier which is unique and refers back to a specific record within your Heap. This is how a non-clustered index is linked to your table… whether it be via the Clustered Key or the RID.

We often hear indexes explained using the analogy of an index in the back of a book. You want to find the information about “rabbits” for instance – and so you look that up in the back and find the list of pages that talk about rabbits.

The COALESCE and ISNULL T-SQL functions are used to return the first nonnull expression among the input arguments. SQL Server practitioners often wonder what the difference is between the two functions.