Beginning of SQL Server Architecture – Terminology

SQL Server Architecture is a very deep subject. Covering it in a single post is an almost impossible task. However, this subject is very popular topic among beginners and advanced users.  Here Anil Kumar who is expert in SQL Domain to help Pinal write  a simple post about Beginning SQL Server Architecture. As stated earlier this subject is very deep subject and in this first article series he has covered basic terminologies. In future article he will explore the subject further down. Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

---

In this Article we will discuss about MS SQL Server architecture.

The major components of SQL Server are:

1. Relational Engine
2. Storage Engine
3. SQL OS

Now we will discuss and understand each one of them.

1) Relational Engine: Also called as the query processor, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

Different Tasks of Relational Engine:

1. Query Processing
2. Memory Management
3. Thread and Task Management
4. Buffer Management
5. Distributed Query Processing

2) Storage Engine: Storage Engine is responsible for storage and retrieval of the data on to the storage system (Disk, SAN etc.). to understand more, let's focus on the concepts.

When we talk about any database in SQL server, there are 2 types of files that are created at the disk level – Data file and Log file. Data file physically stores the data in data pages. Log files that are also known as write ahead logs, are used for storing transactions performed on the database.

Let's understand data file and log file in more details:

Data File: Data File stores data in the form of Data Page (8KB) and these data pages are logically organized in extents.

Extents: Extents are logical units in the database. They are a combination of 8 data pages i.e. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents are dedicated to only one type (object).

Pages: As we should know what type of data pages can be stored in SQL Server, below mentioned are some of them:

• Data Page: It holds the data entered by the user but not the data which is of type text, ntext, nvarchar(max), varchar(max), varbinary(max), image and xml data.
• Index: It stores the index entries.
• Text/Image: It stores LOB ( Large Object data) like text, ntext, varchar(max), nvarchar(max),  varbinary(max), image and xml data.
• GAM & SGAM (Global Allocation Map & Shared Global Allocation Map): They are used for saving information related to the allocation of extents.
• PFS (Page Free Space): Information related to page allocation and unused space available on pages.
• IAM (Index Allocation Map): Information pertaining to extents that are used by a table or index per allocation unit.
• BCM (Bulk Changed Map): Keeps information about the extents changed in a Bulk Operation.
• DCM (Differential Change Map): This is the information of extents that have modified since the last BACKUP DATABASE statement as per allocation unit.

Log File: It also known as write ahead log. It stores modification to the database (DML and DDL).

• Sufficient information is logged to be able to:
  • Roll back transactions if requested
  • Recover the database in case of failure
  • Write Ahead Logging is used to create log entries
    • Transaction logs are written in chronological order in a circular way
    • Truncation policy for logs is based on the recovery model

3) SQL OS: This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. It is a highly configurable operating system with powerful API (application programming interface), enabling automatic locality and advanced parallelism. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure. Other services include exception handling, hosting for external components like Common Language Runtime, CLR etc.

I guess this brief article gives you an idea about the various terminologies used related to SQL Server Architecture. In future articles we will explore them further.

Guest Author 

The author of the article is Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

Reference: http://blog.sqlauthority.com

10 reasons why SQL Server 2008 is going to rock

Just like its predecessor, SQL Server 2008 is taking its sweet time to actually ship.  However, unlike its predecessor, it won't just be a "worthwhile upgrade".  It will kick ass.

Here are the top 10 reasons why.

10.  Plug-in model for SSMS.   SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind.  Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom. 

9.  Inline variable assignment.  I often wondered why, as a language, SQL languishes behind the times.  I mean, it has barely any modern syntactic sugar.  Well, in this version, they are at least scratching the the tip of the iceberg. 

Instead of:

DECLARE @myVar int   SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Sweet.

8.  C like math syntax.  SET @i += 5.  Enough said.  They finally let a C# developer on the SQL team. 

7.  Auditing.  It's a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws.  It's a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it.  SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.

6.  Compression.  You may think that this feature is a waste of time, but it's not what it sounds like.  The release will offer row-level and page-level compression.  The compression mostly takes place on the metadata.  For instance, page compression will store common data for affected rows in a single place. 

The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes).  For instance, length of the varchar will be stored in 3 bits. 

Anyway, I don't really care about space savings - storage is cheap.  What I do care about is that the feature promised (key word here "promises") to reduce I/O and RAM utilization, while increasing CPU utilization.  Every single performance problem I ever dealt with had to do with I/O overloading.  Will see how this plays out.  I am skeptical until I see some real world production benchmarks.

5.  Filtered Indexes.  This is another feature that sounds great - will have to see how it plays out.  Anyway, it allows you to create an index while specifying what rows are not to be in the index.  For example, index all rows where Status != null.  Theoretically, it'll get rid of all the dead weight in the index, allowing for faster queries. 

4.  Resource governor.  All I can say is FINALLY.  Sybase has had it since version 12 (that's last millennium, people).  Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to.  At the very least, it'll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.

Actually Sybase is still ahead of MS on this feature.  Its ASE server allows you to prioritize one user over another - a feature that I found immensely useful.

3.  Plan freezing.  This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc...).  If you've achieved your optimal query plan, now you can stick with it.  Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server - well, this is the chill pill.

2.  Processing of delimited strings.   This is awesome and I could have used this feature...well, always.  Currently, we pass in delimited strings in the following manner:

exec sp_MySproc 'murphy,35;galen,31;samuels,27;colton,42'

Then the stored proc needs to parse the string into a usable form - a mindless task.

In 2008, Microsoft introduced Table Value Parameters (TVP). 

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int)   DECLARE @myPeeps PeepsType   INSERT @myPeeps SELECT 'murphy', 35   INSERT @myPeeps SELECT 'galen', 31   INSERT @myPeeps SELECT 'samuels', 27   INSERT @myPeeps SELECT 'colton', 42    exec sp_MySproc2 @myPeeps 

And the sproc would look like this:

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) ...

The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc.  Say goodbye to all those string parsing routines.

1. Intellisense in the SQL Server Management Studio (SSMS).  This has been previously possible in SQL Server 2000 and 2005 with use of 3rd party add-ins like SQL Prompt ($195).  But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing). 

Built-in intellisense is huge - it means new people can easily learn the database schema as they go.

There are a ton of other great features - most of them small, but hugely useful.  There is a lot of polishing all over the place, like server resource monitoring right in SSMS, a la Vista. 

Ref.:http://angryhacker.com/blog/archive/2008/06/20/10-reasons-why-sql-server-2008-is-going-to-rock.aspx

 

Use local temporary(#table) table of one SP to other SP

--==================================

--Coded in Sql 2000

--It will be helpful to you for seperate a logic

--==================================

CREATE PROC BVTSP1

AS

CREATE TABLE #TEMP (ID INT)

 

INSERT INTO #TEMP

SELECT 1

UNION

SELECT 2

UNION

SELECT 3

UNION

SELECT 4

 

SELECT * FROM #TEMP

 

EXEC BVTSP2

 

GO

------------------------------------

CREATE PROC BVTSP2

AS

 

INSERT INTO #TEMP 

VALUES(16)

 

SELECT * FROM #TEMP

 

GO

 

--Also can try to Exec in other sql editor

Exec BVTSP1

Difference Between Stored Procedure and Functions

Stored Procedure	Functions
have to use EXEC or EXECUTE	can be used with Select statement
return output parameter	Not returning output parameter but returns Table variables
you can not join SP	You can join UDF
can be used to change server configuration	Cannot be used to change server configuration
can have transaction within SP	Cannot have transaction within function
Cannot use in where,having clause	Can be use in where,having clause
Can be used functions in SPs	Cannot execute SP in Function

10 common SQL Server problems and solutions

By Joshua Hoskins, MCSE, MCDBA

1) Users complain that queries take longer than usual. –

If your normal queries take longer than usual, it could be due to resource contention from locking. This will usually occur if new processes have been added to your system, or if the load on your system has recently increased.
You should begin troubleshoot this problem using Query Analyzer and the SP_WHO2 command. The results from this command will contain a BlkBy field. If SPIDs are being blocked, the command output will show the offending SPID number. You may need to follow a large chain of blocks to find the head. Once there, you can use the dbcc inputbuffer command to see the SQL statement that the SPID is running. This will point you toward the problem's cause.

