Tag Archives: #SQLServer

All about Microsoft SQL Server

#0126 – SQL Server – SSMS – Tutorial – Part 01 – Introduction to the Management Studio and Query Editor (L100)

The SQL Server Management Studio

For those who have worked with Microsoft SQL Server in the past (i.e. circa SQL Server 7 or SQL Server 2000 era), the following would be familiar terms to you:

Enterprise Manager
- Provided a Microsoft Management Console (MMC) compliant UI to administer, configure and manage a given set of SQL Server instances
Query Analyzer
- Provided a development interface to develop and debug T-SQL code
Analysis Manager
- Provided an IDE for data warehousing solution development and setup

Starting Microsoft SQL Server 2005, all the 3 interfaces mentioned above have been combined into one – the SQL Server Management Studio, popularly known as the SSMS. In addition to the above, the SSMS also features a broad variety of graphical tools and rich script editors to provide access to SQL Server developers and administrators of various skill levels.

Accessing the SQL Server Management Studio

In a default installation of Microsoft SQL Server 2008 R2, the SSMS is installed in the directory – C:Program FilesMicrosoft SQL Server100ToolsBinnVSShellCommon7IDE.

Program shortcuts in the Start Menu are created for the SSMS. The user can either use these shortcuts or directly execute “ssms” from the “Run…” window.

Connecting to a SQL Server instance

To work with any instance of SQL Server, the first thing that we need to do is to connect to the particular instance. Almost each window of the SSMS establishes its’ own connection to the SQL Server.

Whenever SSMS is launched, one of the first screens that one encounters is the login screen. Based upon the user inputs, the login screen builds a connection string that will be used by the various components of the SSMS. The very basic information that needs to be provided to this screen is the name of the SQL Server instance, the authentication type, user name and password. However, clicking on the “Options” button provides you the option to customize the following parameters of a connection:

Default Database (from a list of all databases on the chosen instance)
Network protocol (auto-determines the protocol to use – shared memory for local connections, TCP/IP for remote instances)
Network Packet Size (default is 4096 bytes)
Connection & Execution timeouts (default is 15 seconds)
Encryption details (not enforced by default)
Any other connection parameters that one might want to use


Login dialog	Login dialog – setting connection properties	Login dialog – add any additional connection parameters here.

The windows of SQL Server Management Studio

Upon successful login, you will see a number of windows and tools in the SQL Server Management Studio, namely:

Query Editor
Object Explorer
Registered Servers
Properties Window
Templates window

Most of these tools & windows, if not available, can be accessed from the View menu.

IMPORTANT: It is important to note that the SQL Server Management Studio is based off the Visual Studio platform. Therefore, the following usability features are directly inherited by the SSMS:

Maximize the user workspace for development and management

Reduce the number of windows open at a time

Provides the ability to customize the user environment

Because it is based upon the Visual Studio shell, the SSMS allows the user to control the window size and therefore, the amount of space that the user wishes to allocate for different windows. The windows can be moved to different locations, undocked and even moved out of the SSMS frame. The query editor windows can be opened in the multi-document interface (MDI) modes also.

Pinal Dave (blog), a Microsoft Database & BI technology evangelist in India, wrote about the multi-monitor support capabilities in the upcoming release of SQL Server – SQL 2012 (code named: “Denali”). You can read about this feature here: http://blog.sqlauthority.com/2011/01/24/sql-server-2011-multi-monitor-ssms-windows/

Query Editor window

One of the most important parts of the SSMS environment is the Query Editor window, which is what will be used to write and execute scripts.

To launch a new query editor window, in addition to opening a pre-existing query, you can follow any one of the following navigation paths:

File -> New -> Database Engine Query
File -> New ->Query with current connection
File -> New ->Analysis Services MDX/DMX/XMLA

Getting comfortable with the Query Editor UI

Because this is one of the most important windows that we would be working with, it is important to get a feel of the various components of this window:

The major parts of the Query editor window are:

The script editor window
- A feature-rich text editing environment supporting find and replace, bulk commenting, custom fonts and colors, and line numbering. Some types of editors contain additional features like outlining and auto-complete
Status bar
- Connection state
- Server Instance Name and version
- Login Name and associated SPID number
- Current database name
- Time taken for last query/batch execution
- Number of rows returned during last query/batch execution

