MyTechnoBook

Zoom Query Surface in SSMS in Denali

noreply@blogger.com (Dattatrey Sindol (Datta)) — Tue, 30 Nov 2010 17:59:00 +0000

SQL Server Management Studio (SSMS) in Denali (CTP1) has got a makeover and is now based on Visual Studio 2010 Shell. This will enable us to utilize lot of great features available in Visual Studio like Improved Intellisense, Dragging Query/Code Window to a different monitor etc.

Another interesting feature introduced in SSMS in Denali CTP1 is the ability to zoom the query/design surface which will enable you to have a bigger size font for your queries and making your experience close to having a bigger size desktop/monitor.

Below is a normal query window with Normal or 100% Zooming.

As you can see there is an option at the Left Bottom Corner to Zoom the Query/Design Surface. Now if we change the Zoom Level to 150% then the Query Window looks something like the one shown below.

As you can see now when we changed the Zoom Level to 150%, we are getting bigger font for our query there by giving us an experience similar to having a bigger size desktop/monitor.

Also, note that the Zoom Level is applied only to the Query/Design Surface and is not applied to any other areas like Results Pane, Object Explorer etc.

There are many more such cool features introduced in Denali CTP1. So stay tuned to learn more new things about Denali CTP1.

Zoom BIDS Package Design Surface in SSIS in Denali

noreply@blogger.com (Dattatrey Sindol (Datta)) — Mon, 29 Nov 2010 19:44:00 +0000

SSIS in Denali comes with a whole bunch of new and exciting features both from an arthitectural/development standpoint and also from a usability standpoint. Here is a really cool feature introduced in Denali Business Intelligence Development Studio (BIDS) for SSIS which allows us to Zoom only the Package Design Surface which will help in navigating easily in the designer especially while developing some really huge packages.

Below is a screenshot of a sample package in the normal 100% zoom mode (default).

Now we can use the slider located on the bottom right corner of the designer surface to zoom the design surface to see larger size images of various tasks/components within the design surface as shown below.

Note that the Zooming is applied only to the design surface and not to any other area like Connection Managers tray, Solution Explorer etc.
There are lot more usability enhancements introduced in Denali which I will post in the coming days. So stay tuned!

What's New in Denali

noreply@blogger.com (Dattatrey Sindol (Datta)) — Mon, 29 Nov 2010 18:41:00 +0000

The Next Version of SQL Server codenamed Denali has been released to the community (CTP1) just a couple of weeks back for evaluation & feedback. I am very much excited about the CTP1 release of the next version of SQL Server and have already started looking into some of the interesting improvements made in Denali. In the next few weeks I will be posting some new and interesting features of Denali and also my experience with Denali. So keep visiting this blog for more interesting stuff on Denali.

In the mean time, I encourage those of you interested in Denali to go over the following links:

You can download the Denali CTP1 from here.

Note: It is highly recommended that you read through the Release Notes and Hardware & Software Requirements for Installing SQL Server "Denali" before proceeding with the installation!

Find The Number of Occurrences of a Character or a String in Another String

noreply@blogger.com (Dattatrey Sindol (Datta)) — Fri, 26 Nov 2010 18:15:00 +0000

Here is one of the common questions asked in many interviews but unfortunately many candidates fail to answer to this question. The question is "How to find the Number of Occurrences of a Character in a String in SQL Server" or "How to find the Number of Occurrences of a String in another String in SQL Server".

Below is a simple query which can answer both the above questions.

Find the Number of Occurrences of a Character in a String using T-SQL
Query:

DECLARE @SourceString VARCHAR(100)
DECLARE @FindString VARCHAR(10)

SET @SourceString = 'Find Number of Occurrences of a Character or a String in Another String'
SET @FindString = 'S'

SELECT (LEN(@SourceString) - LEN(REPLACE(@SourceString, @FindString, ''))) / LEN(@FindString) AS NumberOfOccurrences

Output:

Find the Number of Occurrences of a String in another String using T-SQL

Query:
DECLARE @SourceString VARCHAR(100)
DECLARE @FindString VARCHAR(10)

SET @SourceString = 'Find Number of Occurrences of a Character or a String in Another String'
SET @FindString = 'of'

SELECT (LEN(@SourceString) - LEN(REPLACE(@SourceString, @FindString, ''))) / LEN(@FindString) AS NumberOfOccurrences

Output:

As you can see the same query works for both finding the number of occurrences of a character and also a string inside another string.

COUNT(*) vs. COUNT(1) - Myth vs. Reality

noreply@blogger.com (Dattatrey Sindol (Datta)) — Wed, 17 Nov 2010 16:50:00 +0000

You might have seen in many T-SQL scripts that some people use SELECT COUNT(*) FROM MyTable and some people use SELECT COUNT(1) FROM MyTable. Both of these return the same value and perform the same task of getting record count from a table. However, some techies believe that COUNT(1) is better in terms of performance when compared to COUNT(*). Let us see if this is a Myth or a Reality.

Query Execution Results:

Query Execution Plans:

In the above execution plans the Stream Aggregate (Read it Right to Left. Second from Right.) performs the count operation. Now when you click on the Stream Aggregate and go to the properties (press F4) window, you can see that the underlying query that gets executed uses COUNT(*) in both the above cases. Below is the screenshot of properties window of Stream Aggregate for both these execution plans.

From the above results, it is evident that there is no performance difference between COUNT(*) & COUNT(1) and both of them boil down to COUNT(*) in the background.

Getting Latest Code from TFS - The Right Way!

noreply@blogger.com (Dattatrey Sindol (Datta)) — Wed, 17 Nov 2010 16:19:00 +0000

Sometimes when we try to get the latest code from TFS using Get Latest Version option, it does not get us the latest changes in various scenarios. Example: When some of the files are removed/missing from local folder (workspace) etc.