2) Users receive Out of Space errors. –

If users receive messages that a database is out of space, there are two possible answers. First, the physical drive which stores the database or its transaction log has run out of space. If this is the case, examine that disk for any file(s) that are rapidly growing or any other problems. It's also possible that the database or the transaction log file have a set maximum file size. If this is the case, increase this limit to allow database operation. Users may also receive errors that tempdb is full. To resolve this issue, restart the MSSQL service--restarting SQL will recreate tempdb from scratch. You should also determine if tempdb has a maximum size and increase that size if necessary.

3) Users report permissions denied errors after you grant them rights to a stored procedure. –

Even if you give users rights to execute a stored procedure, you still need to give the appropriate rights on the objects referenced by the stored procedure. For example, if you have a stored procedure that performs a select from the customers table, you must give the user select rights on the customer table.

4) Clients drop their sessions without disconnecting from the SQL Server causing block chains. –

Occasionally, your clients may lose connectivity to the SQL server. This can cause their SPID to hold a lock on a resource while waiting on the timeout--this seems especially prevalent in clients running older versions of Microsoft Access. To resolve the problem, use the commands SP_WHO2 and KILL to delete the orphaned process from the server. You can also shorten your clients' time out value, causing the server to more quickly kill orphaned processes.

5) A new query slows the entire system to a crawl. –

On a multi-CPU system or one with hyper-threading, task manager or performance monitor may show 100 percent utilization on one processor, while the others are nearly idle. If this is the case, the currently-running procedure's execution plan is incorrect. Change the degree of parallelism for this query. To do this, use the MaxDop command (maximum degree of parallelism) within the query to specify the degree of parallelism. You may need to repeatedly change this value to find the option that provides the best performance without causing system-wide problems.

6) A formally solid scheduled job begins failing. –

If a user who is listed as the owner of a SQL Agent job is terminated or has a name change, the SQL job will fail. You must reset the owner name on the job to a currently available user name.

7) A database goes suspect. –

If a database is in suspect mode, it generally indicates corruption within the database. If the database is not corrupt, the database's physical files may no longer exist where the master database references them. This can be caused by a physical disk's drive letter being changed or no longer available. To fix this problem, detach the suspect database, then attach it selecting the file(s) new location.

8) You can connect to a SQL instance locally, but can't connect over the network. –

If you cannot connect to your SQL server over the network, the SQL's default port (1433) may have been changed. You will have to reference the new port (or use named pipes) to connect. Also, some versions of MSDE do not allow network connections by default. To correct this, you will have to use a DISABLENETWORKPROTOCOLS=1 switch on setup to allow network connections.

9) You migrate a database to a new server, but the users in the database cannot connect to it. –

If you move a database from one server to another, the database users and their privileges will migrate with the database, but the new SQL system will not house them as SQL users. To migrate the users, you can use the Data Transformation Service (DTS) or a script available in MSKB article Q246133.

10) Users are reporting errors about deadlocking. –

If users are receiving error messages stating that they have been chosen as deadlock victims (or a similar error depending upon your error reporting scheme), your system is suffering from deadlocking. This is caused when two or more queries each locking resources the other query needs to complete. The SQL Server error log will report when it has resolved a deadlocking situation, and that should be the first place you check if you believe you have this problem. You can also turn on trace flag -t1204, as it will give you more information in your log. The first thing you will need to determine is the contention level on your server. Are these two queries specifically having problems running simultaneously? If so, it may be as simple as scheduling them to run at differing times. If they are commonly run queries, the queries may need to be optimized to limit this behavior.

How to import an Excel file into SQL Server 2005 using Integration Services

By Tim Chapman

Takeaway: Integration Services, which replaces Data Transformation Services (DTS) in SQL Server 2005, is a wonderful tool for extracting, transforming, and loading data. This article describes how you can use the new features of Integration Services to load an Excel file into your database.

Integration Services, which replaces Data Transformation Services (DTS) in SQL Server 2005, is a wonderful tool for extracting, transforming, and loading data. Common uses for Integration Services include: loading data into the database; changing data into to or out from your relational database structures; loading your data warehouse data; and taking data out of your database and moving it to other databases or types of storage. This article describes how you can use the new features of SQL Server 2005 Integration Services (SSIS) to load an Excel file into your database.

Note: There are several wizards that come with SQL Server Management Studio to aid you in the import and export of data into and out of your database. I will not look at those wizards; I will focus on how you can build a package from scratch so that you don't have to rely on the wizards.

To begin the process, I open SQL Server Business Intelligence (BI) Development Studio, a front-end tool that is installed when you install SQL Server 2005. The BI Development Studio is a scaled down version of Visual Studio. Then I select New Integration Services Project and give the project a name. See Figure A.

Figure A

When the project opens, you will see an environment that may look familiar to you if you have used SQL Server DTS; some of the items of the toolbox are the same. For the purposes of this project, I am interested in dragging the Data Flow task item from the toolbar into the Control Flow tab. (The idea of a Data Flow task is one of the major differences between DTS and SSIS packages. In an SSIS package, you can control the manner in which your package logic flows inside of the Control Flow tab. When you need to manage the data aspects of your project, you will use the Data Flow task. You can have several different Data Flow tasks in your project — all of which will reside inside the Control Flow tab.) See Figure B.

Figure B

Double-click the Data Flow task that you have dragged onto the Control Flow tab. The available options in the Toolbar have changed; I now have available Data Flow Sources, Data Flow Destinations, and Data Flow Transformations. Since I am going to import an Excel file into the database, I will drag the Excel Source item from the Toolbar onto the screen. See Figure C.

Figure C

The Excel Source item represents an Excel file that I will import from somewhere on my network. Now I need somewhere to put the data. Since my plan is to put the data into the database, I will need a Data Flow Destination. For the purposes of this example, I will choose SQL Server Destination from the Data Flow Destination portion of the toolbar and drag it onto my Data Flow tab. See Figure D.

Figure D

To designate which Excel file I want to import, I double-click the Excel Source item that I moved onto the screen. From there, I find the Excel file on the network that I want to import. See Figure E.

Figure E

I also need to designate the sheet from the Excel file that I want to import, along with the columns from the sheet that I want to use. Figures F and G depict these options.

Figure F

Figure G

Now that I have defined my Excel source, I need to define my SQL Server destination. Before doing that, I need to indicate the Data Flow Path from the Excel file to the SQL Server destination; this will allow me to use the structure of the data defined in the Excel Source to model my SQL Server table that I will import the data into. To do this, I click the Excel Source item and drag the green arrow onto the SQL Server Destination item. See Figure H.

Figure H

To define the database server and database to import the data, double-click the SQL Server Destination item. I will define the server in which I will import the data, along with the database that the data will reside. See Figure I.

Figure I

I also need to define the table that I will insert the Excel data into. I will create a new table named SalesHistoryExcelData. See Figure J.

Figure J

Under the Mappings section, I define the relationship between the Input Columns (the Excel data) and the Destination Columns (my new SQL Server table). See Figure K.

Figure K

Once I successfully define the inputs and outputs, my screen will look like the one below. All I need to do now is run the package and import the data into the new table by clicking the green arrow in the top-middle of the screen, which executes my package. See Figure L.

Figure L

Figure M shows that my package has successfully executed and that 30,000 records from my Excel Source item have been transferred to my SQL Server destination.

Figure M

You can download the Excel file I used for this article.  

Tasks in SSIS packages

Importing and exporting data are some of the simplest, most useful tasks to accomplish in SQL Server. However, there are literally hundreds of other tasks that can easily be accomplished in SSIS packages that will take a significant amount of time to do by a different means. I plan to take a look at several more of these tasks in future articles.

Creative uses for COALESCE() in SQL Server

By Arthur Fuller

• Email
• Print
• Add to Favorites
• Del.icio.us
• Digg
• Facebook
• Google Buzz

