SQL RNNR

1 DBA's Professional Blog

Tags: Scripts

Foreign Key Hierarchy Update

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Documentation, Foreign Key, Hierarchy, Primary Key, Scripts

Comments: 1 Comment

Published on: July 11, 2011

Today I would like to revisit a post of mine that is rather old. More precisely, the script in that post needs revisiting. This is one of my more favorite scripts and I still have more ideas to implement with it. The post/script in question can be found here.

In revisiting this script, I simplified it a bit. I also had to work on a problem with it that occurs in Hierarchies with circular dependencies. Quite frankly, that was a huge pain in the butt. There are some tricks out there to try and help with it – but I was having a hard time getting any of them to work in this scenario. I also updated the script to better handle self-referencing objects.

When you have circular references, and are trying to recurse the tree via a CTE, an instant blocker comes into play. You can only reference the anchor of the Recursive CTE once. Fixing a circular reference would be many times easier if you could reference the anchor twice.

In the end, the biggest hint to getting this to work came from this post. For it to work, I needed to find which combination of fields would work best. I finally settled on using the Object_ID to help reduce my pain. I settled on using the following in the anchor:

Code block

CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))

and the following in the recursive definition.

Code block

STUFF(('.' + F.SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
						),1,1,'')

You can see that I am concatenating into a string for this column. This seems to work well for the purpose of eliminating those circular references.

Other adjustments to the script are not quite as significant but there is a small performance gain to be seen by these subtle changes. The most notable is the change to remove the two joins out to sys.columns in order to get the column names of the Parent and Child objects. In lieu of these joins, I am using the COL_NAME() function. This little change came to me thanks to a little work done last week on my statistics script that you can read here.

The final notable change comes in the naming of the CTEs in this script. I decided to rename the CTEs to something a bit more meaningful. In this case, Hierarchy and Ancestry are much more appropriate.

Without further adieu, here is the next major revision of that script.

Code block

DECLARE	@StartParentTable	VARCHAR(256)
 
SELECT @StartParentTable = 'yourtable'
 
