Archive for the ‘ T-SQL Tips and Tricks ’ Category

Demystifying T-SQL Subqueries–Part II

In the last post (Demystifying T-SQL Subqueries – Part I) we looked at how to use scalar subqueries. Let’s continue our subquery adventure.

As with Part I, the following T-SQL query samples are using the Chinook database.

Subqueries which return a single list of values

Result

Description

Sample

B

Single Column
or Column List
or Single List of Values

clip_image004_thumb

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.8/10 (9 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)

Demystifying T-SQL Subqueries–Part I

When I teach SQL Server Development/T-SQL courses, subqueries is usually one of those topics that students find confusing. Usually it’s a head scratcher, although there are some who just get it.

So let’s try to demystify subqueries.

Very simply put, a subquery is just a query within a query. It’s a SELECT inside another SELECT.

Now let’s break it down. Let’s start with understanding what a query gives you first.

Understanding results of regular queries

Before we can understand subqueries, we need to understand first the different results we get from regular queries. A regular query always results in a dataset. It gives you the following variations of results:

Result

Description

Sample

A

Scalar Value

subquery that returns scalar value

B

Single Column
or Column List
or Single List of Values

subquery that returns a list (single column) of values

C

Table

subquery that returns a table

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.6/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 2 votes)

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.

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.8/10 (11 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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);

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.7/10 (14 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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 …
Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.9/10 (17 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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! :)

VN:F [1.9.22_1171]
Rating: 8.7/10 (3 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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)

-- using sp_helptext

sp_helptext 'dbo.your_object_name'

Alternative 2: syscomments (T-SQL)

-- using syscomments

SELECT 1 
FROM sys.syscomments
WHERE OBJECT_NAME(id) = 'your_object_name'

Alternative 3: OBJECT_DEFINITION (T-SQL)

-- using built in function OBJECT_DEFINITION

SELECT OBJECT_DEFINITION(OBJECT_ID('your_object_name'))


VN:F [1.9.22_1171]
Rating: 8.9/10 (18 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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)


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 + '%'

Alternative 2: sys.columns (T-SQL)


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 + '%'

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!

VN:F [1.9.22_1171]
Rating: 9.7/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`