The issue can be reproduced using the following steps:

Connect to your Team Project on Source Control/Team Foundation Server.
Create a new workspace by mapping to appropriate folder on local file system.
Download the latest code from TFS into local using Get Latest Version option.
Now, go to file system (workspace) and delete few of the files which you just downloaded in previous step.
Go back to TFS and try to get the latest version of the code using Get Latest Version option for the same folder/workspace for which you downloaded the latest code in previous steps. Now you will see the following dialog box which says all your local files are up to date.

Note that you deleted few files from your local folder but still Get Latest Version option says all the local files are up to date.

To address this issue and to get the latest code from TFS, here is the correct approach. Follow the below mentioned steps to get the Latest Code from TFS in the Right Way!

Connect to your Team Project on Source Control/Team Foundation Server.
Right click on the folder for which you want to download the latest server changes to local and select Get Specific Version… option from the context menu.
Now a dialog box appears as shown below:

The above dialog box is self-explanatory with the items highlighted and the callouts.

The settings in the above dialog box will get the latest version of the code from TFS into your local environment (workspace) by overwriting any modified / unmodified files (locally in file system) and leaving any checked out files untouched/unmodified.

Note: This demonstration is based on Team Foundation Server 2010 (Visual Studio 2010 Ultimate).

Adding an SSIS Package to a Project - Hard Approach vs. Easy Approach

noreply@blogger.com (Dattatrey Sindol (Datta)) — Tue, 16 Nov 2010 16:08:00 +0000

I have been using an easy approach for adding an SSIS Package to an SSIS Project for a couple of years now. But didn't get a chance to actually blog about it. Today I decided to blog it for sure and here I go.

Often there will be more than one option for doing many things in our day to day life and quite often the approach which we discover lately turns out to be an easy & simple approach.

Adding an existing SSIS Package from file system to an SSIS Project is no different. There are basically two different approaches for doing this - The Hard Approach & an Easy Approach.

Hard Approach:

Right click on the "SSIS Packages" folder in the SSIS project in SQL Server Business Intelligence Development Studio (BIDS) and Select "Add Existing Package" from the context menu.
In the "Add Copy of Existing Package" dialog box select "Package location" as "File System" from the dropdown.
Click on the ellipsis button next to the "Package path" textbox.
Locate the SSIS package (.dtsx file) which you want to add from the file system.
Click OK in the "Add Copy of Existing Package" dialog box to finish adding the package.

Easy Approach:

Locate the SSIS package (.dtsx file) which you want to add from the file system.
Right click on the SSIS package in the file system and select "Copy" from the context menu (copy the package).
Right click on the "SSIS Package" folder in the SSIS project in SQL Server Business Intelligence Development Studio (BIDS).
Select "Paste" from the context menu to finish adding the package.

Hope you will find this tip usful.

List all the Columns of an Index in Current Database in SQL Server

noreply@blogger.com (Dattatrey Sindol (Datta)) — Wed, 13 Oct 2010 20:19:00 +0000

Last week I was writing a few validation scripts for some of the recent schema changes / performance tuning which we had done in our project. As part of this we had defined few indexes and most of them were on multiple columns. As a result of this, during validation process, I wanted to list all the columns of all the indexes in a particular database so that by looking at the output I can make sure that all the indexes are created as expected with the required columns in it. After doing some research on some of the system tables (like sys.objects, sys.indexes etc.), I came up with a simple SQL script which will list all the columns contained in all the indexes in the current database.

SELECT
DB_NAME() AS DatabaseName
, SSch.name AS SchemaName
, SObj.name AS ObjectName
, DB_NAME() + '.' + SSch.name + '.' + SObj.name AS FullyQualifiedObjectName
, SObj.type AS ObjectType
, CASE
WHEN SObj.type = N'U' THEN 'Table'
WHEN SObj.type = N'V' THEN 'View'
END AS ObjectTypeDesc
, SIdx.name AS IndexName
, SIdx.type AS IndexType
, SIdx.type_desc AS IndexTypeDesc
, SIdx.is_primary_key AS IsIndexAPrimaryKey
, SIdxCol.index_column_id AS IndexColumnSequence
, SCol.name AS IndexColumnName
FROM
sys.objects SObj
INNER JOIN sys.schemas SSch
ON SObj.schema_id = SSch.schema_id
INNER JOIN sys.indexes SIdx
ON SObj.object_id = SIdx.object_id
INNER JOIN sys.index_columns SIdxCol
ON SIdx.object_id = SIdxCol.object_id
AND SIdx.index_id = SIdxCol.index_id
INNER JOIN sys.columns SCol
ON SIdxCol.object_id = SCol.object_id
AND SIdxCol.column_id = SCol.column_id
WHERE SObj.type IN (N'U', N'V')
ORDER BY DatabaseName, SchemaName, ObjectName, IndexName, IndexColumnSequence

This will produce the following output:

Issue: Execute Package Option Unavailable in SQL Server Business Intelligence Development Studio (BIDS)

noreply@blogger.com (Dattatrey Sindol (Datta)) — Fri, 10 Sep 2010 17:21:00 +0000

Everyone working on SQL Server and its components (SSIS, SSAS & SSRS) would require Business Intelligence Development Studio (BIDS) which is a really cool integrated development environment allowing us to work on different types of projects simultaneously like SSIS, SSAS & SSRS (All in One Solution). I came across an issue with the SSIS project, in which there was no option to execute the package.

Recently one of the developers in my team came to me complaining that while he was working on an SSIS package, he was unable to see the execute package option when he right clicked on the package in Solution Explorer in BIDS. When I took a look at his Visual Studio / BIDS, I found that some of the settings were messed up and the settings did not seemed to be same as that of a standard BIDS environment due to which the execute package option was not available in the solution explorer.