Apart from the above components, the Query Editor window also has the following child-windows which are visible depending upon various factors such as user configuration, whether the query execution has taken place or not. These windows can be seen in tabbed, docked or floating modes. These windows are:

Results tab
- Displays the result of a query execution. Results can be in grid or text format
- This gets reset when the next execution of the query/batch is performed
Messages tab
- Displays any messages that may be generated as part of query execution
- This gets reset when the next execution of the query/batch is performed
Error List
- Displays syntax and semantic errors as you edit the T-SQL scripts
- This gets reset when the next execution of the query/batch is performed
Client Statistics
- This window displays the query execution information grouped into categories
- For multiple rounds of execution, this window shows the aggregated average information

We will take a look at these windows further down the article.

Intellisense & Error List windows

The following is an example of a basic T-SQL query based on the AdventureWorks2008R2 sample database.

USE AdventureWorks2008R2
GO
SELECT * FROM HumanResources.Employee
GO

After logging in to a particular SQL Server instance using SSMS, type in the given script. As you type along in the Query editor window, notice that the Query Editor window prompts you with a list of possible alternatives that it thinks you would type in next. This prompt is what we call – “Intellisense”.

The good thing about “Intellisense” is that the prompts not only contain the list of options, but there are small icons next to the objects that help the user identify the nature of the object, i.e. whether it is a database, schema name, variable name, a table or a column name, etc. Also, we can see some nice tooltips towards the side of the Intellisense prompt that contains a description about the object.

A user can navigate through the Intellisense prompt by using the arrow keys on the keyboard (which I find to be the fastest way to do so) or use the mouse. Once the desired object is selected, all the user has to do is press the Enter or the Tab key (when using the keyboard) or double-click on the mouse.

IMPORTANT:

Intellisense is case-sensitive, making it a key feature for those working on case-sensitive collations

For SQL Server 2008 R2, Intellisense only performs a “begins-with” search, whereas in SQL 2012 (code named: “Denali”), it performs a “contains” or a “like” search. Read my post on this here: http://beyondrelational.com/blogs/nakul/archive/2011/08/11/intellisense-in-sql-11-denali-uses-like-search-a-productivity-enhancement.aspx

Now, navigate out to the View menu, and choose to display the “Error List” window. Assume that you made a mistake during the typing of the query.


Launching the Error List window	Error List window in action

As you can see, the Error list window immediately detects syntax and semantic errors as you type along. Double-clicking on the error brings you to the concerned line that encountered the error. Once corrected, the error clears out from the Error List.

However, the Error List window has a few drawbacks. You can read about them in Aaron Bertrand’s post (blog | twitter) referenced in the Further Reading section below.

Query Regions

Prior to the Management Studio for SQL Server 2008 coming out, all code within the Query editor window was a giant block, which caused readability issues. Let’s say for example that you are working on a lengthy (approximately more than 500 lines) stored procedure/script consisting of several logical sections. After the development of one particular section is complete, you may not need to look at it again, and might prefer to selectively hide it such that it does not interfere with your working area on the query editor.

The SSMS for SQL Server 2008 introduced a new usability feature – Query Regions. A vertical line on the left edge of the editor window uses a square with a minus sign (-) to identify the start of each collapsible code region. When you click a minus sign, the text of the code region is replaced with a box that contains three periods (…), and the minus sign changes to a plus sign (+). Clicking on the (+) sign expands the code section.

When working on a database engine (T-SQL) query, the query editor generates outline regions in the following hierarchy:

Batches
1. From the start of the file to the first GO command (or till the end of the file in case no GO commands are present)
Blocks of code grouped by BEGIN…END keywords. This includes the following:
1. BEGIN…END
2. BEGIN TRY…END TRY
3. BEGIN CATCH…END CATCH
Multi-line statements

