Comparing Simple Efficiencies: T-SQL UDF vs SQLCLR UDF for Splitting Strings

Originally posted in Idera: http://blog.idera.com/sql-server/performance-and-monitoring/comparing-simple-efficiencies-t-sql-udf-vs-sqclr-udf-for-splitting-strings/

Recap of T-SQL vs SQLCLR (pseudo) Debate

There have been numerous posts about benefits of using T-SQL vs SQLCLR, and vice versa. And we all know the mantra – T-SQL for data access, SQLCLR for anything that is computationally intense tasks.

SQL Server is a relational database, and works best with set based operations and direct data access. If you need to do straightforward INSERTs, UPDATEs, DELETEs, SELECTs, stick with T-SQL unless you want to do lots of overtime trying to figure out why you’re data access suddenly became slow.

So when do we use SQLCLR instead of T-SQL?

There are already lots of discussions – even books – about SQLCLR advantages. I will defer you to them (check out the references section), but I will provide a very brief list of scenarios when you might want to consider SQLCLR:

  • Interaction outside SQL Server If you need to work with the OS, files, registry etc.
  • Validation If you need to validate phone numbers, email addresses, postal codes, or any patterns
  • Complex computations If you need running aggregates, complex math equations (what is the square root of x to the nth power divided by 2 * pi?), financial analytics maybe?
  • Custom Data Types If you need to create your own custom business-specific data types. We also have to remember that XML and GEOGRAPHY/GEOMETRY are great additions to SQL Server, and these are technically CLR data types.

The debate between T-SQL vs SQLCLR sometimes can be taken out of context. I call it a pseudodebate, because sometimes it’s made to seem that SQLCLR is meant to replace T-SQL, when it’s not. SQLCLR is *not* evil. It’s just another tool to help you do your job. It *can* become evil though, if you misuse it.

While there are some overlaps in scenarios where you can use both, these two should really be complementary. Where T-SQL is slow or lacking, SQLCLR should at least be considered and tested. Again, consider the right tool for the right job.

Continue reading

Different T-SQL Date Variations for Date Dimensions

Just another handy code snippet for generating dates for date dimensions in your data warehouse.

I am providing just the select statement here, but to generate (massive) date records, just create and set your start and end date variables, and enclose your insert and the code below in your WHILE loop.

Continue reading

How to Programmatically Add a User/Group to your SSRS Report Using PowerShell

Some of my projects are still in SSRS 2005 and PowerShell v1.0, so this script works and is tested on this environment only, for now.
I plan to port this to SSRS2008, PowerShell v2.0 when I get the chance.

I don’t think the code will change much; although one major change would be how I’m creating the proxy right now. PowerShell v2.0 has a cmdlet called New-WebServiceProxy.

To get this to work for PS v1.0, for now, I use New-WebServiceProxy.ps1 from http://poshcode.org/538 to create the proxy.

Steps are fairly simple
1. Create the proxy
2. Create an array of policies (ie existing users/groups for a particular report or folder)
3. Create a new policy
4. Create a new role, and add it to your policy
5. “Re”-set your policies — ex $reportserverproxy.SetPolicies($itempath,$newpolicies);

Continue reading

Retrieve SSRS Report Definition, Role Assignments, Executions Using T-SQL

Just a few queries against the ReportServer database that might come in handy…
Of course, these are just some sample queries. Tune and modify according to your needs …
Continue reading

SQL Server XML Red Gate Ebook, XQuery Labs

Jacob Sebastian is a SQL Server XML Guru!

Check out his collection for XQUery Labs. This is the first 12 of his series, and right now he has 43 and counting:

XQuery Sample Scripts

* XQuery Lab 1 – Transforming rows to columns
* XQuery Lab 2 – An example using OUTER APPLY
* XQuery Lab 3 – Filtering specific nodes
* XQuery Lab 4 – Joining XML Nodes with a Relational Table
* XQuery Lab 5 – Working with Namespaces
* XQuery Lab 6 – Processing Header-Detail information
* XQuery Lab 7 – Extracting a comma separated list of values
* XQuery Lab 8 – How to update the attribute value of an XML variable?
* XQuery Lab 9 – How to delete an attribute from an XML variable?
* XQuery Lab 10 – How to insert an attribute to an XML variable
* XQuery Lab 11 – How to insert an element to an XML variable
* XQuery Lab 12 – Different ways of reading values from an XML variable