To fix this issue or in general to reset all the settings of BIDS to bring it to a state same as that of a new installation, follow the below steps:

1. Open Business Intelligence Development Studio (BIDS).
2. Go to Tools menu and select Import and Export Settings....

3. This will bring up the Import and Export Settings Wizard window, Select Reset all settings option and click Next in the Welcome to the Import and Export Settings Wizard page.

Note: You can use this step / page to even export your custom settings to some other environment or import your settings from a different environment by selecting appropriate options.

4. In the Save Current Settings page of the wizard, select Yes (Recommended) to save the current settings so that it can be used to get back to the previous state in case if you want to revert back to the old settings of if something goes wrong in this process, Else you can may select No if you dont want to save the current settings and click on Next.

5. Choose Business Intelligence Settings in the Choose a Default Collection of Settings page of the wizard and click Finish and then click Close once the BIDS settings are successfully reset.

And now your BIDS should get back to the initial state same as that of a new installation. This is a simple procedure to fix any unwanted settings by resetting all your BIDS settings.

This simple procedure can be used to do one of the following things:

Export customized BIDS settings to a different server / environment.
Import customized BIDS settings from a different server / environment.
Reset all the customized BIDS settings to bring it back to the original state as that of a new installation.

How to Change the Sequence of Columns in Results Pane in SSMS

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sun, 29 Aug 2010 16:08:00 +0000

Here is a really cool feature which not many of you would be aware of. All of us write simple SELECT * FROM queries in SQL Server Management Studio (SSMS). And many times, we copy the data to excel spreadsheets for additional analysis / sorting etc. Here is a simple trick to copy select columns from the results window once the results are rendered.

Go to SQL Server 2005 / 2008 Management Studio (SSMS) and open a new query window. Type a SELECT * FROM TableName query on one of the tables and run the query as shown below.

Now let us say that you want to select only the following columns in the same sequence as specified below:

GeographyKey
EnglishCountryRegionName
StateProvinceName
City

If you observe the results displayed in the previous screenshot, you can see that the columns are not in the same sequence as listed above and the query does not contain the column names, so you cant change the change the sequence of columns in the query. If you have to change the sequence of columns using the query, then you need to type in the column names. But here is a simple way to do it and no need to type the columns.

Now click on the column EnglishCountryRegionName, hold it and drag it next to GeographyKey column till you see a red color line next to the GeographyKey column header as shown below.

Now, similarly move the StateProvinceName column after EnglishCountryRegionName and before City columns as shown below.

Once all the required columns are re-arranged, you can easily copy all the required columns and copy them to excel easily as shown above.

Hope you like this trick. If there is a better and quicker way of achieving the same thing in SSMS, then let me know by leaving a comment below.

Related Articles:

Query to Get Week Start Date, Month Start Date and Year Start Date Based on Input Date

noreply@blogger.com (Dattatrey Sindol (Datta)) — Fri, 20 Aug 2010 16:40:00 +0000

In one of the recent projects, there was a requirement to set the First Day of the Week as Start Date and Current Date as End Date for a Week Level Report. Similarly, First Day of Month & Current Date and First Day of Year & Current Date for Month Level and Year Level Reports respectively.

To address this requirement, one of the developers wrote a query to get the first day of the week considering the week starts on a Monday and the query seemed to work fine on his computer but when we moved that code to my computer then the results were different & were completely incorrect. When I started looking into the issue I found that he was using DATEPART function of SQL Server something like DATEPART(DW, @InputDate). After doing some analysis & referring to the properties / behavior of this function on MSDN library, I found that the return value of this function depends on the setting of DATEFIRST.

One can find out the current state of DATEFIRST property by running the following simple SELECT statement.

SELECT @@DATEFIRST

Refer to the DATEPART() function on MSDN library for more details.

Following is the query which gives accurate results under all circumstances:

DECLARE @dttRequestedDateTime DATETIME = '2010-08-20' -- Replace '2010-08-20' with Input Date or Requested Date
DECLARE @dtInputDate DATE = CAST(@dttRequestedDateTime AS DATE)

