Datasheet

SELECT login_name, COUNT(session_id) as NumberSessions

FROM sys.dm_exec_sessions GROUP BY login_name

In fact, DMVs are also sometimes functions and accept parameters. For example, the following code uses

the

sys.dm_io_virtual_file_stats dynamic management function (we use the term DMV for sim-

plicity throughout this book) to retrieve the I/O statistics for the AdventureWorks data file.

SELECT * FROM

sys.dm_io_virtual_file_stats(DB_ID(‘AdventureWorks’),

FILE_ID(‘AdventureWorks_Data’))

We cover much more about DMVs throughout this book, starting in Chapter 4.

SQL Server 2005 Data Types

As you create a table, you must assign a data type for each column. In this section, we cover some of the

more commonly used data types in SQL Server. Even if you create a custom data type, it must comply with

the standard SQL Server data types in some way. For example, you may have created a custom data type

(

Address) by using the following syntax, but notice that it still has to fit inside the varchar data type.

CREATE TYPE Address

FROM varchar(35) NOT NULL

If you are changing the data type of a column in a very large table in SQL Server Management Studio’s

table designer interface, the operation may take a very long time. The reason for this can be observed by

scripting the change from the Management Studio interface. Management Studio creates a secondary

temporary table that has a name like

tmpTableName and then copies the data into the table. Finally, the

interface deletes the old table and renames the new table with the new data type. There are other steps

along the way, of course, to handle indexes and any relationships in the table.

If you have a very large table with millions of records, this process can take more than ten minutes and

in some cases more than hour. To avoid this, you can use a simple one-line T-SQL statement in the query

window to change the column’s data type. For example, to change the data type of the

Title column in

the

Employees table to a varchar(70), you could use the following syntax.

ALTER TABLE HumanResources.Employee ALTER COLUMN Title Varchar(70)

When you convert to a data type that may be incompatible with your data, you may lose important

data. For example, if you convert from a numeric data type that has data such as 15.415 to an integer,

the number 15.415 would be rounded to a whole number.

Oftentimes, you may wish to write a report against your SQL Server tables to output the data type of

each column inside the table. There are dozens of ways to do this, but one method we often see is to join

the

sys.objects table with the sys.columns table. There are two functions that you may not be famil-

iar with in the following code. The

type_name() function translates the data type id into its proper

name. To go the opposite direction, you could use the

type_id() function. The other function of note is

schema_id(), which is used to return the identity value for the schema. This is mainly useful when you

wish to write reports against the SQL Server metadata.

SQL Server 2005 Architecture

04_055200 ch01.qxp 10/31/06 12:37 PM Page 9