Archive

Archive for September, 2011

SQL Server Denali: New warnings in Actual Execution Plan

29/09/2011 Leave a comment

I’ve played with SQL Server Denali and found some new features and improvements in Query Execution plan. My intention was to test Extended Events and new events: sort_warning and hash_warnig. In SQL Server 2008R2, we can create SQL Trace to capture sort and hash warnings but there is no easy way to find out which statement cause it. I hoped that with new XEvent in Denali we can do that easily.
I reduced maximum server memory for SQL Server, ran some queries with ORDER BY and here is the Actual Execution Plan for one query.

Do you see something new? There is a warning on Sort operator (1) and on SELECT (2).
If we look at the Sort operator and SELECT we can see more information about warnings.

In Denali we see Sort warnings in query execution plan. It seems that some waits are also visible. Is the MemoryGrant only wait type that will appears in query plan?
I don’t know what kinds of warnings are included in Denali actual execution query plan but I think that these two warnings are not the only one.

Categories: Denali, Performance

SQL Server DBA Quiz 2011 – How will you restrict users at certain period?

10/09/2011 Leave a comment

SQL Server DBA Quiz 2011 has started at www.beyondrelational.com. I think that  you can learn a lot from such quiz and at the same time you can test and verify your SQL Server administration skills.
One of the SQL Server DBA Quiz 2011 questions is: How will you restrict users at certain period?

Here is a detailed explanation of the question:
Assume that in your database there is a table named test and a user named A. During 10 AM to 1 PM each day, this particular user should be restricted to make any data changes(insert/update/delete) in the table test. You, as a DBA, are asked to implement this restriction. How will you implement this restriction?

I think that question is very interesting so I will give my solution here also.

My solution to the question:

Create trigger on table Test to prevent INSERT, UPDATE and DELETE statements for specified login and then disable it. Code example:

