In a previous post I had explained how to partition SQL database tables. Actually we can use those partitions when we need to delete particular set of records from those large tables. Otherwise it will takes more time to delete those.
To create a stored procedure to truncate partition data, I reused a script in this link and applied some modification.
The added modifications are
To create a stored procedure to truncate partition data, I reused a script in this link and applied some modification.
The added modifications are
- Fixed the issue with joining the sys.partition_range_values for Right partitions
- Added the non-clustered index
1: IF EXISTS (
2: SELECT *
3: FROM sys.objects
4: WHERE OBJECT_ID = OBJECT_ID(N'[SP_TruncatePartition]')
5: AND TYPE IN (N'P', N'PC')
6: )
7: DROP PROCEDURE [SP_TruncatePartition]
8: GO
9:
10: CREATE PROCEDURE [SP_TruncatePartition]
11: @tableName VARCHAR(255)
12: ,
13: @partitionValueString VARCHAR(8)
14: ,@partitionGranualirity VARCHAR(1)
15: ,@schemaName VARCHAR(255) = 'dbo'
16:
17: AS
18: BEGIN
19:
20: DECLARE @partitionValue INT
21:
22: SET @partitionValue=CAST(@partitionValueString AS INT)
23:
24: IF(@partitionGranualirity='M')
25: BEGIN
26: SET @partitionValue=CAST((SUBSTRING(CAST(@partitionValue AS VARCHAR(8)),1,6)+'01') AS INT)
27: END
28:
29: DECLARE @error INT,
30: @rowsCount INT,
31: @errMsg VARCHAR(1024)
32:
33: DECLARE @partitionSchema VARCHAR(255),
34: @partitionFunction VARCHAR(255),
35: @partitionColumn VARCHAR(255),
36: @tableStorageType VARCHAR(16),
37: @partitionFunctionID INT
38:
39: DECLARE @partitionFileGroupName VARCHAR(255),
40: @databaseName VARCHAR(255),
41: @partitionNumber INT,
42: @cloneTableName VARCHAR(255),
43: @compression TINYINT,
44: @compressionQuery VARCHAR(256)
45:
46: DECLARE @LogHeader VARCHAR(23),
47: @Now DATETIME
48:
49: SELECT @tableName = LTRIM(RTRIM(ISNULL(@tableName, ''))),
50: @schemaName = LTRIM(RTRIM(ISNULL(@schemaName, ''))),
51: @databaseName = DB_NAME(),
52: @schemaName = LTRIM(RTRIM(@schemaName)),
53: @tableName = LTRIM(RTRIM(@tableName)),
54: @compressionQuery = ''
55: --
56: -- find the partition number for the partion value and other information for table partition
57: SELECT @tableStorageType = i.type_desc,
58: @partitionSchema = ps.name,
59: @partitionFunction = pf.name,
60: @partitionFunctionID = pf.function_id
61: -- partition FOR RIGHT and boundary_id begins with id 1
62: ,
63: @partitionNumber = CASE
64: WHEN pf.boundary_value_on_right = 1 THEN rv.boundary_id
65: + 1
66: ELSE rv.boundary_id
67: END,
68: @partitionColumn = c.name,
69: @partitionFileGroupName = fg.name
70: -- compression can be 0: no compresson, 1: row level compression 2: page level compression
71: -- only apply to clustered index?
72: ,
73: @compression = CASE
74: WHEN i.index_id = 1 THEN p.data_compression
75: ELSE 0
76: END
77: FROM sys.schemas s
78: INNER JOIN sys.tables t
79: ON t.schema_id = s.schema_id
80: -- check table storage type
81:
82: INNER JOIN sys.indexes i
83: ON (
84: i.object_id = t.object_id
85: -- 0: heap
86: -- 1: clusterd
87: AND i.index_id IN (0, 1)
88: )
89: INNER JOIN sys.partitions p
90: ON p.object_id = i.object_id
91: AND p.index_id = i.index_id
92: INNER JOIN sys.index_columns ic
93: ON (
94: -- identify partioned column
95: ic.partition_ordinal > 0
96: AND ic.index_id = i.index_id
97: AND ic.object_id = t.object_id
98: )
99: INNER JOIN sys.columns c
100: ON c.object_id = ic.object_id
101: AND c.column_id = ic.column_id
102: INNER JOIN sys.system_internals_allocation_units au
103: ON p.partition_id = au.container_id
104: INNER JOIN sys.partition_schemes ps
105: ON ps.data_space_id = i.data_space_id
106: INNER JOIN sys.partition_functions pf
107: ON pf.function_id = ps.function_id
108: INNER JOIN sys.destination_data_spaces dds
109: ON dds.partition_scheme_id = ps.data_space_id
110: AND dds.destination_id = p.partition_number
111: INNER JOIN sys.filegroups fg
112: ON dds.data_space_id = fg.data_space_id
113: INNER JOIN sys.partition_range_values rv
114: ON pf.function_id = rv.function_id
115: AND p.partition_number = (CASE
116: WHEN pf.boundary_value_on_right = 1 THEN rv.boundary_id
117: + 1
118: ELSE rv.boundary_id
119: END)
120: WHERE -- only look for heap or clustered index
121: i.index_id IN (0, 1)
122: AND s.name = @schemaName
123: AND t.name = @tableName
124: -- need exact match
125: AND CAST(rv.value AS INT) = @partitionValue
126:
127: SELECT @error = @@ERROR,
128: @rowsCount = @@ROWCOUNT,
129: @Now = GETDATE(),
130: @LogHeader = CONVERT(CHAR(23), @Now, 126)
131:
132: IF (@rowsCount <= 0)
133: BEGIN
134: PRINT @LogHeader + ' table ' + @tableName + ' on db ' + @databaseName +
135: ' with schema ' + @schemaName
136: + ' is not partitioned or @partitionValue=' + CAST(@partitionValue AS VARCHAR(16))
137: + ' is not in exsiting partition range. will return.'
138: RETURN 100
139: END
140: ELSE
141: BEGIN
142: PRINT @LogHeader + ' table ' + @tableName + ' on db ' + @databaseName +
143: ' with schema '
144: + @schemaName + ' is partitioned: @partitionSchema=' + @partitionSchema
145: + ', @partitionFunction=' + @partitionFunction
146: + ', @partitionFunctionID=' + CAST(@partitionFunctionID AS VARCHAR(16))
147: + ', @partitionColumn=' + @partitionColumn
148: + ', @tableStorageType=' + @tableStorageType
149: + ', @partitionValue=' + CAST(@partitionValue AS VARCHAR(16))
150: + ', @partitionNumber=' + CAST(@partitionNumber AS VARCHAR(16))
151: + ', @partitionFileGroupName=' + @partitionFileGroupName
152: + ', @compression=' + CAST(@compression AS VARCHAR(16))
153: END
154: --
155: -- create clone table on same file group, drop it if already exists
156: -- we don't need to take care of FK and no clustered for our case
157: -- No clustered index is not needed
158: -- taking care of columns and primary key
159: -- Ironically you could drop a table without droping its primary key!! suprise suprise
160: -- Now we know the primary key name, we can generate the drop statement
161: DECLARE @dropCloneTable VARCHAR(MAX),
162: @createCloneTable VARCHAR(MAX),
163: @primaryKeyName VARCHAR(255),
164: @dropPrimaryKey VARCHAR(MAX),
165: @primayKeyNamePlaceHolder VARCHAR(255),
166: @hasPrimaryKey TINYINT,
167: @switchPartition VARCHAR(1024)
168:
169: SELECT -- A place holder for primary key which will be replace when the value is known
170: @primayKeyNamePlaceHolder = '$$PK_NAME$$',
171: @cloneTableName = @tableName + '_' + CAST(@partitionNumber AS VARCHAR(16)),
172: @dropCloneTable =
173: 'IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N' +
174: '''' + @schemaName + '.' + @cloneTableName + '''' +
175: ') AND type in (N' + '''' + 'U' + '''' + '))' + CHAR(13)
176: + 'BEGIN' + CHAR(13) + 'DROP TABLE ' + @schemaName + '.' + @cloneTableName
177: + CHAR(13) + 'END',
178: @dropPrimaryKey =
179: 'IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N' +
180: '''' + @schemaName + '.' + @cloneTableName + '''' + ') AND name = N' +
181: '''' + @primayKeyNamePlaceHolder + '''' + ')' + CHAR(13)
182: + 'BEGIN' + CHAR(13) + 'ALTER TABLE ' + @schemaName + '.' + @cloneTableName
183: + ' DROP CONSTRAINT ' + @primayKeyNamePlaceHolder + CHAR(13) + 'END',
184: @switchPartition = 'ALTER TABLE ' + @schemaName + '.' + @tableName +
185: ' SWITCH PARTITION ' + CAST(@partitionNumber AS VARCHAR(16)) + ' TO '
186: + @schemaName + '.' + @cloneTableName
187:
188: SELECT @hasPrimaryKey = CASE
189: WHEN tc.Constraint_Name IS NULL THEN 0
190: ELSE 1
191: END,
192: @primaryKeyName = CASE
193: WHEN @hasPrimaryKey = 1 THEN REPLACE(REPLACE(@CloneTableName, @schemaName, ''), '.', '')
194: + '_' + tc.Constraint_Name
195: ELSE ''
196: END,
197: @createCloneTable = 'CREATE TABLE ' + @schemaName + '.' + @CloneTableName
198: + '(' + o.list + ')'
199: + ' ON [' + @partitionFileGroupName + ']'
200: + CASE
201: WHEN @hasPrimaryKey = 0 THEN ''
202: ELSE CHAR(13)
203: + 'ALTER TABLE ' + @schemaName + '.' + @CloneTableName +
204: ' ADD CONSTRAINT ' + @primaryKeyName + ' PRIMARY KEY '
205: + CASE
206: WHEN i.index_id IS NULL THEN 'NONCLUSTERED '
207: ELSE 'CLUSTERED '
208: END
209: + ' (' + LEFT(j.List, LEN(j.List) -1) + ')'
210: END
211: FROM sys.schemas s
212: INNER JOIN sys.objects so
213: ON s.schema_id = so.schema_id
214: CROSS APPLY(
215: SELECT ' [' + column_name + '] '
216: -- deal with computed columns
217: + CASE
218: WHEN cc.is_computed = 1 THEN ' AS (' + cc.definition + ')'
219: + CASE
220: WHEN cc.is_persisted = 1 THEN ' PERSISTED'
221: ELSE ''
222: END + ', '
223: ELSE -- non computed columns case
224: data_type +
225: CASE data_type
226: WHEN 'sql_variant' THEN ''
227: WHEN 'text' THEN ''
228: WHEN 'decimal' THEN '(' + CAST(numeric_precision AS VARCHAR)
229: + ', ' + CAST(numeric_scale AS VARCHAR) +
230: ')'
231: ELSE COALESCE(
232: '(' + CASE
233: WHEN
234: character_maximum_length
235: = -1 THEN 'MAX'
236: ELSE CAST(character_maximum_length AS VARCHAR)
237: END + ')',
238: ''
239: )
240: END
241: + ' '
242: + CASE
243: WHEN EXISTS (
244: SELECT id
245: FROM syscolumns
246: WHERE OBJECT_NAME(id) =
247: 'TestTruncatePartition'
248: AND NAME = column_name
249: AND COLUMNPROPERTY(id, NAME, 'IsIdentity') =
250: 1
251: ) THEN 'IDENTITY(' + CAST(IDENT_SEED('TestTruncatePartition') AS VARCHAR)
252: + ',' + CAST(IDENT_INCR('TestTruncatePartition') AS VARCHAR)
253: + ')'
254: ELSE ''
255: END
256: + ' '
257: + (CASE WHEN c.IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END)
258: + 'NULL '
259: + CASE
260: WHEN c.COLUMN_DEFAULT IS NOT NULL THEN
261: 'DEFAULT ' + c.COLUMN_DEFAULT
262: ELSE ''
263: END + ', '
264: END
265: FROM information_schema.columns c
266: LEFT JOIN sys.computed_columns cc
267: ON OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME) = cc.object_id
268: AND c.COLUMN_NAME = cc.name
269: WHERE c.table_name = so.name
270: AND c.TABLE_SCHEMA = @schemaName
271: ORDER BY
272: ordinal_position
273: FOR XML PATH('')
274: ) o(list)
275: LEFT JOIN information_schema.table_constraints tc
276: ON tc.Table_name = so.Name
277: AND tc.Constraint_Type = 'PRIMARY KEY'
278: AND tc.TABLE_SCHEMA = @schemaName
279: CROSS APPLY(
280: SELECT '[' + Column_Name + '], '
281: FROM information_schema.key_column_usage kcu
282: WHERE kcu.Constraint_Name = tc.Constraint_Name
283: AND kcu.TABLE_SCHEMA = @schemaName
284: ORDER BY
285: ORDINAL_POSITION
286: FOR XML PATH('')
287: ) j(list)
288: -- find out if the primary key is clustered
289: LEFT JOIN sys.indexes i
290: ON so.object_id = i.object_id
291: -- index_id 1 means clustered
292: -- constraint name is primary key name which is same as the index name
293: AND i.index_id = 1
294: AND tc.CONSTRAINT_NAME = i.name
295: AND tc.TABLE_SCHEMA = @schemaName
296: WHERE s.name = @SchemaName
297: AND so.type = 'U'
298: AND so.name NOT IN ('dtproperties')
299: AND so.name = @TableName
300:
301: SELECT @error = @@ERROR,
302: @rowsCount = @@ROWCOUNT,
303: @Now = GETDATE(),
304: @LogHeader = CONVERT(CHAR(23), @Now, 126)
305:
306: PRINT @LogHeader + ' @hasPrimaryKey=' + CAST(@hasPrimaryKey AS VARCHAR(16))
307: IF (@hasPrimaryKey = 1)
308: BEGIN
309: SELECT @dropPrimaryKey = REPLACE(
310: @dropPrimaryKey,
311: @primayKeyNamePlaceHolder,
312: @primaryKeyName
313: )
314: END
315: -- 1: Row compression
316: -- 2: Page compression
317: IF @compression = 1
318: BEGIN
319: SET @compressionQuery = 'ALTER TABLE ' + @schemaName + '.' + @cloneTableName
320: + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)';
321: END
322: ELSE
323: IF @compression = 2
324: BEGIN
325: SET @compressionQuery = 'ALTER TABLE ' + @schemaName + '.' + @cloneTableName
326: + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)';
327: END
328: --
329: -- taking care of clustered index
330: -- taking care of primary key is not enough since a primary key may not be clustered
331: -- so now we have to excludes clustered primary key index since they are already been taken care of
332: -- sample index create and drop statement
333: --
334: -- we may not need to drop the index? just in case
335: DECLARE @indexColums VARCHAR(256),
336: @indexName VARCHAR(256),
337: @indexUnique VARCHAR(8),
338: @hasNonPrimaryKeyClusterdIndex TINYINT,
339: @createClusteredIndex VARCHAR(8000),
340: @dropClusteredIndex VARCHAR(8000)
341:
342: SELECT @indexColums = '(',
343: @indexUnique = '',
344: @indexName = '',
345: @hasNonPrimaryKeyClusterdIndex = 0,
346: @dropClusteredIndex = '',
347: @createClusteredIndex = ''
348:
349: SELECT -- whenever we have result from here, we do have clusterd index
350: @hasNonPrimaryKeyClusterdIndex = 1,
351: @indexColums = @indexColums + ic.columnList,
352: @indexName = @cloneTableName + '_' + i.name,
353: @indexUnique = CASE
354: WHEN i.is_unique = 0 THEN ''
355: ELSE 'UNIQUE '
356: END
357: FROM sys.schemas s
358: INNER JOIN sys.tables t
359: ON t.schema_id = s.schema_id
360: INNER JOIN sys.indexes i
361: ON (
362: i.object_id = t.object_id
363: -- looking for clustered index ONLY
364: AND i.index_id = 1
365: -- ignore primary key, which is taken care of above already
366: AND i.is_primary_key = 0
367: )
368: CROSS APPLY(
369: SELECT c.name + CASE
370: WHEN ic.is_descending_key = 0 THEN ' ASC'
371: ELSE ' DESC'
372: END + ','
373: FROM sys.index_columns ic
374: INNER JOIN sys.columns c
375: ON c.column_id = ic.column_id
376: AND c.object_id = ic.object_id
377: WHERE ic.index_id = i.index_id
378: AND ic.object_id = t.object_id
379: ORDER BY
380: ic.key_ordinal
381: FOR XML PATH('')
382: ) ic(columnList)
383: WHERE s.name = @schemaName
384: AND t.name = @tableName
385:
386:
387:
388: -- Non Clustered Index
389: DECLARE @indexColumsNonClusterd VARCHAR(256),
390: @indexNameNonClusterd VARCHAR(256),
391: @indexUniqueNonClusterd VARCHAR(8),
392: @hasNonClusterdIndex TINYINT,
393: @createNonClusterdIndex VARCHAR(8000),
394: @dropNonClusterdIndex VARCHAR(8000)
395:
396: SELECT @indexColumsNonClusterd = '(',
397: @indexUniqueNonClusterd = '',
398: @indexNameNonClusterd = '',
399: @hasNonClusterdIndex = 0,
400: @dropNonClusterdIndex = '',
401: @createNonClusterdIndex = ''
402:
403: SELECT -- whenever we have result from here, we do have clusterd index
404: @hasNonClusterdIndex = 1,
405: @indexColumsNonClusterd = @indexColumsNonClusterd + ic.columnList,
406: @indexNameNonClusterd = @cloneTableName + '_' + i.name,
407: @indexUniqueNonClusterd = CASE
408: WHEN i.is_unique = 0 THEN ''
409: ELSE 'UNIQUE '
410: END
411: FROM sys.schemas s
412: INNER JOIN sys.tables t
413: ON t.schema_id = s.schema_id
414: INNER JOIN sys.indexes i
415: ON (
416: i.object_id = t.object_id
417: -- looking for clustered index ONLY
418: AND i.index_id = 2
419: -- ignore primary key, which is taken care of above already
420: -- AND i.is_primary_key = 0
421: )
422: CROSS APPLY(
423: SELECT c.name + CASE
424: WHEN ic.is_descending_key = 0 THEN ' ASC'
425: ELSE ' DESC'
426: END + ','
427: FROM sys.index_columns ic
428: INNER JOIN sys.columns c
429: ON c.column_id = ic.column_id
430: AND c.object_id = ic.object_id
431: WHERE ic.index_id = i.index_id
432: AND ic.object_id = t.object_id
433: ORDER BY
434: ic.key_ordinal
435: FOR XML PATH('')
436: ) ic(columnList)
437: WHERE s.name = @schemaName
438: AND t.name = @tableName
439:
440:
441: SELECT @error = @@ERROR,
442: @rowsCount = @@ROWCOUNT,
443: @Now = GETDATE(),
444: @LogHeader = CONVERT(CHAR(23), @Now, 126),
445: @hasNonPrimaryKeyClusterdIndex = ISNULL(@hasNonPrimaryKeyClusterdIndex, 0)
446:
447: PRINT @LogHeader + ' @hasNonPrimaryKeyClusterdIndex=' + CAST(@hasNonPrimaryKeyClusterdIndex AS VARCHAR(16))
448: IF (@hasNonPrimaryKeyClusterdIndex >= 1)
449: BEGIN
450: SELECT @dropClusteredIndex =
451: 'IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'
452: + '''' + @schemaName + '.' + @cloneTableName + '''' +
453: ') AND name = N' + '''' + @indexName + '''' + ')'
454: + CHAR(13) + 'BEGIN' + CHAR(13)
455: + 'DROP INDEX ' + @indexName + ' ON ' + @schemaName + '.' + @cloneTableName
456: + CHAR(13) + 'END',
457: @createClusteredIndex = 'CREATE ' + @indexUnique +
458: 'CLUSTERED INDEX ' + @indexName + ' ON ' + @schemaName + '.' + @cloneTableName
459: + LEFT(@indexColums, LEN(@indexColums) -1) + ')'
460: END
461:
462: IF (@hasNonClusterdIndex >= 1)
463: BEGIN
464: SELECT @dropNonClusterdIndex =
465: 'IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'
466: + '''' + @schemaName + '.' + @cloneTableName + '''' +
467: ') AND name = N' + '''' + @indexNameNonClusterd + '''' + ')'
468: + CHAR(13) + 'BEGIN' + CHAR(13)
469: + 'DROP INDEX ' + @indexNameNonClusterd + ' ON ' + @schemaName + '.' + @cloneTableName
470: + CHAR(13) + 'END',
471: @createNonClusterdIndex = 'CREATE ' + @indexUniqueNonClusterd +
472: 'NONCLUSTERED INDEX ' + @indexNameNonClusterd + ' ON ' + @schemaName + '.' + @cloneTableName
473: + LEFT(@indexColumsNonClusterd, LEN(@indexColumsNonClusterd) -1) + ')'
474: END
475:
476: SELECT @error = @@ERROR,
477: @rowsCount = @@ROWCOUNT,
478: @Now = GETDATE(),
479: @LogHeader = CONVERT(CHAR(23), @Now, 126)
480:
481: PRINT @LogHeader +
482: ' (@dropPrimaryKey, @dropClusteredIndex, @dropCloneTable, @createCloneTable, @createClusteredIndex, @compressionQuery, @switchPartition, @dropPrimaryKey, @dropCloneTable)'
483: PRINT @dropPrimaryKey + CHAR(13) + @dropClusteredIndex + CHAR(13) + @dropCloneTable
484: + CHAR(13) + @createCloneTable + CHAR(13) + @createClusteredIndex + CHAR(13)
485: + @compressionQuery + CHAR(13) + @switchPartition + CHAR(13) + @dropPrimaryKey
486: + CHAR(13) + @dropCloneTable
487: DECLARE @returnCode INT
488: IF (@hasPrimaryKey = 1)
489: BEGIN
490: EXEC (@dropPrimaryKey)
491: SELECT @returnCode = 300,
492: @error = @@ERROR,
493: @rowsCount = @@ROWCOUNT,
494: @Now = GETDATE(),
495: @LogHeader = CONVERT(CHAR(23), @Now, 126),
496: @errMsg = @LogHeader +
497: ' [1] failed to drop primary key for clone table. will return.'
498:
499: IF (@error <> 0)
500: GOTO ERROR
501: END
502:
503: IF (@hasNonPrimaryKeyClusterdIndex = 1)
504: BEGIN
505: EXEC (@dropClusteredIndex)
506: SELECT @returnCode = 310,
507: @error = @@ERROR,
508: @rowsCount = @@ROWCOUNT,
509: @Now = GETDATE(),
510: @LogHeader = CONVERT(CHAR(23), @Now, 126),
511: @errMsg = @LogHeader +
512: ' [1] failed to drop clustered index for clone table. will return.'
513:
514: IF (@error <> 0)
515: GOTO ERROR
516: END
517:
518: IF (@hasNonClusterdIndex = 1)
519: BEGIN
520: EXEC (@dropNonClusteredIndex)
521:
522: IF (@error <> 0)
523: GOTO ERROR
524: END
525:
526: EXEC (@dropCloneTable)
527: SELECT @returnCode = 320,
528: @error = @@ERROR,
529: @rowsCount = @@ROWCOUNT,
530: @Now = GETDATE(),
531: @LogHeader = CONVERT(CHAR(23), @Now, 126),
532: @errMsg = @LogHeader +
533: ' [1] failed to drop clone table. will return.'
534:
535: IF (@error <> 0)
536: GOTO ERROR
537:
538: EXEC (@createCloneTable)
539: SELECT @returnCode = 330,
540: @error = @@ERROR,
541: @rowsCount = @@ROWCOUNT,
542: @Now = GETDATE(),
543: @LogHeader = CONVERT(CHAR(23), @Now, 126),
544: @errMsg = @LogHeader + ' failed to create clone table. will return.'
545:
546: IF (@error <> 0)
547: GOTO ERROR
548:
549: IF (@hasNonPrimaryKeyClusterdIndex = 1)
550: BEGIN
551: EXEC (@createClusteredIndex)
552: SELECT @returnCode = 340,
553: @error = @@ERROR,
554: @rowsCount = @@ROWCOUNT,
555: @Now = GETDATE(),
556: @LogHeader = CONVERT(CHAR(23), @Now, 126),
557: @errMsg = @LogHeader +
558: ' failed to create clustered index for clone table. will return.'
559:
560: IF (@error <> 0)
561: GOTO ERROR
562: END
563:
564: IF (@hasNonClusterdIndex = 1)
565: BEGIN
566: EXEC (@createNonClusteredIndex)
567:
568:
569: IF (@error <> 0)
570: GOTO ERROR
571: END
572:
573: IF (@compression IN (1, 2))
574: BEGIN
575: EXEC (@compressionQuery)
576: SELECT @returnCode = 350,
577: @error = @@ERROR,
578: @rowsCount = @@ROWCOUNT,
579: @Now = GETDATE(),
580: @LogHeader = CONVERT(CHAR(23), @Now, 126),
581: @errMsg = @LogHeader +
582: ' failed to adjust clone table compressoin option. will return.'
583:
584: IF (@error <> 0)
585: GOTO ERROR
586: END
587: --
588: -- now do the partition switch to truncate data
589: EXEC (@switchPartition)
590: SELECT @returnCode = 360,
591: @error = @@ERROR,
592: @rowsCount = @@ROWCOUNT,
593: @Now = GETDATE(),
594: @LogHeader = CONVERT(CHAR(23), @Now, 126),
595: @errMsg = @LogHeader +
596: ' failed to switch partition to clone table. will return.'
597:
598: IF (@error <> 0)
599: GOTO ERROR
600: --
601: -- now drop the coloum table again
602: IF (@hasPrimaryKey = 1)
603: BEGIN
604: EXEC (@dropPrimaryKey)
605: SELECT @returnCode = 370,
606: @error = @@ERROR,
607: @rowsCount = @@ROWCOUNT,
608: @Now = GETDATE(),
609: @LogHeader = CONVERT(CHAR(23), @Now, 126),
610: @errMsg = @LogHeader +
611: ' [2] failed to drop primary key for clone table. will return.'
612:
613: IF (@error <> 0)
614: GOTO ERROR
615: END
616:
617:
618: EXEC (@dropCloneTable)
619: SELECT @returnCode = 390,
620: @error = @@ERROR,
621: @rowsCount = @@ROWCOUNT,
622: @Now = GETDATE(),
623: @LogHeader = CONVERT(CHAR(23), @Now, 126),
624: @errMsg = @LogHeader +
625: ' [2] failed to drop clone table. will return.'
626:
627: IF (@error <> 0)
628: GOTO ERROR
629:
630: RETURN 0
631: ERROR:
632: PRINT @errMsg
633: RETURN @returnCode
634: END
