Collection of some of the important type of MDX queries which you should be prepared with. The queries refer to sample SSAS database that comes with SSAS installation. Some of the queries used here link back to examples mentioned in Microsoft msn forums.
________________________________________
Q: How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?
A: Simply using bottomcount will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT(
NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] )
, ( [Measures].[Internet Sales Amount] ) )
, 10
, ( [Measures].[Internet Sales Amount] )
) ON ROWS
FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;
________________________________________
Q: How in MDX query can I get top 3 sales years based on order quantity?
A: By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are "OrderBy" and "OrderByAttribute". Lets say we want to see order counts for each year. In Adventure Works MDX query would be:
SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1
FROM [Adventure Works];
Same query using TopCount:
SELECT
{[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1
FROM [Adventure Works];
________________________________________
Q: How do you extract first tuple from the set?
A: Use could usefunction Set.Item(0)
Example:
SELECT {{[Date].[Calendar].[Calendar Year].Members
}.Item(0)}
ON 0
FROM [Adventure Works]
________________________________________
Q: How do you compare dimension level name to specific value?
A: Best way to compare if specific dimension is at certain level is by using 'IS' operator:
Example:
WITH MEMBER [Measures].[TimeName] AS
IIF([Date].[Calendar].Level IS [Date].[Calendar].[Calendar Quarter],'Qtr','Not Qtr')
SELECT [Measures].[TimeName] ON 0
FROM [Sales Summary]
WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3])
________________________________________
Q: MDX query to get sales by product line for specific period plus number of months with sales
A: Function Count(, ExcludeEmpty) counts number of non empty set members. So if we crossjoin Month with measure we will get set that we can use to count members.
Query example:
WITH Member [Measures].[Months With Non Zero Sales] AS
COUNT(CROSSJOIN([Measures].[Sales Amount]
, DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}, [Date].[Calendar].[Month]))
, ExcludeEmpty
)
SELECT {[Measures].[Sales Amount], [Measures].[Months With Non Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])
________________________________________
Q: How can I setup default dimension member in Calculation script?
A: You can use ALTER CUBE statement. Syntax:
ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION , DEFAULT_MEMBER='';
________________________________________
Q: I would like to create MDX calculated measure that instead of summing children amounts,uses last child amount
A: Normally best way to create this in SSAS 2005 is to create real measure with aggregation function LastChild. If for some reason you still need to create calculated measure, just use fuction .LastChild on current member of Date dimension, and you will allways get value of last period child.
Example: We want to see last semester value for year level data. Lets first see what data values are at Calendar Semester level:
SELECT {[Measures].[Internet Order Count]} ON 0
, DESCENDANTS([Date].[Calendar].[All Periods],[Date].[Calendar].[Calendar Semester] ) ON 1
FROM [Adventure Works]
________________________________________
Q: How to calculate YTD monthly average and compare it over several years for the same selected month?
A: MDX Query:
WITH MEMBER Measures.MyYTD AS SUM(YTD([Date].[Calendar]),[Measures].[Internet Sales Amount])
MEMBER Measures.MyMonthCount AS SUM(YTD([Date].[Calendar]),(COUNT([Date].[Month of Year])))
MEMBER Measures.MyYTDAVG AS Measures.MyYTD / Measures.MyMonthCount
SELECT {Measures.MyYTD, Measures.MyMonthCount,[Measures].[Internet Sales Amount],Measures.MyYTDAVG} On 0,
[Date].[Calendar].[Month] On 1
FROM [Adventure Works]
WHERE ([Date].[Month of Year].&[7])
________________________________________
Q: MDX query to get sales by product line for specific period plus number of months with non empty sales.
A: You can use COUNT() function with ExcludeEmpty option. For count function you specify set that is corssjoin of Date members at the month level and measure that you are interested in.
WITH Member [Measures].[Months With Above Zero Sales] AS
COUNT(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) * [Measures].[Sales Amount]
, ExcludeEmpty
)
SELECT {[Measures].[Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])
________________________________________
Q: How do I group dimension members dynamically in MDX? Source: MSDN SSAS Newsgroup.
A: You can create calculated members for dimension and then use them in the query. Example below will create 3 calculated members based on filter condition:
WITH MEMBER [Product].[Category].[Case Result 1] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") < "3"))'
MEMBER [Product].[Category].[Case Result 2] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") = "3"))'
MEMBER [Product].[Category].[Case Result 3] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") > "3"))'
SELECT NON EMPTY {[Measures].[Order Count] } ON COLUMNS
, {[Product].[Category].[Case Result 1],[Product].[Category].[Case Result 2],[Product].[Category].[Case Result 3] } ON ROWS
FROM [Adventure Works]
________________________________________
Q: How can I compare members from different dimensions that have the same key values?
Lets say I have dimensions [Delivery Date] and [Ship Date]. How can I select just records that were Delivered and Shipped the same day?
A: You can use FILTER function and compare member keys using Properties function:
SELECT {[Measures].[Internet Order Count]} ON 0
, FILTER( NonEmptyCrossJoin( [Ship Date].[Date].Children, [Delivery Date].[Date].Children
)
, [Ship Date].[Date].CurrentMember.Properties('Key')
= [Delivery Date].[Date].Properties('Key')
) ON 1
FROM [Adventure Works]
________________________________________
Q: How can I get attribute key with MDX
A:
To do so, use Member_Key function:
WITH
MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_Key
SELECT {Measures.ProductKey} ON axis(0),
[Product].[Product Categories].Members on axis(1)
FROM [Adventure Works]
________________________________________
Q: How do I create a Rolling 12 Months Accumulated Sum (InternetSalesAmtR12Acc) that can show a trend without seasonal variations?
A: Here is query example
WITH MEMBER [Measures].[InternetSalesAmtYTD] AS SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]), Format_String = "### ### ###"
MEMBER [Measures].[InternetSalesAmtPPYTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember)),
[Measures].[Internet Sales Amount]), Format_String = "### ### ###"
MEMBER [Measures].[InternetSalesAmtPY] AS SUM(Ancestor(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),[Date].[Calendar].[Calendar Year]),
[Measures].[Internet Sales Amount]),Format_String = "### ### ###"
MEMBER [Measures].[InternetSalesAmtR12Acc] AS ([Measures].[InternetSalesAmtYTD]+[Measures].[InternetSalesAmtPY] )- [Measures].[InternetSalesAmtPPYTD]
Select {[Measures].[Internet Sales Amount], Measures.[InternetSalesAmtYTD], [Measures].[InternetSalesAmtPPYTD],[Measures].[InternetSalesAmtR12Acc]} On 0,
[Date].[Calendar].[Month].Members On 1
From [Adventure Works]
Where ([Date].[Calendar Year].&[2004]);
________________________________________
Q: How to setup calculated measure as default measure for a cube?
A: Use ALTER Cube statement on measures dimension. Example:
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER=[Measures].[Profit]
________________________________________
Q: How can I write MDX query for the count of customers for whom the earliest sale in the selected time period (2002 and 2003) occurred in a particular Product Category
A: Example of such query:
WITH SET [FirstSales] AS
FILTER(NONEMPTY( [Customer].[Customer Geography].[Customer].MEMBERS
* [Date].[Date].[Date].MEMBERS
, [Measures].[Internet Sales Amount])
AS MYSET,
MYSET.CURRENTORDINAL = 1 or
NOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(MYSET.CURRENTORDINAL-2).ITEM(0)))
MEMBER [Measures].[CustomersW/FirstSales] AS
COUNT(NonEmpty([FirstSales], [Measures].[Internet Sales Amount])),
FORMAT_STRING = '#,#'
SELECT {[Measures].[Internet Sales Amount],[Measures].[CustomersW/FirstSales]} ON 0,
[Product].[Product Categories].[Category] ON 1
FROM [Adventure Works]
WHERE ({[Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Calendar Year].&[2003]}, [Customer].[Customer Geography].[City].&[Calgary]&[AB]);
________________________________________
Q: How do you write MDX query that returns measure ratio to parent value?
A: Below is example on how is ratio calculated for measure [Order Count] using Date dimension. Using parent function, your MDX is independant on level that you are querying data on. In example below, if you query data at year level, ratio will be calculated to level [All]:
WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL
, [Measures].[Order Count]
/
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
)
, FORMAT_STRING = "Percent"
SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods]
} ON 1
FROM [Adventure Works]
Friday, July 8, 2011
SSAS Interview Questions
Whenever one wants to learn something or make sure one is competent enough to take the helm of any challenge in a particular technology, the first thing one needs to know is what one should be knowing. In simple words one should be aware of the topics that one needs to cover, then the next point is how much ground has already been covered and how much is yet to be covered. Below is a list of roughly drafted high level areas of SSAS in no particular order, which can be considered as a descent coverage, whether it's considered for SSAS training / SSAS interview. Keep in view that though the below coverage covers a major ground, it's not exhaustive and it can be used as a reference check to make sure you cover enough in your trainings / to make sure you have covered major fundamental areas.
- Types of Dimensions
- Types of Measures
- Types of relationships between dimensions and measuregroups: None (IgnoreUnrelatedDimensions), Fact, Regular, Reference, Many to Many, Data Mining
- Star Vs Snowflake schema and Dimensional modeling
- Data storage modes - MOLAP, ROLAP, HOLAP
- MDX Query syntax
- Functions used commonly in MDX like Filter, Descendants, BAsc and others
- Difference between EXISTS AND EXISTING, NON EMPTY keyword and function, NON_EMPTY_BEHAVIOR, ParallelPeriod, AUTOEXISTS
- Difference between static and dynamic set
- Difference between natural and unnatural hierarchy, attribute relationships
- Difference between rigid and flexible relationships
- Difference between attirubte hierarchy and user hierarchy
- Dimension, Hierarchy, Level, and Members
- Difference between database dimension and cube dimension
- Importance of CALCULATE keyword in MDX script, data pass and limiting cube space
- Effect of materialize
- Partition processing and Aggregation Usage Wizard
- Perspectives, Translations, Linked Object Wizard
- Handling late arriving dimensions / early arriving facts
- Proactive caching, Lazy aggregations
- Partition processing options
- Role playing Dimensions, Junk Dimensions, Conformed Dimensions, SCD and other types of dimensions
- Parent Child Hierarchy, NamingTemplate property, MemberWithLeafLevelData property
- Cube performance, MDX performance
- How to pass parameter in MDX
- SSAS 2005 vs SSAS 2008
- Dimension security vs Cell security
- SCOPE statement, THIS keyword, SUBCUBE
- CASE (CASE, WHEN, THEN, ELSE, END) statement, IF THEN END IF, IS keyword, HAVING clause
- CELL CALCULATION and CONDITION clause
- RECURSION and FREEZE statement
- Common types of errors encountered while processing a dimension / measure groups / cube
- Logging and monitoring MDX scripts and cube performance
SQL Overview SSIS Package - Retrieving SQL Error Log
SQL Overview SSIS Package - Retrieving SQL Error Log
In part I, I presented how to create a SSIS package to collect the database statuses for all instances. This approach works fine when a single select statement is executed on the remote instance. When using multiple statements or a stored procedure, a modified approach is needed to return the results.
This approach creates a table on the remote instance in the TEMPDB database. The output from multiple queries or stored procedures is collected in this table. After the data has been collected, a single select statement can be used to retrieve the data from the remote instance. This table does not have to be in TEMPDB. It's just a convenient place to put it because every instance has a TEMPDB database and the table does not need to be recoverable.
I want to capture the errors that are in the SQL Server Error Log for all servers. To accomplish this, I will be using the stored procedure xp_readerrorlog to read the SQL Server Error Log files from each server\instance. Only the last two days of records will be retrieved. This will be sufficient because this package is expected to be executed daily. SQL Server 2000 and 2005 have a different format for the ErrorLog file. Therefore the SQL script will need to handle each format. When the data is finally collected from each server\instance, a query can be used to report all errors for every server\instance.
Items used:
• SQL Server Business Intelligence Development Studio for SQL Server 2005 x64 SP2 with hot fix KB934459
• Package from Part I
As it was in Part I, some of these instructions are very detailed and will bore those very familiar with SSIS. I am sorry for that but I wanted a level of detail to allow those still somewhat new to SSIS to be able to follow along.
Create the ErrorLog Table
USE [SQL_Overview]
GO
CREATE TABLE [dbo].[ErrorLog](
[Server] [nvarchar](128) NOT NULL,
[dtMessage] [datetime] NULL,
[SPID] [varchar](50) NULL,
[vchMessage] [nvarchar](4000) NULL,
[ID] [int] NULL
) ON [PRIMARY]
This table will contain all the ErrorLog records for all the entries in the SSIS_ServerList table.
Create ErrorLog TEMPDB Table
This TEMPDB table will be used on each instance to collect the SQL Server error log information. This table must be created on the instance that the package will be executed from before the package can be updated. The package will then create the table on all of the other server\instance listed in the SSIS_ServerList table.
IF OBJECT_ID('tempdb.dbo.ErrorLog') IS NOT NULL
DROP TABLE tempdb.dbo.ErrorLog
GO
CREATE TABLE tempdb.dbo.ErrorLog(
[Server] [nvarchar](128) NOT NULL,
[dtMessage] [datetime] NULL,
[SPID] [varchar](50) NULL,
[vchMessage] [nvarchar](4000) NULL,
[ID] [int] NULL
) ON [PRIMARY]
Updating the SSIS Package
Open the SQL Overview package created in Part I.
Create Tasks
Truncate ErrorLog Table Task
This task will truncate the table ErrorLog in the SQL_Overview database.
• Using the Toolbox, add "Execute SQL Task" object to the Truncate Tables "Sequence Container"
• Settings - Double Click on Icon
•
o Name: Truncate ErrorLog
o Connection: to QASRV.SQL_Overview
o SQL Statement: TRUNCATE Table ErrorLog
o BypassPrepare: False
Load ErrorLog Container
This container will loop through the server names passed in the SRV_Conn variable, connect to each server, and execute three SQL tasks.
1. Add "Foreach Loop Container" to the right of the "Collect Database Status"
2.
1. Connect the "Collect Database Status" container to this object with the green line/arrow
2. Settings
3.
1. General
2.
1. Name: Collect ErrorLog
3. Collection
4.
1. Change Enumerator to Foreach ADO enumerator
2. Select ADO object source variable User::SQL_RS
5. Variable Mapping
6.
1. Add User::SRV_Conn
7. Click OK
4. Right Click on this Container
5. Select Properties
6. Set MaximumErrorCount to 999
3. Add "Execute SQL Task" to the "Collect Error Log" container.
This Task will be used to create a TEMPDB table and populate it with the last 2 days of error logs on the remote instance. The SQL used is long and complex. Testing it is recommended.
4.
1. Settings - Double Click on Icon
2.
Name: Get ErrorLog
Connection: to MultiServer
SQL Statement:
-- Drop Temporary Tables
IF OBJECT_ID('tempdb..#Errors8') IS NOT NULL
DROP TABLE #Errors8
IF OBJECT_ID('tempdb..#Errors9') IS NOT NULL
DROP TABLE #Errors9
IF OBJECT_ID('tempdb..#ErrorLogs') IS NOT NULL
DROP TABLE #ErrorLogs
IF OBJECT_ID('tempdb.dbo.ErrorLog') IS NOT NULL
DROP TABLE tempdb.dbo.ErrorLog
GO
CREATE TABLE tempdb.dbo.ErrorLog(
[Server] [nvarchar](128) NOT NULL,
[dtMessage] [datetime] NULL,
[SPID] [varchar](50) NULL,
[vchMessage] [nvarchar](4000) NULL,
[ID] [int] NULL
) ON [PRIMARY]
-- Set extract date for 2 days
DECLARE @ExtractDate datetime
SET @ExtractDate = DATEADD(dd,-2,CURRENT_TIMESTAMP)
SELECT 'Extract Date = ' + CONVERT(CHAR(26),@ExtractDate)
-- SQL Server 2000 and 2005 each has different formats for reading the Error Log file
DECLARE @VersionId AS CHAR(1)
SELECT @VersionId = LEFT(CONVERT(VARCHAR(100),SERVERPROPERTY('ProductVersion')),1)
CREATE TABLE #ErrorLogs (intFileId INT
, dtLastChangeDate DateTime NOT NULL
, biLogFileSize bigint)
INSERT INTO #ErrorLogs
EXEC master.dbo.xp_enumerrorlogs
DECLARE @SQL AS VARCHAR(256)
-- Define Temp Table to contain error log messages
IF @VersionId = 8
CREATE TABLE #Errors8 (vchMessage VARCHAR(255), ID INT)
ELSE
CREATE TABLE #Errors9 (LogDate datetime, Processinfo VARCHAR (10), vchMessage NVARCHAR(4000))
-- Processes Error Logs files modified since last Run Date
DECLARE ErrorLog_cursor CURSOR FOR
SELECT intFileId
FROM #ErrorLogs
WHERE dtLastChangeDate > @ExtractDate
OPEN ErrorLog_cursor
DECLARE @intFileId INT
FETCH NEXT
FROM ErrorLog_cursor INTO @intFileId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
-- Load Error Log into temporary Table
IF @intFileId = 0
IF @VersionId = 8
INSERT #Errors8
EXEC master.dbo.xp_readerrorlog
ELSE
INSERT #Errors9
EXEC master.dbo.xp_readerrorlog
ELSE
IF @VersionId = 8
INSERT #Errors8
EXEC master.dbo.xp_readerrorlog @intFileId
ELSE
INSERT #Errors9
EXEC master.dbo.xp_readerrorlog @intFileId
END
FETCH NEXT
FROM ErrorLog_cursor INTO @intFileId
END
CLOSE ErrorLog_cursor
DEALLOCATE ErrorLog_cursor
-- Extract all error log record for last two days
IF @VersionId = 8
BEGIN
INSERT INTO tempdb.dbo.ErrorLog
([Server]
,[dtMessage]
,[SPID]
,[vchMessage]
,[ID])
SELECT @@SERVERNAME
,CASE ISDATE( LEFT(vchMessage,22))
WHEN 1 THEN LEFT(vchMessage,22)
ELSE '1900-01-01'
END
,NULL
,vchMessage, ID
FROM #Errors8
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ErrorLog
([Server]
,[dtMessage]
,[SPID]
,[vchMessage]
,[ID])
SELECT @@SERVERNAME
,LogDate
,Processinfo
,vchMessage, NULL
FROM #Errors9
WHERE LogDate >= @ExtractDate
END
DELETE
FROM tempdb.dbo.ErrorLog
WHERE [dtMessage] < @ExtractDate SELECT * FROM tempdb.dbo.ErrorLog -- Drop Temporary Tables IF OBJECT_ID('tempdb..#Errors8') IS NOT NULL DROP TABLE #Errors8 IF OBJECT_ID('tempdb..#Errors9') IS NOT NULL DROP TABLE #Errors9 IF OBJECT_ID('tempdb..#ErrorLogs') IS NOT NULL DROP TABLE #ErrorLogs BypassPrepare: False Click OK Right Click on Get ErrorLog Select Properties Properties to set MaximumErrorCount to 999 1. Add "Data Flow Task" to the "Collect Error Log" container 2. 1. Connect the "Get ErrorLog" Task to this object with the green line/arrow 2. Rename to Load ErrorLog 3. Right Click on this Task 4. Select Properties 5. Set MaximumErrorCount to 999 3. Next, two data flow elements will be added to the "Data Flow Task". The first will read the ErrorLog table on the remote instance and the other will save it in the local database. 4. Select the Data Flow Tab or double click on icon for the "Data Flow task" 5. Add "OLE DB Source" from toolbox 6. 1. Double Click Icon 2. OLE DB Connection manager: MultiServer 3. Change Data access mode to SQL Command 4. SQL Command Text: SELECT [Server] ,[dtMessage] ,[SPID] ,[vchMessage] ,[ID] FROM [tempdb].[dbo].[ErrorLog] 5. Click Preview to verify the SQL and then click close when done 6. Click OK 7. Add "OLE DB Destination" from toolbox 8. o Connect the "OLE DB Source" to this element with the green line/arrow o Double click on the Icon for OLE DB Destination and make the following changes o 1. OLE connection manager: QASRV.SQL_Overview 2. Name of the table or the view: [dbo].[ErrorLog] 3. Click Mappings and confirm the column mappings are correct 4. Click OK Ready to be Tested • Click Control Flow tab • Save all by pressing Ctrl+Shift +S • Press F5 to run • To review any errors by checking the Progress tab. • When done, click the blue line to get back to edit mode Query the Error Logs SQL Server Error Logs contain a variety of information. Now by using this SSIS package the error logs for all the instances can be queried from a single table. I've created a SQL statement that returns any error or warning messages I believe warrant review for possible problems. SELECT [Server] ,[dtMessage] ,[SPID] ,[vchMessage] ,[ID] FROM [SQL_Overview].[dbo].[ErrorLog] WHERE ([vchMessage] LIKE '%error%' OR [vchMessage] LIKE '%fail%' OR [vchMessage] LIKE '%Warning%' OR [vchMessage] LIKE '%The SQL Server cannot obtain a LOCK resource at this time%' OR [vchMessage] LIKE '%Autogrow of file%in database%cancelled or timed out after%' OR [vchMessage] LIKE '% is full%' OR [vchMessage] LIKE '% blocking processes%' ) AND [vchMessage] NOT LIKE '%\ERRORLOG%' AND [vchMessage] NOT LIKE '%Attempting to cycle errorlog%' AND [vchMessage] NOT LIKE '%Errorlog has been reinitialized.%' AND [vchMessage] NOT LIKE '%found 0 errors and repaired 0 errors.%' AND [vchMessage] NOT LIKE '%without errors%' AND [vchMessage] NOT LIKE '%This is an informational message%' AND [vchMessage] NOT LIKE '%WARNING:%Failed to reserve contiguous memory%' AND [vchMessage] NOT LIKE '%The error log has been reinitialized%' AND [vchMessage] NOT LIKE '%Setting database option ANSI_WARNINGS%' AND [vchMessage] NOT LIKE '%Error: 15457, Severity: 0, State: 1%' AND [vchMessage] <> 'Error: 18456, Severity: 14, State: 16.'
Conclusion
The package now collects from each instance the current status of each database and last two days of error log messages. This is still just start the of what this type of package can do. In an upcoming article I will be providing the full version of this package along with some sample reports.
In part I, I presented how to create a SSIS package to collect the database statuses for all instances. This approach works fine when a single select statement is executed on the remote instance. When using multiple statements or a stored procedure, a modified approach is needed to return the results.
This approach creates a table on the remote instance in the TEMPDB database. The output from multiple queries or stored procedures is collected in this table. After the data has been collected, a single select statement can be used to retrieve the data from the remote instance. This table does not have to be in TEMPDB. It's just a convenient place to put it because every instance has a TEMPDB database and the table does not need to be recoverable.
I want to capture the errors that are in the SQL Server Error Log for all servers. To accomplish this, I will be using the stored procedure xp_readerrorlog to read the SQL Server Error Log files from each server\instance. Only the last two days of records will be retrieved. This will be sufficient because this package is expected to be executed daily. SQL Server 2000 and 2005 have a different format for the ErrorLog file. Therefore the SQL script will need to handle each format. When the data is finally collected from each server\instance, a query can be used to report all errors for every server\instance.
Items used:
• SQL Server Business Intelligence Development Studio for SQL Server 2005 x64 SP2 with hot fix KB934459
• Package from Part I
As it was in Part I, some of these instructions are very detailed and will bore those very familiar with SSIS. I am sorry for that but I wanted a level of detail to allow those still somewhat new to SSIS to be able to follow along.
Create the ErrorLog Table
USE [SQL_Overview]
GO
CREATE TABLE [dbo].[ErrorLog](
[Server] [nvarchar](128) NOT NULL,
[dtMessage] [datetime] NULL,
[SPID] [varchar](50) NULL,
[vchMessage] [nvarchar](4000) NULL,
[ID] [int] NULL
) ON [PRIMARY]
This table will contain all the ErrorLog records for all the entries in the SSIS_ServerList table.
Create ErrorLog TEMPDB Table
This TEMPDB table will be used on each instance to collect the SQL Server error log information. This table must be created on the instance that the package will be executed from before the package can be updated. The package will then create the table on all of the other server\instance listed in the SSIS_ServerList table.
IF OBJECT_ID('tempdb.dbo.ErrorLog') IS NOT NULL
DROP TABLE tempdb.dbo.ErrorLog
GO
CREATE TABLE tempdb.dbo.ErrorLog(
[Server] [nvarchar](128) NOT NULL,
[dtMessage] [datetime] NULL,
[SPID] [varchar](50) NULL,
[vchMessage] [nvarchar](4000) NULL,
[ID] [int] NULL
) ON [PRIMARY]
Updating the SSIS Package
Open the SQL Overview package created in Part I.
Create Tasks
Truncate ErrorLog Table Task
This task will truncate the table ErrorLog in the SQL_Overview database.
• Using the Toolbox, add "Execute SQL Task" object to the Truncate Tables "Sequence Container"
• Settings - Double Click on Icon
•
o Name: Truncate ErrorLog
o Connection: to QASRV.SQL_Overview
o SQL Statement: TRUNCATE Table ErrorLog
o BypassPrepare: False
Load ErrorLog Container
This container will loop through the server names passed in the SRV_Conn variable, connect to each server, and execute three SQL tasks.
1. Add "Foreach Loop Container" to the right of the "Collect Database Status"
2.
1. Connect the "Collect Database Status" container to this object with the green line/arrow
2. Settings
3.
1. General
2.
1. Name: Collect ErrorLog
3. Collection
4.
1. Change Enumerator to Foreach ADO enumerator
2. Select ADO object source variable User::SQL_RS
5. Variable Mapping
6.
1. Add User::SRV_Conn
7. Click OK
4. Right Click on this Container
5. Select Properties
6. Set MaximumErrorCount to 999
3. Add "Execute SQL Task" to the "Collect Error Log" container.
This Task will be used to create a TEMPDB table and populate it with the last 2 days of error logs on the remote instance. The SQL used is long and complex. Testing it is recommended.
4.
1. Settings - Double Click on Icon
2.
Name: Get ErrorLog
Connection: to MultiServer
SQL Statement:
-- Drop Temporary Tables
IF OBJECT_ID('tempdb..#Errors8') IS NOT NULL
DROP TABLE #Errors8
IF OBJECT_ID('tempdb..#Errors9') IS NOT NULL
DROP TABLE #Errors9
IF OBJECT_ID('tempdb..#ErrorLogs') IS NOT NULL
DROP TABLE #ErrorLogs
IF OBJECT_ID('tempdb.dbo.ErrorLog') IS NOT NULL
DROP TABLE tempdb.dbo.ErrorLog
GO
CREATE TABLE tempdb.dbo.ErrorLog(
[Server] [nvarchar](128) NOT NULL,
[dtMessage] [datetime] NULL,
[SPID] [varchar](50) NULL,
[vchMessage] [nvarchar](4000) NULL,
[ID] [int] NULL
) ON [PRIMARY]
-- Set extract date for 2 days
DECLARE @ExtractDate datetime
SET @ExtractDate = DATEADD(dd,-2,CURRENT_TIMESTAMP)
SELECT 'Extract Date = ' + CONVERT(CHAR(26),@ExtractDate)
-- SQL Server 2000 and 2005 each has different formats for reading the Error Log file
DECLARE @VersionId AS CHAR(1)
SELECT @VersionId = LEFT(CONVERT(VARCHAR(100),SERVERPROPERTY('ProductVersion')),1)
CREATE TABLE #ErrorLogs (intFileId INT
, dtLastChangeDate DateTime NOT NULL
, biLogFileSize bigint)
INSERT INTO #ErrorLogs
EXEC master.dbo.xp_enumerrorlogs
DECLARE @SQL AS VARCHAR(256)
-- Define Temp Table to contain error log messages
IF @VersionId = 8
CREATE TABLE #Errors8 (vchMessage VARCHAR(255), ID INT)
ELSE
CREATE TABLE #Errors9 (LogDate datetime, Processinfo VARCHAR (10), vchMessage NVARCHAR(4000))
-- Processes Error Logs files modified since last Run Date
DECLARE ErrorLog_cursor CURSOR FOR
SELECT intFileId
FROM #ErrorLogs
WHERE dtLastChangeDate > @ExtractDate
OPEN ErrorLog_cursor
DECLARE @intFileId INT
FETCH NEXT
FROM ErrorLog_cursor INTO @intFileId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
-- Load Error Log into temporary Table
IF @intFileId = 0
IF @VersionId = 8
INSERT #Errors8
EXEC master.dbo.xp_readerrorlog
ELSE
INSERT #Errors9
EXEC master.dbo.xp_readerrorlog
ELSE
IF @VersionId = 8
INSERT #Errors8
EXEC master.dbo.xp_readerrorlog @intFileId
ELSE
INSERT #Errors9
EXEC master.dbo.xp_readerrorlog @intFileId
END
FETCH NEXT
FROM ErrorLog_cursor INTO @intFileId
END
CLOSE ErrorLog_cursor
DEALLOCATE ErrorLog_cursor
-- Extract all error log record for last two days
IF @VersionId = 8
BEGIN
INSERT INTO tempdb.dbo.ErrorLog
([Server]
,[dtMessage]
,[SPID]
,[vchMessage]
,[ID])
SELECT @@SERVERNAME
,CASE ISDATE( LEFT(vchMessage,22))
WHEN 1 THEN LEFT(vchMessage,22)
ELSE '1900-01-01'
END
,NULL
,vchMessage, ID
FROM #Errors8
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ErrorLog
([Server]
,[dtMessage]
,[SPID]
,[vchMessage]
,[ID])
SELECT @@SERVERNAME
,LogDate
,Processinfo
,vchMessage, NULL
FROM #Errors9
WHERE LogDate >= @ExtractDate
END
DELETE
FROM tempdb.dbo.ErrorLog
WHERE [dtMessage] < @ExtractDate SELECT * FROM tempdb.dbo.ErrorLog -- Drop Temporary Tables IF OBJECT_ID('tempdb..#Errors8') IS NOT NULL DROP TABLE #Errors8 IF OBJECT_ID('tempdb..#Errors9') IS NOT NULL DROP TABLE #Errors9 IF OBJECT_ID('tempdb..#ErrorLogs') IS NOT NULL DROP TABLE #ErrorLogs BypassPrepare: False Click OK Right Click on Get ErrorLog Select Properties Properties to set MaximumErrorCount to 999 1. Add "Data Flow Task" to the "Collect Error Log" container 2. 1. Connect the "Get ErrorLog" Task to this object with the green line/arrow 2. Rename to Load ErrorLog 3. Right Click on this Task 4. Select Properties 5. Set MaximumErrorCount to 999 3. Next, two data flow elements will be added to the "Data Flow Task". The first will read the ErrorLog table on the remote instance and the other will save it in the local database. 4. Select the Data Flow Tab or double click on icon for the "Data Flow task" 5. Add "OLE DB Source" from toolbox 6. 1. Double Click Icon 2. OLE DB Connection manager: MultiServer 3. Change Data access mode to SQL Command 4. SQL Command Text: SELECT [Server] ,[dtMessage] ,[SPID] ,[vchMessage] ,[ID] FROM [tempdb].[dbo].[ErrorLog] 5. Click Preview to verify the SQL and then click close when done 6. Click OK 7. Add "OLE DB Destination" from toolbox 8. o Connect the "OLE DB Source" to this element with the green line/arrow o Double click on the Icon for OLE DB Destination and make the following changes o 1. OLE connection manager: QASRV.SQL_Overview 2. Name of the table or the view: [dbo].[ErrorLog] 3. Click Mappings and confirm the column mappings are correct 4. Click OK Ready to be Tested • Click Control Flow tab • Save all by pressing Ctrl+Shift +S • Press F5 to run • To review any errors by checking the Progress tab. • When done, click the blue line to get back to edit mode Query the Error Logs SQL Server Error Logs contain a variety of information. Now by using this SSIS package the error logs for all the instances can be queried from a single table. I've created a SQL statement that returns any error or warning messages I believe warrant review for possible problems. SELECT [Server] ,[dtMessage] ,[SPID] ,[vchMessage] ,[ID] FROM [SQL_Overview].[dbo].[ErrorLog] WHERE ([vchMessage] LIKE '%error%' OR [vchMessage] LIKE '%fail%' OR [vchMessage] LIKE '%Warning%' OR [vchMessage] LIKE '%The SQL Server cannot obtain a LOCK resource at this time%' OR [vchMessage] LIKE '%Autogrow of file%in database%cancelled or timed out after%' OR [vchMessage] LIKE '% is full%' OR [vchMessage] LIKE '% blocking processes%' ) AND [vchMessage] NOT LIKE '%\ERRORLOG%' AND [vchMessage] NOT LIKE '%Attempting to cycle errorlog%' AND [vchMessage] NOT LIKE '%Errorlog has been reinitialized.%' AND [vchMessage] NOT LIKE '%found 0 errors and repaired 0 errors.%' AND [vchMessage] NOT LIKE '%without errors%' AND [vchMessage] NOT LIKE '%This is an informational message%' AND [vchMessage] NOT LIKE '%WARNING:%Failed to reserve contiguous memory%' AND [vchMessage] NOT LIKE '%The error log has been reinitialized%' AND [vchMessage] NOT LIKE '%Setting database option ANSI_WARNINGS%' AND [vchMessage] NOT LIKE '%Error: 15457, Severity: 0, State: 1%' AND [vchMessage] <> 'Error: 18456, Severity: 14, State: 16.'
Conclusion
The package now collects from each instance the current status of each database and last two days of error log messages. This is still just start the of what this type of package can do. In an upcoming article I will be providing the full version of this package along with some sample reports.
Friday, January 21, 2011
To Find the SQL SERVER DATABASE SIZES AND LOCATIONS
select d.name,round(sum(mf.size) * 8 /1024,0) from sys.master_files mf
inner join sys.databases d
on d.database_id = mf.database_id
where d.database_id > 4
group by d.name
order by d.name
SELECT name, physical_name AS current_file_location
FROM sys.master_files
inner join sys.databases d
on d.database_id = mf.database_id
where d.database_id > 4
group by d.name
order by d.name
SELECT name, physical_name AS current_file_location
FROM sys.master_files
Transfer Multiple Files from or to FTP remote path to local path - SSIS
This is a common mistake which SSIS developer's thinks that they need Foreach Loop for transfer multiple files from ftp to local or from local to ftp.
I had a post about using masks in FTP task for transfer multiple files, but it seems that I should clarify this more than before.
Note that you don't need any Loop structure for transfer multiple files from remote path to local or from local path to remote. you just need to use MASK.
I explain in details with this scenario:
you have a remote ftp directory which you want to transfer all files in this folder to a local folder ( from FTP address to local) . and then copy all files from this local folder to another remote path ( from local to FTP address ).
Solution:
I had a post about using masks in FTP task for transfer multiple files, but it seems that I should clarify this more than before.
Note that you don't need any Loop structure for transfer multiple files from remote path to local or from local path to remote. you just need to use MASK.
I explain in details with this scenario:
you have a remote ftp directory which you want to transfer all files in this folder to a local folder ( from FTP address to local) . and then copy all files from this local folder to another remote path ( from local to FTP address ).
Solution:
you have two steps:
1- receive files from remote ftp address:
add a FTP task, set ftp connection, then go to file transfer tab,
set Operation as "Receive files"
then set localPath to an existing folder as you want, for example:
D:\MyTempFolder
then in RemotePath set address to remote directory as you want, BUT NOTE THAT YOU SHOULD ADD MASK AFTER IT.
for example like this:
/myftpurl.com/wwwroot/myRemoteFolder/*.*
note that "*.*" means that you want to receive all files from this remote directory.
mid step: create new variable of string datatype in package scope, let's name it as LocalPath
and set it with your local address but with MASK, for example in our case it will be:
D:\MyTempFolder\*.*
2- send files from local directory to remote ftp address:
add another ftp task, set ftp connection, then in file transfer tab,
set Operation as "Send files"
set IsLocalPathVariable as True
then set LocalVariable with User::LocalPath
( the reason of using variable as localpath is that you can not set localpath directly with MASK, SSIS will raise compilation error. but if you put a variable there and set mask in variable value, SSIS will try to solve it at run time and everything will work fine)
then set remotePath to the remote folder which is your destination, you don't need to add mask here, for example it can be:
/mysecondftpurl.com/wwwroot/DestinationFolder
That's all. execute it now.
1- receive files from remote ftp address:
add a FTP task, set ftp connection, then go to file transfer tab,
set Operation as "Receive files"
then set localPath to an existing folder as you want, for example:
D:\MyTempFolder
then in RemotePath set address to remote directory as you want, BUT NOTE THAT YOU SHOULD ADD MASK AFTER IT.
for example like this:
/myftpurl.com/wwwroot/myRemoteFolder/*.*
note that "*.*" means that you want to receive all files from this remote directory.
mid step: create new variable of string datatype in package scope, let's name it as LocalPath
and set it with your local address but with MASK, for example in our case it will be:
D:\MyTempFolder\*.*
2- send files from local directory to remote ftp address:
add another ftp task, set ftp connection, then in file transfer tab,
set Operation as "Send files"
set IsLocalPathVariable as True
then set LocalVariable with User::LocalPath
( the reason of using variable as localpath is that you can not set localpath directly with MASK, SSIS will raise compilation error. but if you put a variable there and set mask in variable value, SSIS will try to solve it at run time and everything will work fine)
then set remotePath to the remote folder which is your destination, you don't need to add mask here, for example it can be:
/mysecondftpurl.com/wwwroot/DestinationFolder
That's all. execute it now.
Wednesday, September 22, 2010
Top 10 SQL Server 2008 Development Features
SQL server 2008 improves developer productivity by providing seamless integration between frameworks, data connectivity technologies, programming languages, Web services, development tools and data. This article covers the top 10 developer features introduced in SQL server 2008.
Introduction:
Many new developer features were introduced in SQL Server 2008database to facilitate robust database development. SQL server 2008 improves developer
productivity by providing seamless integration between frameworks, data
connectivity technologies, programming languages, Web services, development
tools and data. This article discusses the new top 10 developer features
introduced in SQL server 2008.
Feature -1 Change's in the DATE and TIME DataTypes
In SQL Server 2005, there were DATETIME or SMALLDATETIME data
types to store datetime values but there was no specific datatype to store date
or time value only. In addition, search functionality doesn't work on DATETIME
or SMALLDATETIME fields if you only specify a data value in the where clause.
For example the following SQL query will not work in SQL server 2005 as you
have only specified the date value in the where clause.
time component in the where clause.
SELECT * FROM tblMyDate Where [MyDateTime] = '2010-12-11 11:00 PM'With introduction of DATE datatype the above problem
is resolved in SQL Server 2008. See the following example.
DECLARE @mydate as DATE
SET @ mydate = getdate()
PRINT @dt
The output from the above SQL query is the present date only
(2010-12-11), no time component is added with the output.
TIME datatype is also introduced in SQL server 2008. See the
following query using TIME datatype.
DECLARE @mytime as TIME
SET @mytime = getdate ()
PRINT @mytime
The output of the above SQL script is a time only value. The range
for the TIME datatype is 00:00:00.0000000 through 23:59:59.9999999.
SQL server 2008 also introduced a new datatype called DATETIME2.
In this datatype, you will have an option to specify the number of fractions
(minimum 0 and maximum 7). the following example shows how to use DATETIME2
datatype.
DECLARE @mydate7 DATETIME2 (7)
SET @mydate7 = Getdate()
PRINT @mydate7
The result of above script is 2010-12-11 22:11:19.7030000.
The new DATETIMEOFFSET datatype, which indicates what time zone
that date and time belong to was also introduced in SQL Server 2008. This
datatype will be required when you are keeping the date time value of different
countries with different time zones in SQL Server. The following example shows
the usage of the DATETIMEOFFSET datatype.
DECLARE @mydatetime DATETIMEOFFSET(0)
DECLARE @mydatetime1 DATETIMEOFFSET(0)
SET @ mydatetime = '2010-12-11 21:53:56 +5:00'
SET @ mydatetime1 = '2010-12-11 21:53:56 +10:00'
SELECT DATEDIFF(hh,@mydatetime1,@mydatetime)
Feature -2 New Date and Time functions
In SQL Server 2005 and SQL Server 2000 there are few functions toretrieve the current date and time. Adding to that In SQL Server 2008, five new
functions were introduced: SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME
SWITCHOFFSET and TODATETIMEOFFSET. The SYSDATETIME function returns the present
system timestamp without the time zone, with an accuracy of 10 milliseconds.
The SYSDATETIMEOFFSET function works like SYSDATETIME but the only difference
is it includes the time zone.
known as Greenwich Mean Time within an accuracy of 10 milliseconds.
Select SYSUTCDATETIME () will show output '2010-12-11
21:53:05.7131792'
SWITCHOFFSET returns a datetimeoffset value that is changed from
the stored time zone offset to a specified new time zone offset. See the
following examples.
SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-04:00') 'GetCurrentOffSet-4';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET (), '+00:00') 'GetCurrentOffSet+0';
Feature -3 Sparse columns
Sparse column, whichoptimizes storage for null values, is introduced in SQL Server 2008. When a
column value contains a substantial number of null values, defining the column
as sparse saves a significant amount of disk space. In fact, null value in a
sparse column doesn't take any space.
If you decide to implement a sparse column,
it must be nullable and cannot be configured with the ROWGUIDCOL or IDENTITY
properties, cannot include a default and cannot be bound to a rule. In
addition, you cannot define a column as sparse if it is configured with certain
datatypes, such as TEXT, IMAGE, or TIMESTAMP. T following SQL script shows how
to create a table with sparse column.
Create table mysparsedtable
(
column1 int primary key,
column2 int sparse,
column3 int sparse,
column4 xml column_set for all_sparse_columns
)
Feature -4 Large UDTs in SQL server 2008 (ADO.NET)
User defined types (UDT's)were first introduced with SQL server 2005 but were restricted to a maximum
size of 8 kilobytes. In SQL Server 2008, this restriction has been removed.
Using Common Language Runtime (CLR), now SQL Server 2008 supports binary data that's
up to 2GB in size. The following C# code snipped shows how to retrieve large
UDT data from a SQL server 2008 database.
SqlConnection myconnection = new SqlConnection(myconnectionString, mycommandString); // myconnectionString and
// mycommandString must be declared
myconnection.Open();
SqlCommand mycommand = new SqlCommand(mycommandString);
SqlDataReader myreader = mycommand.ExecuteReader();
while (myreader.Read())
{
int id = myreader.GetInt32(0);
LargeUDT myudt = (LargeUDT)myreader[1];
Console.WriteLine("ID={0} LargeUDT={1}", id, myudt);
}
myreader.closeO
Feature -5 Passing tables to functions or procedures using new Table-Value parameters
SQL Server 2008introduces a new feature to pass a table datatype into stored procedures and
functions. The table parameter feature greatly helps to reduce the development
time because developers no longer need to worry about constructing and parsing
long XML data. Using this feature, you can also allow the client-side
developers (using .NET code) to pass data tables from client-side code to the
database. The following example shows how to use Table-Value parameter in
stored procedures.
GO
CREATE TABLE [dbo].[TblStudent]
(
[StudentID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[StudentName] [varchar](30) NOT NULL,
[RollNo] [int] NOT NULL,
[Class] [varchar](10) NOT NULL
)
GO
Next, I have created table
datatype for Student table.
GOThen I have created a
CREATE TYPE TblStudentTableType AS TABLE
(
[StudentName] [varchar](30) NOT NULL,
[RollNo] [int] NOT NULL,
[Class] [varchar](10) NOT NULL
)
GO
stored procedure with table datatype as an input parameter and to insert data
in Student table.
GOIn the last step, I have
CREATE PROCEDURE sp_InsertStudent
(
@TableVariable TblStudentTableType READONLY
)
AS
BEGIN
INSERT INTO [TblStudent]
(
[StudentName] , [RollNo] , [Class]
)
SELECT
StudentName , RollNo , Class FROM @TableVariable WHERE StudentName = 'Tapas Pal'
END
GO
entered one sample student record in the table variable and executed the stored
procedure to enter a sample record in the TblStudent table.
DECLARE @DataTable AS TblStudentTableType
INSERT INTO @DataTable(StudentName , RollNo , Class)
VALUES ('Tapas Pal','1', 'Xii')
EXECUTE sp_InsertStudent
@TableVariable = @DataTable
Feature -6 New MERGE command for INSERT, UPDATE and DELETE operations
SQL server 2008 provides
the MERGE command that is an efficient way to perform multiple DML (Data
Manipulation Language) operations at the same time. In SQL server 2000 and
2005, we had to write separate SQL statements for INSERT, UPDATE, or DELETE
data based on certain conditions, but in SQL server 2008, using the MERGE statement
we can include the logic of similar data modifications in one statement based
on where condition match and mismatch. In the following example, I have created
two tables (TblStudent and TblStudentMarks) and inserted sample data to show
how MERGE command works.
GO
CREATE TABLE [dbo].[TblStudent]
(
[StudentID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[StudentName] [varchar](30) NOT NULL,
[RollNo] [int] NOT NULL,
[Class] [varchar](10) NOT NULL
)
GO
CREATE TABLE TblStudentMarks
(
StudentID INTEGER REFERENCES TblStudent,
StudentMarks INTEGER
)
GO
INSERT INTO TblStudent VALUES('Tapas', '1', 'Xii')
INSERT INTO TblStudent VALUES('Vinod', '2', 'Xiv')
INSERT INTO TblStudent VALUES('Tamal', '3', 'Xii')
INSERT INTO TblStudent VALUES('Tapan', '4', 'Xiii')
INSERT INTO TblStudent VALUES('Debabrata', '5', 'Xv')
INSERT INTO TblStudentMarks VALUES(1,230)
INSERT INTO TblStudentMarks VALUES(2,280)
INSERT INTO TblStudentMarks VALUES(3,270)
INSERT INTO TblStudentMarks VALUES(4,290)
INSERT INTO TblStudentMarks VALUES(5,240)
Now to perform the following operations, I have written a single SQL
statement.
1. Delete Record with student name 'Tapas'
2. Update Marks and Set to 260 if Marks is
<= 230
3. Insert a record in TblStudentMarks table
if the record doesn't exist
MERGE TblStudentMarks AS stm
USING (SELECT StudentID,StudentName FROM TblStudent) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND sd.StudentName = 'Tapas' THEN DELETE
WHEN MATCHED AND stm.StudentMarks <= 230 THEN UPDATE SET stm.StudentMarks = 260
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO
Feature -7 New HierarchyID datatype
SQL server 2008 provides new HierarchyID data type that
allows database developers to construct relationships among data elements
(columns) within a table. HierarchyID data type has a set of methods
that provide tree like functionality. These methods are GetAncestor,
GetDescendant, GetLevel, GetRoot, IsDescendant, Parse, Read, Reparent,
ToString, Write etc. The following example shows how to create a HIERARCHYID
column in a table.
CREATE TABLE dbo.ProductCategory
(
ProductSubCategoryID IDENTITY(1,1) NOT NULL ,
ProductCategoryID NOT NULL,
lvl AS hid.GetLevel() PERSISTED,
ProductSubCatName VARCHAR(25) NOT NULL,
ProductSubCatDesc VARCHAR(250)NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX idx_first ON dbo.Employees( ProductSubCategoryID);
CREATE UNIQUE INDEX idx_second ON dbo.Employees(lvl, ProductCategoryID);
Feature -8 Spatial datatypes
Spatial is the new data type introduced in SQL server 2008 that is
used to represent the physical location and shape of any geometric object.
Using spatial data types you can represent countries, roads etc. Spatial data
type in SQL server 2008 is implemented as .NET Common Language Runtime (CLR)
data type. There are two types of spatial data type's available, geometry and
geography data type. Let me show you example of a geometric object.
DECLARE @point geometry;
SET @point = geometry::STGeomFromText ('POINT (4 9)', 0);
SELECT @point.STX; -- Will show output 4
SELECT @point.STY; -- Will show output 5
You can use methods STLength, STStartPoint, STEndPoint, STPointN,
STNumPoints, STIsSimple, STIsClosed and STIsRing with geometric objects.
Feature -9 Manage your files and documents efficiently by implanting FILESTREAM datatype
SQL server 2000 and 2005 do not provide much for storing videos,
graphic files, word documents, excel spreadsheets and other unstructured data.
In SQL Server 2005 you can store unstructured data in VARBINARY (MAX) columns
but the maximum limit is 2 GB. To resolve the unstructured files storing issue,
SQL Server 2008 has introduced the FILESTREAM storage option. The FILESTREAM
storage is implemented in SQL Server 2008 by storing VARBINARY (MAX) binary
large objects (BLOBs) outside the database and in the NTFS file system. Before
implementing FILESTREAM storage, you need to perform following steps.
1. Enable your SQL Server database instance
to use FILESTREAM (enable it using the sp_filestream_configure stored
procedure. sp_filestream_configure @enable_level = 3)
2. Enable your SQL Server database to use
FILESTREAM
3. Create "VARBINARY (MAX)
FILESTREAM" datatype column in your database
Feature -10 Faster queries and reporting with grouping sets
SQL Server 2008 implements grouping set, an extension to the GROUP
BY clause that helps developers to define multiple groups in the same query.
Grouping sets help dynamic analysis of aggregates and make querying/reporting
easier and faster. The following is an example of grouping set.
SELECT StudentName, RollNo, Class , Section
FROM dbo.tbl_Student
GROUP BY GROUPING SETS ((Class), (Section))
ORDER BY StudentName
Conclusion
The above-mentioned are the 10 most significant and beneficial features
provided by SQL server 2008 for developers. I hope this article will help a lot
to database developers want to learn SQL server 2008.
Additional Resources
TechNet Magazine: SQL Server 2008 - What's New
SQL Server Books Online
Top 9 SQL Server Developer Interview Questions
The questions you may wish to ask or that will be asked during a SQL Server Developer interview will vary. Deanna Dicken shares the top 9 interview questions she asks when interviewing a potential SQL Server developer.
Introduction
In my many years in Information Technology, I have performed so many SQL Server interviews that I have long since lost count. I thought I would share with you some of the questions I find important when interviewing a potential SQL Server developer.
What is Referential Integrity?
Let's start with the basics. Regardless of the RDBMS they are targeting, I'd really like them to understand that beyond all else we need to ensure referential integrity. To ensure it, we have to understand it. I am listening for an answer along the lines of ensuring the relationships between parent and child tables and that the primary keys and foreign keys facilitate this integrity.
What is the difference between a clustered and a non-clustered index?
This is one of my favorite questions for sorting out the developers that have an understanding beyond your basic T-SQL to more of an understanding of performance. I'm looking for them to tell me that a clustered index represents the physical order of the rows as they are stored in the database. A non-clustered index is a logical ordering of the rows.
Both types of indexes facilitate faster lookups and joins.
As a follow-up question, should they get this right, ask them to tell you how many clustered indexes you can have on a table. The answer should be one. Rows can only be physically stored with one ordering.
What are DML triggers and what types of triggers can you have on a SQL Server table?
A trigger, as the name implies, is a collection of SQL statements triggered to execute by an insert, update, or delete action on a table that affects its contents. INSTEAD OF and AFTER can be specified for each trigger. When INSTEAD OF is specified, the trigger takes the place of the triggering action. So if you had an INSTEAD OF UPDATE trigger, the code inside the trigger would be executed instead of the original update. AFTER triggers are the same as the FOR used in earlier versions of SQL Server. This just means the trigger will execute following the original action. An AFTER UPDATE trigger will allow the update to complete and then the update trigger will execute. For more information, see DML Trigger Planning Guidelines.
What is a four-part name?
This question can help you get a sense for whether the candidate ever crosses database boundaries with his queries. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Prod instance, the four part name would look like this: Prod.AdventureWorks.HumanResources.Employee.
What is a Linked Server?
Another question that can tell you if the candidate has experience with distributed databases is "What is a Linked Server?" A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.
How do you handle an error from within a stored procedure?
This one's a little open-ended. One, it depends on which version of SQL Server they work with. In SQL Server 2005 Microsoft introduced TRY…CATCH error handling. The code that could error is placed within a TRY block. If an error does occur, the code within the CATCH block executes. Several functions are available to assist in gleaning information about the error. See TRY…CATCH in Books Online for more information.
Prior to this, @@Error could be checked for an error code and then an error thrown using RAISERROR. Some may answer that they return the error in an output parameter or by using PRINT. RAISERROR can still be used to throw an error. As a matter of fact, a RAISERROR called from the TRY block with a severity between 11 and 19 will cause execution to jump to the CATCH block.
How do you auto number rows in SQL Server?
Sometimes I have to rephrase this one to get the answer. What I'm looking for here is the candidate's knowledge of identity columns. Identity columns are used to automatically increment a column in a table from a seed (or starting) value and by a defined increment. If no seed is specified and no increment is specified, the first entry is 1 and the subsequent entries are incremented by 1. If either the seed or the increment is specified, other must be specified.
What is a left outer join? Give an example.
The answers I get on this one blow me away. I can't believe how many candidates I've interviewed that do not grasp this concept. Assume you have two tables, TableA and TableB. You need all the rows from TableA and all matching rows from TableB. You would use a left outer join to accomplish this with TableA being the left table as in the following.
SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.Col1 = TableB.Col1
What is a cursor and why would you use it?
The candidate will probably be able to tell you cursors are used to perform row-by-row processing on a result set. The candidate might tell you they use them for batch type processing. In their answer though I am secretly hoping the candidate will elaborate on performance issues with cursors and how it is preferred to use loops or set-based processing instead of cursors. If not, the follow up question here is "Is there another way to process the result set row-by-row without using a cursor?" To see more of my thoughts on cursors, see What Every DBA Ought to Know About SQL Server Cursors (and Their Alternatives).
Conclusion
The questions you may wish to ask or that will be asked of you will vary (like your mileage). Questions should be tailored to the job for which the candidate is applying. For instance, if you don't use triggers in your workplace, does it really matter if the candidate has a working knowledge of SQL Server triggers?
Introduction
In my many years in Information Technology, I have performed so many SQL Server interviews that I have long since lost count. I thought I would share with you some of the questions I find important when interviewing a potential SQL Server developer.
What is Referential Integrity?
Let's start with the basics. Regardless of the RDBMS they are targeting, I'd really like them to understand that beyond all else we need to ensure referential integrity. To ensure it, we have to understand it. I am listening for an answer along the lines of ensuring the relationships between parent and child tables and that the primary keys and foreign keys facilitate this integrity.
What is the difference between a clustered and a non-clustered index?
This is one of my favorite questions for sorting out the developers that have an understanding beyond your basic T-SQL to more of an understanding of performance. I'm looking for them to tell me that a clustered index represents the physical order of the rows as they are stored in the database. A non-clustered index is a logical ordering of the rows.
Both types of indexes facilitate faster lookups and joins.
As a follow-up question, should they get this right, ask them to tell you how many clustered indexes you can have on a table. The answer should be one. Rows can only be physically stored with one ordering.
What are DML triggers and what types of triggers can you have on a SQL Server table?
A trigger, as the name implies, is a collection of SQL statements triggered to execute by an insert, update, or delete action on a table that affects its contents. INSTEAD OF and AFTER can be specified for each trigger. When INSTEAD OF is specified, the trigger takes the place of the triggering action. So if you had an INSTEAD OF UPDATE trigger, the code inside the trigger would be executed instead of the original update. AFTER triggers are the same as the FOR used in earlier versions of SQL Server. This just means the trigger will execute following the original action. An AFTER UPDATE trigger will allow the update to complete and then the update trigger will execute. For more information, see DML Trigger Planning Guidelines.
What is a four-part name?
This question can help you get a sense for whether the candidate ever crosses database boundaries with his queries. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Prod instance, the four part name would look like this: Prod.AdventureWorks.HumanResources.Employee.
What is a Linked Server?
Another question that can tell you if the candidate has experience with distributed databases is "What is a Linked Server?" A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.
How do you handle an error from within a stored procedure?
This one's a little open-ended. One, it depends on which version of SQL Server they work with. In SQL Server 2005 Microsoft introduced TRY…CATCH error handling. The code that could error is placed within a TRY block. If an error does occur, the code within the CATCH block executes. Several functions are available to assist in gleaning information about the error. See TRY…CATCH in Books Online for more information.
Prior to this, @@Error could be checked for an error code and then an error thrown using RAISERROR. Some may answer that they return the error in an output parameter or by using PRINT. RAISERROR can still be used to throw an error. As a matter of fact, a RAISERROR called from the TRY block with a severity between 11 and 19 will cause execution to jump to the CATCH block.
How do you auto number rows in SQL Server?
Sometimes I have to rephrase this one to get the answer. What I'm looking for here is the candidate's knowledge of identity columns. Identity columns are used to automatically increment a column in a table from a seed (or starting) value and by a defined increment. If no seed is specified and no increment is specified, the first entry is 1 and the subsequent entries are incremented by 1. If either the seed or the increment is specified, other must be specified.
What is a left outer join? Give an example.
The answers I get on this one blow me away. I can't believe how many candidates I've interviewed that do not grasp this concept. Assume you have two tables, TableA and TableB. You need all the rows from TableA and all matching rows from TableB. You would use a left outer join to accomplish this with TableA being the left table as in the following.
SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.Col1 = TableB.Col1
What is a cursor and why would you use it?
The candidate will probably be able to tell you cursors are used to perform row-by-row processing on a result set. The candidate might tell you they use them for batch type processing. In their answer though I am secretly hoping the candidate will elaborate on performance issues with cursors and how it is preferred to use loops or set-based processing instead of cursors. If not, the follow up question here is "Is there another way to process the result set row-by-row without using a cursor?" To see more of my thoughts on cursors, see What Every DBA Ought to Know About SQL Server Cursors (and Their Alternatives).
Conclusion
The questions you may wish to ask or that will be asked of you will vary (like your mileage). Questions should be tailored to the job for which the candidate is applying. For instance, if you don't use triggers in your workplace, does it really matter if the candidate has a working knowledge of SQL Server triggers?
Subscribe to:
Posts (Atom)