;With Hierarchy (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
			,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
			,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
			,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
			,0 AS FKLevel
			,CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
		FROM sys.foreign_key_columns sfkc
			INNER Join sys.foreign_keys sfk
				ON sfkc.constraint_object_id = sfk.OBJECT_ID
		WHERE OBJECT_NAME(sfk.referenced_object_id) = @StartParentTable
	UNION All
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
			,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
			,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
			,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
			,FKLevel + 1
			,STUFF(('.' + F.SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
						),1,1,'')
		FROM sys.foreign_keys sfk
			INNER Join Hierarchy F
				ON OBJECT_NAME(sfk.referenced_object_id) = f.ChildTable
				And F.SortCol NOT LIKE '%'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'%'
			INNER Join sys.foreign_key_columns sfkc
				ON sfkc.constraint_object_id = sfk.OBJECT_ID
		WHERE OBJECT_NAME(sfk.referenced_object_id) <> @StartParentTable
			And sfk.referenced_object_id <> sfk.parent_object_id
), Ancestry (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT DISTINCT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
		,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
		,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
		,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
		,-1 AS FKLevel
		,CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
	FROM Hierarchy F
		INNER Join sys.foreign_keys sfk
			ON f.ChildTable = OBJECT_NAME(sfk.parent_object_id)
			And F.ParentTable <> OBJECT_NAME(sfk.referenced_object_id)
		INNER Join sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.OBJECT_ID
	UNION All
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
		,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
		,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
		,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
		,f.FKLevel -1
		,STUFF(('.' + SortCol + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
					),1,1,'')
	FROM Ancestry F
		INNER Join sys.foreign_keys sfk
			ON f.parentTable = OBJECT_NAME(sfk.parent_object_id)
		INNER Join sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.OBJECT_ID
	WHERE f.parentTable not in (SELECT ParentTable FROM Hierarchy)
		And sfk.referenced_object_id <> sfk.parent_object_id
		And f.ChildTable not IN (SELECT ChildTable FROM Hierarchy)
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM Hierarchy
UNION All
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM Ancestry
	ORDER BY SortCol ASC
	OPTION (maxrecursion 500)

I hope you will play with this script, test it out and make recommendations or even find bugs with it and let me know.

SQL Statistics – another Spin

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Scripts, statistics, TSQL

Comments: 5 Comments

Published on: July 1, 2011

I was reading a blog by Pinal Dave that was about using catalog views to obtain information about stats for the entire database. While reading the blog, I was reminded about an interesting tidbit of information I had seen once upon a time concerning statistics naming in SQL Server.

This got me looking for that information and burrowing down the rabbit hole. I found the reference for the naming convention concerning auto generated statistics. That reference is from Paul Randal and can be found here. In that article, Paul outlines the five parts of the name of an auto-created statistic – with each part being separated by an underscore. This got the wheels churning a bit more.

I had to go do a bit more research in order to put all the pieces together. Based on the naming convention, I knew I needed to convert Hex to Integer, so I could see the object id (column or table). You might think this would be straight forward, but the value in the name of the statistic is not a true Hex Value. That value is a hex string and needs a bit of manipulation in order to convert from string to Hex.

After some searching, I found the solution. Peter Debetta created this handy little function for this very purpose. I found that script here. After some testing, and comparing results I was pleased with how well it worked. Now that I had the pieces necessary, I could venture further down that little rabbit hole.

First though, there is one more piece that I want to discuss. Remember that naming scheme for that auto created statistics? I noted that the name is separate by an underscore – each element of the name anyway. I decided that I would use that underscore as my string delimiter and implement a string split function to break out the name parts. Any number of splitter functions would work. I chose to use the same function that was written about by Jeff Moden here.

Now all of the pieces have been referenced and are in play. Let’s take a look at the query and some of those catalog views.

Code block

WITH autostatnames AS (
	SELECT split.itemnumber AS Statnum,S.name, split.item AS Statnamepart,s.OBJECT_ID
		FROM sys.stats S
		CROSS APPLY dbo.DelimitedSplit8K(s.name,'_') split
		WHERE s.auto_created = 1
), statpart1 AS (
	SELECT asn.name,CONVERT(INT,master.dbo.HexStrToVarBin('0x' + asn.Statnamepart)) AS StatCol
		FROM autostatnames asn
		WHERE asn.Statnum = 4
), statpart2 AS (
	SELECT asn.name,CONVERT(INT,master.dbo.HexStrToVarBin('0x' + asn.Statnamepart)) AS StatObj
		FROM autostatnames asn
		WHERE asn.Statnum = 5
)
 
SELECT DISTINCT s.name AS StatName, OBJECT_NAME(s2.StatObj) AS TableName,COL_NAME(s2.StatObj,s1.StatCol) AS ColName
		,s.auto_created
		,s.user_created
		,s.no_recompute
		,STATS_DATE(s.[OBJECT_ID], s.stats_id) AS LastUpdated
	FROM statpart1 s1
		INNER Join statpart2 s2
			ON s1.name = s2.name
		INNER Join sys.stats s
			ON s.name = s1.name
UNION All
SELECT DISTINCT
		OBJECT_NAME(s.[OBJECT_ID]) AS TableName,
		COL_NAME(s.OBJECT_ID,sc.column_id) AS ColName,
		s.name AS StatName,
		s.auto_created,
		s.user_created,
		s.no_recompute,
		STATS_DATE(s.[OBJECT_ID], s.stats_id) AS LastUpdated
	FROM sys.stats s
		INNER Join sys.stats_columns sc
			ON sc.[OBJECT_ID] = s.[OBJECT_ID]
			AND sc.stats_id = s.stats_id
	WHERE s.auto_created = 0
	ORDER BY s.name DESC

I started this little script out with three quick CTEs. The first is simply to gather the auto created stats. It is in this first CTE that the string splitter is utilized. The next two help me to separate out the data so I can work with it. The second CTE is to manage the data related to the column part of the statistics name. In this CTE, you will note that I employ the use of that Hex function. The third CTE is just like the second, but it treats the data related to the table part of the statistics name.

When splitting the data in that first CTE, and due to the naming format, we see that the column is always the fourth part of the name and the table is the fifth part. The first part is really an empty result due to the name leading off with an underscore.

Once the data is grouped into those CTEs, I can then return it in a friendlier result set. Here I use more functions that are built in with SQL server. This reduces the number of catalog views to which I need to JOIN. And since the CTEs are only working with statistics that are auto generated, I need to UNION in another query to pull in those statistics that are not auto created. Note that this query only looks at two catalog views.

What is the point of such an elaborate query in the first part to get this information when I can simply use the second part to do all of it? Not a whole lot of point other than to prove a point. There is consistency between the queries. There is consistency between the naming of those statistics and the object data that can be found in those catalog views.

Any of these methods will return the desired results concerning statistics in the database. You have essentially two methods displayed by me, and then the method employed by Pinal. Have fun with it!!

CTE, Recursion and Math

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Scripts, TSQL

Comments: No Comments

Published on: May 23, 2011

Earlier this month we had a TSQL Tuesday on the topic of CTEs. I bailed on my submission because I already posted some CTE examples and was bone dry on what I could write about.

Later I saw a request for some help on creating combinations and permutations from TSQL. I thought about this request and finally came up with the idea to use CTEs to do it. The idea came about based on seeing multiple suggestions in that thread and then combining some of the pieces together. Though not a suggestion, per-se, but seeing in the code submitted that a factorial was being used along with the sum of all integers from 1 to the given integer (or summation), I decided I would incorporate that into my solution.

I thus set out to create a solution that would use a CTE to generate those permutations, the summation, and the factorial. In this article I will just be focusing on how I came to produce the factorial and summation using a little bit of Recursive CTE. Also, we will see an alternative solution to calculating the summation. For help in setting up the structure of a recursive CTE, you may want to refer to this article.

Here is the script first, and then I will explain later.

Code block

DECLARE @FactNum INT
SET @FactNum = '50';
 
WITH E1(N) AS ( --=== Create Ten 1's
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 --10
               ),
      E2(N) AS (SELECT 1 FROM E1 a, E1 b),   --100
      E4(N) AS (SELECT 1 FROM E2 a, E2 b),   --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4
),CTEMathN(N,Factorial,Aggreg,Iteration) AS (
	SELECT N
			,CAST(1 AS NUMERIC(38,0)) AS Factorial
			,N AS Aggreg
			,CAST(1 AS INT) AS Iteration
		FROM ctetally
		WHERE N <= @FactNum
			AND @FactNum <= 33
	UNION ALL
	SELECT t.N
			,t.n * CAST(f1.Factorial AS NUMERIC(38,0)) AS Factorial
			,t.N + Aggreg AS Aggreg
			,CAST(f1.Iteration + 1 AS INT) AS Iteration
		FROM CTEMathN f1
			INNER JOIN cteTally T
				ON t.n = f1.n +1
				AND t.N <= @FactNum
),CTEMathF(N,Factorial,Aggreg,Iteration) AS (
	SELECT N
			,CAST(1 AS FLOAT) AS Factorial
			,N AS Aggreg
			,CAST(1 AS INT) AS Iteration
		FROM ctetally
		WHERE N <= @FactNum
			AND @FactNum > 33
	UNION ALL
	SELECT t.N
			,t.n * CAST(f1.Factorial AS FLOAT) AS Factorial
			,t.N + Aggreg AS Aggreg
			,CAST(f1.Iteration + 1 AS INT) AS Iteration
		FROM CTEMathF f1
			INNER JOIN cteTally T
				ON t.n = f1.n +1
				AND t.N <= @FactNum
)
 