• Hacker News
• LinkedIn
• Reddit
• StumbleUpon
• Technorati
• Twitter

COALESCE() accepts a series of values and a value to use inthe event that all items in the list are null; then, it returns the firstnot-null value. This tip describes two creative uses of the COALESCE()function in SQL Server.

Here is a simple example: You have a table of persons whosecolumns include FirstName, MiddleNameand LastName. The table contains these values:

• John A. MacDonald
• Franklin D. Roosevelt
• Madonna
• Cher
• Mary Weilage

If you want to print their complete names as single strings,here's how to do it with COALESCE():

SELECT  FirstName + ' ' +COALESCE(MiddleName,'')+ ' ' +COALESCE(LastName,'')

If you don't want to write that for every query, Listing Ashows how you can turn it into a function. Now whenever you need this script (regardless of what the columns are actuallynamed) just call the function and pass the threecolumns. In the examples below, I'm passing literals, but you can substitutecolumn names and achieve the same results:

SELECT dbo.WholeName('James',NULL,'Bond')
UNION
SELECT dbo.WholeName('Cher',NULL,NULL)
UNION
SELECT dbo.WholeName('John','F.','Kennedy')

Here is theresult set:

Cher  
James  Bond
John F. Kennedy

You'llnotice a hole in our thinking -- there are two spaces in James Bond's name.It's easy to fix this by changing the @result line to the following:

SELECT @Result = LTRIM(@first + ' ' + COALESCE(@middle,'') + ' ') + COALESCE(@last,'')

Here's another use of COALESCE().In this example, I will produce a list of monies paid to employees. The problemis there are different payment arrangements for different employees (e.g., someemployees are paid by the hour, by piece work, with a weekly salary, or bycommission).

Listing Bcontains the code to create a sample table. Here are afew sample rows, one of each type:

1     18.0040    NULL  NULL  NULL  NULL
2     NULL  NULL  4.00  400   NULL  NULL
3     NULL  NULL  NULL  NULL  800.00      NULL
4     NULL  NULL  NULL  NULL  500.00      600

Use the following code to list the amount paid to employees (regardless of how they are paid)in a single column:

SELECT 
      EmployeeID,
      COALESCE(HourlyWage * HoursPerWeek,0)+
      COALESCE(AmountPerPiece * PiecesThisWeek,0)+
      COALESCE(WeeklySalary + CommissionThisWeek,0)AS Payment
FROM [Coalesce_Demo].[PayDay]

Here is theresult set:

EmployeeID  Payment
1     720.00
2     1600.00
3     800.00
4     1100.00

You mightneed that expression in several places in your application and, although itworks, it isn't very graceful. Thisis how you can create a calculated column to do it:

ALTER TABLE Coalesce_Demo.PayDay
ADD Payment AS
      COALESCE(HourlyWage * HoursPerWeek,0)+
      COALESCE(AmountPerPiece * PiecesThisWeek,0)+
      COALESCE(WeeklySalary + CommissionThisWeek,0)

Now asimple SELECT * displays the pre-calculated results.

Summary

This tip demonstrates some unusual ways and places to applythe power of COALESCE(). In my experience, COALESCE() most often appears within a very specificcontent, such as in a query or view or stored procedure.

You can generalize the use of COALESCE()by placing it in a function. You can also optimize its performance and make itsresults constantly available by placing it in a calculated column.

Split the String

Copy & Paste below script into Query window and execute it

/*------------Create Split function--------------------*/
CREATE FUNCTION [dbo].[Split]
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
value nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (value)
Select
value = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (value)
Select value = ltrim(rtrim(@RowData))

Return
END

/*-------------How toUse--------------*/

declare @string varchar(200)
set @string='a,b,c,d,e,f,g'
select value from dbo.Split(@string,',')

10 things you should know about SQL Server 2005 Management Studio

Takeaway: SQL Server 2005 Management Studio is a phenomenal advance over the tools provided with SQL Server 2000. Check out the improvements in SQL Server Management Studio, which makes it easier to locate errors in your code and allows you to keep tabs on your reports.

By Arthur Fuller and Stephen Giles

Microsoft SQL Server 2005 contains a number of major new features, but the feature that we like the most is the SQL Server Management Studio. This tool is leaps and bounds beyond what was available in earlier versions of SQL Server. Here is a list of the 10 things that we find most useful about SQL Server Management Studio.

1. Combines the best features of many tools
In earlier versions of SQL Server, you had two main tools: a graphical administration tool (Enterprise Manager) and a Transact SQL Editor (Query Analyzer). The problem with this split is that we do development and administration on SQL Server (sometimes at the same time) and often have to flip back and forth between the two. In SQL Server Management Studio, the Enterprise Manager and Query Analyzer are combined into one common interface, allowing you to manage your servers graphically and to write Transact SQL.

SQL Server Management Studio also allows you to access an Object Browser for all registered servers, which combines the features of the Object Browser from Query Analyzer with the Server tree view from Enterprise Manager. In addition, it provides a workspace similar to Query Analyzer, with the expected tools like the Language Parser and the Graphical Show plan. Now you can write queries and scripts and manipulate objects with Wizards and Property sheets in the same tool at the same time.

SQL Server Management Studio's interface has a separate Registered Servers view that allows you to work with multiple servers at the same time. You can do this in Enterprise Manager; however, SQL Server Management Studio allows you to register server instances as well as all Analysis Services, Reporting Services, SQL Server Integration Services, and Mobile SQL instances. Thus, you can obtain an enterprise view or concentrate on the particular instances and objects of interest.

2. Work with projects and solutions
If you have worked with Visual Studio, then you are familiar with the concept of projects and solutions. In a nutshell, projects allow you to group files together and access them as a unit. A solution is a series of projects, enabling you to drill down to projects just as OLAP users can drill down to the data dimension of interest.

A project can contain .sql, .mdx, .xmla, and .dmx scripts. You can also add other files (such as XML or CSV files) to a project. Therefore, the project itself is a drill-down object.

To create a new project, follow these steps:
1. Click File | New | Project.
2. Choose the type of project you want to create (SQL Server Scripts, Analysis Services Scripts, or SQL Mobile Scripts).
3. Give your project and solution a name.
4. Select the path where you want to store the files.
5. Click OK.

Now you can define various data sources (if your project touches more than one database) and add files effortlessly (simply right-click the Scripts folder in the Solution Explorer and select the items to add). You can also import scripts into a project if you have done some work already.

