:::: MENU ::::

Posts Categorized / Table Partitioning

  • Aug 31 / 2017
  • 0
T-SQL Scripts, Table Partitioning

Verify next month partitions in all databases

I have databases with partitioned tables in some databases. I occasionally had to verify that next month partition is there in each database. It required to check in each database one by one. To improve the process i created a script that parses the databases with partitioned tables one by one and checks either partition for next month data is there or not.

Specifications

My created script is specific to scenario and following are the specifications

  1. All partitioned tables in a DB are created on a single partition scheme. Hence there is only one partition scheme and partition function in a DB.
  2. Partitions are created with one month span. i.e. Each partition has one month data.
  3. Partitions are created with right boundary (less than)
  4.  Script checks either partition for next months data is added or not

Script



SET NOCOUNT ON
GO

Print 'Report for partitioning status on '+@@servername
-- Create tempDB to hold DB names with partitioned tables
IF OBJECT_ID(N'tempdb.dbo.#DBs') IS NOT NULL
	DROP TABLE #DBs

CREATE TABLE #DBs (DBName VARCHAR(50))

-- Insert DB names with partitioned tables
INSERT INTO #DBs (DBName)
EXEC sp_msforeachdb 
'use ?; if exists (select top 1 * from ?.[sys].[partitions] where partition_number >1) select db_name()'
GO

-- Declare variables
DECLARE @DB_Name VARCHAR(50)
DECLARE @Command NVARCHAR(1000)
-- Declare cursor for checking DBs one by one
DECLARE database_cursor CURSOR
FOR

SELECT DBname
FROM #DBs

OPEN database_cursor

FETCH NEXT
FROM database_cursor
INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Prepare sql for last partition check
	SELECT @Command = 'use ' + @DB_Name + ';
	 if (SELECT convert(bigint, max(r.value) ) 
	 FROM [sys].[partition_range_values] r inner join [sys].[partition_functions] f
  on r.function_id = f.function_id) 
  < (select CONVERT([bigint],replace
  (replace(CONVERT([varchar](13),DATEADD(DAY, 1,(DATEADD(MONTH,1 , EOMONTH(getdate())))),(121)),''-'',''''),'' '','''')+''00''))
  Print ''Partitioning issue in DB ' + @DB_Name + ''';' + 'else print ''Partitioning Ok in DB ' + @DB_Name + ''' ;'

	--print @Command;
	EXEC sp_executesql @Command;

	FETCH NEXT
	FROM database_cursor
	INTO @DB_Name
END

CLOSE database_cursor

DEALLOCATE database_cursor

GO

Output

Output will be in following format.

Verify next month partitions in all databases

 

  • Aug 29 / 2017
  • 0
Data Modeling and Database Design, Table Partitioning

ALTER TABLE SWITCH statement failed because column ‘%’ does not have the same ANSI trimming semantics in tables ‘%’ and ‘%’.

While trying to switch a partition from one table to another i got following error message

ALTER TABLE SWITCH statement failed because column ‘%’ does not have the same ANSI trimming semantics in tables ‘%’ and ‘%’.

It is related to difference of ANSI Padding setting between any column of both the tables involved in partition switch. Surprisingly i did not find the match of this setting as a partition switch prerequisite in BOL.

ANSI_Padding

ANSI Padding relates to storage of char, varchar, binary, and varbinary data types. It is by default ON in SQL Server unless explicitly set OFF for a connection. Best practice is to always set it ON. Data types like nchar, nvarchar, ntext, text, image, varbinary(max), varchar(max), and nvarchar(max) have always ANSI_Padding ON and cannot be changed. So we will be dealing with char, varchar, binary, and varbinary data typesto manage this setting.

Problem while switching partition

Coming back to our partition switch issue, we are required to eliminate the ANSI_Padding difference between both tables to perform the partition switch. Let us create two tables, one with ANSI_Padding ON and other with OFF.

-- Select database
USE DBDigs
GO

-- Set ANSI_Padding for first table (tbl_APOff)
SET ANSI_PADDING OFF
GO

-- Create first table (tbl_APOff)
IF EXISTS (
SELECT NAME
FROM sys.objects
WHERE type = 'U'
AND NAME = 'tbl_APOff'
)
DROP TABLE tbl_APOff
GO

CREATE TABLE tbl_APOff
(id INT identity(1, 1), firstName VARCHAR(50), lastname VARCHAR(50))
GO


-- Set ANSI_Padding for second table (tbl_APOn)
SET ANSI_PADDING ON
GO

IF EXISTS (
SELECT NAME
FROM sys.objects
WHERE type = 'U'
AND NAME = 'tbl_APOn'
)
DROP TABLE tbl_APOn
GO

-- Create second table (tbl_APOn)
CREATE TABLE tbl_APOn
(id INT identity(1, 1), firstName VARCHAR(50), lastname VARCHAR(50))
GO

Above script will create tbl_APOff with ANSI_Padding OFF and tbl_APOn with ANSI_Padding ON. Both tables are exact copies of each other except the difference of ANSI_Padding setting that can be verified with following script

-- Select database
USE DBDigs
GO

-- Check ANSI_PAdding for columns in both tables
SELECT s1.NAME AS TableAPOn, s1.Is_ANSI_Padded AS APOnSetting,
s2.NAME AS TableAPOff, s2.Is_ANSI_Padded AS APOffSetting
FROM sys.columns s1
INNER JOIN (
SELECT NAME, Is_ANSI_Padded
FROM sys.columns
WHERE object_Name(object_ID) IN ('tbl_APOff')
) AS s2 ON s1.NAME = s2.NAME
WHERE object_Name(object_ID) IN ('tbl_APOn')
ORDER BY APOnSetting
GO

Columns does not have the same ANSI trimming semantics

At this stage we have idea of issue and a script to detect and analyze the issue.

Fix

To fix the ANSI_Padding difference issue we have two primary  options.

  • ALTER the ANSI_Padding  setting of columns in one table to match other
  • Recreate one of these tables with ANSI_Padding setting matching to other one

While applying any of above approach we should prefer to make ANSI_Padding ON so that best practice can be implemented.

ALTER the ANSI_Padding  setting

Let us change the ANSI_Padding setting for both columns in tbl_APOff. This can be done with following ALTER COLUMN commands.

 
USE DBDigs
GO

SET ANSI_PADDING ON 
GO 

ALTER TABLE tbl_APOFF 
ALTER COLUMN firstName VARCHAR (50) NULL; 
GO 

ALTER TABLE tbl_APOFF 
ALTER COLUMN  lastname VARCHAR(50); 
GO 

Now we can match and verify the ANSI_Padding setting in both tables by re-executing the previous script. And result confirms the fix.

Match the ANSI_Padding setting of columns in two tables

Issue is fixed and we can switch the partitions

Recreate table with modified ANSI_Padding

As a second option you can drop and re-create the table and do not forget to mention

 
SET ANSI_PADDING ON 
GO 

at start of script.

Consult us to explore the Databases. Contact us