SELECT
      @dtInputDate AS [InputDate],
      CASE
         WHEN DATENAME(DW, @dtInputDate) = 'Monday' THEN @dtInputDate
         WHEN DATENAME(DW, @dtInputDate) = 'Tuesday' THEN DATEADD(DD, -1, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Wednesday' THEN DATEADD(DD, -2, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Thursday' THEN DATEADD(DD, -3, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Friday' THEN DATEADD(DD, -4, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Saturday' THEN DATEADD(DD, -5, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Sunday' THEN DATEADD(DD, -6, @dtInputDate)
      END AS [WeekStartDate]
      , DATEADD(DD, 1 - DATEPART(DAY, @dtInputDate), @dtInputDate) AS [MonthStartDate]
      , DATEADD(DD, 1 - DATEPART(DAYOFYEAR, @dtInputDate), @dtInputDate) AS [YearStartDate]

Here is the output of this query:

InputDate WeekStartDate MonthStartDate YearStartDate
---------- ------------- -------------- -------------
2010-08-20 2010-08-16 2010-08-01 2010-01-01

(1 row(s) affected)

Week Start Date, Month Start Date & Year Start Date

Let me know if you have a better way of getting the same results by leaving a comment below.

Related Articles and Links

Create Attractive Technical and Non-Technical Charts and Diagrams Online For FREE

noreply@blogger.com (Dattatrey Sindol (Datta)) — Tue, 06 Jul 2010 17:07:00 +0000

We create Flow Charts, Architecture Diagrams, Diagrams for visual representation of Processes and Concepts etc. There are various tools in the market for different types of diagrams with various features to suit different set of requirements like Erwin is a data modeling tool used to create data model, Microsoft Office Visio is used for creation of various categories of diagrams like basic flow chart, process flow diagram etc. We can also create diagrams in Microsoft Word. However most of these products are paid versions and need an installation on your computer.

Ever thought of a tool which offers you with most of the features which are offered by the tools mentioned above and lot of other attractive features in addition to those offered by the above listed tools absolutely free! Yes here is a great tool called FlowChart.com (www.flowchart.com), which is a fully loaded tool for creation of flow charts, process flow diagrams, architecture diagrams and a lot of other attractive diagrams and guess what ? All this for free and on the go with an online interface.

The User Interface / Grid View of the tool looks similar to the grid view of Microsoft Office Visio.Below is a snapshot of the Online User Interface of FLOWCHART.COM.

Currently the tool is in its beta version and offers a limited set of registrations based on the availability. Below is a sample flow diagram created using FLOWCHART.COM with the steps signing up with FLOWCHART.COM and creation of flowcharts / diagrams.

There are few sample video demos available on their site as well as on other Video Hosting sites. Below is a sample video of FLOWCHART.COM.

Here are few of the key highlights of the tool:

Fully loaded tool offering lot of attractive features for creation of charts / diagrams etc.
Does not require any installation.
You can create diagrams on the go by just having access to internet.
All these features for Free!

Visit FLOWCHART.COM to Sign-Up and start creating attractive charts / diagrams with this fully loaded tool :-)

Difference between SSRS 2005 and SSRS 2008

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sun, 27 Jun 2010 16:30:00 +0000

Few of my colleagues and friends have been asking me for a while to write something on the differences between SQL Server Reporting Services versions 2005 and 2008. Especially when they were preparing for interviews / attending interviews, this was one of the most common questions they were asked by the interviewers. However when they browsed on the net, they could not get a comprehensive list of differences between SSRS 2005 and SSRS 2008 not even in a single place. Hence I came up with this idea of presenting some of the major differences between SSRS 2005 & 2008 so that it is helpful for the people who are curious to know about the differences between SSRS 2005 & 2008 and also for those people who are preparing for an interview on SQL Server Reporting Services.

Below table shows few of the differences between 2005 and 2008 versions of SQL Server Reporting Services in the order of priority (major to minor differences from top to bottom).

Comparison of SSRS 2005 and SSRS 2008
SQL Server 2005 Reporting Services	SQL Server 2008 Reporting Services
Multiple service architecture (IIS Service & Windows Service)	Single service architecture (Windows Service)
Depends on IIS	Comes with Built-In Web Server and hence does not depend on IIS
Complex deployment due to the need for separate configuration for IIS	Deployment is simple due to inbuilt web server and Reporting Services Configuration Manager
Charts, Dials etc. had to be separately installed and integrated	Comes packaged with Charts, Dials, Indicators, Sparklines etc.
Limited exporting options	More exporting options including Microsoft Word etc
You can only format entire text in a textbox with one single formatting style (bold, italicize, colors etc.)	Allows you to format pieces of text or words or letters within a single textbox with different formatting styles like bold, italicize, colors etc. Example: a Disclaimer Textbox, Header Textbox etc.

Apart from this there are quite a few other differences or improvements in SQL Server 2008 Reporting Services. For more details, please see the Product Information Page for SQL Server 2008 Reporting Services.

This post is specially for Suneel who had asked for this quite long time back :-)

If there are any other major differences which are worth mentioning then do leave a comment, so that the post can be updated and other readers can benefit from it.

As always for any comments / suggestions / opinions, please leave a comment below.

Related Articles:

T-SQL Tidy - An Online T-SQL Statement Parser and Formatter

noreply@blogger.com (Dattatrey Sindol (Datta)) — Tue, 22 Jun 2010 16:36:00 +0000

Yesterday I came across another interesting and useful online tool for formatting T-SQL queries quickly. T-SQL Tidy is a free online tool which can format numerous lines of T-SQL code within seconds.

I had written another article about Instant SQL Formatter (Read more here) which is also a great tool for formatting T-SQL queries & also various other database queries like MS Access, Oracle etc and offers various output options including HTML, C#, VB.Net etc. On the other hand T-SQL Tidy offers more formatting options along with a beautiful HTML output with alternate colored lines.

Here is a snapshot of the online version of the T-SQL Tidy user interface with the formatting options highlighted.

Apart from the online version, T-SQL Tidy also has the following other Versions / Add-ins:

Here is the official T-SQL Tidy Blog. Visit this blog regularly to stay updated on the improvements & new features / add-ins available for T-SQL Tidy.

Here are few of the important features of T-SQL Tidy:

Offers more formatting options.
Version Control Add-in for SSMS (2008) allows you to view the version of objects in SSMS and maintains the history, which will help you identify what changes were made, when and what is the difference between any two particular versions. This can be really useful if you want to revert back to previous version of a Table Structure or any other database object. Watch video demo here.
HTML output comes with a choice to make alternate rows in different colors.

One of the limitations of this tool is limited output options (T-SQL, HTML etc.) and supports only SQL Server (2000 / 2005 / 2008).

Overall, its a great tool to instantly format your T-SQL queries, which can save you lot of time, especially when your typing speed is slow and you cannot switch between upper & lower case characters, cannot use frequent tabs to include necessary indentation etc.

Read more about another T-SQL formatting tool, Instant SQL Formatter here.

Formatting T-SQL Queries Made Easy with Instant SQL Formatter

noreply@blogger.com (Dattatrey Sindol (Datta)) — Thu, 17 Jun 2010 17:59:00 +0000

Writing well formatted T-SQL queries is an art and takes quite a bit of patience to do the formatting. While working with various project teams, I often advise my team members and people who are new to SQL Server to format the SQL queries and always follow some conventions / standards (Read more on T-SQL Coding Standards & Best Practices here).

However, quite a few times either due to laziness or due to time constraints, many developers do not format the queries properly. This makes the queries less readable and difficult to maintain.

I came across a really cool tool few months back and wanted to share the same with broader audience, but missed many times due to various distractions. And today finally I decided to post it on my blog :-)

