Create your own shortcut in SQL Management Studio 2008

Hi All,

Shortcut is good way to increase the speed of development and work. SQL Server Management Studio 2008 have numerous shortcuts.

We are writing various SQL statement, few statements are common like, SELECT * FROM [TABLE_NAME] which we are using very frequent. its always tedious job to write SELECT * FROM statement, can’t we create shortcut for that? Yes we can. Here is the steps to create your own shortcut with screenshot may be it helps you to increase productivity.

Step 1: Find the Keyboard setting [Tools – Options – Keyboard]

Step 2: Pick the available Shortcut [Query Shortcuts]

Here you can see the default query shortcuts. Find the available and create your own.

Lets create one shortcut for SELECT Statement

 

Press OK, you are done with your first shortcut. Before we use the shortcut and it get applies we need to restart IDE so our shortcut will get effected.

After restarting IDE, in Query write the table name, select it and apply your shortcut [Ctrl+f1].

You can see it works like SELECT * FROM EmpMaster. Now let’s apply WHERE clause and see if its works or not?

It’s working perfectly. Use shortcuts be more productive.

GROUP BY GROUPING SETS - KATMAI

IN SQL 2008 KATMAI, there new extension for group by clause. Which is GROUPING SETS. Its provides you to create sets of columns for grouping from you group by column. For example if you have having group on year, month its allow you to group first on year+month then year as single group, which means now you can do multiple grouping in the single query!

Lets see the example. We are having data for Sales. We need sales for each Month as well as each Year. So each year will have again sum of Month. Here is the pictorial representation of last statement.

So our data:



We need this as output:



In SQL 2000/2005 We may need CURSOR or TEMPORARY TABLE or TABLE VARIABLE, and I am sure that will be tedious job. So lets see how we can achieve this functionality in KATMAI using GROUPING SETS. Here is the small and sweet query :)

SELECT 
    S_Year, S_Month, SUM(S_Amt) as AmountSum, AVG(S_Amt) AvgAmt
FROM @Sales
GROUP BY GROUPING SETS ( 
    (S_Year, S_Month)
    ,(S_Year)
    )
ORDER BY S_Year

You can see the new keyword GROUPING SETS which has more then one Group Clause. This indicates that first you do Group on S_Year + S_Month then you Group it on S_Year.

Lets see the output.



You see the NULL in S_Month? That indicates the sum on months for particular Year mean its in the S_Month grouping. And KATMAI provides one function GROUPING which indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified. Read more on span class="keyword">GROUPING. Lets change query to get proper result.

SELECT 
    CASE 
        WHEN 
            GROUPING(S_Month) = 1 THEN 'Year Total: ' + CAST(S_Year AS VARCHAR(5)) 
        ELSE 
            'Month : ' + CAST(S_Month as VARCHAR(3)) 
        END 
    AS 'Grouping',
    
    SUM(S_Amt) as AmountSum, AVG(S_Amt) AvgAmt
FROM @Sales
GROUP BY GROUPING SETS ( 
    (S_Year, S_Month)
    ,(S_Year)
    )
ORDER BY S_Year, ISNULL(S_Month,15);

And lets see the output.



Download the SQL statements from here.

Check the Origional post

Its very useful while creating Reports.

Visual Studio IDE Support for SQL Server

Hello,

As we all know Visual Studio is now supporting Database project, which allows you to write query, procedure etc... from IDE only.

Recently I found the error while connecting SQL2008 from VS2008.

This server version is not supported. Only servers up to Microsoft SQL Server 2005 are supported.

I found after doing some googling; that there is no patch available which allos me to connect SQL2008 to VS2008!!!.

I found few comments which says that it will come soon.

Somasegar's WebLog
Euan Garden's BLOG

However there is Sservice Pack for Visual Studio 2008 Team Foundation Server which has ability to Support for SQL Server 2008, you can download that form Microsoft Download Center

Let me add that there is Service Pack for Visual Studio 2005 Support for SQL Server 2008, which you can download it from Microsoft Download Center

Limitations of the XML Data Type

Hi all,

I found the limitation of XML Data Type introduced in SQL Server 2005.

Although the XML datatype is treated like many other datatypes in SQL Server 2005, there are specific limitations to how it is used. These limitations are:

1.    XML types cannot convert to text or ntext data types.


2.    No data type other than one of the string types can be cast to XML.


3.    XML columns cannot be used in GROUP BY statements.


4.    Distributed partitioned views or materialized views cannot contain XML data types.


5.    Use of the sql_variant instances cannot include XML as a subtype.


6.    XML columns cannot be part of a primary or foreign key.


7.    XML columns cannot be designated as unique.


8.    Collation (COLLATE clause) cannot be used on XML columns.


9.    XML columns cannot participate in rules.


10.    The only built-in scalar functions that apply to XML columns are ISNULL and COALESCE. No other scalar built-in functions are supported for use against XML types.


11.    Tables can have only 32 XML columns.


12.    Tables with XML columns cannot have a primary key with more than 15 columns.


13.    Tables with XML columns cannot have a timestamp data type as part of their primary key.


14.    Only 128 levels of hierarchy are supported within XML stored in the database.

SQL Server 2008 (KATMAI) - New Data Type: DATETIME2

Check it out here

SQL SERVER 2008 (KATMAI): SQL Server 2008 (KATMAI) TSQL Enhancements - 1

SQL SERVER 2008 (KATMAI): SQL Server 2008 (KATMAI) TSQL Enhancements - 1

The 25 New Features in SSMS 2008

Check it out :

The 25 New Features in SSMS 2008