pmurwin - Tumblr blog

[pmurwin]

Read from XML into Temp Table

SQL Server 2008 apparently has a bug that causes issues when trying to read from XML into a Temporary Table.  This can be overcome with the simple addition of an option to your query:

Insert into #TaxRate(DealerCode, City, [State], Zip, Name, CityZip, TaxRateText) select c.value('(Column1)[1]','varchar(50)') ,c.value('(Column2)[1]','varchar(50)') ,c.value('(Column3)[1]','varchar(20)') ,c.value('(Column4)[1]','varchar(20)') ,c.value('(Column5)[1]','varchar(200)') ,c.value('(Column6)[1]','varchar(75)') ,c.value('(Column7)[1]','varchar(max)') from @XML.nodes('DocumentElement/Rates') t(c) where c.value('(Column1)[1]','varchar(50)') <> 'DlrPCode' Option (optimize for(@xml = null))

The final line of the query (Option) is the trick.

Additional Information is available at the following locations:

MS Connect

StackOverflow

[pmurwin]

TSQL Temporary Tables

Found some interesting information while researching "keepfixed plan" option for TSQL queries.  A couple of links:

http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/what-is-the-difference-between--table-variable-and-temp-tables.aspx

http://blog.sqlauthority.com/2010/02/18/sql-server-plan-recompilation-and-reduce-recompilation-performance-tuning/

http://msdn.microsoft.com/en-us/library/ms189741(v=sql.105).aspx

Basically a temp table can have statistics applied to it where as a table variable cannot.  This is explains why temp tables can help improve performance without making any changes other than converting a table variable to a temp table.  The table now allows the optimizer to apply statistics to the contents of the table.

*Some might say this should be common knowledge but it's never come up for me as a problem in the past so I'd argue that the clarification here is useful. :)

[pmurwin]

TSQL Query Running Processes

This query can be run in a database to see what queries are actively being executed.

select session_id, request_id, start_time, status, left(txt.text, 100) as BeginningText, ( SELECT TOP 1 SUBSTRING(txt.text, statement_start_offset/2, ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),txt.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS sql_statement, prev_error, nest_level, blocking_session_id, open_transaction_count, open_resultset_count, row_count, wait_type, wait_time, last_wait_type, wait_resource, percent_complete, estimated_completion_time from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) AS txt where database_id = db_id() and status <> 'sleeping' and session_id <> @@spid

[pmurwin]

Searching a string for multiple characters in TSQL

TSQL doesn't offer any native RegEx functionality but it does offer the PATINDEX function which allows us to perform some very basic character searches.  The sample below allows us to search for the presence of multiple characters within a string.    The sample is specifically looking for XML reserved characters.

Testing of PATINDEX against a table with 11,000 records shows little difference between this and a separate OR statement for each character to test for.  It does appear to offer a bit more consistency in timings than the stack of OR statements.  It's also a bit cleaner should we need to include other items in the where clause.

declare @testvalue varchar(max) set @testvalue = 'San<t>aC%l"a''u&s' select patindex('%[&''"%><]%',@testvalue)

[pmurwin]

Find SQL Server Jobs with a specific command

The following query can be run against the msdb database to find jobs that utilize a specific command regardless of if that's an exe or a stored procedure, etc...

select * from sysjobs j join sysjobsteps s on s.job_id = j.job_id where s.command like '%pStoredProc%'

[pmurwin]

TSQL Xquery Optimizations

This information was gathered from Jeff Wharton's blog: http://beyondrelational.com/modules/2/blogs/66/posts/9923/part-5-openxml-and-xquery-optimisation-tips.aspx

XQuery

The following performance tips where sourced from Bob Beauchemin’s SQL Server 2008 Microsoft Certified Master (MCM) XML readiness video.

1) Queries that use parent axis generate extra query plan steps so use multiple CROSS APPLY steps to get nodes at multiple nesting levels rather than using the parent axis.

Bad:

select o.value('../@id', 'int') as CustID, o.value('@id', 'int') as OrdID from T cross apply x.nodes('/doc/customer/orders') as N(o)

Good:

select c.value('@id', 'int') as CustID, o.value('@id', 'int') as OrdID from T cross apply x.nodes('/doc/customer') as N1(c) cross apply c.nodes('orders') as N2(o)

2) Move ordinals to the end of path expressions

Bad:

/book[1]/@isbn

Good:

(/book/@isbn)[1]

3) Avoid predicates in the middle of path expressions

Bad:

book[@ISBN = "1-8610-0157-6"]/author[first-name = "Davis"]

Good:

/book[@ISBN = "1-8610-0157-6"] "n" /book/author[first-name = "Davis"]

4) Use context item in predicate to lengthen path in exist()

Bad:

SELECT * FROM docs WHERE 1 = xCol.exist('/book/subject[text() = "security"]')

Good:

SELECT * FROM docs WHERE 1 = xCol.exist('/book/subject/text()[. = "security"]')

5) Casting from XML to SQL

BAD:

CAST( CAST(xmldoc.query('/a/b/text()') as nvarchar(500)) as int)

GOOD:

xmldoc.value('(/a/b/text())[1]', 'int')

BAD:

node.query('.').value('@attr', 'nvarchar(50)')

GOOD:

node.value('@attr', 'nvarchar(50)')

6) Use temp table (insert into #temp select … from nodes()) or Table-valued parameter instead of XML

7) Specify a single root node in query as the optimizer assumes that XML can be a fragment.

[pmurwin]

SQL XML Manipulation