(If you don't see the Solution Explorer in your SQL Server Management Studio, select View | Solution Explorer or hit [Ctrl][Alt]L.)

3. The tool is a data analyst's best friend
Thanks to the integration of the OLAP tools, SQL Server Management Studio is a great tool for working with your cubes. The object browser allows you to access Analysis Services objects to graphically manage your cubes. It also lets you write and execute MDX and DMX and XMLA statements from within the editor window, allowing you to run both OLTP and OLAP queries from the same tool and even from within the same project.

4. You can display line numbers

How many times within Query Analyzer have you had to walk down the lines to count up to the line flagged with an error? With SQL Server Management Studio, you can display line numbers in the code editor, which is extremely helpful when you're parsing code to locate the problem line and inevitable typos.

This feature is not turned on by default. Here's how to enable line numbers:
1. Select Tool | Options.
2. Expand Text Editor in the Options Tree and select All Languages.
3. In the property pane on the right, select the Line Numbers check box under the Display heading.

There is one "feature" with line numbering. If you have batch statements in your script (Begin...End or Go statements), the results page will recalculate line numbering within the block (i.e., it will start counting from line 1).

5. It's easier to find errors
SQL Server Management Studio has retained one of our favorite features of Query Analysis: linking to errors in the body of your script from the error message in the Message pane. Note that the line number referenced in the error message may not correspond to the line numbering if a script contains multiple batches. You can, however, find the line causing the error simply by double-clicking the error (the red text) in the Message pane. This action will highlight the offending line in the body of the script. (You may want to use the pre-parse function with this feature to clean up all syntax errors before running a script.)

6. Get started faster with an expanded Template Explorer
Transact SQL is the language of SQL Server, and (as with other versions of SQL Server) you can perform all tasks from queries to object creation through Transact SQL. We like scripting objects primarily because it allows us to have absolute control over what we create, and we can save scripts to document objects and move them easily from a test to a production environment.

However, new features mean new syntax, and thus much more to remember. To make life easier, the SQL Server Management Studio includes an updated Template Explorer (View | Template Explorer or [Ctrl][Alt]T) that lays out the structure of more than 100 objects and tasks in Transact SQL, including administrative tasks like backing up and restoring databases.

Due to the integration of formerly disparate tools, the Template Explorer now includes templates for both Analysis Services and SQL Mobile commands. This means that you can drive DMX, MDX, and XMLA expressions through script templates the same way you could create objects in earlier versions of Query Analyzer.

7. It (sort of) plays well with previous versions
SQL Server Management Studio can run through the SQL Server Distributed Management Objects (DMO) as well as the SQL Management Objects (SMO), which are the preferred management objects for SQL Server 2005. This means that you can administer SQL Server 2000 and MSDE databases using the SQL Server Management Studio. This allows you to keep your databases in previous versions while administering them from SQL Server 2005. One caveat is that SQL Server Management Studio's version of DMO will not allow you to administer SQL Server 7.0 servers. You should consider this yet another good reason to upgrade.

8. Name that registered server
When registering our databases (both in Enterprise Manager and SQL Server Management Studio), we like to use an IP address rather than a server name; this approach facilitates remote connections across a VPN, and name resolution can sometimes be a problem. In Enterprise Manager, we had to remember the IP address for each particular server for which we work. For this purpose, we kept a text file listing all the connection parameters.

In SQL Server Management Studio, you can register by IP address but still give the computer a more recognizable name and even add a description of the server. The name and description will show up on the Registered Servers pane (View | Registered Servers or press [Ctrl][Alt]G), so you always know which server you are working on.

9. Manage your SQL Server Integration Services pages
Microsoft intended for SQL Server Management Studio to enable you to manage all services in one consistent UI, which is the case with the SQL Server Integration Services (SSIS). SSIS is the replacement for DTS in earlier versions of SQL Server and is also utilized by the new Maintenance Plan Wizard.

In SQL Server Management Studio, you can view all packages on a server and see which packages are currently running. You can also Import and Export packages using this tool (which is something that was not easy to do in earlier versions of SQL Server), and run packages from within the SQL Server Management Studio. To access SSIS through SQL Server Management Studio, follow these steps:
1. Register a server through the Integration Services tab in Registered Servers.
2. Right-click the Server and select Connect | Object Explorer.
3. Manage your packages through the tree that appears in the Object Explorer.

10. Keep tabs on your reports
As with Analysis Services and SSIS, you can use SQL Server Management Studio to manage your Reporting Services. In the SQL Server 2000 version of Reporting Services, all administration was carried out through a Web-based administrator that was installed as part of Reporting Services. If you managed several report servers, you had to manage each one through separate admin sites.

In SQL Server Management Studio, you can register all of your Reporting Services and administer them through the Object Explorer. You can also perform all tasks that were available on the Reporting Services admin site through the Object Explorer.

To access Reporting Services through SQL Server Management Studio, follow these steps:
1. Register a server through the Reporting Services tab in Registered Servers.
2. Right-click the Server and select Connect | Object Explorer.
3. Manage your packages through the tree that appears in the Object Explorer.

Conclusion
SQL Server 2005 Management Studio is a phenomenal advance over the tools provided with SQL Server 2000. We have gone so far as to remove the SQL Server 2000 tools, and we now do everything in SQL Server 2005. This version of SQL Server provides great leaps forward plus backward compatibility, which in our book is a winning combination.

Additional T-SQL operations in SQL Server 2008

Scalar Operators

Scalar operators are used for operations with scalar values. Transact-SQL supports numeric and Boolean operators as well as concatenation.

There are unary and binary arithmetic operators. Unary operators are + and – (as signs). Binary arithmetic operators are +, –, *, /, and %. (The first four binary operators have their respective mathematical meanings, whereas % is the modulo operator.)

You are reading part 4 from "The basic objects of T-SQL in SQL Server 2008," excerpted from Microsoft SQL Server 2008: A Beginner's Guide, by Dusan Petkovic, copyright 2008, printed with permission from McGraw-Hill Osborne Media.

Boolean operators have two different notations depending on whether they are applied to bit strings or to other data types. The operators NOT, AND, and OR are applied to all data types (except BIT). They are described in detail in Chapter 6.

The bitwise operators for manipulating bit strings are listed here, and Example 4.8 shows how they are used:

• ~ Complement (i.e., NOT)
• & Conjunction of bit strings (i.e., AND)
• | Disjunction of bit strings (i.e., OR)
• ^ Exclusive disjunction (i.e., XOR or Exclusive OR)

Example 4.8
~(1001001) = (0110110)
(11001001) | (10101101) = (11101101)
(11001001) & (10101101) = (10001001)
(11001001) ^ (10101101) = (01100100)

The concatenation operator + can be used to concatenate two character strings or bit strings.

Global Variables

Global variables are special system variables that can be used as if they were scalar constants. Transact-SQL supports many global variables, which have to be preceded by the prefix @@. The following table describes several global variables. (For the complete list of all global variables, see Books Online.)

Variable	Explanation
@@CONNECTIONS	Returns the number of login attempts since starting the system.
@@CPU_BUSY	Returns the total CPU time (in units of milliseconds) used since starting the system.
@@ERROR	Returns the information about the return value of the last executed Transact-SQL statement.
@@IDENTITY	Returns the last inserted value for the column with the IDENTITY property (see Chapter 6).
@@LANGID	Returns the identifier of the language that is currently used by the database system.
@@LANGUAGE	Returns the name of the language that is currently used by the database system.
@@MAX_CONNECTIONS	Returns the maximum number of actual connections to the system.
@@PROCID	Returns the identifier for the stored procedure currently being executed.
@@ROWCOUNT	Returns the number of rows that have been affected by the last Transact-SQL statement executed by the system.
@@SERVERNAME	Retrieves the information concerning the local database server. This information contains, among other things, the name of the server and the name of the instance.
@@SPID	Returns the identifier of the server process.
@@VERSION	Returns the current version of the database system software.

NULL Values

A NULL value is a special value that may be assigned to a column. This value is normally used when information in a column is unknown or not applicable. For example, in the case of an unknown home telephone number for a company's employee, it is recommended that the NULL value be assigned to the home_telephone column.

Any arithmetic expression results in a NULL if any operand of that expression is itself a NULL value. Therefore, in unary arithmetic expressions (if A is an expression with a NULL value), both +A and –A return NULL. In binary expressions, if one (or both) of the operands A or B has the NULL value, A + B, A – B, A * B, A / B, and A % B also result in a NULL. (The operands A and B have to be numerical expressions.)

If an expression contains a relational operation and one (or both) of the operands has (have) the NULL value, the result of this operation will be NULL. Hence, each of the expressions A = B, A <> B, A < B, and A > B also returns NULL.

In the Boolean AND, OR, and NOT, the behavior of the NULL values is specified by the following truth tables, where T stands for true, U for unknown (NULL), and F for false. In these tables, follow the row and column represented by the values of the Boolean expressions that the operator works on, and the value where they intersect represents the resulting value.

AND	T	U	F		OR	T	U	F		NOT
T	T	U	F		T	T	T	T		T	F
U	U	U	F		U	T	U	U		U	U
F	F	F	F		F	T	U	F		F	T

Any NULL value in the argument of aggregate functions AVG, SUM, MAX, MIN, and COUNT is eliminated before the respective function is calculated (except for the function COUNT(*)). If a column contains only NULL values, the function returns NULL. The aggregate function COUNT(*) handles all NULL values the same as non-NULL values. If the column contains only NULL values, the result of the function COUNT(DISTINCT column_name) is 0.

A NULL value has to be different from all other values. For numeric data types, there is a distinction between the value zero and NULL. The same is true for the empty string and NULL for character data types.

A column of a table allows NULL values if its definition explicitly contains NULL. On the other hand, NULL values are not permitted if the definition of a column explicitly contains NOT NULL. If the user does not specify NULL or NOT NULL for a column with a data type (except TIMESTAMP), the following values are assigned:

• NULL - If the ANSI_NULL_DFLT_ON option of the SET statement is set to ON
• NOT NULL - If the ANSI_NULL_DFLT_OFF option of the SET statement is set to ON

If the SET statement isn't activated, a column will contain the value NOT NULL by default. (The columns of TIMESTAMP data type can only be declared as NOT NULL columns.)

There is also another option of the SET statement: CONCAT_NULL_YIELDS_ NULL. This option influences the concatenation operation with a NULL value so that anything you concatenate to a NULL value will yield NULL again. For instance:

'San Francisco' + NULL = NULL

Conclusion

The basic features of Transact-SQL consist of data types, predicates, and functions. Data types comply with data types of the ANSI SQL92 standard. Transact-SQL supports a variety of useful system functions.

The next chapter introduces you to Transact-SQL statements in relation to SQL's data definition language. This part of Transact-SQL comprises all the statements needed for creating, altering, and removing database objects.

Exercises

E.4.1
What is the difference between the numeric data types INT, SMALLINT, and TINYINT?

E.4.2
What is the difference between the data types CHAR and VARCHAR? When should you use the latter (instead of the former) and vice versa?

E.4.3
How can you set the format of a column with the DATE data type so that its values can be entered in the form 'yyyy/mm/dd'?

In the following two exercises, use the SELECT statement in the Query Editor component window of SQL Server Management Studio to display the result of all system functions and global variables. (For instance, SELECT host_id() displays the ID number of the current host.)

E.4.4
Using system functions, find the ID number of the test database (Exercise 2.1).

E.4.5
Using the system variables, display the current version of the database system software and the language that is used by this software.

E.4.6
Using the bitwise operators &, |, and ^, calculate the following operations with the bit strings:
(11100101) & (01010111)
(10011011) | (11001001)
(10110111) ^ (10110001)

E.4.7
What is the result of the following expressions? (A is a numerical and B a logical expression.)
A + NULL
NULL = NULL
B OR NULL
B AND NULL

E.4.8
When can you use both single and double quotation marks to define string and temporal constants?

E.4.9
What is a delimited identifier and when do you need it?

SQL Server 2008 function types in T-SQL

Transact-SQL Functions

Transact-SQL functions can be either aggregate functions or scalar functions. The following sections describe these function types.

Aggregate functions

Aggregate functions are applied to a group of data values from a column. Aggregate functions always return a single value. Transact-SQL supports several groups of aggregate functions:

You are reading part 3 from "The basic objects of T-SQL in SQL Server 2008," excerpted from Microsoft SQL Server 2008: A Beginner's Guide, by Dusan Petkovic, copyright 2008, printed with permission from McGraw-Hill Osborne Media.
• Convenient aggregate functions
• Statistical aggregate functions
• User-defined aggregate functions
• Analytic aggregate functions

Statistical and analytic aggregates are discussed in Chapter 24. User-defined aggregates are beyond the scope of this book. That leaves the convenient aggregate functions, described next:

• AVG - Calculates the arithmetic mean (average) of the data values contained within a column. The column must contain numeric values.
• MAX and MIN - Calculate the maximum and minimum data value of the column, respectively. The column can contain numeric, string, and date/time values.
• SUM - Calculates the total of all data values in a column. The column must contain numeric values.
• COUNT - Calculates the number of (non-null) data values in a column. The only aggregate function not being applied to columns is COUNT(*). This function returns the number of rows (whether or not particular columns have NULL values).
• COUNT_BIG - Analogous to COUNT, the only difference being that COUNT_BIG returns a value of the BIGINT data type.

The use of convenient aggregate functions with the SELECT statement are described in detail in Chapter 6.

Scalar functions

In addition to aggregate functions, Transact-SQL provides several scalar functions that are used in the construction of scalar expressions. (A scalar function operates on a single value or list of values, as opposed to aggregate functions, which operate on the data from multiple rows.) Scalar functions can be categorized as follows:

• Numeric functions
• Date functions
• String functions
• System functions
• Metadata functions

The following sections describe these function types.

Numeric functions

Numeric functions within Transact-SQL are mathematical functions for modifying numeric values. The following numeric functions are available:

Function	Explanation
ABS(n)	Returns the absolute value (i.e., negative values are returned as positive) of the numeric expression n. Example: SELECT ABS(–5.767) = 5.767, SELECT ABS(6.384) = 6.384
ACOS(n)	Calculates arc cosine of n. n and the resulting value belong to the FLOAT data type.
ASIN(n)	Calculates the arc sine of n. n and the resulting value belong to the FLOAT data type.
ATAN(n)	Calculates the arc tangent of n. n and the resulting value belong to the FLOAT data type.
ATN2(n,m)	Calculates the arc tangent of n/m. n, m, and the resulting value belong to the FLOAT data type.
CEILING(n)	Returns the smallest integer value greater or equal to the specified parameter. Examples: SELECT CEILING(4.88) = 5 SELECT CEILING(–4.88) = –4
COS(n)	Calculates the cosine of n. n and the resulting value belong to the FLOAT data type.
COT(n)	Calculates the cotangent of n. n and the resulting value belong to the FLOAT data type.
DEGREES(n)	Converts radians to degrees. Examples: SELECT DEGREES(PI()/2) = 90.0 SELECT DEGREES(0.75) = 42.97
EXP(n)	Calculates the value e^n. Example: SELECT EXP(1) = 2.7183
FLOOR(n)	Calculates the largest integer value less than or equal to the specified value n. Example: SELECT FLOOR(4.88) = 4
LOG(n)	Calculates the natural (i.e., base e) logarithm of n. Examples: SELECT LOG(4.67) = 1.54 SELECT LOG(0.12) = –2.12
LOG10(n)	Calculates the logarithm (base 10) for n. Examples: SELECT LOG10(4.67) = 0.67 SELECT LOG10(0.12) = –0.92
PI()	Returns the value of the number pi (3.14).
POWER(x,y)	Calculates the value x^y. Examples: SELECT POWER(3.12,5) = 295.65 SELECT POWER(81,0.5) = 9
RADIANS(n)	Converts degrees to radians. Examples: SELECT RADIANS(90.0) = 1.57 SELECT RADIANS(42.97) = 0.75
RAND	Returns a random number between 0 and 1 with a FLOAT data type.
ROUND(n, p,[t])	Rounds the value of the number n by using the precision p. Use positive values of p to round on the right side of the decimal point and use negative values to round on the left side. An optional parameter t causes n to be truncated. Examples: SELECT ROUND(5.4567,3) = 5.4570 SELECT ROUND(345.4567,–1) = 350.0000 SELECT ROUND(345.4567,–1,1) = 340.0000
ROWCOUNT_BIG	Returns the number of rows that have been affected by the last Transact-SQL statement executed by the system. The return value of this function has the BIGINT data type.
SIGN(n)	Returns the sign of the value n as a number (+1 for positive, –1 for negative, and 0 for zero). Example: SELECT SIGN(0.88) = 1
SIN(n)	Calculates the sine of n. n and the resulting value belong to the FLOAT data type.
SQRT(n)	Calculates the square root of n. Example: SELECT SQRT(9) = 3
SQUARE(n)	Returns the square of the given expression. Example: SELECT SQUARE(9) = 81
TAN(n)	Calculates the tangent of n. n and the resulting value belong to the FLOAT data type.

Date Functions

Date functions calculate the respective date or time portion of an expression or return the value from a time interval. Transact-SQL supports the following date functions:

Function	Explanation
GETDATE()	Returns the current system date and time. Example: SELECT GETDATE() = 2008-01-01 13:03:31.390
DATEPART(item,date)	Returns the specified part item of a date date as an integer. Examples: SELECT DATEPART(month, '01.01.2005') = 1 (1 = January) SELECT DATEPART(weekday, '01.01.2005') = 7 (7 = Sunday)
DATENAME(item,date)	Returns the specified part item of the date date as a character string. Example: SELECT DATENAME(weekday, '01.01.2005') = Saturday
DATEDIFF(item,dat1,dat2)	Calculates the difference between the two date parts dat1 and dat2 and returns the result as an integer in units specified by the value item. Example: SELECT DATEDIFF(year, BirthDate, GETDATE()) AS age FROM employee; -> returns the age of each employee.
DATEADD(i,n,d)	Adds the number n of units specified by the value i to the given date d. Example: SELECT DATEADD(DAY,3,HireDate) AS age FROM employee; -> adds three days to the starting date of employment of every employee (see the sample database).

String Functions

String functions are used to manipulate data values in a column, usually of a character data type. Transact-SQL supports the following string functions:

Function	Explanation
ASCII(character)	Converts the specified character to the equivalent integer (ASCII) code. Returns an integer. Example: SELECT ASCII('A') = 65
CHAR(integer)	Converts the ASCII code to the equivalent character. Example: SELECT CHAR(65) = 'A'.
CHARINDEX(z1,z2)	Returns the starting position where the partial string z1 first occurs in the string z2. Returns 0 if z1 does not occur in z2. Example: SELECT CHARINDEX('bl', 'table') = 3.
DIFFERENCE(z1,z2)	Returns an integer, 0 through 4, that is the difference of SOUNDEX values of two strings z1 and z2. (SOUNDEX returns a number that specifies the sound of a string. With this method, strings with similar sounds can be determined.) Example: SELECT DIFFERENCE('spelling', 'telling') = 2 (sounds a little bit similar, 0 = doesn't sound similar)
LEFT(z, length)	Returns the first length characters from the string z.
LEN(z)	Returns the number of characters, instead of the number of bytes, of the specified string expression, excluding trailing blanks.
LOWER(z1)	Converts all uppercase letters of the string z1 to lowercase letters. Lowercase letters and numbers, and other characters, do not change. Example: SELECT LOWER('BiG') = 'big'
LTRIM(z)	Removes leading blanks in the string z. Example: SELECT LTRIM(' String') = 'String'
NCHAR(i)	Returns the Unicode character with the specified integer code, as defined by the Unicode standard.
QUOTENAME(char_string)	Returns a Unicode string with the delimiters added to make the input string a valid delimited identifier.
PATINDEX(%p%,expr)	Returns the starting position of the first occurrence of a pattern p in a specified expression expr, or zeros if the pattern is not found. Examples: 1) SELECT PATINDEX('%gs%', 'longstring') = 4; 2) SELECT RIGHT(ContactName, LEN(ContactName)-PATINDEX('% %',ContactName)) AS First_name FROM Customers; (The second query returns all first names from the customers column.)
REPLACE(str1,str2,str3)	Replaces all occurrences of the str2 in the str1 with the str3. Example: SELECT REPLACE('shave' , 's' , 'be') = behave
REPLICATE(z,i)	Repeats string z i times. Example: SELECT REPLICATE('a',10) = 'aaaaaaaaaa'
REVERSE(z)	Displays the string z in the reverse order. Example: SELECT REVERSE('calculate') = 'etaluclac'
RIGHT(z,length)	Returns the last length characters from the string z. Example: SELECT RIGHT('Notebook',4) = 'book'
RTRIM(z)	Removes trailing blanks of the string z. Example: SELECT RTRIM('Notebook ') = 'Notebook'
SOUNDEX(a)	Returns a four-character SOUNDEX code to determine the similarity between two strings. Example: SELECT SOUNDEX('spelling') = S145
SPACE(length)	Returns a string with spaces of length specified by length. Example: SELECT SPACE = ' '
STR(f,[len [,d]])	Converts the specified float expression f into a string. len is the length of the string including decimal point, sign, digits, and spaces (10 by default), and d is the number of digits to the right of the decimal point to be returned. Example: SELECT STR(3.45678,4,2) = '3.46'
STUFF(z1,a,length,z2)	Replaces the partial string z1 with the partial string z2 starting at position a, replacing length characters of z1. Examples: SELECT STUFF('Notebook',5,0, ' in a ') = 'Note in a book' SELECT STUFF('Notebook',1,4, 'Hand') = 'Handbook'
SUBSTRING(z,a,length)	Creates a partial string from string z starting at the position a with a length of length. Example: SELECT SUBSTRING('wardrobe',1,4) = 'ward'
UNICODE	Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
UPPER(z)	Converts all lowercase letters of string z to uppercase letters. Uppercase letters and numbers do not change. Example: SELECT UPPER('loWer') = 'LOWER'

