MS SQL 2005 Tips

2 posts / 0 new
Last post
benzzon
benzzon's picture
MS SQL 2005 Tips
Sorting SQL Project Files in SQL Server Management Studio:

Enisey - add-in to SQL Server Management Studio (generate insert-statements):

Run Scripts Add-In for SQL Server 2005 Management Studio
 
benzzon
benzzon's picture
.

*** Test of "table partitioning".. This test did not give me any performance increase, but it might

come in handy in future if several disk partitions also are used..
(16 partitions is generated for the table, split on first character of the GUID-field..)
 
 

-- *** 1:

CREATE PARTITION FUNCTION ptnCR_UserData_T2_FI2SPACE_CO_ID (varchar(36))

 AS RANGE LEFT FOR

  VALUES ('1','2','4','5','6','8')  

-- DROP PARTITION FUNCTION ptnCR_UserData_T2_FI2SPACE_CO_ID

 

-- *** 2:

CREATE PARTITION SCHEME scmCR_UserData_T2_FI2SPACE_CO_ID AS

 PARTITION ptnCR_UserData_T2_FI2SPACE_CO_ID ALL TO ([PRIMARY])

-- DROP PARTITION SCHEME scmCR_UserData_T2_FI2SPACE_CO_ID

 

 

-- *** 3:

-- create table with ending statement "ON scmCR_UserData_T2_FI2SPACE_CO_ID (FI2SPACE_CO_ID)"

-- *** 4:

-- Fill table with data using ordinary "insert"..

 

-- *** 5: Check partition structure of table, and nr of records:

SELECT * FROM sys.partitions

 WHERE OBJECT_ID = OBJECT_ID('tblCR_UserData_T2')