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
- Subtopics
- Transact-SQL (T-SQL)
- Writings
Sources & Bookmarks