-- Create trigger to prevent user A to execute insert/update/delete on table test.
CREATE TRIGGER dbo.TriggerForUserA ON dbo.Test
FOR INSERT, UPDATE,DELETE
AS
-- Run this trigger if login is A.
IF SYSTEM_USER = 'A'
BEGIN
RAISERROR ('User A can''t insert/update/delete into table Test during 10 AM to 1 PM each day', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
GO
-- Disable Trigger
DISABLE TRIGGER dbo.TriggerForUserA ON dbo.Test;
GO

Trigger is created but disabled. Now, you need to create SQL server job to runs every day at 10 AM and at 1 PM. Job needs to enable trigger dbo.TriggerForUserA at 10 AM and disable it at 1PM.

Here is sample code for SQL Job step:

IF convert(varchar(5),GETDATE(),114) = '10:00'
BEGIN
ENABLE TRIGGER dbo.TriggerForUserA ON dbo.Test;
END
IF convert(varchar(5),GETDATE(),114) = '13:00'
BEGIN
DISABLE TRIGGER dbo.TriggerForUserA ON dbo.Test;
END

That is my solution. If you know some other way to resolve the problem you can post it in the comment.

SQL Server Denali – FORMAT() string function

02/09/2011 Leave a comment

SQL Server Denali brings us new string function, FORMAT function. It will make formatting of date/time and number values easier.

Syntax: FORMAT (value, format [,culture])

Formatting Date/Time

Let’s see how it works:

DECLARE @a datetime = getdate()
-- If the culture argument is not provided,
-- then the language of the current session is used.
SELECT FORMAT(@a, N'd') AS d,
FORMAT(@a, N'dd') AS dd,
FORMAT(@a, N'ddd') AS ddd,
FORMAT(@a, N'dddd') AS dddd

SELECT FORMAT(@a, N'M') AS M,
FORMAT(@a, N'MM') AS MM,
FORMAT(@a, N'MMM') AS MMM,
FORMAT(@a, N'MMMM') AS MMMM

SELECT FORMAT(@a, N'y') AS y,
FORMAT(@a, N'yy') AS yy,
FORMAT(@a, N'yyy') AS yyy

If you specify culture argument, you can easily format date to other languages. Next examples show you how to format date, weekday and month to Croatian language.

-- Formatting with Croatian language
SELECT FORMAT(@a, N'd', N'hr-HR') AS d,
FORMAT(@a, N'dd', N'hr-HR') AS dd,
FORMAT(@a, N'ddd', N'hr-HR') AS ddd,
FORMAT(@a, N'dddd', N'hr-HR') AS dddd

SELECT FORMAT(@a, 'M','hr-HR') AS M,
FORMAT(@a, 'MM','hr-HR') AS MM,
FORMAT(@a, 'MMM','hr-HR') AS MMM,
FORMAT(@a, 'MMMM','hr-HR') AS MMMM

Examples with German and Italian languages.

-- Formatting with German language
SELECT FORMAT(@a, N'd', N'de-DE') AS d,
FORMAT(@a, N'dd', N'de-DE') AS dd,
FORMAT(@a, N'ddd', N'de-DE') AS ddd,
FORMAT(@a, N'dddd', N'de-DE') AS dddd

-- Formatting with Italian language
SELECT FORMAT(@a, N'M', N'it-IT') AS M,
FORMAT(@a, N'MM', N'it-IT') AS MM,
FORMAT(@a, N'MMM', N'it-IT') AS MMM,
FORMAT(@a, N'MMMM', N'it-IT') AS MMMM

List of all available languages you can find in National Language Support (NLS) API Reference. With FORMAT function you can also use custom formatting. Let’s see how custom formatting works.

DECLARE @a datetime = getdate()

-- Custom formatting of date
SELECT FORMAT ( @a, N'dd/MM/yyyy') AS 'dd/MM/yyyy',
FORMAT ( @a, N'dd.MM.yyy')  AS 'dd.MM.yyy',
FORMAT ( @a, N'dd.MMMM.yy', 'hr-HR') AS 'dd.MMMM.yy Croatian',
FORMAT ( @a, N'dd.MMMM.yyy (dddd)', 'hr-HR') AS 'dd.MMMM.yyy (dddd) Croatian',
FORMAT ( @a, N'dddd, dd.MMM.yy', 'hr-HR') AS 'dddd, dd.MMM.yy Croatian',
FORMAT ( @a, N'dddd MMMM dd, yyyy', 'en-US') AS 'MMMM dd, yyyy (dddd) English'

-- Custom formatting of time
SELECT FORMAT ( @a, N'hh:mm:ss') AS 'hh:mm:ss',
FORMAT ( @a, N'hh:mm') AS 'hh:mm',
FORMAT ( @a, N'hh') AS 'hh',
FORMAT ( @a, N'mm') AS 'mm',
FORMAT ( @a, N'ss') AS 'ss'

Formatting Number

FORMAT function is very useful if you need to format currency value. Here are some examples for formatting currency value.

DECLARE @a numeric(10,2)
SET @a = '5132.45'
SELECT FORMAT(@a, N'c') AS SessionLanguage,
FORMAT(@a, N'c', 'hr-Hr') AS Croatian,
FORMAT(@a, N'c', 'de-DE') AS German,
FORMAT(@a, N'c', 'it-IT') AS Italian

-- You can also control number of digits after the decimal point
SELECT FORMAT(@a, N'c0', 'de-DE') AS German0,
FORMAT(@a, N'c1', 'de-DE') AS German1,
FORMAT(@a, N'c2', 'de-DE') AS German2,
FORMAT(@a, N'c3', 'de-DE') AS German3

With FORMAT function you can even format value to scientific and hexadecimal.

DECLARE @I int = 90
SELECT FORMAT(@I,'e') AS Scientific,
FORMAT(@I,'x') AS Hexa,
FORMAT(@I,'x4') AS Hexa1

Format function is nice addition to the T-SQL language and it will make formatting much easier. Various tricks with CAST and CONVERT functions will no longer be required.

Categories: Denali, String Functions, TSQL