TSQL Tip for the Day

I'm reading TSQL 2008 Programmer's guide. Today we will look at two tips

Tip #1

TABLESAMPLE clause -  Select random records from table. It works by sampling the rows from query results.

Example - Randomly Select 5 Percent of Records. Below query based on table created in previous post

SELECT
EmployeeId,
EmployeeName,
DeptId
FROM Employee
TABLESAMPLE (5 PERCENT);

Observation - 5% records returned were contiguous / sequential records. For Generating test data from production this can be used.

Tip #2

NEWSEQUENTIALID function - Generates sequential GUID. This would be useful if you decide to use GUID column as primary key, Data would be in increasing fashion. This avoids possibility to reorder / rearranging indexes when data is inserted.

Reusing schema definiton from Employee Table in previous post

use AppDB

GO
IF OBJECT_ID ('Employee') IS NOT NULL DROP TABLE Employee
CREATE TABLE Employee
(
  SSNNo uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY WITH (FillFactor = 70),
  EmployeeName CHAR(100) NOT NULL,
  DeptId  smallint NOT NULL,
  CreatedDate  DateTime NOT NULL,
  ModifiedDate  DateTime NOT NULL
  )

Related Read - GUIDs as PRIMARY KEYs and/or the clustering key

Happy Reading!!