Here is a tool called Instant SQL Formatter which is really cool, easy to use, pretty powerful and can format your T-SQL queries in seconds. Many of you might already be knowing about this tool, since this has been in the market for quite some time now. However I have presented few of the benefits and other details so that people who do not know about this can get benefited. Here is a screenshot of the online version of the Instant SQL Formatter.

Here are few cool features of Instant SQL Formatter:

Supports queries from databases like MS Access, DB2, Microsoft SQL, MySQL, Oracle / PLSQL and other Generic queries.
Supports various output formats like Text, HTML, Java, C#, VB.Net, XML and many more.
Gives control over how you want to format Keywords (Uppercase, Lowercase, InitCap, Unchanged), Identifiers (Uppercase, Lowercase, InitCap, Unchanged, Init cap each word), Functions (Uppercase, Lowercase, InitCap, Unchanged) etc.
Formats 100's of lines of lines of code in just few 10's of seconds.

Guess what, the best thing I like about Instant SQL Formatter is its ability to output formatted T-SQL code in Text format along with HTML format, which bloggers like me can use to embed code into blog posts :-)

Apart from the Online Version, here are few other Versions / Add-Ins of Instant SQL Formatter:

Instant SQL Formatter - Desktop Version
Instant SQL Formatter - Add-In for Visual Studio
Instant SQL Formatter - Add-In for SQL Server Management Studio (SSMS) (More on SSMS Shortcuts here)

This is a real time saver tool and is worth giving a try.

Here are few of the most important advantages of writing well formatted T-SQL queries:

Makes the query readable and makes it look cleaner.
Easy to maintain due to high readability.
Using same set of formatting rules / standards across modules / projects ensures consistency.

And as always, for any comments / suggestions, please leave a comment below.

T-SQL Coding Made Easy with Inbuilt SQL Server Templates

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 12 Jun 2010 12:23:00 +0000

I have seen many people, even though they know what function / clause / keyword to be used for a particular T-SQL / Other type of query / operation, they don't remember the exact syntax [Including me at times :-)]. From my perspective as long as we know what keywords / functions / clauses need to be used for performing a specific operation using T-SQL / Other language (like MDX, DMX etc), we can always get the syntax from various sources like msdn, SQL Server Books Online (Local / Online version) etc. and get the task done.

However, if you have an option of getting the well formatted code snippet with the syntax of all the commonly performed T-SQL / MDX / DMX etc. operations right in the query editor window, then nothing like it. I would love such a feature.

Microsoft as always known for offering most powerful and user friendly tools has provided a separate section / explorer in SQL Server Management Studio 2005 / 2008 which contains the templates / code snippets for all the commonly performed operations in T-SQL, MDX, DMX etc. This feature / explorer is called as Template Explorer. Few of you reading this post might already be knowing about this feature, but explaining it in detail for those readers who have not used this feature before.

Following is a snapshot of Template Explorer in SQL Server 2008 Management Studio showing the list of various well formatted code snippets grouped into various categories. Below snapshot shows all the available categories of code snippets for T-SQL operations in SQL Server 2008 with the list of code snippets expanded for commonly performed T-SQL operations on tables.

Template Explorer in SQL Server Management Studio (SSMS) can be accessed using the following ways:

SSMS Menus - View -> Template Explorer
SSMS Shortcut - Ctrl + Alt + T

Following screenshots show the comparison of Template Explorer between SQL Server 2005 & SQL Server 2008 for T-SQL / SQL Server, MDX, Mobile / Compact Editions.

Now let us take a look at how to generate the table creation script for a sample table using the Create Table template available in Template Explorer.

Go to template explorer and expand the Tables folder under SQL Server section in Template Explorer as shown in the above figure 1. Now double click on the Create Table template to open it in a new query editor window.

Following code snippet is present in the Create Table template:

-- ========================================= -- Create table template -- ========================================= USE <database, sysname, AdventureWorks> GO  IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL   DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table> GO  CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table> (  <columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,   <column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,   <column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,      CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>) ) GO

Now go to Query -> Specify Values from Template Parameters... in SSMS. This brings up a pop-up window "Specify Values from Template Parameters". In this window specify the parameters like Column Names, Column Data Type, NULLability, Constraints etc. as shown in the below screenshot.

Now when you click OK, the Table Creation Script for the SampleEmployeeTable gets generated as shown below.

-- ========================================= -- Create table template -- ========================================= USE MyTechnoBook GO  IF OBJECT_ID('dbo.SampleEmployeeTable', 'U') IS NOT NULL   DROP TABLE dbo.SampleEmployeeTable GO  CREATE TABLE dbo.SampleEmployeeTable (  EmployeeId INT NOT NULL,   EmployeeName NVARCHAR(255) NULL,   EmployeeAddress NVARCHAR(512) NULL,      CONSTRAINT PK_SampleEmployeeTable_EmployeeId PRIMARY KEY (EmployeeId) ) GO

Using Template Explorer we can perform the following tasks:

Edit the existing templates to include additional details like parameters, fields, comments, formatting etc.
Create new templates for commonly used scripts in a module / project so that they can be reused by other team members and also across various modules / projects.
Rename existing folders / templates as per your convenience (Though this is not required 99.9% of the times as all the folders which ship with SQL Server are named appropriately).
Create new folders / Renaming existing folders to add new or existing templates.
Re-organizing the templates by moving them across folders.

Following are few of the advantages / benefits of using Template Explorer:

These templates act as a quick reference for getting the syntax in the absence of locally installed SQL Server Books Online or Access to Internet.
Saves time required for formatting the T-SQL code since the templates are pre-formatted hence ensures consistency in terms of formatting across various modules / projects.
We can share commonly used scripts across all the team members working across modules / projects.