SELECT ISNULL(CAST(CN.N AS VARCHAR(100)),CAST(CF.N AS VARCHAR(100))) AS N
		,ISNULL(CAST(CN.Factorial AS VARCHAR(100)),CAST(CF.Factorial AS VARCHAR(100))) AS Factorial
		,ISNULL(CAST(CN.Aggreg AS VARCHAR(100)),CAST(CF.Aggreg AS VARCHAR(100))) AS Aggreg
	FROM CTEMathN CN
	FULL OUTER JOIN CTEMathF CF
		ON CN.N = CF.N
	WHERE ISNULL(CN.Iteration,CF.Iteration) = @FactNum

As you can see, I am using several CTEs in this script. The first group of CTEs is to create a Dynamic Tally (Numbers) table. The last two CTEs are performing the same thing but with a bit of filtering applied. When you look at them, you should note that the first one is Casting the Factorial to a Numeric data type. The last CTE is casting the Factorial to a FLOAT data type. This isn’t necessarily required but more of an aesthetic for myself. So any number greater than 33 will use the FLOAT data type and anything less than 34 will use the numeric data type.

The FLOAT data type will support those smaller numbers – that’s not an issue. The Numeric data type will not support the larger values (it is limited to 38 characters). We start running into arithmetic overflow at 34! and must use the FLOAT at that point. The problem is that I prefer to avoid the overflow style of displaying the numbers except when necessary. For simplicity sake, you could eliminate the filtering between the CTEs and simply use the FLOAT across the board.

Since I am trying to filter between the two and create a single row result set, I also have a second issue that is created by this code. That issue is resolved and seen when performing the final select from the CTEs. If the query requires the use of FLOAT, I will get an error message with an arithmetic overflow again unless I Convert the Numeric and Float results into something that is compatible between the two. I chose to use VarChar(100) in this case.

Note, also, that I am using a FULL OUTER Join in the final query. This is due to the fact that I will see results in either the CTEMathN or CTEMathF CTE but not both.

Now down into the nitty gritty. Let’s look at CTEMathN to see how we are getting the factorial and the summation.

Code block

SELECT N
			,CAST(1 AS NUMERIC(38,0)) AS Factorial
			,N AS Aggreg
			,CAST(1 AS INT) AS Iteration
		FROM ctetally
		WHERE N <= @FactNum
			AND @FactNum <= 33
	UNION ALL
	SELECT t.N
			,t.n * CAST(f1.Factorial AS NUMERIC(38,0)) AS Factorial
			,t.N + Aggreg AS Aggreg
			,CAST(f1.Iteration + 1 AS INT) AS Iteration
		FROM CTEMathN f1
			INNER JOIN cteTally T
				ON t.n = f1.n +1
				AND t.N <= @FactNum

First we have the required anchor definition. Second we have the recursion definition joined by the Union All and referencing the same CTE where both of these pieces reside.

The factorial is easy enough. Since a Factorial is n! or n(n-1) we can multiply n by the previous n in the sequence. We can get both of those values through our join statement where we join back out to the Tally CTE and showing an increment in the number (here I am join CTETally.n to CTEMathN +1). This ensures we will move through the record set (or recurse).

