SQL RNNR

1 DBA's Professional Blog

Tags: Scripts

A Trio of Functions

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Scripts, Security, TSQL

Comments: No Comments

Published on: January 17, 2012

I found myself perusing an execution plan the other day. I know, big surprise there. This execution plan showed me some interesting things I had never really paid much attention to in the past. When I started paying attention to these things, I found myself jumping down a rabbit hole.

It all started with a bit of curiosity to see if I could make an “admin” script perform a bit better. The execution plans started showing some table valued functions that I knew I hadn’t included in the query. Subsequently, I found myself wondering – what is that?

The items that made me curious were all table valued functions. There were three of them (different) in this particular plan. I started looking hither and thither to find these functions. It didn’t take long to figure out that I could find them in the mssqlsystemresource database. So I proceeded to making a copy of the database and attaching a copy of it for further learning opportunities.

The three functions are:

SYSSESSIONS

FNGETSQL

SYSCONNECTIONS

Knowing the query and based on these names, I began looking in the appropriate DMOs to see what I could find. Here are the scripts for each of those DMO’s.

Code block

USE [mssqlsystemresource_test]
GO
 
/****** Object:  View [sys].[dm_exec_connections]    Script Date: 01/16/2012 22:39:32 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [sys].[dm_exec_connections] AS
	SELECT *
	FROM OPENROWSET(TABLE SYSCONNECTIONS)
 
GO
 
/****** Object:  View [sys].[dm_exec_sessions]    Script Date: 01/16/2012 22:39:37 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [sys].[dm_exec_sessions] AS
	SELECT *
	FROM OPENROWSET(TABLE SYSSESSIONS)
 
GO
 
/****** Object:  UserDefinedFunction [sys].[dm_exec_sql_text]    Script Date: 01/16/2012 22:39:55 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [sys].[dm_exec_sql_text](@handle VARBINARY(64))
RETURNS TABLE
AS
	RETURN SELECT * FROM OPENROWSET(TABLE FNGETSQL, @handle)
 
GO

Cool. I can now see the internals of each of the DMOs – sort of. You see, there is an OPENROWSET call in each of these objects. Each call uses an undocumented feature called TABLE. This is an internal command used by the engine and you won’t find much on it (mostly people asking what it is and Microsoft saying they won’t tell).

Here is the fun part. If you try to run that code outside of querying the DMO, you will receive error messages. If you try to create a new view utilizing the Openrowset, it will fail. It is reserved for internal usage. With that said, just continue to use the DMO and you will be fine. Personally, I was curious to find out how it worked so I tried a bit to find it.

So there you have it. If you are curious what is the internal makings of these DMOs, you can script them from the resource database. Alternatively, you could also run sp_helptext. I like to check these things from the resource database. It feels more like an adventure. Have fun with it and see what you will learn.

Missing Indexes

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Index Maintenance, Scripts, TSQL

Comments: 9 Comments

Published on: January 12, 2012

SQL Server has means built into it to track possible missing indexes. This used to be found through the use of the Index Tuning Wizard. The process has improved over time (you can sort of see that from my April Fools post).

As luck would have it, I was recently asked to help fix a query that was somewhat related to the whole process. You see, since SQL Server 2005, there are DMOs that help to track metadata related to column and index usage. And if there a query is repeated enough that doesn’t have a good matching index, then the engine may think that a new index is needed. This potential index information is recorded and becomes visible via the DMOs.

The query that I was asked to help fix was a dynamic query within a cursor that read information from the DMOs in order to generate some missing index information. That particular query was failing for a couple of reasons, but on the same token it gave me an idea to modify and adapt the query to something more in line with what I might use. After all, the queries that I used were in need of updating and this gets me started in that direction.

First, a little on why the query was failing. A common problem with dynamic queries is the placement of quotes and having enough quotes in all required locations. When you start nesting more levels into a dynamic query, the more confusing the quotes can get. When running into something like this, I like to print the statement that I am trying to build dynamically. If it doesn’t look right, then adjust the quotes until it looks right.

The second reason it was failing was a simple oversight. Whether building dynamic queries or just using variables in your code, make sure you use properly sized variables. In this case, the dynamic query variable was substantially inadequate. The use of a print statement also helps to catch these types of errors rather quickly.

There were also a few things that would cause me to not use the original query in any environment. The first problem is that the script contains a column which is the create statement for each proposed index. In this create statement, all indexes were given the same name. That would be a bit of a problem.

The next issue is my concern with the creation of indexes without ensuring that the index is going to provide greater benefit than cost. Better stated is that the creation of these indexes just because the script spewed them out is no better than to create all of the indexes proposed by the Database Engine Tuning Advisor. For this, I added a cautionary statement next to every index create statement.

So with these tweaks, as well as other less significant tweaks, here is the query.

Code block

DECLARE AllDatabases CURSOR FOR
SELECT [name] FROM master.dbo.sysdatabases WHERE dbid > 4
 
OPEN AllDatabases
 
DECLARE @DBNameVar NVARCHAR(128),@STATEMENT NVARCHAR(MAX)
 
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'--CHECKING DATABASE ' + @DBNameVar
SET @STATEMENT = N'USE [' + @DBNameVar + ']'+ CHAR(13) +';' +CHAR(13)
+ N'
SELECT SO.name
		, ((CONVERT(Numeric(19,6), migs.user_seeks)+CONVERT(Numeric(19,6), migs.unique_compiles))
			*CONVERT(Numeric(19,6), migs.avg_total_user_cost)
			*CONVERT(Numeric(19,6), migs.avg_user_impact/100.0)) AS Impact
		,''DO NOT AUTO CREATE THESE INDEXES - Creating these indexes could be as bad as blindly using DTA'' as Note
		, ''CREATE NONCLUSTERED INDEX IDX_'' + SO.name +''_'' + STUFF (
		(SELECT ''_'' + column_name FROM sys.dm_db_missing_index_columns(mid.index_handle) WHERE column_usage IN (''Equality'',''InEquality'') FOR XML PATH (''''))
		, 1, 1, '''')  + '' ON ['+@DBNameVar+'].'' + schema_name(SO.schema_id) + ''.'' + SO.name COLLATE DATABASE_DEFAULT + '' ( '' + IsNull(mid.equality_columns, '''') + CASE WHEN mid.inequality_columns IS NULL
		THEN ''''
		ELSE CASE WHEN mid.equality_columns IS NULL
		THEN ''''
		ELSE '','' END + mid.inequality_columns END + '' ) '' + CASE WHEN mid.included_columns IS NULL
		THEN ''''
		ELSE ''INCLUDE ('' + mid.included_columns + '')'' END + '';'' AS CreateIndexStatement
		, mid.equality_columns
		, mid.inequality_columns
		, mid.included_columns
	FROM sys.dm_db_missing_index_group_stats AS migs
		INNER JOIN sys.dm_db_missing_index_groups AS mig
			ON migs.group_handle = mig.index_group_handle
		INNER JOIN sys.dm_db_missing_index_details AS mid
			ON mig.index_handle = mid.index_handle
			AND mid.database_id = DB_ID()
		INNER JOIN sys.objects SO WITH (nolock)
			ON mid.OBJECT_ID = SO.OBJECT_ID
	WHERE (migs.group_handle IN
			(
			SELECT TOP (500) group_handle
			FROM sys.dm_db_missing_index_group_stats WITH (nolock)
			ORDER BY ((CONVERT(Numeric(19,6), migs.user_seeks)+CONVERT(Numeric(19,6), migs.unique_compiles))
				*CONVERT(Numeric(19,6), migs.avg_total_user_cost)
				*CONVERT(Numeric(19,6), migs.avg_user_impact/100.0)) DESC))
		AND OBJECTPROPERTY(SO.OBJECT_ID, ''isusertable'')=1
	ORDER BY 2 DESC , 3 DESC' 
 
PRINT @STATEMENT
EXEC SP_EXECUTESQL @STATEMENT
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
 
CLOSE AllDatabases
DEALLOCATE AllDatabases

As I post this message, as I tend to do, I am looking for ways to improve upon the query and make it better. This script should only be used with caution. It is to provide an insight into potential missing indexes in each database. A score is assigned to each potential index. It is with the highest score indexes, that I typically begin analysis to improve performance. I typically start from a query and execution plan to performance tune. There are times when an alternative starting point is necessary. This script is a tool for those times. Please head the warning that these should be created with extreme caution.

Database Data and Log Size Info

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Scripts, TSQL

Comments: 5 Comments

Published on: December 5, 2011

Have you ever wanted or needed to get the data and log file size for all of the databases in your server?

I have seen scripts that would get this information in the past. All of the scripts seemed to use either a cursor or the proc from Microsoft called sp_MSforeachdb. Having seen some of these scripts, I wondered if there might be a different way of doing it (that was also different than you see in my TableSpace Scripts).

After putting some thought into it, I decided on a plan of attack and went to work on building a query that would do the trick. But before I continue, I will advise that running a script like this on large databases may take a while to execute.

Keeping these things in mind (potential slowness when run on large db and wanting to try something different), I came up with this method (tested on Instances with small databases as well as Instances with 2TB Databases).

Code block

DECLARE @MaxDataSize	INT
SELECT @MaxDataSize = MAX(d.cntr_value)/1024/1024 --Convert to GB
    FROM sys.dm_os_performance_counters d
    WHERE d.OBJECT_NAME like '%databases%'
       and d.counter_name = 'Data File(s) Size (KB)'
    GROUP BY d.counter_name,d.OBJECT_NAME
 
DECLARE
@DbSize TABLE (Database_Id INT, DataUsedMB FLOAT)
 
IF @MaxDataSize > 250 --If Largest DB is >250GB then use forEachdb, else use Physical Index Stats
BEGIN
	INSERT INTO @DbSize (Database_Id,DataUsedMB)
	EXEC sp_MSforeachdb
	'select db_id(''?'') , (SUM(ps.reserved_page_count)*8)/1024 from ?.sys.dm_db_partition_stats ps'; 
 
	SELECT
        DB_ID(instance_name) Database_Id
        , instance_name Database_Name
        ,CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024 AS DataFileSizeMB
        ,isnull(ca.DataUsedMB,.1) AS DataUsedMB
        ,(CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1) AS DataFreeMB
        , CASE WHEN pvt.[DATA FILE(s) SIZE (KB)] = 0
			THEN 0
			ELSE ROUND((((CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1)) / (CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024))*100, 2)
			END DataFreePercent
        ,CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT)/1024 AS LogFileSizeMB
        ,CAST(pvt.[LOG FILE(s) Used SIZE (KB)] AS FLOAT)/1024 AS LogFileUsedMB
        ,(CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT) - pvt.[LOG FILE(s) Used SIZE (KB)])/1024 LogFreeSizeMB
        ,pvt.[PERCENT LOG Used] AS LogUsedPercent
        ,100-pvt.[PERCENT LOG Used] AS LogFreePercent
    FROM sys.dm_os_performance_counters d
    PIVOT (SUM(cntr_value) FOR counter_name IN ([DATA FILE(s) SIZE (KB)],[LOG FILE(s) SIZE (KB)],[LOG FILE(s) Used SIZE (KB)],[PERCENT LOG Used])) pvt
    LEFT Outer Join (SELECT Database_Id,Null AS page_count,DataUsedMB FROM @DbSize) CA
		ON (ca.database_id = DB_ID(instance_name))
    WHERE OBJECT_NAME like '%databases%'
		and pvt.[DATA FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) Used SIZE (KB)] IS not null
		And pvt.[PERCENT LOG Used] IS not null
		and instance_name <> '_Total'
    ORDER BY Database_ID
END
ELSE
BEGIN
SELECT
        DB_ID(instance_name) Database_Id
        , instance_name Database_Name
        ,CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024 AS DataFileSizeMB
        ,isnull(ca.DataUsedMB,.1) AS DataUsedMB
        ,(CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1) AS DataFreeMB
        , CASE WHEN pvt.[DATA FILE(s) SIZE (KB)] = 0
			THEN 0
			ELSE ROUND((((CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024) - isnull(ca.DataUsedMB,.1)) / (CAST(pvt.[DATA FILE(s) SIZE (KB)] AS FLOAT)/1024))*100, 2)
			END DataFreePercent
        ,CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT)/1024 AS LogFileSizeMB
        ,CAST(pvt.[LOG FILE(s) Used SIZE (KB)] AS FLOAT)/1024 AS LogFileUsedMB
        ,(CAST(pvt.[LOG FILE(s) SIZE (KB)] AS FLOAT) - pvt.[LOG FILE(s) Used SIZE (KB)])/1024 LogFreeSizeMB
        ,pvt.[PERCENT LOG Used] AS LogUsedPercent
        ,100-pvt.[PERCENT LOG Used] AS LogFreePercent
    FROM sys.dm_os_performance_counters d
    PIVOT (SUM(cntr_value) FOR counter_name IN ([DATA FILE(s) SIZE (KB)],[LOG FILE(s) SIZE (KB)],[LOG FILE(s) Used SIZE (KB)],[PERCENT LOG Used])) pvt
    LEFT Outer Join (SELECT ips.database_id,SUM(page_count) AS page_count,CAST((SUM(page_count)*8) AS FLOAT)/1024 AS DataUsedMB
		FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,'sampled') ips
		GROUP BY ips.database_id
		) CA
		ON (ca.database_id = DB_ID(instance_name))
    WHERE OBJECT_NAME like '%databases%'
		and pvt.[DATA FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) SIZE (KB)] IS not null
		And pvt.[LOG FILE(s) Used SIZE (KB)] IS not null
		And pvt.[PERCENT LOG Used] IS not null
		and instance_name <> '_Total'
    ORDER BY Database_ID
END

You will see that I did not entirely eliminate the looping mechanism. Truth be told, it is so much faster on servers with Large Databases.

Also take note of the DMV that is in use in this query. I am taking advantage of the performance monitor stats that are exposed through the DMV sys.dm_os_performance_counters. One caveat to this method, is that this DMV shows us the size of the resource database as well. I think it is fine to report that information back – but it won’t change much over time. It is for that purpose that I use the Left Join in the query.

The other trick that I utilized is to Pivot that performance monitor data. I think this works better than to write a bunch of sub-queries to generate the same sort of data layout.

You have probably also noted that I have chosen 250GB as the tipping point in this query. There is no particular reason for that size – just a large enough database size to make the point. For some, the tipping point may be a much smaller database size. Feel free to change this value to suit your needs.

Some other caveats. The perfmon cntr_value data is presented as an Int. For more accurate math operations, I chose to cast many of these to a Float.

I also ran into a case where the perfmon data was reporting 0 as the Data File Size for one of my databases. I had to throw a case statement in for the Data File Used Percent in order to avoid a Divide by 0 error.

I also wrapped the DataUsedMB in an ISNULL. This was due to the model and mssqlsystemresource databases not having data in the dm_db_physical_stats function. I could have left those as NULL, but wanted to show something for them both.

Check it out and Enjoy!

Table Hierarchy goes CS

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Documentation, Foreign Key, Scripts, SQL Script, TSQL

Comments: No Comments

Published on: November 16, 2011

At the urging of a friend, this script is being updated for those that are dealing with Case Sensitivity. The first few rounds, I neglected Case Sensitivity and never tested for that. It makes sense to have this script updated for that if anybody out there is using it.

The updates are simple enough, it is just frustrating if you run into an error caused by CS and then you waste time troubleshooting it. Believe me, it has happened to me recently – and I don’t much like it.

Without further ado, here is the udpated script:

Code block

DECLARE    @StartParentTable    VARCHAR(256)
 
SELECT @StartParentTable = 'Calendar'
 
;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)

Still on the todo list is to make this bad boy run faster in the event of circular references. If you find something else with it that you think could use adjusting, let me know.

TSQL Challenge 63 – Update

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Scripts, Sudoku, TSQL Challenge

Comments: No Comments

Published on: November 14, 2011

If you recall, I like Sudoku. I even posted a script for solving it via TSQL. I went so far as to enter my script into a TSQL Challenge. That all started way back in August. Today, I have an update!!

I was notified this morning from BeyondRelational.com that I have earned a new badge. Cool, what’s the badge? I clicked the link and it took me to this badge.
Huh? I’m a winner of the SQL Sudoku Challenge? Awesome!

Looking it over, I am winner #3. This means I could have done better with my solution. And looking at the other solution stats, it appears I will need to find time to see what the others did to make their solutions go sooooo fast. I have some learning to do – woohoo.

So, now that means I need to post my solution.

Code block

--
--These Variables are intended to be used as input parameters if made into a proc.
DECLARE @SudokuNo INT = 3 --my script is setup to allow the table to contain multiple puzzles.
		,@SudokuGivens VARCHAR(100) = '53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79'
									--'  15       6     7    9  4   5   1  9   4   8  3   6   2  7    8     7       35  '
									--'   9 1   6     5            7 2   1        29    3    4   6 7 55     8     1     '
		,@FromTableorString	TINYINT = 1 --1 = run from TC63, else run from Input Parm
 
DECLARE @SudTable	TABLE (RowCol INT PRIMARY KEY CLUSTERED, ConcatRow VARCHAR(10))
 
IF @FromTableorString = 1
BEGIN
--Populate Data for missing vectors (Col/Row) with A space
--Use an Isnull and Outer Apply in case there are no givens for a particular row.
	WITH dual(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
	),rowcols AS (SELECT DISTINCT ROW.N AS ROW,Col.N AS Col FROM Dual ROW Cross Apply Dual Col)
 
	--Concatenate into a string if the SudokuSource is a Table
	SELECT @SudokuGivens = (SELECT  Isnull(t.DATA,0)
		FROM TC63 T
		RIGHT Outer Join rowcols D
			ON D.ROW = T.ROW
			And D.Col = T.Col
			And SudokuNo = @SudokuNo
		FOR xml PATH(''))  
 
END
	SELECT @SudokuGivens = REPLACE(@SudokuGivens,'0',' ') --If from table, replace commas.  From a String can have spaces or commas
 
--Solve the Sudoku - into a string
;WITH x( s, ind ) AS
( SELECT @SudokuGivens, CHARINDEX(' ',@SudokuGivens ) AS ind
  UNION all
  SELECT CONVERT(VARCHAR(100),SUBSTRING( s, 1, ind - 1 ) + CONVERT(CHAR(1),z) + SUBSTRING( s, ind + 1 ,81))
       , CHARINDEX(' ', s, ind + 1 ) AS ind
  FROM x
    CROSS APPLY ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) z (z)
  WHERE ind > 0
  and not exists (SELECT null
					FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) ww (lp)
					WHERE z = SUBSTRING( s, ( ind - 1)% 9  - 8 + lp * 9, 1 )
						or    z = SUBSTRING( s, ( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
						or    z = SUBSTRING( s, (( ( ind - 1 ) / 3 )%3) * 3
                                      + ( ( ind - 1 ) / 27 ) * 27 + lp
                                      + ( ( lp - 1 ) / 3 ) * 6
                                   , 1 )
                 )
), Sud AS (
--Create a 9 record result set that has the string solution duplicated 9 times.  Then show only relevant 9 data for each row
SELECT TOP 9 SUBSTRING(s, ROW_NUMBER() OVER (ORDER BY s) * 9 - 8, ROW_NUMBER() OVER (ORDER BY s) * 9 - (ROW_NUMBER() OVER (ORDER BY s) * 9 - 9)) AS ConcatRow
FROM x
Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) y(N)
WHERE ind = 0
)
 
--Populate a Table Variable for further Row/Col manipulation
INSERT INTO @SudTable (RowCol,ConcatRow)
	SELECT Row_Number() OVER (ORDER BY (SELECT 1)),ConcatRow
		FROM Sud
 
--Pivot the data out to produce a 9x9 grid
SELECT @SudokuNo AS SudokuNo,c1.RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
	FROM @SudTable S
	Cross Apply (SELECT RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
					FROM (SELECT S.RowCol
							,ColNo = Row_Number() OVER (Partition BY RowCol ORDER BY ConcatRow)
							,DATA = SUBSTRING(ConcatRow, ROW_NUMBER() OVER (Partition BY S.RowCol ORDER BY ConcatRow), 1)
					FROM @SudTable S
						Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
						) Intr
						Pivot (
						MAX(DATA)
						FOR ColNo IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])
		) PVT) AS C1
	WHERE c1.RowCol = S.RowCol
	ORDER BY S.RowCol ASC

Sadly, that is not the most recent version of the script that I had. I had intended on submitting this version, which is still slightly faster.

Code block

--
--These Variables are intended to be used as input parameters if made into a proc.
DECLARE @SudokuNo INT = 3 --my script is setup to allow the table to contain multiple puzzles.
		,@SudokuGivens VARCHAR(100) = '53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79'
									--'  15       6     7    9  4   5   1  9   4   8  3   6   2  7    8     7       35  '
									--'   9 1   6     5            7 2   1        29    3    4   6 7 55     8     1     '
		,@FromTableorString	TINYINT = 1 --1 = run from TC63, else run from Input Parm
 
DECLARE @SudTable	TABLE (RowCol INT PRIMARY KEY CLUSTERED, ConcatRow VARCHAR(10))
 
IF @FromTableorString = 1
BEGIN
--Populate Data for missing vectors (Col/Row) with A space
--Use an Isnull and Outer Apply in case there are no givens for a particular row.
	WITH dual(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
	),rowcols AS (SELECT DISTINCT ROW.N AS ROW,Col.N AS Col FROM Dual ROW Cross Apply Dual Col)
 
	--Concatenate into a string if the SudokuSource is a Table
	SELECT @SudokuGivens = (SELECT  Isnull(t.DATA,0)
		FROM TC63 T
		RIGHT Outer Join rowcols D
			ON D.ROW = T.ROW
			And D.Col = T.Col
			And SudokuNo = @SudokuNo
		FOR xml PATH(''))  
 
END
	SELECT @SudokuGivens = REPLACE(@SudokuGivens,'0',' ') --If from table, replace commas.  From a String can have spaces or commas
 
--Solve the Sudoku - into a string
;WITH x( s, ind ) AS
( SELECT @SudokuGivens, CHARINDEX(' ',@SudokuGivens ) AS ind
  UNION all
  SELECT CONVERT(VARCHAR(100),SUBSTRING( s, 1, ind - 1 ) + CONVERT(CHAR(1),z) + SUBSTRING( s, ind + 1 ,81))
       , CHARINDEX(' ', s, ind + 1 ) AS ind
  FROM x
    CROSS APPLY ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) z (z)
  WHERE ind > 0
  and not exists (SELECT null
					FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) ww (lp)
					WHERE z = SUBSTRING( s, ( ind - 1)% 9  - 8 + lp * 9, 1 )
						or    z = SUBSTRING( s, ( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
						or    z = SUBSTRING( s, (( ( ind - 1 ) / 3 )%3) * 3
                                      + ( ( ind - 1 ) / 27 ) * 27 + lp
                                      + ( ( lp - 1 ) / 3 ) * 6
                                   , 1 )
                 )
), Sud AS (
--Create a 9 record result set that has the string solution duplicated 9 times.  Then show only relevant 9 data for each row
SELECT TOP 9 SUBSTRING(s, ROW_NUMBER() OVER (ORDER BY s) * 9 - 8, ROW_NUMBER() OVER (ORDER BY s) * 9 - (ROW_NUMBER() OVER (ORDER BY s) * 9 - 9)) AS ConcatRow
FROM x
Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) y(N)
WHERE ind = 0
)
 
--Populate a Table Variable for further Row/Col manipulation
INSERT INTO @SudTable (RowCol,ConcatRow)
	SELECT Row_Number() OVER (ORDER BY (SELECT 1)),ConcatRow
		FROM Sud
 
--Pivot the data out to produce a 9x9 grid
SELECT @SudokuNo AS SudokuNo,c1.RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
	FROM @SudTable S
	Cross Apply (SELECT RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
					FROM (SELECT S.RowCol
							,ColNo = Row_Number() OVER (Partition BY RowCol ORDER BY ConcatRow)
							,DATA = SUBSTRING(ConcatRow, ROW_NUMBER() OVER (Partition BY S.RowCol ORDER BY ConcatRow), 1)
					FROM @SudTable S
						Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
						) Intr
						Pivot (
						MAX(DATA)
						FOR ColNo IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])
		) PVT) AS C1
	WHERE c1.RowCol = S.RowCol
	ORDER BY S.RowCol ASC

Still, I am certain that (without having looked at the other winning solutions) this is not on par with the best solutions. And I have a lot to learn.

Column Level Permissions

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Audit, Scripts, Security

Comments: No Comments

Published on: September 19, 2011

Did you know that you can grant permissions down to the column level in SQL Server? Well, if you didn’t know that – you do now.

It is actually rather simple to grant permissions at the column level. This can be demonstrated by the following script.

Code block

GRANT SELECT ON ColorPlate (ColorID) TO testU

If you want to check out more on that syntax, read here.

And then…

Why is it important to know that you can do this? Well, it is quite possible you have some of these permissions already in place. It is possible you may have inherited something like this. Just maybe there is a business requirement requiring that certain users or groups only have access to certain data within certain columns.

That brings up a new problem then. How do you find out what columns have specific permissions applied to certain users? Well, that is actually pretty straight forward. We can query the system views and determine column level permissions.

Code block

SELECT dp.grantee_principal_id,p.name AS UName
		,dp.permission_name,c.name
		,OBJECT_NAME(o.OBJECT_ID) AS TabName
	FROM sys.database_permissions dp
	INNER JOIN Sys.objects O
		ON dp.major_id = o.OBJECT_ID
	INNER JOIN sys.columns C
		ON c.OBJECT_ID = O.OBJECT_ID
		AND c.column_id = dp.minor_id
	INNER JOIN sys.database_principals P
		ON p.principal_id = dp.grantee_principal_id

The previous query is a really simple version of how to find this information. As you can see, I am simply returning the UserName, TableName and ColumnName along with the permission in effect on that column.

You should also be able to see that the mapping between these system views is pretty straight forward as well. Major_id maps to object_id and column_id maps to minor_id.

Conclusion

This query can be of good use to determine permissions in place for columns in tables within your database. Furthermore, you can even use this query to simply test your curiosity as you check to determine what has been put into effect in the databases you manage.

There are more complex methods to determine these permissions. With there being more complex methods, I am sure there are also some easier methods. Let me know what you do to query these permissions.

Send DBMail

by Jason Brimhall

Categories: News, Professional, SSC

Tags: administration, Scripts, SQL Server

Comments: No Comments

Published on: August 15, 2011

With SQL Server 2005, Microsoft improved the methods available for DBAs to send email from SQL Server. The new method is called Database Mail. If you want to send emails programmatically, you can now use sp_send_dbmail. You can read all about that stored procedure here.

What I am really looking to share is more about one of the variables that has been introduced with sp_send_dbmail. This parameter is @query. As the online documentation states, you can put a query between single quotes and set the @query parameter equal to that query. That is very useful.

Why am I bringing this up? Doing something like this can be very useful for DBAs looking to create cost-effective monitoring solutions that require emailing result sets to themselves. I ran across one scenario recently where a DBA was looking for help doing this very thing. In this case, the query was quite simple. He just wanted to get a list of databases with the size of those databases to be emailed.

Here is a quick and dirty of one method to do such a thing.

Code block

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLDBA' ,
    @recipients = 'myEmail@myDomain.com' ,
    @subject = 'List of Databases'
    ,@query = 'Exec sp_databases'

As I said, this is a real quick and dirty example of how to send an email with query results. The results of the query in the @query parameter (in this case) will be in the body of the email. A slightly modified version of that first solution is as follows.

Code block

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLDBA' ,
    @recipients = 'myEmail@myDomain.com' ,
    --@body = @messageBody ,
    @subject = 'List of Databases'
    ,@query = '    select
        DATABASE_NAME   = db_name(s_mf.database_id),
        DATABASE_SIZE   = convert(int,
                                    case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
                                    when convert(bigint, sum(s_mf.size)) >= 268435456
                                    then null
                                    else sum(s_mf.size)*8 -- Convert from 8192 byte pages to Kb
                                    end)
    from
        sys.master_files s_mf
    where
        s_mf.state = 0 and -- ONLINE
        has_dbaccess(db_name(s_mf.database_id)) = 1
    group by s_mf.database_id'

This is only really slightly modified because I took the guts of sp_databases and dumped that into this query. The modification being that the remark column was removed. Why do this? Well, to demonstrate two different methods to get the same data from the @query parameter. We can either pass a stored procedure to the parameter, or we can build an entire SQL statement and pass that to the parameter.

This is just a simple little tool that can be used by DBAs. Enjoy!

TSQL Tuesday 21 – FAIL FAIL FAIL

by Jason Brimhall

Categories: News, Professional, SSC, TSQL Tuesday

Tags: Scripts, TSQL Tuesday

Comments: No Comments

Published on: August 10, 2011

It is TSQL Tuesda… err Wedn… err Tuesday for August 2011. This month the party is a day later and bringing us oodles of failure.

Adam Machanic is hosting this month. He thought it would be a good idea to poke and prod us while getting us to torture ourselves. It seems to be a month long theme (as he alluded to in his announcement of the topic).

See, the topic this month requires us to share our failures. More specifically we are to share our ugly code that we know better than to do. Crap Code is a failure. At least we have come to recognize that it was crap code and that it was indeed a failure. The question now is this: What have we done about what we learned?

I put a fair amount of thought into this topic. I could come up with several possible experiences for this topic. I kept running into a wall though. That wall was recalling detailed facts and examples of those experiences. Many of them were documented and left behind with former employers. I no longer have the “dirt” on those dirty little secrets.

Then it hit me! I had posted some blog entries about some of my experiences and was certain that something would be applicable. You know what? It’s a good thing I have this blog going. Not only does it help me to learn many things and write better, it serves as a repository for introspection and recollection. So, I will rely on a couple of posts from the early days of this blog to help write my entry for TSQL Tuesday this month. And, I hope that what I share and what I learned from my experience will prove helpful to somebody else.

The FAILURE / Crap Code

If you want, you can read the entire first part here then skip to the next heading. Or you can continue reading as I recount that article.

I had been asked to provide a report that would provide data according to pay periods. I had no calendar table yet I needed to be able to compare dates passed to the report and match them to this pay period calendar. Despite requests to the business (the pay periods for this report did not line up with the normal business pay periods) to get a list of those pay periods in order to create a Calendar table, I got nothing. I did however receive generic instruction as to the general time-lines for these pay periods. Based on this, I decided to get tricky and try to meet some basic guidelines that I created for myself.

The payperiods need to be accessible to the query
No Physical Lookup Table
Do not hard-code the data in the code.

Based on that, I came up with a pretty neat little solution.

Code block

WITH periodstarts (StartID, StartDate,EndDate)AS (
SELECT 1 AS StartID,@StartDate,DATEADD(wk, DATEDIFF(wk, 0, @StartDate) + 2, 0) -1
UNION All
SELECT StartID + 1,DATEADD(wk, DATEDIFF(wk, 0, StartDate) + 2, 0) AS StartDate,DATEADD(wk, DATEDIFF(wk, 0, EndDate) + 2, 0)-1 AS EndDate
FROM PeriodStarts
WHERE DATEADD(wk, DATEDIFF(wk, 0, StartDate) + 2, 0) > StartDate
And Startid < 105

This worked well – for awhile. Performance degraded over time, then it didn’t seem so cool.

Egg On Face

The update to that first article can be read in full here. In short, that method really did not work out as well as I had hoped. In the end, I had to create a table that would need to be maintained – at some point. I populated the data in the table with 5 years worth of pay periods. I hope somebody reads the documentation and takes care of that!

With the implementation of the table, I updated the CTE and the code being run by the report. Performance of this updated version outperformed the first version – even when it was running fast. I probably could have gotten away with doing an Itzik style numbers CTE in lieu of that recursive CTE – as I look back on it now. Again, that is another level of learning and could potentially be an improvement on that first round of crap code I offered up for that report.

Conclusion

I learned from that experience. First, it was a pretty neat little trick. Secondly, I really should have tested it more. Thirdly, I can likely still learn from it and improve on it because I am getting better at writing faster code and testing my proposed solutions (and I keep learning new tips/tricks). Obviously something like this has not deterred me. Quite the opposite really. Because of a little failure like this, I work harder to get better at what I do.

FK Hierarchy v 2.1

by Jason Brimhall

Categories: News, Professional, SSC

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

Comments: No Comments

Published on: August 9, 2011

Last month I published an update to my Foreign Key Hierarchy script. Today, I am providing a new update for that script. A friend (Rémi Grégoire) helped out with some mods to this script. The change for this month is nothing too intrusive. The script is now updated for those databases that are Case Sensitive.

Code block

DECLARE    @StartParentTable    VARCHAR(256)
 
SELECT @StartParentTable = 'my starting table'
 
;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)

This update should make it more usable for any that may be using it or is interested in using it. Thanks for Rémi for taking the time to propose this update.

Activity Monitor and Profiler

by Jason Brimhall

Categories: News, Professional, Scripts, SQLServerPedia Syndication, SSC

Tags: Scripts, TSQL

Comments: 2 Comments

Published on: July 28, 2011

Today I came across a link to a neat little script for SQL 2005 / 2008 to help derive the head blocker in a blocking chain. That script can be found here. I was looking at the script and thought it looked pretty cool and also wondered why it might look a little familiar. Not too big of a concern since many admin type scripts can look familiar.

Then I noticed that the title contained the words Activity Monitor. I wondered to myself, was this taken from activity monitor? So, I decided to play with Profiler for a bit to see if I could generate the same query. The process really isn’t too difficult. Here is what I did.

Open Activity Monitor. Simple enough to do. Right click the instance (in SSMS) you wish to monitor, select Activity Monitor from the context menu.
Open Profiler. Again from within SSMS, click the tools menu and then select SQL Server Profiler.
Start a New trace. When starting the trace ensure that you are connecting to the same instance as you started Activity Monitor. Select the TSQL-Replay template (other tsql will probably work, this is the one that I used). Goto the events selection tab and click on the Column Filters button. Select “LoginName” and then expand “Like” on the right hand side. In the new text box, type the login you are using to authenticate to the SQL Instance in question (domain\user or SQLLogin).
Click run and browse through the results for a bit. It shouldn’t take too long for that query to pop up.
Once you find the query, make sure you stop the trace.

What is the point in this? Well, as explained in that other blog, sometimes it is just better to run a query than to run Activity Monitor. Furthermore, you can take this method and trap many of the queries that Microsoft built into that tool. You can either use them for learning or troubleshooting. This is just one more tool to have in your arsenal in order to become better at your job.

page 1 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

A Trio of Functions

Missing Indexes

Database Data and Log Size Info

Table Hierarchy goes CS

TSQL Challenge 63 – Update

Column Level Permissions

And then…

Conclusion

Send DBMail

TSQL Tuesday 21 – FAIL FAIL FAIL

The FAILURE / Crap Code

Egg On Face

Conclusion

FK Hierarchy v 2.1

Activity Monitor and Profiler

Categories

Popular Posts

Blogroll

Now Reading

Planned books:

Current books:

Recent books:

SQLHelp