This is a collection of sql scraps useful in manipulating XML in a sql stored proc or script.

--== get rid of NULL values, since the .modify() doesn't know how to handle NULL UPDATE TableName SET XMLParam = '' WHERE XMLParam IS NULL --== Insert the parameter "paramname" if it does not already exist. Default value is "0" UPDATE TableName SET XMLParam.modify(' insert into (/paramlist)[1]') WHERE XMLParam.exist('(/paramlist/param[@name="paramname"])[1]')=0 --== Modify the parameter "paramname" UPDATE TableName SET XMLParam.modify(' replace value of (/paramlist/param[@name="paramname"]/@value)[1] with "1" ') --== How to pull out the item name and item value from XML DECLARE @iXML XML SELECT @iXML = '<root><ID>1</ID><ThingNumber>2</ThingNumber></root>' SELECT c.query('local-name(.)').value('.','varchar(50)') as 'ElementName', c.value('.', 'varchar(200)') as 'ElementValue' FROM @iXML.nodes('root/node()') t(c) --Insert a new attribute declare @test xml set @test = ' ' set @test.modify(' insert attribute MiddleName {"Smith" } into (/root/Employee[@EmployeeID=6700])[1] ') select @test

[pmurwin]

Find Foreign Keys Referencing a TableName

This script is useful for finding forgotten foreign keys that link one table to another.  Useful when trying to put together a deletion script either for data or for the tables themselves.

select t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn from sys.foreign_key_columns as fk inner join sys.tables as t on fk.parent_object_id = t.object_id inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id where fk.referenced_object_id = (select object_id from sys.tables where name = 'HumanResources.Employee') order by TableWithForeignKey, FK_PartNo

[pmurwin]

Find lines of code in a database

select sum(t.lines_of_code) as lines_ofcode from ( select o.name as sp_name ,(len(c.text) - len(replace(c.text, char(10), ''))) as lines_of_code ,case when o.xtype = 'P' then 'Stored Procedure' when o.xtype in ('FN', 'IF', 'TF') then 'Function' end as type_desc from sysobjects o join syscomments c on c.id = o.id where o.xtype in ('P', 'FN', 'IF', 'TF') and o.category = 0 and o.name not in ('fn_diagramobjects', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams', 'sysdiagrams') ) t select t.sp_name as name, sum(t.lines_of_code) - 1 as lines_ofcode, t.type_desc as typedesc from ( select o.name as sp_name ,(len(c.text) - len(replace(c.text, char(10), ''))) as lines_of_code ,case when o.xtype = 'P' then 'Stored Procedure' when o.xtype in ('FN', 'IF', 'TF') then 'Function' end as type_desc from sysobjects o join syscomments c on c.id = o.id where o.xtype in ('P', 'FN', 'IF', 'TF') and o.category = 0 and o.name not in ('fn_diagramobjects', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams', 'sysdiagrams') ) t group by t.sp_name, t.type_desc --having sum(t.lines_of_code) - 1 > 500 order by sum(t.lines_of_code) - 1

[pmurwin]

Checking databases on a server for something

Often times it would be convenient to be able to check a particular "thing" in every database on a server.  I keep this bit of code handy for those situations.  It gets a list of online databases that have a name like whatever you choose.  Once it has that list it uses a cursor to run a small bit of code and list any database that matches the criteria...

declare @cDBName varchar(150) ,@sql varchar(max) declare cDB cursor local fast_forward for select name from sys.databases where state_desc = 'ONLINE' and name like '%Adventure%' --This is simply a filter you might apply to the name order by name open cDB while 1=1 begin fetch next from cDB into @cDBName if @@fetch_status <> 0 begin break; end select @sql = 'if exists(select 1 from [{DBNAME}].HumanResources.Employee where EmployeeID > 4 select ''{DBNAME}''' set @sql = replace(@sql, '{DBNAME}',@cDBName) exec(@sql) --print @sql end close cDB deallocate cDB

[pmurwin]

How to stub a scalar function in T-SQL

It should be noted that this manner of stubbing a function does not work for both scalar and table universally.  The below example is scalar only, once the stub is created altering it to become a table based function will not work.

if object_id('[dbo].[FunctionStub]') is null begin Declare @sql nvarchar(max) set @sql = N'CREATE FUNCTION [dbo].[FunctionStub] ( @a datetime ) RETURNS datetime AS BEGIN return @a END' exec(@sql) end GO

[pmurwin]

How to create T-SQL object stubs

In the event that we want to update/modify a sql object we can drop the object and re-create it which is really easy but causes issues. Alternatively we can check for object existence and create a stub first. Then we can alter the existing proc (which may be a stub if it never existed).

USE AdventureWorks GO IF OBJECT_ID('dbo.uspGetEmployeeDetails') IS NULL -- Check if SP Exists  EXEC('CREATE PROCEDURE dbo.uspGetEmployeeDetails AS SET NOCOUNT ON;') -- Create dummy/empty SP GO ALTER PROCEDURE dbo.uspGetEmployeeDetails -- Alter the SP Always  @EmployeeID INT AS BEGIN  SET NOCOUNT ON;    SELECT   HRE.EmployeeID   , PC.FirstName + ' ' + PC.LastName AS EmployeeName   , HRE.Title AS EmployeeTitle   , PC.EmailAddress AS EmployeeEmail   , PC.Phone AS EmployeePhone  FROM   HumanResources.Employee AS HRE   LEFT JOIN Person.Contact AS PC    ON HRE.ContactID = PC.ContactID  WHERE HRE.EmployeeID = @EmployeeID END GO