System Functions

System functions of Transact-SQL provide extensive information about database objects. Most system functions use an internal numeric identifier (ID), which is assigned to each database object by the system at its creation. Using this identifier, the system can uniquely identify each database object. System functions provide information about the database system. The following table describes several system functions. (For the complete list of all system functions, please see Books Online.)

Function	Explanation
CAST(a AS type [(length)]	Converts an expression a into the specified data type type (if possible). a could be any valid expression. Example: SELECT CAST(3000000000 AS BIGINT) = 3000000000
COALESCE(a1,a2,…)	Returns for a given list of expressions a1, a2,... the value of the first expression that is not NULL.
COL_LENGTH(obj,col)	Returns the length of the column col belonging to the database object (table or view) obj. Example: SELECT COL_LENGTH('customers', 'cust_ID') = 10
CONVERT(type[(length)],a)	Equivalent to CAST, but the arguments are specified differently. CONVERT can be used with any data type.
CURRENT_TIMESTAMP	Returns the current date and time. Example: SELECT CURRENT_TIMESTAMP = '2008-01-01 17:22:55.670'
CURRENT_USER	Returns the name of the current user.
DATALENGTH(z)	Calculates the length (in bytes) of the result of the expression z. Example: SELECT DATALENGTH(ProductName) FROM products. (This query returns the length of each field.)
GETANSINULL('dbname')	Returns 1 if the use of NULL values in the database dbname complies with the ANSI SQL standard. (See also the explanation of NULL values at the end of this chapter.) Example: SELECT GETANSINULL('AdventureWorks') = 1
ISNULL(expr, value)	Returns the value of expr if that value is not null; otherwise, it returns value (see Example 5.22).
ISNUMERIC(expression)	Determines whether an expression is a valid numeric type.
NEWID()	Creates a unique ID number that consists of a 16-byte binary string intended to store values of the UNIQUEIDENTIFIER data type.
NEWSEQUENTIALID()	Creates a GUID that is greater than any GUID previously generated by this function on a specified computer. (This function can only be used as a default value for a column.)
NULLIF(expr1,expr2)	Returns the NULL value if the expressions expr1 and expr2 are equal. Example: SELECT NULLIF(project_no, 'p1') FROM projects. (The query returns NULL for the project with the project_no = 'p1').
SERVERPROPERTY(propertyname)	Returns the property information about the database server.
SYSTEM_USER	Returns the login ID of the current user. Example: SELECT SYSTEM_USER = LTB13942dusan
USER_ID([user_name])	Returns the identifier of the user user_name. If no name is specified, the identifier of the current user is retrieved. Example: SELECT USER_ID('guest') = 2
USER_NAME([id])	Returns the name of the user with the identifier id. If no name is specified, the name of the current user is retrieved. Example: SELECT USER_NAME = 'guest'

All string functions can be nested in any order; for example, REVERSE(CURRENT_USER).

Metadata Functions

Generally, metadata functions return information concerning the specified database and database objects. The following table describes several metadata functions. (For the complete list of all metadata functions, please see Books Online.)

Function	Explanation
COL_NAME(tab_id, col_id)	Returns the name of a column belonging to the table with the ID tab_id and column ID col_id. Example: SELECT COL_NAME(OBJECT_ID('employee') , 3) = 'emp_lname'
COLUMNPROPERTY(id, col, property)	Returns the information about the specified column. Example: SELECT COLUMNPROPERTY(object_id('project'), 'project_no', 'PRECISION') = 4
DATABASEPROPERTY(database, property)	Returns the named database property value for the specified database and property. Example: SELECT DATABASEPROPERTY('sample', 'IsNullConcat') = 0. (The IsNullConcat property corresponds to the option CONCAT_NULL_YIELDS_NULL, which is described at the end of this chapter.)
DB_ID([db_name])	Returns the identifier of the database db_name. If no name is specified, the identifier of the current database is returned. Example: SELECT DB_ID('AdventureWorks') = 6
DB_NAME([db_id])	Returns the name of the database with the identifier db_id. If no identifier is specified, the name of the current database is displayed. Example: SELECT DB_NAME(6) = 'AdventureWorks'
INDEX_COL(table, i, no)	Returns the name of the indexed column in the table table, defined by the index identifier i and the position no of the column in the index.
INDEXPROPERTY(obj_id, index_name, property)	Returns the named index or statistics property value of a specified table identification number, index or statistics name, and property name.
OBJECT_NAME(obj_id)	Returns the name of the database object with the identifier obj_id. Example: SELECT OBJECT_NAME(453576654) = 'products'
OBJECT_ID(obj_name)	Returns the identifier of the database object obj_name. Example: SELECT OBJECT_ID('products') = 453576654
OBJECTPROPERTY(obj_id,property)	Returns the information about the objects from the current database.

Using T-SQL data types in SQL Server 2008

Data Types

All the data values of a column must be of the same data type. (The only exception specifies the values of the SQL_VARIANT data type.) Transact-SQL uses different data types, which can be categorized as follows:

• Numeric data types
• Character data types
• Temporal (date and/or time) data types
• Miscellaneous data types
• DECIMAL with VARDECIMAL storage type

The following sections describe all these categories.

Numeric Data Types

Numeric data types are used to represent numbers. The following table shows the list of all numeric data types:

Data Type	Explanation
INTEGER	Represents integer values that can be stored in 4 bytes. The range of values is –2,147,483,648 to 2,147,483,647. INT is the short form for INTEGER.
SMALLINT	Represents integer values that can be stored in 2 bytes. The range of values is –32768 to 32767.
TINYINT	Represents nonnegative integer values that can be stored in 1 byte. The range of values is 0 to 255.
BIGINT	Represents integer values that can be stored in 8 bytes. The range of values is –2^63 to 2^63 – 1.
DECIMAL(p,[s])	Describes fixed-point values. The argument p (precision) specifies the total number of digits with assumed decimal point s (scale) digits from the right. DECIMAL values are stored, depending on the value of p, in 5 to 17 bytes. DEC is the short form for DECIMAL.
NUMERIC(p,[s])	Synonym for DECIMAL.
REAL	Used for floating-point values. The range of positive values is approximately 2.23E – 308 to 1.79E +308, and the range of negative values is approximately –1.18E – 38 to –1.18E + 38 (the value zero can also be stored).
FLOAT[(p)]	Represents floating-point values, like REAL. p defines the precision with p < 25 as single precision (4 byte) and p >= 25 as double precision (8 byte).
MONEY	Used for representing monetary values. MONEY values correspond to 8-byte DECIMAL values and are rounded to four digits after the decimal point.
SMALLMONEY	Corresponds to the data type MONEY but is stored in 4 bytes.

Character Data Types

There are two general forms of character data types. They can be strings of single-byte characters or strings of Unicode characters. (Unicode uses several bytes to specify one character.) Further, strings can have fixed or variable length. The following character data types are used:

Data Type	Explanation
CHAR[(n)]	Represents a fixed-length string of single-byte characters, where n is the number of characters inside the string. The maximum value of n is 8000. CHARACTER(n) is an additional equivalent form for CHAR(n). If n is omitted, the length of the string is assumed to be 1.
VARCHAR[(n)]	Describes a variable-length string of single-byte characters (0 < n ≤ 8000). In contrast to the CHAR data type, the values for the VARCHAR data type are stored in their actual length. This data type has two synonyms: CHAR VARYING and CHARACTER VARYING.
NCHAR[(n)]	Stores fixed-length strings of Unicode characters. The main difference between the CHAR and NCHAR data types is that each character of the NCHAR data type is stored in 2 bytes, while each character of the CHAR data type uses 1 byte of storage space. Therefore, the maximum number of characters in a column of the NCHAR data type is 4000.
NVARCHAR[(n)]	Stores variable-length strings of Unicode characters. The main difference between the VARCHAR and the NVARCHAR data types is that each NVARCHAR character is stored in 2 bytes, while each VARCHAR character uses 1 byte of storage space. The maximum number of characters in a column of the NVARCHAR data type is 4000.

 

Note: The VARCHAR data type is identical to the CHAR data type except for one difference: if the content of a CHAR(n) string is shorter than n characters, the rest of the string is padded with blanks. (A value of the VARCHAR data type is always stored in its actual length.)

Temporal Data Types

Transact-SQL supports the following temporal data types:

• DATETIME
• SMALLDATETIME
• DATE
• TIME
• DATETIME2
• DATETIMEOFFSET

The DATETIME and SMALLDATETIME data types specify a date and time, with each value being stored as an integer value in 4 bytes or 2 bytes, respectively. Values of DATETIME and SMALLDATETIME are stored internally as two separate numeric values. The date value of DATETIME is stored in the range 01/01/1753 to 12/31/9999. The analog value of SMALLDATETIME is stored in the range 01/01/1900 to 06/06/2079. The time component is stored in the second 4-byte (or 2-byte for SMALLDATETIME) field as the number of three-hundredths of a second (DATETIME) or minutes (SMALLDATETIME) that have passed since midnight.

The use of DATETIME and SMALLDATETIME is rather inconvenient if you want to store only the date part or time part. For this reason, SQL Server 2008 introduces the new data types DATE and TIME, which store just the DATE or TIME component of a DATETIME, respectively. The DATE data type is stored in 3 bytes and has the range 01/01/0001 to 12/31/9999. The TIME data type is stored in 3–5 bytes and has an accuracy of 100 nanoseconds (ns).

The DATETIME2 data type is also a new data type that stores high-precision date and time data. The data type can be defined for variable lengths depending on the requirement. (The storage size is 6–8 bytes). The accuracy of the time part is 100 ns. This data type isn't aware of Daylight Saving Time.

All the temporal data types described thus far don't have support for the time zone. The new data type called DATETIMEOFFSET has the time zone offset portion. For this reason, it is stored in 6–8 bytes. (All other properties of this data type are analogous to the corresponding properties of DATETIME2.)

The date value in Transact-SQL is by default specified as a string in a format like 'mmm dd yyyy' (e.g., 'Jan 10 1993') inside two single quotation marks or double quotation marks. (Note that the relative order of month, day, and year can be controlled by the SET DATEFORMAT statement. Additionally, the system recognizes numeric month values with delimiters of / or –.) Similarly, the time value is specified in the format 'hh:mm' and Database Engine uses 24-hour time (23:24, for instance).

 

Note: Transact-SQL supports a variety of input formats for DATETIME values. As you already know, both objects are identified separately; thus, date and time values can be specified in any order or alone. If one of the values is omitted, the system uses the default values. (The default value for time is 12:00 AM.)

Examples 4.4 and 4.5 show the different ways, how date or time values can be written using the different formats.

Example 4.4
The following date descriptions can be used:

'28/5/1959' (with SET DATEFORMAT dmy)
'May 28, 1959'
'1959 MAY 28'

Example 4.5
The following time descriptions can be used:

'8:45 AM'
'4 pm'

Miscellaneous Data Types

Transact-SQL supports several data types that do not belong to any of the data type groups described previously:

• Binary data types
• BIT
• Large object data types
• CURSOR (discussed in Chapter 8)
• UNIQUEIDENTIFIER
• SQL_VARIANT
• TABLE (discussed in Chapter 8)
• XML (discussed in Chapter 28)
• Spatial (e.g., GEOGRAPHY and GEOMETRY) data types (discussed in Chapter 29 )
• HIERARCHYID
• TIMESTAMP data type
• User-defined data types (discussed in Chapter 5)

The following sections describe each of these data types (other than those designated as being discussed in another chapter).

Binary and BIT Data Types

BINARY and VARBINARY are the two binary data types. They describe data objects being represented in the internal format of the system. They are used to store bit strings. For this reason, the values are entered using hexadecimal numbers.

The values of the BIT data type are stored in a single bit. Therefore, up to 8 bit columns are stored in 1 byte. The following table summarizes the properties of these data types:

Data Type	Explanation
BINARY[(n)]	Specifies a bit string of fixed length with exactly n bytes (0 < n ≤ 8000).
VARBINARY[(n)]	Specifies a bit string of variable length with up to n bytes (0 < n ≤ 8000).
BIT	Used for specifying the Boolean data type with three possible values: FALSE, TRUE, and NULL.

Large Object Data Types

Large objects (LOBs) are data objects with the maximum length of 2GB. These objects are generally used to store large text data and to load modules and audio/video files. Transact-SQL supports two different ways to specify and access LOBs:

• Use the data types VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX)
• Use the so-called text/image data type

