Generate Create statements for Statstics in SQL Server


 

Howdy EveryOne

if you want to if exists delete and create scripts for the statstics in the SQL Server, the below code works fine.

declare @SQ VARCHAR(4)
SET @SQ = ''''
;WITH StatNames
AS
(
SELECT  s.name StatName, T.name as TableName,s.stats_id stats_id, T.object_id [object_id]
FROM    sys.stats   S   WITH (NOLOCK)
JOIN    sys.Tables  T   WITH (NOLOCK)
ON      S.object_id     = T.object_id
AND     S.user_created  = 1
),
Deflat
AS
(
SELECT SN.StatName,SN.TableName,STUFF(
(  select  ',' + C.name
FROM    sys.stats_columns SC   WITH (NOLOCK)
JOIN    sys.columns C WITH (NOLOCK)
ON      SC.column_id = C.column_id
AND     C.object_id = SN.object_id
AND     SC.stats_id = SN.stats_id
AND     C.object_id = SC.object_id
ORDER   BY SC.stats_column_id
FOR     XML PATH ('')
),1,1,'') Cols
FROM  StatNames SN
)
SELECT 'IF EXISTS ( SELECT 1 FROM sys.stats S, sys.tables T WHERE S.name = ' + @SQ +  StatName + @SQ + ' AND T.name = ' + @SQ + TableName + @SQ + ' ) '
+ ' DROP STATISTICS ' + TableName +'.'+StatName
+ ' CREATE  STATISTICS ' + StatName  + ' ON ' + TableName + '(' + Cols + ')'
FROM Deflat

Advertisements

Generate a Sequence number for each row in a table.


Howdy EveryOne

Generating a sequence a number for each and every row in a table…. Hmm if you are familiar with TSQL already I think you are thinking of creating Identity column like me…. (ofcourse if you are not an advanced user)…..

Now you can create the Identity column in a temporary table followed by the same schema of your own table to generate a sequence number for all the rows in your table, But in order to accomplish this task we are using a temporary table in between. If the data too small we might not see any performance issue but if the data is too large (too many record sets) then you are in serious trouble of performance.

How can we accomplish this task without the temp table in between… Is it possible, Yes it is ?

And again Thanks to Kent W. on MSDN forums who shown the best approach to do this….

DECLARE @a table
(
	a int
)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
select row_number() over(order by (select 1)) , a from @a

Concatenate rows into a column separated by a delimiter


Howdy EveryOne

While working on the MSDN Forums for quite a while, I see every now and then  people wants to know how to concatenate a single column value of multiple rows into a single column…. I think it might be better understood by a example

Table A: INPUT

Col1
Row 1 a
Row 2 b
Row 3 c
Row 4 d

Desired output:

a,b,c,d

If this is the case then without using the XML tags previously we used to do the below one

declare @t table
(
	a char
)
insert into @t values ('a')
insert into @t values ('b')
insert into @t values ('c')
insert into @t values ('d')
declare @concate varchar(100)
set @concate = ''
SELECT @concate = @concate +','+ a
FROM @t
select STUFF(@concate,1,1,'')

Although it is a good approach, The select statement (concate = concate+…) needs to be executed in a separate batch. This approach will need an extra lines of code incase if this concatenated value is needed else where….

So, in search of a better solution, I stumbled upon a post written by Kent W. in MSDN forums with FOR XML PATH which will accomplish my task very smoothly.

Below is an example of how to write the above code with the FOR XML PATH

declare @t table
(
	a char
)
insert into @t values ('a')
insert into @t values ('b')
insert into @t values ('c')
insert into @t values ('d')
SELECT STUFF( (SELECT ',' + a FROM @t FOR XML PATH ('')),1,1,'')

Advantages of the second approach:
No need of additional variables.
Can be included in a subquery directly.