PK_Tests.sql

-- Setup
CREATE TABLE Test_INT ([Id] [int] NOT NULL primary key clustered,
                            [col1] [int] NULL,
                            [col2] [int] NULL,
                            [col3] [varchar](50) NULL); 

CREATE TABLE Test_GUID ([Id] [uniqueidentifier] NOT NULL primary key clustered,
                            [col1] [int] NULL,
                            [col2] [int] NULL,
                            [col3] [varchar](50) NULL); 


CREATE TABLE Test_VARCHAR ([Id] varchar(36) NOT NULL primary key clustered,
                            [col1] [int] NULL,
                            [col2] [int] NULL,
                            [col3] [varchar](50) NULL); 

DECLARE @val INT
SELECT @val=1
WHILE @val < 100000
BEGIN  
   INSERT INTO test_INT (Id, col1, col2, col3) 
     VALUES (@val,round(rand()*100000,0),round(rand()*100000,0),'TEST' + CAST(@val AS VARCHAR))

   INSERT INTO test_GUID (Id, col1, col2, col3) 
     VALUES (newid(),round(rand()*100000,0),round(rand()*100000,0),'TEST' + CAST(@val AS VARCHAR))

	 
   INSERT INTO Test_VARCHAR (Id, col1, col2, col3) 
     VALUES (newid(),round(rand()*100000,0),round(rand()*100000,0),'TEST' + CAST(@val AS VARCHAR))

   SELECT @val=@val+1
END
GO 


-- SELECT
SELECT * FROM Test_INT WHERE Id=1750
GO
SELECT * FROM Test_GUID WHERE Id='9DEE1ED9-50A8-4887-8BE7-00BCDDA28113'
GO
SELECT * FROM Test_VARCHAR WHERE Id='0095797E-2F53-43B3-9127-C00AD87440D1'
GO

-- INSERT
INSERT INTO Test_INT (Id, col1,  col2, col3) 
   VALUES (5000001, round(rand()*100000,0),round(rand()*100000,0),'TEST' + CAST(5000001 AS VARCHAR))
GO
INSERT INTO Test_GUID (Id, col1,  col2, col3) 
   VALUES (newid(), round(rand()*100000,0),round(rand()*100000,0),'TEST' + CAST(5000001 AS VARCHAR))
GO
INSERT INTO Test_VARCHAR (Id, col1,  col2, col3) 
   VALUES (newid(), round(rand()*100000,0),round(rand()*100000,0),'TEST' + CAST(5000001 AS VARCHAR))
GO

-- UPDATE
UPDATE Test_INT SET col1=232342,col3=232340,col4='TESTUPDATE'
WHERE col1=3023481
GO
UPDATE Test_GUID SET col1=232342,col3=232340,col4='TESTUPDATE'
WHERE col1='FA7B4737-70ED-49EA-BD26-19B737294C9D'
GO
UPDATE Test_VARCHAR SET col1=232342,col3=232340,col4='TESTUPDATE'
WHERE col1='FA7B4737-70ED-49EA-BD26-19B737294C9D'
GO

-- DELETE
DELETE FROM Test_INT WHERE Id=2789341
GO
DELETE FROM Test_GUID WHERE Id='477F0D8E-9B70-4573-8E67-006FC7797C27'
GO
DELETE FROM Test_VARCHAR WHERE Id='0055E12C-2095-4166-83B5-719B9A2957D0'
GO 


-- Metrics/Stats
SELECT OBJECT_NAME(i.[object_id]) AS TableName,
       i.[name] AS IndexName,
       SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.[index_id] = i.[index_id]
WHERE OBJECT_NAME(i.[object_id]) like '%Test%'
GROUP BY i.[name],i.[object_id];


SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
       ind.name AS IndexName, 
       indexstats.index_type_desc AS IndexType, 
       indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id 
WHERE OBJECT_NAME(ind.OBJECT_ID) like '%Test%'
ORDER BY indexstats.avg_fragmentation_in_percent DESC 


-- MISC
SELECT * FROM Test_INT
SELECT * FROM Test_GUID
SELECT * FROM Test_VARCHAR
SELECT COUNT(*) FROM Test_VARCHAR



-- RESET
DROP TABLE Test_INT
DROP TABLE Test_GUID
DROP TABLE Test_VARCHAR