The following subsections describe the two forms of LOBs.

The MAX Specifier Starting with SQL Server 2005, you can use the same programming model to access values of standard data types and LOBs. In other words, you can use convenient system functions and string operators to work with LOBs.

Database Engine uses the MAX specifier with the data types VARCHAR, NVARCHAR, and VARBINARY to define variable-length columns. When you use MAX by default (instead of an explicit value), the system analyzes the length of the particular string and decides whether to store the string as a convenient value or as a LOB. The MAX specifier indicates that the size of column values can reach the maximum LOB size of the current system. (In a future version of SQL Server, it is possible that MAX will have a higher maximum value for strings.)

Although the database system decides how a LOB will be stored, you can override this default specification using the sp_tableoption system procedure with the LARGE_ VALUE_TYPES_OUT_OF_ROW option. If the option's value is set to 1, the data in columns declared using the MAX specifier will be stored separately from all other data. If this option is set to 0, Database Engine stores all values for the row size < 8060 bytes as regular row data.

In SQL Server 2008, you can apply the new FILESTREAM attribute to a VARBINARY(MAX) column to store large binary data directly in an NTFS file system. The main advantage of this attribute is that the size of the corresponding LOB is limited only by the file system volume size.

TEXT, NTEXT, and IMAGE Data Types The data types TEXT, NTEXT, and IMAGE constitute the so-called text/image data types. Data objects of the type IMAGE can contain any kind of data (load modules, audio/video), while data objects of the data types TEXT and NTEXT can contain any text data (that is, printable data).