The same principle applies for the summation. We add each number in the sequence from 1 to n to retrieve our result. So, much the same as the factorial, I add n to the previous value of n and proceed from there through the record set.

Note also that I threw an additional limiting factor on the where clause. I want to make sure that I do not try to recurse through a record set that includes numbers outside of my range.

Now, since I only really care about the factorial and summation for the number I inserted in the variable at the beginning, I want to make sure that I have a filter added to the final select clause. Here is that final filter…

Code block

WHERE ISNULL(CN.Iteration,CF.Iteration) = @FactNum

This enforces a single record will be returned by selecting only the last iteration. Iteration is another column in the CTEs that is used during our recursion process. The iteration column just increments by a value of one with each pass.

Pretty easy eh?

Now, if all you need is a summation and it is not used to control result sets (like mine will be in the next bit where we discuss permutations), then you could rip out the aggregation stuff and just use this in the final select statement…

Code block

((@FactNum * @FactNum)+ @FactNum )/ 2 AS Summation

That would be in place of this bit…

Code block

ISNULL(CAST(CN.Aggreg AS VARCHAR(100)),CAST(CF.Aggreg AS VARCHAR(100))) AS Aggreg

Have fun with it!!

T-SQL Tuesday #18 – CTEs

by Jason Brimhall

Categories: News, Professional, SSC, TSQL Tuesday

Tags: Scripts, SQL, TSQL Tuesday

Comments: 3 Comments

Published on: May 10, 2011

To CTE or not to CTE, that is the Question

So my post really has nothing to do with the title. This is not a post to help you determine whether a CTE is appropriate or not. Or is it?

This month, we have the 18th installment in the TSQLTuesday series. We are being hosted by Bob Pusateri (Blog|@SQLBob) this month. The essence of the topic this month is around CTEs (common table expressions). There are a great many uses for a CTE in SQL server and this was a nice addition to the product.

One thing I like about CTEs is how much cleaner the code looks to me. Another benefit for me is the recursion that is available through the use of a CTE. An observation about CTEs is that a common use would be to use them to replace inline derived tables (which lends to cleaner looking code for some).

Think Think Think

I gave this topic a good long thought. As I thought about the topic, I came to the conclusion that I had nothing new or unique on the subject. I did however have some scripts that I posted once upon a time that would work very well for this topic. Though it is a bit of a cop out, it is an appropriate solution for this month.

In the case of the CTE that I have chosen, there are multiple CTEs being used. I use the CTE to recurse through data, and then to recurse that same data again – in reverse. This particular script was created to traverse through system catalogs and create a hierarchy of table relationships. I use this hierarchy to better understand the structure of the database and the interrelationship of the data between objects. It is a cheap way of mapping out the objects in an effort to better understand it.

I had thought about using this script once upon a time for a different TSQLTuesday, but thought better of it that time. Since the original post is more than a year old, it is a good time to bring it up and use it again. Without further adieu, you can read about that script and CTE here. I hope you enjoy.

Physical Row Location

by Jason Brimhall

Categories: News, Professional, SSC

Tags: Scripts, SQL Internals, TSQL

Comments: No Comments

Published on: April 29, 2011

SQL Server 2008 has presented us a couple of options to aid in becoming better DBA’s. You can see this evidenced in many ways in the product. A couple of the things that make me think this is the case boils down to two functions that are new in SQL 2008. I learned about these while trying to learn how to do something else. It just so happens that these functions could possibly help me in the other process (I’ll write more about that later when I have finished it).

These new functions are: sys.fn_PhysLocFormatter and sys.fn_PhysLocCracker. The two functions are really very similar. The first of the two does as the name implies and formats the physical location, while the second of the two provides a table output of the location. If you look at the sp_helptext of both, you can see that they only have minor differences.

physlocformat

-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocFormatter
--
-- Description:
-- Formats the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
CREATE FUNCTION sys.fn_PhysLocFormatter (@physical_locator BINARY (8))
RETURNS VARCHAR (128)
AS
BEGIN  
 
 DECLARE @page_id BINARY (4)
 DECLARE @FILE_ID BINARY (2)
 DECLARE @slot_id BINARY (2)  
 
 -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
 --
 SELECT @page_id = CONVERT (BINARY (4), REVERSE (SUBSTRING (@physical_locator, 1, 4)))
 SELECT @FILE_ID = CONVERT (BINARY (2), REVERSE (SUBSTRING (@physical_locator, 5, 2)))
 SELECT @slot_id = CONVERT (BINARY (2), REVERSE (SUBSTRING (@physical_locator, 7, 2)))  
 
 RETURN '(' + CAST (CAST (@FILE_ID AS INT) AS VARCHAR) + ':' +
     CAST (CAST (@page_id AS INT) AS VARCHAR) + ':' +
     CAST (CAST (@slot_id AS INT) AS VARCHAR) + ')'
