Exam 70-461 Querying Microsoft SQL Server

To prepare the Exam 70-461 Querying Microsoft SQL Server, we can refer to the following link:-

https://drive.google.com/file/d/0By910BPLu7nTaHZ0bTRydDlyRzg/view?usp=sharing

PowerPivot vs Tabular Data Model vs Multidimensional Cube (MS 10778)

This diagram is to help MS10778 students to understand the overview of Microsoft Business Intelligence Solution, PowerPivot and Tabular Data Model is designed in Microsoft In Memory X-Velocity technology, the performance is very fast compare to traditional Multidimensional Cubes.

 

In MS10778 hyper-v image, there are 3 SSAS Instances -

1. MIA-SQLBI - This instance is the main SSAS instance
2. MIA-SQLBI\POWERPIVOT - This instance is Tabular Data Model used by SharePoint
3. MIA-SQLBI\SQL2 - This instance hosted Workspace Database and the real Tabular Data Model databases.

 

T-SQL Split

I still have seen developer asking me how to Split in SQL Server, although we can enable CLR Integration in SQL Server, here is the classic one from SQL Server Magazine that I have used since SQL Server 2000 and it is very powerful, Treat Yourself to Fn_Split() - This UDF lets you emulate VB's Split() function and parameter arrays

CREATE FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can抰 find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0 
END
END

END

RETURN
END
GO

-- Sample Code for Using Fn_Split() in Pubs to Emulate a Parameter Array

Use Pubs

CREATE  PROCEDURE procPubsTitleView

@sAuthors varchar(8000)
AS
SELECT title, au_lname, price FROM Pubs.dbo.titleview
JOIN dbo.fn_Split(@sAuthors, ',') 
ON au_lname = value
RETURN
GO

Community Launch for Heroes

I have presented LINQ to SQL Introduction on 28 June 2008 (Saturday) at Microsoft Auditorium, details at http://www.microsoft.com/malaysia/events/communityheroes/

Project Server

I implemented a windows sharepoint services + SQL Server 2000 Reporting Services project in Australia last year, I used Smart Part http://www.microsoft.com/belux/nl/msdn/community/columns/u2u/smartpart.mspx developed for Sharepoint Web Parts, the result is good, when combining with SQL Reporting Services can become a impressive BI portal.

Anyway the new SQL Server 2005 has been released last year after end of my Project, everybody have to pick up the new skill asap to keep the competency :)

SQL Black Belt Advance Articles

Recently just involved in SQL Server 2000 Reporting Services Business Intelligence Development, found out a must read articles for any one who would like to master in SQL Server, the web site is at http://databasejournal.com/article.php/1459531 by William E. Pearson, III

Dynamic SQL FAQ 1: Convert conditional dynamic SQL string to stored proc

A lot of programmers coded the SQL query in dynamic SQL either in presentation layer or inside stored procedure, it has created a lot of problems in performance, security, maintenanbility, network and memory. More in The Curse and Blessings of Dynamic SQL.

From today onwards, I will compose a FAQ how to convert dynamic SQL to proper stored proc.

Dynamic SQL FAQ 1: Convert conditional dynamic SQL string to stored proc

The first FAQ I always heard is "I have a query based on a parameter, if the parameter is ALL, there will be no filter, if the parameter is a comma delimeter string (CSV), the query result will be filtered by the multiple values string in CSV format.

It has 2 challenges here:-
1) How to split the comma delimeter string
2) How to filter the result when it is not 'All'

It can be achieved easily by using dynamic SQL, the sample below is using SQL Server Northwind database with querying from SQL Query Analyser:-

declare @strSQL varchar(1000), @param varchar(100)
set @param = 'All'
set @param = '1,2,3'
set @strSQL = 'select * from employees'
if @param <> 'All'
set @strSQL = @strSQL + ' where employeeid in (' + @param + ')'
exec(@strSQL)

Let's see the 1st challenge, to split the CSV string, we can use the split function provided from Microsoft SQL Server Magazine Treat Yourself to Fn_Split():
This UDF lets you emulate VB's Split() function and parameter arrays

Then you can split the string by using the query easily:-

SELECT value FROM dbo.fn_split(@s, ',')

To filter either 'All' or not 'All', we have to use the power of "OR" operator, then the above dynamic SQL can be converted to proper SQL query as below:-

declare @s varchar(100)

set @s = '1,2,3'
--set @s = 'All'

select * from employees
where (@s <> 'All' AND employeeid in (SELECT value FROM dbo.fn_split(@s, ',')))
OR (@s = 'All')

Let's give me FIVE!

Convert dynamic SQL to stored procedure by using CASE

I always access other programmer code, I saw the code interesting and challenging today - the code is in ASP VBScript + dynamic SQL, the purpose of the code is to generate the SQL query string based on web page querystring, the code is as below:-

StrSQL = "select * from fact_drive_failures2 where day_id = " & request("day_id")
If request("floor") <> "" Then
StrSQL = strSQL & " and floor_id = " & request(“floor_id”)
End If
I believe this is a very common practice for many programmers. With the dynamic SQL string with the ASP code, it has many pitfalls in security, performance, permission, network traffic and many more disadvantages...

I convert this code to MS SQL Server stored procedure:-

CREATE PROCEDURE dbo.GET_DATA
(
@pn_DayId int,
@pv_FloorId varchar(1000)
)
AS
Select * from fact_data
where day_id = @pn_dayid
and floor_id =
case @pv_Floor = ‘’ then floor_id
else @pv_floor end

Now the stored procedure can be called by front-end application without worrying the string concatenation, easier to maintain, better security and the performance is much better.