The text/image data types are stored by default separately from all other values of a database using a B-tree structure that points to the fragments of that data. (A B-tree structure is a treelike data structure in which all of the bottom nodes are the same number of levels away from the root of the tree.) For columns of a text /image data type, Database Engine stores a 16-byte pointer in the data row that specifies where the data can be found.

If the amount of text/image data is less than 32KB, the pointer points to the root node of the B-tree structure, which is 84 bytes long. The root node points to the physical blocks of the data. If the amount of the data is greater than 32KB, Database Engine builds intermediate nodes between the data blocks and the root node.

For each table that contains more than one column with such data, all values of the columns are stored together. However, one physical page can hold only text/image data from a single table.

Although text/image data is stored separately from all other data, you can modify this using the sp_tableoption system procedure with the TEXT_IN_ROW option. Using this option, you can specify the maximum number of bytes, which are stored together with the regular data.

The text/image data types discussed this far have several limitations. You can't use them as local variables (in stored procedures or in groups of Transact-SQL statements). Also, they can't be a part of an index or can't be used in the following clauses of the SELECT statement: WHERE, ORDER BY, and GROUP BY. The biggest problem concerning all text/image data types is that you have to use special operators (READTEXT, WRITETEXT, and UPDATETEXT) to work with such data.

 

Note: The text/image data types are marked as a deprecated feature and will be removed in a future version of Database Engine. Use VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) instead.