END

and

physloccracker

-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
-- Cracks the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
CREATE FUNCTION sys.fn_PhysLocCracker (@physical_locator BINARY (8))
RETURNS @dumploc_table TABLE
(
 [FILE_ID] INT not null,
 [page_id] INT not null,
 [slot_id] INT not null
)
AS
BEGIN  
 
 DECLARE @page_id BINARY (4)
 DECLARE @FILE_ID BINARY (2)
 DECLARE @slot_id BINARY (2)  
 
 -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
 --
 SELECT @page_id = CONVERT (BINARY (4), REVERSE (SUBSTRING (@physical_locator, 1, 4)))
 SELECT @FILE_ID = CONVERT (BINARY (2), REVERSE (SUBSTRING (@physical_locator, 5, 2)))
 SELECT @slot_id = CONVERT (BINARY (2), REVERSE (SUBSTRING (@physical_locator, 7, 2)))  
 
 INSERT INTO @dumploc_table VALUES (@FILE_ID, @page_id, @slot_id)
 RETURN
END

When you look at these two functions, you can easily say that they are similar right up until the end where they diverge in functionality. The first casts the data into the “formatted” version, while the cracker simply outputs to a table.

Use of these functions is also quite easy.

usage

SELECT TOP (10)
	yt.*,
	pl.FILE_ID, pl.page_id, pl.slot_id
FROM yourtable AS yt
cross apply sys.fn_PhysLocCracker(%%physloc%%) AS pl;
 
SELECT TOP 10
	sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID], yt.*
	FROM yourtable yt;

These functions can prove to be very helpful in your troubleshooting or dives into Internals. Check them out and enjoy.

T-SQL Tuesday #17 – APPLY Knowledge

by Jason Brimhall

Categories: News, Professional, SSC, TSQL Tuesday

Tags: Scripts, Security, TSQL Tuesday

Comments: 2 Comments

Published on: April 12, 2011

We have another opportunity to write as a part of TSQL Tuesday today. This month Matt Velic (Blog | Twitter). Matt has proposed a challenge that was derived from a comment on twitter. The challenge this month is to write about “Apply.”

Admins Apply Within

As an administrator of databases, do you use Apply? Not only can Apply be used to help return result sets as a part of your application, it can come in quite handy from an administration point of view too. It can be used to help retrieve the text of currently executing code or code stored in cache. Apply can be used to help retrieve index information and it can come in quite handy when performing a security audit.

Some Back Story

Just a few days ago, I posted some scripts to help find role membership of logins at the server level. I wasn’t satisfied with the query and decided to rewrite it as a part of my entry for this months blog party. You can read up on that script here. My dissatisfaction with the query was that it felt klugy. I wanted to write the query with something other than the Union and to present fewer rows to user. So, I took another look at the query and purpose for the query and decided to use a combination of Apply and Pivot.

My new take on the script is simple, I will create a result set that will show a 1 for each server role for which a login is a member. Each login will only have 1 row, opposed to the row per server role membership from the prior query.

The Script

Code block

SELECT PrincipalName,CA.[PUBLIC],sysadmin,bulkadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator
	FROM    (
		SELECT  SUSER_NAME(SR.role_principal_id) AS ServerRole, SP.name AS PrincipalName
			FROM sys.server_role_members SR
				INNER Join sys.server_principals SP
					ON SR.member_principal_id = SP.principal_id
		) Roles
		PIVOT   (
			COUNT(serverrole)
			FOR serverrole IN (sysadmin,bulkadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator)
		) PVT
	Cross Apply (SELECT 1 AS [PUBLIC]) CA

In this example, the bulk of the heavy lifting is done through the pivot. You will see that the Apply only seems to play a minor role in this script. It is an important role nonetheless. All logins (unless you have altered the public role) are also a member of the public role. That membership is not displayed through the views I have used in this query. Due to that handling of the public role, and to ensure that the membership in that role is not forgotten, it must be accounted for in the query.

This query will add a new column for the public role to those columns already presented via the pivot. In this column we will show that every login returned via the query is a member of the public role. But how is that done? Using the apply, in really basic terms, acts like a join to this subquery that I use for the public column. I am taking the value of 1 for column public, and applying that value to all of the results from the Pivot portion of the query. I am using the Cross version and there is one more version – called Outer. In my testing, both the Outer and the Cross Apply will return the same results for this particular query.

Some common uses for Apply are in conjunction with TVFs. Should you use the Apply with a function where the “Join” criteria would be more tightly defined, then the use of Cross V. Outer will produce different results. Just as with an Outer Join, the Outer would return results for all records in the “outer” or “left” part of the join whether there was a matching record in the TVF or not. In the same sort of setup, the Cross Apply would only return records that had a match in the TVF.

Conclusion

Again, this is an oversimplification of the Apply statement. My main goal was to present a use for Apply. Apply can be a useful tool in the hands of the database administrator. Examine it and learn what other potential it may have for some of your administration needs.