Please let me know your opinion about this article / feature by leaving a comment below.

Here are couple of related links and articles that I have previously published.

Colored Connections in SQL Server 2008

noreply@blogger.com (Dattatrey Sindol (Datta)) — Wed, 05 May 2010 17:27:00 +0000

Have you ever heard of a colored connection in SQL Server? Well, may be few but not everyone reading this blog post would have come across colored connections feature of SQL Server 2008. Lets take a closer look at colored connections feature of SQL Server 2008 Management Studio in this article.

Well, as we all know while laying electric cables / wiring the entire house / campus, various colored cables / wires are used. The main purpose of this is to be able to differentiate the cables using colors both at the time of initial setup as well as during repair / maintenance. Colored Connections in SQL Server also work pretty much on the similar lines.

In SQL Server 2008 Management Studio (SSMS), while connecting to a server / database in the "Connect to Server" window where we enter the server details and the credentials, there is an "Option" button at the Right-Bottom corner as shown in the below window.

In the above window click on "Options>>" button & go to "Connection Properties" tab and check the "Use Custom Color" check box. Click on "Select..." button to select any color of your choice from the list of available colors or you can define your own custom color as shown in the below screenshot.

In the above screenshot I have selected the color green since its a development server and one can do anything & everything as long as the database does not conflict with other developers.

With the use of this feature one can give distinct color to each of the servers / databases that are frequently used like Dev, Test, UAT / Pre-Prod, Prod etc as shown in the below screenshot.

This color coding / coloring a connection in SQL Server Management Studio reminds the developers / testers etc about the environment in which they are working, so that they can pay necessary attention before firing any query.

Lastly, there is a feature in SQL Server Management Studio (SSMS) which is called "Registered Servers" / "Server Registration" which helps us in registering most commonly used servers / databases. I would prefer to keep all my frequently used servers / databases registered and grouped into separate Server Groups with a different color for each type of connection as shown in the below screenshot.

Well, that is all about Colored Connections, which is a very nice feature introduced in SQL Server 2008 Management Studio and also a little bit on the best practices of Registering / Grouping frequently used Servers / Databases in SQL Server.

Let me know if you liked this post by leaving a comment below.

T-SQL - Coding Standards & Best Practices

noreply@blogger.com (Dattatrey Sindol (Datta)) — Tue, 30 Mar 2010 16:15:00 +0000

We see a lots of articles on the net and lots of documents are prepared by different project teams in the industry to present / capture the conventions, coding standards etc. However in reality there is nothing like a standard / convention as such in most cases. Conventions / Standards are defined to ensure consistency in the coding / documentation across various modules / projects.

Having said that, here are few of the conventions / standards that I use while writing T-SQL queries / functions / SPs etc.

Always type the SQL Keywords in CAPITAL Letters.
Use proper case while typing names of any database objects/entities. Usually database objects should be named using InitCap / Camel Case.
Use proper indentation.
Use Comments wherever necessary.
Name the table aliases by concatenating the first letter of all the words in the name of the table. Example: If the table name is FactInternetSales then one of the possible alias is FIS. Define the aliases considering the conflicts if any with the other table aliases.
While executing stored procedures using "EXEC" command use the variable names while passing input values to the Stored Procedure (SP).
Bad Example:

USE AdventureWorksDW
GO
EXEC sp_spaceused 'FactInternetSales'
GO

Good Example:

USE AdventureWorksDW
GO
EXEC sp_spaceused @objname = 'FactInternetSales'
GO

Above snapshot shows few of the most commonly used T-SQL Statements.

These conventions are purely based on my comfort level and there is no hard & fast rule that these are followed / should be following widely in the industry. One can define their own standards / conventions based on their comfort level to ensure consistency.

This blog post is updated as and when I find something interesting which I feel like adopting as part of my coding.

How to Display Report Execution Time in Reporting Services (SSRS)

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sun, 21 Mar 2010 16:18:00 +0000

Often in Reporting Applications, faster rendering of reports is very essential. Especially in case of Operational Reporting Systems with large number of concurrent users, Reporting Rendering SLA is defined during the requirement phase of the project and is a very critical component of the Requirements / Reporting Application.

In these kind of solutions its good to display the total time a report takes for rendering from the moment the View Report button is clicked in the report manager. This feature can be used as a means of communicating the exact report rendering time to the users and also to identify the reports / scenarios which are exceeding / missing the rendering SLA.

Now let us see how to display the execution time in an SSRS report.

Since SSRS uses functions based on .Net framework we can use the following expression to get the report rendering time in seconds:

System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds

Similary to display the time in terms of minutes and hours we can just replace Seconds in the above expression with Minutes and Hours respectively.

Though execution time in terms of Hours is rarely / never used.

To make the display more user friendly or more presentable we can use the following expression:

="Execution Time: " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)"

Here is a sample report displaying the execution time in terms of Hours, Minutes and Seconds as shown below.

As shown in the above screenshot the report takes 1 Min 3 Seconds or 63 Seconds to render.

Now let us verify how accurate this number is by querying the SSRS report tables which hold the history / log of a report execution.

I ran the following query in the ReportServer database:

SELECT TOP 1 ReportID, TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing 

, TimeRendering, TimeDataRetrieval + TimeProcessing + TimeRendering AS TotalRenderingTime

FROM dbo.ExecutionLog

WHERE ReportID = '90ABF38F-E280-4B75-B807-343AFB5FE696'

ORDER BY TimeStart DESC

And here are the results of the above query:

From the above screenshot we can see that the total time is 63172 milliseconds or ~63 Seconds or 1 Min & 3 Seconds, which is same as the Execution Time displayed in the previous screenshot.

