SQL Server

Memory prompts and snippets, for useful SQL Server stuff.

Date Formatting

Cast a date to a string, using SQL-Server’s frankly ridiculous magic codes to represent date formats.  Full info here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

SELECT u.UserName,
       CONVERT(VARCHAR(30), u.DateRegistered, 127) DateRegStr
FROM   Users u;

Transactions

If you’re unsure about the havoc you’re about to unleash on the DB, wrap your statement in a transaction, this will allow you to rollback (undo) what you’ve done, if an error has occurred – note transaction ids are case sensitive.

BEGIN TRAN T1
   UPDATE users SET username = 'iain' WHERE userid = 16;

-- You can now check the result
SELECT * FROM users WHERE userid = 16;

-- If you're happy
COMMIT TRAN T1;

-- If error has occurred
ROLLBACK TRAN T1;

Returning Top n results

If you only want to return the first n rows from a query, use the TOP statement:

SELECT TOP(2) u.*
FROM   users u
WHERE  u.surname = 'Hunter';

Declare Variables

Variables can be declared in a number of ways

DECLARE @ParentSocialGraphId AS INT = 1;
DECLARE @ParentHierarchyId AS HIERARCHYID;
DECLARE @Theme VARCHAR(25) = 'HorribleHistories';
DECLARE @userid INT, @username VARCHAR(50), @dateRegStr VARCHAR(30);

DDL

Basic DDL statements for creating tables etc, without using the IDE

Create Table

CREATE TABLE Hotels(
    HotelId INT PRIMARY KEY IDENTITY,
    HotelName VARCHAR(50));

Create new User

SQL Server has a confusing method of creating and assigning a login to a new user.  Anyway here’s the 3 statements that will create a new user who can access everything.

CREATE LOGIN iainLogin WITH password='passw0rd';
CREATE USER iain FROM LOGIN iainLogin;
EXEC sp_addrolemember 'db_owner', iain;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s