exec sp_who3
exec sp_whoisactive
drop partition scheme CoreCollectionPartitionScheme
drop PARTITION FUNCTION [SwimlaneRangePartitionFunction]
CREATE PARTITION FUNCTION [SwimlaneRangePartitionFunction] (nvarchar(100))
AS RANGE RIGHT FOR VALUES ('LA4PRDCLT101', 'LA4PRDCLT201', 'LA4PRDCLT301',
'LA4PRDCLT401', 'LD4PRDCLT101', 'LD4PRDCLT102', 'LD4PRDCLT201');
CREATE PARTITION SCHEME CoreCollectionPartitionScheme
AS PARTITION SwimlaneRangePartitionFunction
TO ([primary],sl1,sl2,sl3,sl4,sl5,sl51,sl6);
GO
drop table [user_demographics_partition_by_swimlane]
go
CREATE TABLE [dbo].[user_demographics_partition_by_swimlane](
[user_id] [int] NULL,
[swimlane] [nvarchar](100) NULL,
[portal] [nvarchar](200) NULL,
[status_id] [int] NULL,
[user_type_id] [int] NULL,
[gender] [varchar](100) NULL,
[user_country] [varchar](100) NULL,
[user_state] [nvarchar](50) NULL,
[user_zipcode] [nvarchar](50) NULL,
[birth_dt] [datetime] NULL,
[hire_dt] [datetime] NULL,
[term_dt] [datetime] NULL,
[timezone] [int] NULL,
[date_stamp] [datetime] NULL,
[user_country_profile_base] [varchar](100) NULL,
[user_state_profile_base] [nvarchar](50) NULL,
[user_zipcode_profile_base] [nvarchar](50) NULL
) ON CoreCollectionPartitionScheme ([swimlane])
GO
1 comment:
--to find out tables that are partitioned
SELECT t.name AS TableName, ps.name AS PartitionScheme,
ps.data_space_id, pf.name AS PartitionFunction, pf.function_id , i.index_id
FROM sys.TABLES t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
--to find out the partition boundries
SELECT r.boundary_id, r.VALUE FROM sys.partition_range_values r
JOIN sys.partition_functions pf ON r.function_id = pf.function_id
WHERE pf.name = 'SwimlaneRangePartitionFunction'
ORDER BY r.VALUE
--To determine the boundary values for a partitioned table
SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.partitions AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s
ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE t.name = 'user_demographics_partition_by_swimlane' AND i.type <= 1
ORDER BY p.partition_number;
--To determine the partition column for a partitioned table
SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id as PartitioningColumnID, c.name AS PartitioningColumnName
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.columns AS c
ON t.object_id = c.object_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal > 0
WHERE t.name = 'user_demographics_partition_by_swimlane'
AND i.type <= 1
AND c.column_id = 1;
--to find out record counts on each partition
select b.name, a.* , b.*
From sys.dm_db_partition_stats a
inner join sys.indexes b on a.object_id = b.object_id and b.index_id = a.index_id
where OBJECT_NAME(a.object_id) = 'user_demographics_partition_by_swimlane'
--Getting the number of rows in each nonempty partition of a partitioned table or index
SELECT $PARTITION.iislogs_partitinfunction_weekly(EntryDate) AS Partition,
COUNT(*) AS [COUNT] FROM dbo.IISLogs
GROUP BY $PARTITION.iislogs_partitinfunction_weekly(EntryDate)
ORDER BY Partition ;
GO
--Returning all rows from one partition of a partitioned table or index
SELECT top 10 *
FROM dbo.IISLogs
WHERE $PARTITION.iislogs_partitinfunction_weekly(EntryDate) = 23
--Returning all rows from one partition of a partitioned table or index
SELECT top 10 *
FROM dbo.IISLogs
WHERE $PARTITION.iislogs_partitinfunction_weekly(EntryDate) = 23
order by entrydate desc
SELECT top 10 *
FROM IISLogs
WHERE entrydate > '2013-01-21';
select count(*) From dbo.IISLogs
Post a Comment