Auto Number / Sequence Generator in SQL Server

This magic code will create a sequence of numbers that can be used like a primary key without having to use a table's IDENITITY column.

SELECT ROW_NUMBER() OVER (ORDER BY col1) AS rn, * FROM tbl1 

Using CTE (Common Table Expression)

To create a collection of rows without querying a table or a repeating series of INSERT statements.

declare @seed int
select @seed = 8;

WITH RecurseTable(Id)
AS
(
SELECT @seed

UNION ALL

SELECT R.Id -1
FROM RecurseTable R
WHERE R.Id > 1
)

select *
from RecurseTable

Querying Table Definitions

A query to return metadata about a table. This is useful for dynamic query building like in a SqlAdapter, for example.

SELECT C.COLUMN_NAME,
       C.DATA_TYPE,
       C.CHARACTER_MAXIMUM_LENGTH,
       CASE WHEN PK.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END IS_KEY,
       CASE WHEN IC.object_id IS NULL THEN 0 ELSE 1 END IS_IDENTITY
FROM INFORMATION_SCHEMA.COLUMNS C
       LEFT OUTER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK ON C.TABLE_NAME = PK.TABLE_NAME
                                      AND C.COLUMN_NAME = PK.COLUMN_NAME
       INNER JOIN
sys.tables T ON C.TABLE_NAME = T.name
            AND T.[type] = 'U'
       LEFT OUTER JOIN
sys.identity_columns IC ON T.object_id = IC.object_id
                       AND IC.[name] = C.COLUMN_NAME
WHERE C.TABLE_NAME = @TableName

Efficiently Rounding a SQL Server datetime

Truncates hours, minutes and seconds without messy string manipulation:

CAST(FLOOR(CAST(GETDATE() AS DECIMAL(8,2))) AS datetime)

Underdocumented SQL Server mail parameter

Due to popular demand, Microsoft added the @query_result_no_padding to the sp_send_dbmail stored procedure.

Sadly, you wont find this in the online help. It was added as part of a Service Pack.

Use this parameter to trim excess spaces from the query results attached to the email. Otherwise, the column output pads out to the length of the char OR varchar field.

For example:
declare @t TABLE (field varchar(10))

insert into @t
select 'hello'

When sent with @query_result_no_padding = 1
the result is:

field
-----
hello

otherwise

field
----------
hello

Decode HTML Strings in SQL Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- EXEC UrlDecode '%c3%a1'
-- EXEC UrlDecode '\\Tul1ciprmw1\RichMediaAssets\Open\Telemundo\Al Rojo Vivo con Mar%c3%ada Celeste\_derived_jpg_q90_172x128_m1\3ARValx05Mar%c3%ada Celeste Arrar%c3%a1s.jpg'
-- EXEC UrlDecode '%4D'

ALTER PROCEDURE UrlDecode
-- Add the parameters for the stored procedure here
@URL varchar(2000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Position INT,
@Base CHAR(16),
@High TINYINT,
@Low TINYINT,
@NewHigh INT,
@Pattern CHAR(21)

SELECT @Base = '0123456789abcdef',
@Pattern = '%[%][0-9a-f][0-9a-f]%',
@Position = PATINDEX(@Pattern, @URL)

SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base) -1,
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base) -1

WHILE @Position > 0
BEGIN

IF (@High & 15) = 12 -- xC0
BEGIN
SELECT @NewHigh = @Low * POWER(2, 6) --Shift Low 6 bits

SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 4, 1), @Base) -1
SELECT @Low = CHARINDEX(SUBSTRING(@URL, @Position + 5, 1), @Base) -1

SELECT @URL = STUFF(@URL, @Position, 6, CHAR(@NewHigh | (16 * @High) | @Low))

SELECT @High = 0, @Low = 0, @NewHigh = 0

END
ELSE
BEGIN
SELECT @URL = STUFF(@URL, @Position, 3, CHAR(16 * @High | @Low))

END

SELECT @Position = PATINDEX(@Pattern, @URL)
IF @Position > 0
BEGIN
SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base) -1,
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base) -1
END
END

SELECT @URL

END
GO

Selecting XPath Nodes

To select a range of nodes based on their position, use the really cool position() function.

Using the code example below, this syntax will get the first two nodes:

var nodeList = xmlDoc.selectNodes("/root/items/item[position()<3]");