Various SQL Server Notes

Auto-increment field in SQL Server.
To create an auto increment field in SQL server, create an integer field and within its “Identity Specification” property group, set the following (note you may also want to set this field to be the primary key):
Is Identity – yes
Identity Increment – 1
Identity Seed – 1

UDL Files
An easy way to create a database connection string is to create a blank file with a .udl extension. When you open this file (by double clicking) you will be presented with dialog to help you create a connection string. When done, simple view the contents of the udl file in notepad to see the contents.

Simulating offset and limit with SQL Server 2005
In MySql, you can specify a limit and offset on a select statement. This is incredibly useful for pagination etc. In SQL Server, there is the TOP keyword which is equivalent to LIMIT, however there is no easy way of specifying an offset (well, none until SQL Server 2005).

In SQL Server 2005, a function named ROW_NUMBER has been introduced. This is used to indicate the position of records within a SELECT result. An example is set out below (demonstrating how to use ROW_NUMBER) for pagination. Note that with this example, if there are only 15 records in the overall query, only 5 will be returned.

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY orderByField ASC) AS RowNumber, *
FROM tableName)
SELECT *
FROM Ordered
WHERE RowNumber BETWEEN 10 AND 20

Note that when running this via the server explored in Visual Studio, I got a warning “The OVER SQL construct or statement is not supported.”, however it works anyway (and when executed within code, there are no issues)

There are ways of almost achieving a similar results in SQL Server 2000, although these are based on using the top keyword – the last page still always returns a fixed number of result when used for pagination, and not less as above. See http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/ for details. (note that there’s an interesting post on here by Mathew Waters, although I couldn’t get his idea to work – as I already had an Identity field). Note that you could maybe use cursors to get the desired result.

Some related links…
http://weblogs.asp.net/Firoz/archive/2005/06/12/411949.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenethowto05.asp
http://www.extremeexperts.com/SQL/Scripts/SQLRowNum.aspx

Delimiting
Fieldnames are delimited by [] in SQL Server (TSQL), ยด in MySql,
In both SQL Server and MySql, values are quote normally with single quotes. Note that this is irrelevant of field type (eg can still quote if field is an int). It is good practice to delimit fields and quote values to mitigate against SQL injection hacks.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>