Please let me know your comments / opinions about this article by leaving a comment below.

Note: This demonstration is tested in SSRS 2008. The implementation should be almost the same for SSRS 2005 as well.

Query to Select a Random Record from a Table in SQL Server

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sun, 07 Mar 2010 16:55:00 +0000

Often people struggle to get a RANDOM record from a table in SQL Server. There is a function in SQL Server RAND() which generates Random Numbers. However this function does not work as expected while selecting a RANDOM row from a table in SQL Server.

To address this issue, there is a workaround to select a RANDOM row from a table in SQL Server. Let us see how we can achieve this using the following demonstration.

Create a Temp Table using the following query:

CREATE TABLE #TempTable (
  IntValue INT NOT NULL,
  StrValue NVARCHAR(20) NOT NULL)
GO

Now insert some sample data into the Temp Table using the following query:

INSERT INTO #TempTable (IntValue,StrValue)
SELECT IntValue, StrValue
FROM (SELECT 1 AS IntValue, 'String Value 1' AS StrValue
      UNION ALL
      SELECT 2 AS IntValue, 'String Value 2' AS StrValue
      UNION ALL
      SELECT 3 AS IntValue, 'String Value 3' AS StrValue
      UNION ALL
      SELECT 4 AS IntValue, 'String Value 4' AS StrValue
    UNION ALL
      SELECT 5 AS IntValue, 'String Value 5' AS StrValue
      UNION ALL
      SELECT 6 AS IntValue, 'String Value 6' AS StrValue
      UNION ALL
      SELECT 7 AS IntValue, 'String Value 7' AS StrValue
      UNION ALL
      SELECT 8 AS IntValue, 'String Value 8' AS StrValue
    UNION ALL
      SELECT 9 AS IntValue, 'String Value 9' AS StrValue
      UNION ALL
      SELECT 10 AS IntValue, 'String Value 10' AS StrValue) StaticData
GO

Now run the following queries to see how 2 RANDOM rows are selected from Temp Table every time you run these queries.

SELECT TOP 2 *
FROM #TempTable
ORDER BY NEWID()
GO

SELECT TOP 2 *
FROM #TempTable
ORDER BY NEWID()
GO

Here is the output of the above queries. Run these queries a couple times to see the difference in the number of selected rows every time the above queries are run.

Find this post useful ? Please do let me know by leaving a comment below :-)

Query To Get The COUNT of Records in All The Tables in a Database in SQL Server

noreply@blogger.com (Dattatrey Sindol (Datta)) — Fri, 19 Feb 2010 17:40:00 +0000

Often we take the COUNT of records in a table or many of the times all the tables in a database. We might need to get the COUNT of records in all the tables especially for validation purposes.

For instance when you are loading the data into your Staging Database in an incremental fashion, you need to do few checks to make sure that the incremental logic is working fine. As part of this, one of the most basic checks is to first get the COUNT of records from all the tables in Source & Staging Databases and compare the COUNTs.

Here is a very simple way to get the COUNT of records from all the tables in a database. Run the following query in the database in which you need to get the COUNTs of all the tables.

DECLARE @QueryString NVARCHAR(MAX)

SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','') + 'SELECT ' + '''' + TABLE_SCHEMA + '.' + TABLE_NAME + '''' + ' AS TableName, COUNT(1) AS RecordCount FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

EXEC Sp_executesql @QueryString

When you run the above query in AdventureWorksDW database then the results will be as shown below (Tables/Counts might slightly vary depending on the version of AdventureWorksDW).

Feel free to leave your comments if you like this post.

Error - The path is not valid. The path must confirm to Uniform Naming Conventions (UNC) format.

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sun, 14 Feb 2010 12:04:00 +0000

Recently I started looking into one of the great features of Reporting Services (SSRS 2008) and that is Subscriptions. A Subscription is a feature in SSRS to deliver a report to the users at specified time in their intended format.

There are basically two types of Subscriptions - Standard and Data-Driven Subscriptions. Each of these Subscription types offer delivery options like Delivery via E-Mail, Delivery to a specified location on the File System etc. These Subscriptions also offer various rendering/delivery formats like Excel, PDF etc. Basically all the exporting formats that are available in Report Server/Manager. However getting into the details of Subscriptions is out of scope of this article. More information on this topic can be found on msdn at the following location:

http://msdn.microsoft.com/en-us/library/ms155911%28SQL.90%29.aspx

Today I was working on Subscription with Windows File Share/File System delivery option. While configuring this option, we need to specify the location to which the report need to be delivered/saved on the file system. I gave the location for delivery as a local drive on my personal computer.

When I gave the local path as "F:\Datta\MyTechnoBook - Files\" and got an error as

The path is not valid. The path must confirm to Uniform Naming Conventions (UNC) format.

This error indicates that the path should be provided in the form of Uniform Naming Conventions (UNC) format. Or in simple terms we need to provide the path in the format of Shared Drive as follows:

\\ServerName\FolderLocation\

When I gave the location as "\\SINDOL\F$\Datta\MyTechnoBook - Files\" it solved the error.

Hope you find this article helps! If yes then do leave your comments :-)

How to Copy Table Structure With Or Without Data in SQL Server

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 06 Feb 2010 17:45:00 +0000

Often while working with T-SQL we create copies of tables as a backup of the original/base table so that we can make changes to the data in the original/base table for testing purposes, and revert back to the backup table if needed. SQL Server provides few Key Words or options to perform this operation.

However there are few key differences to note between the original table and the backup table while creating the copies of the tables With/Without data, and these differences could be really important to consider especially when we decide to drop the original table since you have taken the backup. Before you drop the original table, be careful and think twice before you do this! Read through this article to know more about the differences between the Original Table and the Copy of the table.

Now in this article lets take a look at how to create a copy of a table in No Time in either of the following ways:

