Terminology

ALTER DATABASE <database> SET MULTI_USER Make a database multiuser.

ALTER DATABASE <database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE Set database to single user. This is an effective way to quickly disconnect all users who are connected to a database.

CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER) Generate zero GUID in SQL Server.

CONVERT(VARCHAR(<width>),<variable>) Convert an integer into a string.

RAND Returns a pseudo-random float value from 0 through 1, exclusive.

ROW_NUMBER( ) OVER ( <partition_by_clause> <order_by_clause> ) Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' Query that returns all of the tables in the current database.

sp_who Provides information about current users, sessions, and processes in an instance of the SQL Server database engine.

Facts, Thoughts and Opinions

Business Intelligence Experience

BI experience in SQL Server, SSAS and SharePoint.

Data Warehousing Experience

Data warehousing experience in SQL Server.

Randomizing and Shuffling Data in SQL Server

The intuitive manner is to use RAND(), like so:

SELECT ROW_NUMBER() OVER(ORDER BY RAND()) AS [RowNumber], <otherFieldsFromTable>
FROM <someTableOrView>

However, this doesn't work because RAND() is evaluated only once per column. TO make this work, use NEWID() instead of RAND().

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

One could randomize data in this manner:

CROSS JOIN (
    SELECT COUNT(*) AS [count] FROM <tableOrView>
) <alias>Count
LEFT OUTER JOIN (
    SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) AS [RowNumber], <fields>
    FROM <tableOrView>
) <alias> ON <alias>.[RowNumber] = n.Number % <alias>Count.[count] + 1

However, NEWID() is an expensive option. If the row has an ID, you can generate one random number and use it as a modulus:

SELECT TOP 500 * FROM <tableOrView> ORDER BY [ID] % CAST(RAND()*400 AS INT)

Get a random date up to one year in the future:

SELECT DATEADD(DD, DATEDIFF(DD,0, GETDATE())+ ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 365, 0) AS [Date]

SQL Server Experience

Experience in 2000 (old), 2005, and 2008 versions. Worked with databases containing hundreds of tables. Developed new databases.

SQL Server Numbers Table

Creates a table containing all integers between 0 and 65,535 inclusive.

CREATE TABLE Numbers
(
    N INT NOT NULL,
    CONSTRAINT PK_Numbers 
        PRIMARY KEY CLUSTERED (N)
        WITH FILLFACTOR = 100
)
 
INSERT INTO Numbers
SELECT
    (a.Number * 256) + b.Number AS N
FROM 
    (
        SELECT number
        FROM master..spt_values
        WHERE type = 'P' AND number <= 255
    ) a (Number),
    (
        SELECT number
        FROM master..spt_values
        WHERE type = 'P' AND number <= 255
    ) b (Number)
GO

Another way to populate the numbers table if your database doesn't support master..spt_values.

INSERT INTO Numbers
SELECT ROW_NUMBER() OVER(ORDER BY a.object_id,b.object_id) AS N
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

Images

Baby Got Attributes
[[/div]]

  •   Writings

  Sources & Bookmarks

Name/Link Date
Baton Rouge SQL Server User Group 2012-09-08
Be Careful with the MERGE Statement
Boomerang: A notification framework for SQL Server 2014-04-30
Building a Free Virtual Machine for SQL Server
Calling a Web service from T-SQL 2015-07
Coding Best Practices: GUIDs vs Custom ID Generation 2014-09
Consuming JSON Strings in SQL Server 2014-04-29
CROSS APPLY VALUES approach to UNPIVOT
Detecting Changes to a Table
Encrypt a Column of Data in SQL Server 2014-11-18
FUNCTION dbo.Split 2015-06-01
Get detailed list of connections to a database 2015-06-05
Get the maximum value of the primary key of each table in a database. (SQL Server) 2015-07
Getting Started Testing Databases with tSQLt 2013-05-16
How to Make Scalar UDFs Run Faster
How to save results of a procedure with more than one result set 2012-09-08
Identifying and Correcting SQL Server Implicit Conversion
Integrating Dynamic Languages into Your Enterprise Applications
Keyword searches in SQL 2014-04-29
MERGE Statement Tips Pending
Practical PowerShell for SQL Server Developers and DBAs 2015-02-05
Query to return the code for an object by name 2015-06-01
Rendering PDFs Natively in SQL GCR employment
Return all objects that contain a particular string. 2015-07
Return the primary key for a particular table. (SQL Server) 2015-07
Seven Sins against T-SQL Performance
Spatial Data in SQL Server 2008
SQL Server 2014-10-22
SQL Server Blitz
SQL Server Central Stairways series
SQL Server Extended Events
SQL Server JSON to Table and Table to JSON 2013-05-08
SQL Server Secrets 2012-08-17
SQL Server Team Blog
Stairway to SQL PowerShell
Stairway to SQL Server Indexes
Stairway to SQLCLR 2015-01-20
Transparent Data Encryption and SQL Server Databases
TSQL Code Smells Finder Pending