Performance Tuning - Unicode Datatypes
use tempdb
GO
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#UnicodeTest1') IS NOT NULL
DROP TABLE #UnicodeTest1
IF OBJECT_ID('tempdb..#UnicodeTest2') IS NOT NULL
DROP TABLE #UnicodeTest2
CREATE TABLE #UnicodeTest1 (ID VARCHAR(50))
CREATE TABLE #UnicodeTest2 (ID NVARCHAR(50))
INSERT INTO #UnicodeTest1
SELECT RAND()
GO 30000
INSERT INTO #UnicodeTest2
SELECT *
FROM #UnicodeTest1
SELECT t.NAME AS TableName
,s.Name AS SchemaName
,p.rows AS RowCounts
,SUM(a.total_pages) * 8 AS TotalSpaceKB
,CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB
,SUM(a.used_pages) * 8 AS UsedSpaceKB
,CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB
,(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
,CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME LIKE '#UnicodeTest%'
GROUP BY t.Name
,s.Name
,p.Rows
ORDER BY t.Name
SET STATISTICS IO ON
SELECT ID
FROM #UnicodeTest1
SELECT ID
FROM #UnicodeTest2
SET STATISTICS IO OFF