I will use the same example as BOL (http://msdn.microsoft.com/en-us/library/cc281835.aspx) to demonstrate the behavior.

USE AdventureWorks2008R2
GO
CREATE PROCEDURE Sales.SampleProc --Outline region 1
AS
BEGIN --Outline region 2 
  SELECT GETDATE() AS TimeOfQuery;
  SELECT * --Outline region 3
  FROM sys.transmission_queue;
  SELECT @@VERSION;
END;
GO


Fully expanded regions	Fully closed regions

Innermost region closed	As we close the higher-level regions, the inner levels are closed alongwith

Getting familiar with the Query Editor Toolbars

Now that we have written our first T-SQL query in the query editor, it is time for us to explore the SSMS toolbars.

To view a toolbar, you can:

Use the View -> Toolbar menu
Right-click on the toolbar space in the Query editor window and choose the required toolbar

The most important toolbars that I find are:

Standard
SQL Editor
Text Editor

The Standard Toolbar

The Standard toolbar allows you to perform the most of the generic activities that SSMS can perform. This includes:

Write a new Query (all types)
Perform basic file operations (open, save and print)
Launch a tool called “Activity Monitor” (to be discussed in future tutorial)

The SQL Editor Toolbar

Connect to or Disconnect from a SQL Server instance
Change the active connection
Execute, Debug or cancel execution of a query in the active query editor window
Parse the query in the active query editor window
Modify the query options for the particular connection
Toggle Intellisense state
Choose whether to display results in the grid/text or export to a file
Comment/Uncomment or adjust indentation of a given section of text
Display Execution plans and client statistics
Specify template parameters (to be covered in a future tutorial)

The Text Editor toolbar

In addition to allowing the user to navigate through the script, the text editor toolbar allows the user to:

Work with Intellisense options
- Display an object member list
- Display parameter info
- Display quick info
- Display word completion

Executing a query

Our very first T-SQL query has been written and we are now familiar with the toolbars on the SSMS window. It is now time for us to press the “Execute” toolbar button on the SQL Editor toolbar.

Results tab

One of the first things that you notice as you execute the query is that we now have an additional set of tabbed windows towards the bottom of the SSMS window. The first of these tabs is the “Results” tab, which contains the results of query that was executed.

Results are contained in grid format on the Results Tab. Based on your requirement, you can choose whether to display these in text or to export them to a file on disk.

TIP: There is a way to discard these results automatically. That is to say that the query will be executed, but no results would be returned to the calling SSMS connection, saving memory, while providing actual query execution information useful when performance tuning. You can find information on this in the “Exercises/How To…?” section.

Messages tab

Any messages that need to be displayed to the user (includes status, error and user generated messages) are displayed in the Messages Tab.

In addition, the system may also present the query results in the messages tab if the user has chosen to view the query results in text.

Client Statistics

Although this is not really in scope for this level of the discussion, an optional client statistics tabbed window can be invoked to obtain vital information of importance during performance tuning. It returns us the information about the query cost in terms of execution, network and time statistics.

To know more about this, refer the “Further Reading” section.

Practice Exercises/How To…?

There can be no learning without some exercises. Because we are dealing with SSMS and the Query Editor, these exercises will help you explore the various configuration options that you can use to create your very own, customized version of the SSMS environment.

SSMS

Query Editor

How to use the zoom feature in upcoming SQL2012 (Code Named: “Denali”)?
How to use the Clipboard ring feature inherited from Visual Studio 2010?
How to change the colour of the query editor status bar?
How to use the Query editor to generate comma-separated or tab-separated output without BCP or query changes?
Type in the following T-SQL query. Identify the colour coding scheme used to identify the various object types:
- Database name
- Keywords
- Table/object names
- System Function Names
Use the Query editor options to modify the information shown on the default sections of the Query editor

IMPORTANT: Keep in mind that the colour coding scheme used by SQL Server 2008 is different from what is used in SQL Server 2012 (code named: “Denali”) CTP03 or RC0 releases.

SQL Server – Know your query SET options – Properties window and DBCC USEROPTIONS

Retrieving SET options for a given session

To quickly review the SET options for a given session, we have two methods:

Properties Window
- The Properties window is available if we are in a SSMS session – this method is not applicable when attempting to fetch the SET options via code
- I described this method in my previous post, August 01, 2011 (http://beyondrelational.com/blogs/nakul/archive/2011/08/01/ssms-properties-window-know-your-query-options-ansi-nulls-quoted-identifier-arithabort-and-other-set-options.aspx)
Using DBCC commands
- For those would be interested to get these SET options via code, the DBCC comes to our rescue
- Let’s take a quick look at this method to get the user options

Demo

Using the Properties window to get the user options involves executing a query workload and retrieving the actual execution plan. DBCC, on the other hand allows us to fetch the user options without any workload and without retrieving the execution plan.

For this demonstration, let’s make a small change to the default user options, and run the DBCC statement in the query below.

--Change the connection option
SET ANSI_NULLS OFF

--Retrieve the user options for this connection
DBCC USEROPTIONS
GO

Now, let’s roll back the change (or launch a new session by creating a new query editor window), and run the following statement.

--Change the connection option
SET ANSI_NULLS ON

--Retrieve the user options for this connection
DBCC USEROPTIONS
GO

Comparing the outputs:

Comparing the output from the two sessions, we can see that the user options that are turned OFF do not appear on the list.

ANSI_NULLS OFF		ANSI_NULLS ON
Set option	Value	Set option	Value
textsize	2147483647	textsize	2147483647
language	us_english	language	us_english
dateformat	mdy	dateformat	mdy
datefirst	7	datefirst	7
lock_timeout	-1	lock_timeout	-1
quoted_identifier	SET	quoted_identifier	SET
arithabort	SET	arithabort	SET
ansi_null_dflt_on	SET	ansi_null_dflt_on	SET
ansi_warnings	SET	ansi_warnings	SET
ansi_padding	SET	ansi_padding	SET
		ansi_nulls	SET
concat_null_yields_null	SET	concat_null_yields_null	SET
isolation level	read committed	isolation level	read committed

Reference

Books On line page for DBCC USEROPTIONS – http://msdn.microsoft.com/en-us/library/ms180065.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

SQL Server – Performance tuning – DBCC FLUSHPROCINDB – Flush procedures of a particular database from cache

References

Plan caching in SQL Server 2008 – http://msdn.microsoft.com/en-us/library/ee343986(SQL.100).aspx

NOTE: To the best of my knowledge, this is an undocumented DBCC command. Please use it with utmost caution. If you find the official Books On Line documentation, please let me know and I will update the post to reflect the same.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

SQL Server – Experiments with BIT data type – Is it really Boolean? Can values other than 0 and 1 be assigned?

2 Replies

Recently at the office, we ended up discussing the BIT data type (http://msdn.microsoft.com/en-us/library/ms177603.aspx). While we all understand that BIT is supposed to represents a Boolean value, we were not quite convinced. Boolean values are two-state – they can only be TRUE or FALSE (or 1 and 0). However, we have seen BIT to be NULL a couple of times. Therefore, the questions that we had in our minds were:

Is BIT really a two-state data type? Or is it tri-state (for those not familiar with the world of electronics, the tri-state is a state that’s undefined – it’s neither ON nor OFF)
Do we need to use only a 0 or a 1 to initialize the BIT data type?
Can we use ‘TRUE’ and ‘FALSE’ with the BIT datatype?

Finally, we decided to perform a little experiment. We prepared a small script covering the possible scenarios and checked their outputs. The script is available for your reference below:

--Declare the variable
DECLARE @bit BIT

--Check for default value
SELECT @bit AS DefaultValue

--Set to a positive value, other than 0 or 1
SET @bit = 99;
SELECT @bit AS [PositiveValue];

--Set to a negative value
SET @bit = -99;
SELECT @bit AS [NegativeValue];

--Set to a decimal value > 0 and < 1
SET @bit = 0.25;
SELECT @bit AS [DecimalValue025];

--Set to a decimal value > 0 and < 1
SET @bit = 0.50;
SELECT @bit AS [DecimalValue050];

--Set to a decimal value > 0 and < 1
SET @bit = 0.75;
SELECT @bit AS [DecimalValue075];

--Set to a string value - TRUE
SET @bit = 'TRUE';
SELECT @bit AS [StringTRUE],
       CASE
           WHEN @bit = 'TRUE'
           THEN 'Yes'
           ELSE 'No'
       END AS  [StringComparison];

--Set to a string value - FALSE
SET @bit = 'FALSE';
SELECT @bit AS [StringFALSE],
       CASE
           WHEN @bit = 'TRUE'
           THEN 'Yes'
           ELSE 'No'
       END AS  [StringComparison];

The results that came up resolved all our questions.

Results from my experiments with the BIT datatype. (Image (c)SQLTwins, nakulvachhrajani.com) — Experimenting with the BIT datatype in SQL Server

Conclusion

The above experiment helped us conclude that:

BIT values are not completely Boolean, they are tri-state with NULL, 0 and 1 being possible values
If left unassigned, the value is NULL
If assigned with a value anything other than 0 or NULL, the value is taken as 1
You can use a ‘TRUE’ and ‘FALSE’ string values with the BIT data type
Because the default value of BIT is NULL, always assign your BIT variables! (for that matter, always assign a default value to any variable!)

I trust you found the above experiment interesting. I would welcome your ideas for future experiments.

Until we meet next time,

Be courteous. Drive responsibly.

SQL Server – Storage requirements – CHAR v/s VARCHAR – choose the correct data-type

1 Reply

I work with academia frequently, and therefore, get a chance to interact with students and experience the issues they face first hand. I recently had a very interesting experience during one of these visits. I will try to present the experience as a story.

The Problem

One of the students had developed a system which involved some database interaction. As I was reviewing the system, I noticed an issue with the table design. The design that the student had come up with was something like the following:

USE tempdb
GO
--Create the table, with fixed length columns
CREATE TABLE CityMaster (CityId      INT IDENTITY (1,1),
                         CityName    CHAR(50),
                         StateName   CHAR(50),
                         CountryName CHAR(50)
                        )
GO

(The design above is not exactly what he had, but you get the general idea.)

The problem that I had was with his choice of data types. He had chosen a fixed-length data type – CHAR.

I explained that CHAR, being a fixed-length data type, pads a string with trailing blanks when the data is stored to achieve the fixed-length. Variable length data types (e.g. VARCHAR), on the other hand, do not pad trailing blanks, and therefore, have a variable space requirement.

To explain this with an example, the simplest thing for me to do was to insert some test values in his database. Once the test data was ready, I ran the following query, which uses the DATALENGTH() and LEN() functions. For those who came in late,

DATALENGTH = Returns the number of bytes used to represent an expression
- This can be used to estimate the storage space that SQL Server has taken up for an expression
LEN = Returns the number of characters of the specified string expression, excluding trailing blanks

USE tempdb
GO                        
--Insert test data
INSERT INTO CityMaster (CityName, StateName, CountryName)
VALUES ('Ahmedabad','Gujarat',    'India'),
       ('Mumbai'   ,'Maharashtra','India'),
       ('New Delhi','Delhi'      ,'India'),
       ('Bengaluru','Karnataka'  ,'India')
GO

USE tempdb
GO
--View the length and the data length for the data in the table
SELECT CityMaster.CityId,
       CityMaster.CityName,    LEN(CityMaster.CityName)    AS CityNameLength,    DATALENGTH(CityMaster.CityName)  AS CityNameDataLength,
       CityMaster.StateName,   LEN(CityMaster.StateName)   AS StateNameLength,   DATALENGTH(CityMaster.StateName) AS StateNameDataLength,
       CityMaster.CountryName, LEN(CityMaster.CountryName) AS CountryNameLength, DATALENGTH(CityMaster.CountryName) AS CountryNameDataLength
FROM CityMaster
GO

Using the output of the above query, it became easy for me to explain that because CHAR consumes the full 50 characters, the SQL Server consumes a space of 50 + 50 + 50 = 150 bytes irrespective of the amount of data actually being requested for storage.

The solution

As a solution, I recreated the table with the following design:

USE tempdb
GO
--Create the table, with variable length columns
CREATE TABLE CityMaster_v2 (CityId      INT IDENTITY (1,1),
                            CityName    VARCHAR(50),
                            StateName   VARCHAR(50),
                            CountryName VARCHAR(50)
                           )
GO

Again, I inserted the test data and ran the length determination queries.

USE tempdb
GO                          
--Insert test data
INSERT INTO CityMaster_v2 (CityName, StateName, CountryName)
VALUES ('Ahmedabad','Gujarat',    'India'),
       ('Mumbai'   ,'Maharashtra','India'),
       ('New Delhi','Delhi'      ,'India'),
       ('Bengaluru','Karnataka'  ,'India')
GO

USE tempdb
GO
--View the length and the data length for the data in the table
SELECT CityMaster_v2.CityId,
       CityMaster_v2.CityName,    LEN(CityMaster_v2.CityName)    AS CityNameLength,    DATALENGTH(CityMaster_v2.CityName)  AS CityNameDataLength,
       CityMaster_v2.StateName,   LEN(CityMaster_v2.StateName)   AS StateNameLength,   DATALENGTH(CityMaster_v2.StateName) AS StateNameDataLength,
       CityMaster_v2.CountryName, LEN(CityMaster_v2.CountryName) AS CountryNameLength, DATALENGTH(CityMaster_v2.CountryName) AS CountryNameDataLength
FROM CityMaster_v2
GO

The results spoke for themselves, and he quickly grasped the concept that I was trying to put forward, i.e. variable length datatypes have varying storage requirements, making them more efficient from a storage perspective.

ALTER TABLE…ALTER COLUMN will not help in releasing space from fixed-length data-types

Because the concept was clear, he proceeded with converting his existing table designs to use variable-length data types using the ALTER TABLE…ALTER COLUMN statement.

USE tempdb
GO
--Changing CHAR to VARCHAR will NOT release space back once the allocations have been done
ALTER TABLE CityMaster
    ALTER COLUMN CityName VARCHAR(50)

ALTER TABLE CityMaster
    ALTER COLUMN StateName VARCHAR(50)
    
ALTER TABLE CityMaster
    ALTER COLUMN CountryName VARCHAR(50)
GO

However, he found that the ALTER TABLE…ALTER COLUMN statement did not make a difference to the storage requirements.

The reason, I explained, was that once SQL Server has added trailing spaces, it is no longer able to distinguish between an intentionally padded and an automatically padded version of the same string (e.g. ‘BeyondRelational.com ‘ (3 spaces) from ‘BeyondRelational.com’ (no spaces)) and therefore, SQL Server will not be able to release the space allocated to the trailing spaces back.

The only option for him was to drop-and-recreate the tables and then re-insert all his test data.

Now that we were all set with the concept, I pointed him to one of Vinod Kumar’s (blog|twitter) posts: http://blogs.extremeexperts.com/2003/10/10/using-varchars/

Conclusion

There are not one, but 2 very good reasons why I shared this experience with the community.

The prime reason being that this post might be helpful to students trying to understand and explore databases, and inculcate in them a habit of choosing the correct data-types
I have seen production code with such issues, such as the incorrect choice of data-types. For us in the industry, this is a capital mistake that should not be made, ever

Reference

CHAR & VARCHAR Datatypes: http://msdn.microsoft.com/en-us/library/ms176089.aspx
DATALENGTH: http://msdn.microsoft.com/en-us/library/ms173486.aspx
LEN: http://msdn.microsoft.com/en-us/library/ms190329.aspx
http://blogs.extremeexperts.com/2003/10/10/using-varchars/

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql

The SQL Server Management Studio

Accessing the SQL Server Management Studio

Connecting to a SQL Server instance

The windows of SQL Server Management Studio

Query Editor window

Getting comfortable with the Query Editor UI

Intellisense & Error List windows

Query Regions

Getting familiar with the Query Editor Toolbars

The Standard Toolbar

The SQL Editor Toolbar

The Text Editor toolbar

Executing a query

Results tab

Messages tab

Client Statistics

Practice Exercises/How To…?

SSMS

Query Editor

Further reading

SSMS

Query Editor

Retrieving SET options for a given session

Demo

Comparing the outputs:

Reference

References

Conclusion

The Problem

The solution

ALTER TABLE…ALTER COLUMN will not help in releasing space from fixed-length data-types

Conclusion

Reference