UNIQUEIDENTIFIER Data Type

As its name implies, a value of the UNIQUEIDENTIFIER data type is a unique identification number stored as a 16-byte binary string. This data type is closely related to the globally unique identifier (GUID), which guarantees uniqueness worldwide. Hence, using this data type, you can uniquely identify data and objects in distributed systems.

The initialization of a column or a variable of the UNIQUEIDENTIFIER type can be provided using the functions NEWID and NEWSEQUENTIALID, as well as with a string constant written in a special form using hexadecimal digits and hyphens. (The functions NEWID and NEWSEQUENTIALID are described in the section "System Functions" later in this chapter.)

A column of the UNIQUEIDENTIFIER data type can be referenced using the keyword ROWGUIDCOL in a query to specify that the column contains ID values. (This keyword does not generate any values.) A table can have several columns of the UNIQUEIDENTIFIER type, but only one of them can have the ROWGUIDCOL keyword.

SQL_VARIANT Data Type

The SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values. (The only types of values that cannot be stored are TIMESTAMP values.) Each value of an SQL_VARIANT column has two parts: the data value and the information that describes the value. (This information contains all properties of the actual data type of the value, such as length, scale, and precision.)

Transact-SQL supports the SQL_VARIANT_PROPERTY function, which displays the attached information for each value of an SQL_VARIANT column. For the use of the SQL_VARIANT data type, see Example 5.5 in Chapter 5.

 

Note: Declare a column of a table using the SQL_VARIANT data type only if it is really necessary. A column should have this data type if its values may be of different types or if it is not possible to determine the type of a column during the database design process.

HIERARCHYID Data Type

The HIERARCHYID data type is used to store an entire hierarchy. It is implemented as a Common Language Runtime (CLR) user-defined type that comprises several system functions for creating and operating on hierarchy nodes. The following functions, among others, belong to the methods of this data type: GetAncestor(), GetDescendant(), Read(), and Write(). (The detailed description of this data type is outside the scope of this book.)

TIMESTAMP Data Type

The TIMESTAMP data type specifies a column being defined as VARBINARY(8) or BINARY(8), depending on nullability of the column. The system maintains a current value (not a date or time) for each database, which it increments whenever any row with a TIMESTAMP column is inserted or updated and sets the TIMESTAMP column to that value. Thus, TIMESTAMP columns can be used to determine the relative time when rows were last changed. (ROWVERSION is a synonym for TIMESTAMP.)

 

Note: The value stored in a TIMESTAMP column isn't important by itself. This column is usually used to detect whether a specific row has been changed since the last time it was accessed.

DECIMAL with VARDECIMAL Storage Format

The DECIMAL data type is typically stored on the disk as fixed-length data. Since SQL Server 2005 SP2, this data type can be stored as a variable-length column by using the new storage format called VARDECIMAL. Using VARDECIMAL, you can significantly reduce the storage space for a DECIMAL column in which values have significant difference in their lengths.

 

Note: VARDECIMAL is a storage format and not a data type.

The VARDECIMAL storage format is helpful when you have to specify the largest possible value for a field in which the stored values usually are much smaller. Table 4-1 shows this.

 

Note: The VARDECIMAL storage format works the same way for the DECIMAL data type as the VARCHAR data type works for alphanumerical data.

Precision	No. of Bytes: VARDECIMAL	No. of Bytes: Fixed Length
0 or NULL	2	5
1	4	5
20	12	13
30	16	17
38	20	17

Table 4–1 Number of Bytes for Storing VARDECIMAL and Fixed Length

To enable the VARDECIMAL storage format, you first have to enable it for the database and then enable it for the particular table of that database. The sp_db_ vardecimal_storage_format system procedure is used for the first step, as Example 4.6 shows.

Example 4.6
EXEC sp_db_vardecimal_storage_format 'sample', 'ON';

The VARDECIMAL STORAGE FORMAT option of the sp_table option system procedure is used to turn on this storage for the table. Example 4.7 turns on the VARDECIMAL storage format for the project table.

Example 4.7
EXEC sp_tableoption 'project', 'vardecimal storage format', 1

As you already know, the main reason to use the VARDECIMAL storage format is to reduce the storage size of the data. If you want to test how much storage space could be gained by using this storage format, use the dynamic management view called sys.sp_estimated_rowsize_reduction_for_vardecimal. This dynamic management view gives you a detailed estimate for the particular table.