Copy Table Structure With Data
Copy Only Table Structure Without Data

Create a sample table as follows and call it as ExistingEmployeeTable.

/* Create Table */
CREATE TABLE ExistingEmployeeTable (
EmployeeId INT IDENTITY (1,1) NOT NULL,
EmployeeName NVARCHAR(100) NOT NULL,
ManagerId INT)
GO

Create a Primary Key on EmployeeId.

/* Create Primary Key */
ALTER TABLE ExistingEmployeeTable
ADD CONSTRAINT PK_ExistingEmployeeTable_EmployeeId PRIMARY KEY (EmployeeId)
GO

Now create another table called ExistingManagerTable with ManagerId as Primary Key.

/* Create Table */
CREATE TABLE ExistingManagerTable (
ManagerId INT IDENTITY (1,1) NOT NULL,
ManagerName NVARCHAR(100) NOT NULL)
GO

/* Create Primary Key */
ALTER TABLE ExistingManagerTable
ADD CONSTRAINT PK_ExistingManagerTable_ManagerId PRIMARY KEY (ManagerId)
GO

Now create a Foreign Key table in ExistingEmployeeTable on ManagerId and also create a Non Clustered Index on ManagerId.

/* Create Foreign Key */
ALTER TABLE ExistingEmployeeTable
ADD CONSTRAINT FK_ExistingEmployeeTable_ManagerId FOREIGN KEY (ManagerId) REFERENCES ExistingManagerTable(ManagerId)
GO

/* Create Non Unique Non Clustered Index */
CREATE NONCLUSTERED INDEX IDX_NU_NCL_ExistingEmployeeTable_ManagerId ON ExistingEmployeeTable(ManagerId)
GO

Now lets take a look at the structure of the two newly created tables as below.

Figure 1.0

Now lets us insert some sample data.

INSERT INTO ExistingManagerTable(ManagerName)
SELECT 'John' AS ManagerName
UNION
SELECT 'James' AS ManagerName
UNION
SELECT 'Michael' AS ManagerName
GO

INSERT INTO ExistingEmployeeTable(EmployeeName, ManagerId)
SELECT 'Robert' AS EmployeeName, 1 AS ManagerId
UNION
SELECT 'Daniel' AS EmployeeName, 2 AS ManagerId
UNION
SELECT 'David' AS EmployeeName, 3 AS ManagerId
UNION
SELECT 'Steven' AS EmployeeName, 2 AS ManagerId
UNION
SELECT 'Albert' AS EmployeeName, 3 AS ManagerId
GO

Now lets create a copy of the ExistingEmployeeTable along With Data as follows. You can either create a Physical Table or a Temporary Table based on your needs.

/* Create Copy of Table With Data */
SELECT *
INTO dbo.NewEmployeeTable
FROM dbo.ExistingEmployeeTable

You can also create a copy of the ExistingEmployeeTable Without Data as follows. Again here you can create either a Physical Table or a Temporary Table.

/* Create Copy of Table Without Data */
SELECT *
INTO dbo.#NewEmployeeTable
FROM dbo.ExistingEmployeeTable
WHERE 1 = 2

In this query SQL Server creates a copy of the table structure, but since the condition in the WHERE clause is FALSE, the data will not be copied to the newly created table.

Now lets take a look at the structure of the backup/newly created NewEmployeeTable table.

Figure 2.0

Now compare the table structures of ExistingEmployeeTable (Old Table) & NewEmployeeTable (New Table), and here are few of the key differences to note:

Primary & Foreign Keys are not created in the Newly Created Table (NewEmployeeTable)

Indexes are not created in the Newly Created Table (NewEmployeeTable)

Default/Check Constraints are not created in the Newly Created Table (NewEmployeeTable)

These are few most important differences which one should note before deciding to drop the original table after taking a backup of the table using SELECT * INTO statement.

Happy Birthday To Me :)

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sun, 31 Jan 2010 18:31:00 +0000

Today, 1st Feb 2010 is a very special day for me. Guess why ? It's my B'day today!!!

Happy B'day to me

Happy B'day dear Datta!!!
Happy B'day to me :)

Today is very special day in another aspect, that is the date today is 01022010 (DDMMYYYY). If you read it Left-to-Right or Right-to-Left its the same :)

Once again wishing myself a Very Happy B'day!!!

Formatting Minutes as Hours and Minutes in SQL Server Reporting Services (SSRS)

noreply@blogger.com (Dattatrey Sindol (Datta)) — Sat, 23 Jan 2010 12:18:00 +0000

Today I was working on an SSRS report for one of our clients and came across a specific formatting requirement around formatting Minutes as HH:MM. Browsed a little bit but could not get it working. Then sat for a while & tried few tricks by playing around with the expressions and finally got it working :-)

Here is how I solved the problem.

The data available in the database is numeric and represents Total Minutes. When I initially wrote some expressions to derive Hours and Minutes from the Total Minutes I was able to get the data something like this:

10 Minutes -> 0:10

65 Minutes -> 1:5 etc

However the need was to display the data something like below:

10 Minutes -> 00:10

65 Minutes -> 01:05 etc

Now to get this formatting, first split the Minutes into Hours & Remaining Minutes as follows:

Hours part of TotalMinutes = Floor(Fields!Minutes.Value/60)

Remaining Minutes part of TotalMinutes = Fields!Minutes.Value Mod 60

Now to display the data in the intended format put the following expression in the textbox of the detailed row of the Table/Matrix of SSRS:

=Format(Floor(Fields!Minutes.Value/60),"00") + ":" + Format((Fields!Minutes.Value Mod 60),"00")

Below is a sample report with formatted minutes.

Hope you will find this useful. If yes feel free to leave a comment below.

Note: This article/demonstration is based on SSRS 2008. The options should be almost the same in SSRS 2005 as well.