This is just one piece of the puzzle when performing a security audit. Getting this information quickly when requested for an audit can be very helpful. The output format is quite simple and very conducive for many auditors and management. A simple output report for the Server Roles in your environment is merely one example of the usefulness of Apply.

SSIS Job Ownership

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Scripts, Security, SQL, SSIS

Comments: 2 Comments

Published on: April 11, 2011

I was strolling along one day when I saw somebody asking how to find out who owns a maintenance plan. That evolved into finding out who owns the the job associated with the maintenance plan. All of this in SQL 2005 and SQL 2008.

Well, we were stumped for a bit trying to figure the link between the job tables in the msdb database and the ssis table in the same database. Linking the two together is not very obvious and we struggled with it for a bit. After some research and trying this that and the other, I was able to come up with the below script.

Code block

-- Display SSIS Package Owners and Job Owners SQL 2008
 SELECT dts.[name]
        ,dts.[description]
        ,dts.createdate
        ,dts.ownersid
        ,p.name AS PackageOwner
        ,JOB.name AS JobOwner
        ,SSISPackageType =
			CASE dts.packagetype
				WHEN 0 THEN 'default value'
				WHEN 1 THEN 'SQL Server Import and Export Wizard'
				WHEN 2 THEN 'DTS Designer in SQL Server 2000'
				WHEN 3 THEN 'SQL Server Replication'
				WHEN 5 THEN 'SSIS Designer'
				WHEN 6 THEN 'Maintenance Plan Designer or Wizard'
			END
   FROM msdb.dbo.sysssispackages       dts
	LEFT Join master.sys.server_principals   p
		ON p.sid = dts.ownersid
	LEFT Outer Join msdb.dbo.sysjobsteps SJS
		ON dts.name = SUBSTRING(SJS.command,CHARINDEX('\',sjs.command)+1,charindex('"',sjs.command,CHARINDEX('\',sjs.command))-CHARINDEX('\',sjs.command)-1)
		And SJS.subsystem = 'ssis'
	Left Outer Join msdb.dbo.sysjobs SJ
		On SJS.job_id = SJ.job_id
	Left Outer Join master.sys.server_principals JOB
		On JOB.sid = SJ.owner_sid
Go

This script is set to work out of the gate with SQL 2008. Should you want it to work with SQL 2005 the change is simple. Change the sysssispackages table to sysdtspackages90. As you can see, the query joins the SSIS table to the jobsteps table with a pretty nasty string extraction. There are other ways of extracting this information (I’m sure of it). This works quite well for what it is intended.

Using this script, you can find out the jobowner, the packageowner, and the packagetype. This is pretty good information to have on hand if you have several ssis packages that are stored in msdb and are run from a job. One thing this script does not yet handle is if the SSIS file is stored on the file system. Note that I only coded it so far to work with files stored in SQL. When looking in the jobsteps table, you can tell the difference quickly by seeing that those stored in msdb have a /SQL at the beginning of the command string. Those in the filesystem have a /FILE en lieu of that /SQL.

In a future revision I will work on parsing the package name out of that string that represents those stored in the file system. And despite that nasty join, this runs quickly on my systems. I am open to suggestions or other solutions that can provide this kind of insight.

NULLIF

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Scripts, TSQL

Comments: 3 Comments

Published on: March 30, 2011

Do you use NULLIF? For me, this command has been seldom used. Because of that, I have been dabbling with it a bit lately to make sure I have a better understanding of it.

What is it?

This is a function that compares two values. If the two values are the same, then the result of the operation is a Null matching the datatype of the first expression. If the two expressions are different, then the result is the value of the first expression.

Seems pretty simple.

In Action

If you look online at MSDN, you can see a couple of examples and a more complete description of what this function is and does. The MSDN article can be found here. I wanted something a little different and decided to visualize it differently for my learning. So here is what I did.

Code block

WITH randnums AS (
	SELECT TOP 100
			RowNum = ROW_NUMBER()  OVER (ORDER BY (SELECT 1))
			,FirstVal = ABS(CHECKSUM(NEWID()))%10+1
			,SecVal = ABS(CHECKSUM(NEWID()))%10+1
		FROM Master.dbo.SysColumns t1
			CROSS JOIN Master.dbo.SysColumns t2
	)
 
SELECT RowNum,FirstVal,SecVal, NULLIF(FirstVal,SecVal) AS 'Null if Equal'
	FROM randnums
	ORDER BY RowNum

This will give me a nice random sampling of values to compare. I simply compare the firstval column to the secondval column. Both columns are populated with random numbers. As a part of the result set, I am labeling the comparison field to something descriptive of the field. I am returning all of the columns so I can see what the values are, and the result of the comparison. This visualization can help to understand what is happening with the code. Now I know that if I see a null value, then the two columns are equal.

Do You Solution?

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Projects, Scripts, SQL, SSMS

Comments: No Comments

Published on: March 29, 2011

Do you ever find yourself working on different scripts at once? Maybe you are working on something that tests certain functionality in SQL Server, you have some presentation scripts you are working on, and possibly three or four scripts related to a project at work. I find myself in a situation like this from time to time – multiple scripts open checking this or that.

How do you organize your scripts? Do you leave them all open in SSMS? Do you create a folder structure on your disk somewhere and set yourself a reminder task? I have left them all open in SSMS on occasion because I wanted to come back to them and work on them and it can help remind me where I am in the thought process and what needs to be done next.

Did you know that there is a feature in SSMS to help you with organizing yourself? It’s not there just to help you get organized – but it can also help with efficiency and recoverability of files. I’ll explain the recoverability a bit later.

SSMS has a feature called “Solution Explorer.” Much like Visual Studio or BIDs (which is Visual Studio), you have the ability to create a solution file to manage your projects. This solution can then be stored on disk and updated as you progress through the solution or projects.

If you want to display this feature, click the View Menu from the toolbar and then select “Solution Explorer.” This will open a new pane in your management studio from which you can add new or existing projects as well as rename the solution. To the right is an example of one of my solutions. In this image, you can see that I have two projects displayed associated with this particular solution. In these projects, I have loaded numerous script files for each of the projects as I work on them. Some of these scripts are testing the results of stored procs, while others may be stored procs, and yet others may be tables. What the script performs immaterial to Solution Explorer.

Should I decide that I need multiple files open from the project, I can highlight them all and open them all at once. Nothing earth shattering, but now I have an easy way to see the files associated with each task or project as I work on the task/project.

The project files are stored in XML format and are saved off to disk in a directory of your liking. An unfortunate problem with this kind of storage is the sorting of your scripts in the project. Despite this problem there is a way to fix it. I found the fix for that at this blog. It is nice to have scripts sorted properly to your liking. I recommend giving that blog a read and I would recommend the use of this feature in SSMS – if you aren’t doing something already (for instance in Visual Studio, SVN or something like that).

I like having the ability to see all of the scripts quickly that I may need on the same screen I am working in. I can bounce quickly between the scripts as needed and I don’t necessarily have to keep them open indefinitely while working out the task at hand.

I mentioned earlier that it also helps with the recoverability of files. Let me explain a bit. Have you ever been in the middle of working on a script and then the dreaded forced shutdown occurs due to updates? Just in case you did not get your files saved, SSMS has an autorecover feature as well as an autosave feature. Files that are Autorecover and Autosave (recovered) are saved commonly in Solution1. They are stored in your user profile directory and can be brought back so you can continue working on them.

Should you need to find these files, here is a common path you can use to retrieve them.

%userprofile%\Documents\SQL Server Management Studio\Backup Files\Solution1

Do you want to learn more about this feature? Check out the Microsoft documentation on the topic. You can view that here.

Something that I really like about this feature is the ability to create a script either directly in the project, add an existing script, or take a script that you started working on outside of the project and move it into the project from within SSMS. It took me a bit of looking to find how to add a script from SSMS directly to the project (when not initiated from within the project). It is really easy to do once you find it. Here is how to do it.

With the focus in SSMS active on the script you wish to move, click on the File menu. From the File menu you will have an option to Move the script that you are currently working on in SSMS. From there Select the appropriate project and it is that simple.

*Note: In the example of the image being displayed, you can see that the query was SQLQuery1.sql that I wanted to move.

SQL Server Startup

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Database Maintenance, Scripts, SQL Internals, SQL Maintenance

Comments: 5 Comments

Published on: March 23, 2011

From time to time I see a question asking how to determine the last time a SQL Server was either restarted or when the last time was that the database was used. For the first question there is a pretty common answer that works for SQL 2000 and up. In answering both questions though, I am going to show you three quick similar scripts that can be used to find this information. The catch is that these queries will not work on SQL 2000. On the flipside, it should give you an idea of what can be done to find out the same info for SQL 2000.

TempDB

Code block

WITH lastDBaccess AS (
	SELECT
		DB_NAME(database_id) AS DatabaseName,
		MAX(COALESCE(last_user_seek,last_user_scan,last_user_lookup)) AS LastRead,
		MAX(last_user_update) AS LastWrite
	FROM sys.dm_db_index_usage_stats
	GROUP BY database_id
)
SELECT CDate.create_date AS ServerStartupDate
	,LA.* FROM lastDBaccess LA
	Cross Apply (SELECT create_date FROM sys.databases
					WHERE name = 'tempdb') CDate
	ORDER BY DatabaseName

A common answer to the question of when was SQL Server last started is to check the Create_Date of the tempdb database. Since tempdb is recreated every time SQL Server is restarted, it is a pretty accurate timestamp of the server start. This query was written to work for SQL 2005 and above, but you can still find the create_date of the tempdb database in SQL 2000 by using a different query.

sys.dm_exec_requests

Code block

WITH lastDBaccess AS (
	SELECT
		DB_NAME(database_id) AS DatabaseName,
		MAX(COALESCE(last_user_seek,last_user_scan,last_user_lookup)) AS LastRead,
		MAX(last_user_update) AS LastWrite
	FROM sys.dm_db_index_usage_stats
	GROUP BY database_id
)
SELECT CDate.start_time AS ServerStartupDate
	,LA.* FROM lastDBaccess LA
	Cross Apply (SELECT start_time FROM sys.dm_exec_requests
					WHERE session_id = 1) CDate
	ORDER BY DatabaseName

In this query we access one of those Dynamic Management views available in SQL 2005 and up. In this case, we look at the first process id similar to what one may have done with sysprocesses in sql 2000. Now is probably a good time to also note something that is happening inside the CTE. Notice the COALESCE that is being used? I am taking the Max from those fields that are associated with read type of activities in a database. This information is being pulled from a DMV called sys.dm_index_usage_stats. This query will get me the most recent activity for reads and writes in each database (notice the group by).

Default Trace

Code block

DECLARE @TraceFile VARCHAR(256)
 
SELECT @TraceFile = CONVERT(VARCHAR(256),VALUE) FROM [FN_TRACE_GETINFO](NULL)
WHERE [property] = 2;
 
;
WITH lastDBaccess AS (
	SELECT
		DB_NAME(database_id) AS DatabaseName,
		MAX(COALESCE(last_user_seek,last_user_scan,last_user_lookup)) AS LastRead,
		MAX(last_user_update) AS LastWrite
	FROM sys.dm_db_index_usage_stats
	GROUP BY database_id
)
SELECT CDate.StartTime AS ServerStartupDate
	,LA.* FROM lastDBaccess LA
	Cross Apply (SELECT StartTime
					FROM [FN_TRACE_GETTABLE](@TraceFile, DEFAULT)
					WHERE EventSequence = 1
						And IsSystem = 1) CDate
	ORDER BY DatabaseName

The last one is to make use of the default trace in SQL 2005, 2008 and 2008 R2. The default trace records the time of certain events that occur in the database and that includes when the database is starting up. To find the server startup time in this case, I am checking the EventSequence and IsSystem fields for a value of 1. As you can see, I also dumped the filepath for the default trace file into a variable and use that in the function to get the data.

When comparing performance of these three options, they pretty much came out even. Each took its turn performing faster – with regards to time. However, the default trace method did return a more expensive execution plan every single time. With how fast these queries run, I’m not real sure that that is very conclusive nor that it would be a heavy concern. These queries are designed more for the occasional run by the DBA rather than to be executed millions of times a day. You decide which will be best for you. I think with the use of the function to get the default trace info, I was mildly surprised that the query performed that well.

Another thing to note is that each of these methods will return a slightly different timestamp. For instance, the tempdb timestamp gives me 3:21:28 and the sys.dm_exec_requests produces a timestamp of 3:21:36 and the tracefile shows a timestamp of 3:21:24. I don’t have any hard foundation for why that is – just speculation that seems to make sense. I will just leave it at that though.

In addition to these methods there is also the option of checking the system event logs to determine the SQL Service startup time. This information should help you when investigating your databases and server and would be something handy to keep in your toolbox.

«page 2 of 6»

May 2012
M	T	W	T	F	S	S
« Apr
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

Content

Now Reading

Planned books:

Throne of Fire, the by Rick Riordan
Seeds of Rebellion (Beyonders) by Brandon Mull

Current books:

ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running by Danny Dreyer, Katherine Dreyer
Advanced Marathoning – 2nd Edition by Peter Pfitzinger, Scott Douglas
SQL Server MVP Deep Dives by Nielsen Paul, Delaney Kalen, Machanic Adam, Tripp Kimberly, Randal Paul, Low Greg
A World Without Heroes (Beyonders) by Brandon Mull

Recent books:

The Red Pyramid (The Kane Chronicles, Book 1) by Rick Riordan
Fablehaven, Book 5:Keys to the Demon Prison by Brandon Mull
Catching Fire (The Second Book of the Hunger Games) by Suzanne Collins
Pirate Latitudes by Michael Crichton
The Battle of the Labyrinth (Percy Jackson and the Olympians, Book 4) by Rick Riordan

View full Library

SQL RNNR

Foreign Key Hierarchy Update

SQL Statistics – another Spin

CTE, Recursion and Math

T-SQL Tuesday #18 – CTEs

To CTE or not to CTE, that is the Question

Think Think Think

Physical Row Location

T-SQL Tuesday #17 – APPLY Knowledge

Admins Apply Within

SSIS Job Ownership

NULLIF

What is it?

In Action

Do You Solution?

SQL Server Startup

TempDB

sys.dm_exec_requests

Default Trace

Categories

Popular Posts

Blogroll

Now Reading

Planned books:

Current books:

Recent books:

SQLHelp