Jacob Sebastian has also released a free ebook via RedGate – The Art of XSD – SQL Server XML Schema Collections

Check it out, all 483 pages! 🙂

How to Get Definition for Stored Procedures, UDFs and Triggers using T-SQL

If you want to list the definition for a stored procedure, User Defined Function, or Trigger, you can use one of the following ways:

Alternative 1: sp_helptext (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— using sp_helptext

sp_helptext ‘dbo.your_object_name’

[/sql]

Alternative 2: syscomments (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— using syscomments

SELECT [text]
FROM sys.syscomments
WHERE OBJECT_NAME(id) = ‘your_object_name’

[/sql]

Alternative 3: OBJECT_DEFINITION (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— using built in function OBJECT_DEFINITION

SELECT OBJECT_DEFINITION(OBJECT_ID(‘your_object_name’))

[/sql]

How to Search for Columns in SQL Server

There are several ways to look for a column in your SQL Server database.

Alternative 1: INFORMATION_SCHEMA.COLUMNS (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @colName NVARCHAR(30)
SET @colName = ‘name’

SELECT
TABLE_CATALOG AS ‘Database’,
TABLE_SCHEMA AS ‘Schema’,
TABLE_NAME AS ‘Table’,
COLUMN_NAME AS ‘Column’
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME LIKE ‘%’ + @colName + ‘%’

[/sql]

Alternative 2: sys.columns (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

SELECT
sys.tables.name AS ‘Table’,
sys.columns.name AS ‘Column’
FROM
sys.columns
INNER JOIN sys.tables
ON sys.tables.object_id = sys.columns.object_id
WHERE
sys.columns.name LIKE ‘%’ + @colName + ‘%’

[/sql]

Alternative 3: Object Search (SSMS)

In SQL Server 2000, there was an “Object Search” tool in Query Analyzer to search for objects.

To get to this tool in SQL Server 2000, either
– Go to Tools > Object Search, or
– Press F4

Unfortunately this was removed in SQL Server 2005.

But back again in SQL Server 2008:
– Go to View > Object Explorer Details
– Type object name in the Search bar
– Press Enter to Search

Yay!

Handling Division By Zero Scenarios in T-SQL

Sometimes it is inevitable to encounter scenarios that will give division by zero errors

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

SELECT @dividend/@divisor

/*
Error:
Msg 8134, Level 16, State 1, Line 7
Divide by zero error encountered.
*/

[/sql]

What you can do is you can code around it, so your users and your app do not get this error.
Continue reading

SQLXML : How to Work With XML Elements (or Nodes) in SQL Server

Assume this is your XML snippet
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @authorsXML XML

SET @authorsXML = ‘
<Author>
<ID>172-32-1176</ID>
<LastName>White</LastName>
<FirstName>Johnson</FirstName>
<Address>
<Street>10932 Bigge Rd.</Street>
<City>Menlo Park</City>
<State>CA</State>
</Address>
</Author>

[/sql]

Note that the examples below show how you can manipulate XML nodes – but most operations require singleton values. Ie, the changes must affect one and only one node. Thus in most the examples we specify the index of the node we want to target.

For example:

[sql collapse=”false” firstline=”1″ gutter=”false” smarttabs=”true” tabsize=”4″ toolbar=”false”]

(/Author/LastName)[1]

[/sql]

which means we are only targetting the first instance of LastName under the Author node. If you need to do a mass update, you may need to use a cursor.

Continue reading

SQLXML : How To Save XML Query Results to a File Using BCP

One way to save your XML query results to the file system is by using bcp (bulk copy program).

Be aware of the following before deciding to use bcp for your regular export requirements:

  • bcp is a program external to SSMS. If you need to use this from within your scripts, you will need to enable xp_cmdshell. xp_cmdshell is an extended stored procedure that allows external command line processes to be executed from within SQL Server. Enabling xp_cmdshell is considered to be a big no no in terms of security because this opens up avenues for malicious attacks through SQL Server.
  • Depending on how much data you need to export, you may need to batch your export to overcome rowsize limitations of bcp.

If you intend to use bcp from within SSMS, you will need to enable xp_cmdshell first, otherwise you will get the following error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

The following example walks you through enabling xp_cmdshell, and using bcp from within SSMS to save your XML query to an external file.

Continue reading