Variable Declaration and Assignment

A long time ago, I learned that T-SQL was not like other programming languages. For a long
time, I accepted that when I declared a variable, all I could do was to declare it. Sure, stored
procedure and user-defi ned function parameters could be declared and set to a default, but
that simply didn’t happen for inline variable declarations in T-SQL. That is, until now.
Indeed, it is true. You can now declare and assign a variable in one statement. So instead of
writing this code:
DECLARE
@someIntVal INT,
@someStringVal varchar(100)
SET @someIntVal = 1
SET @someStringVal = ‘One’
You can now write this code:
DECLARE
@someIntVal INT = 1,
@someStringVal varchar(100) = ‘One’
I know it seems like such a simple change, but believe me, it adds up over time. For example,
look at this set of variable declarations and assignments from the aspnet_Membership_
CreateUser stored procedure:
DECLARE
@someIntVal INT,
@someStringVal varchar(100)
SET @someIntVal = 1
SET @someStringVal = ‘One’
DECLARE
@someIntVal INT = 1,
@someStringVal varchar(100) = ‘

Spatial Indexing in sql server

Spatial Indexing
Now that you have these two new spatial data types, how can you use them efficiently? Like
the XML data type, spatial types have a special indexing ability that is focused on optimizing
the querying abilities, as well as the storage of said index data.
Spatial indexes use a decomposition methodology that partitions the area being indexed into
a grid. A second layer (or level) contains grid cells from the first layer, each of which is broken
down into another grid. This process continues for a total of four layers, each containing a
more detailed breakdown of the previous layer. By default, each layer uses an 8x8 grid breakdown
as its basis, which means a default spatial index would be decomposed as follows:
Level 1 would be an 8x8 grid, or 64 cells.
Level 2 would have an 8x8 grid for each of the 64 cells in level 1, or 4,096 cells.
Level 3 would have an 8x8 grid for each of the 4,096 cells in level 2, or 262,144 cells.
Level 4 would have an 8x8 grid for each of the 262,144 cells in level 3, or 16,777,216
cells.
When creating the index, you can override the density of the grid at each level. There are
three levels of density available to use:
LOW Uses a 4x4 grid for a total of 16 cells
MEDIUM Uses an 8x8 grid, or 64 cells
HIGH Uses a 16x16 grid, or 256 cells
Once the index decomposes the space into the four levels, it then begins to tessellate each
spatial object on a row-by-row basis. Tessellation is the process that fits the object into the
grid levels, starting at level 1, and working down until the Cells-Per-Object rule states that it
should stop processing the object. You can use the spatial index’s CELLS_PER_OBJECT option
to any integer value from 1 to 8,192.
The default for the CELLS_PER_OBJECT option is 16; this default value generally provides a
good balance between the index precision and the size of the index. As you increase this
number, the precision increases but so does the size of the index, and a large index can adversely
affect performance.
More Info The tessellation process for both

Policy In SQL server 2008

Policy Management in SQL Server 2008
Yes, it’s true. SQL Server 2008 introduces a new feature known as the Policy-Based
Management. This framework allows you to define policies on a variety of objects and then
either manually or automatically prevent changes based on said policies. Management is also
very simple using SQL Server Management Studio (preferred), or you can write your own
code to manage policies. But I am getting ahead of myself. Let’s start at the beginning.
This management framework allows you to easily and proactively manage a variety of policies,
ranging from security to metadata. It does this in two ways:
It allows you to monitor changes to these policies, with options to manually check policies,
check policies on schedule, check policies on change and log violations, or check
policies on change and prevent the change if the policy is violated.
It allows you to manage one or more SQL Server instances on a single server or across
multiple servers.
Rather than waiting for something to go awry, you can set policies based on your server
specifications and then have the framework proactively prevent changes based on these
policies or inform you via policy logs when these policies are being violated. The ability to
prevent certain changes depends on the type of feature, or facet, for which you are creating
a policy. For example, if you want to ensure that xp_cmdshell is never turned on for any sever
you are managing, you can create a policy and have it inform you when a change occurs or
even have it check for changes on a schedule, but you cannot prevent it from being changed.
The ability to prevent changes varies from facet to facet.

Spatial in the World

Spatial in the World
All of the examples shown so far in this section have been for the GEOMETRY data type,
so I thought a good way to round out this chapter is to see some practical use of the
GEOGRAPHY data type. I have loaded several sets of GEOGRAPHY data into a database
called NewYorkCity, which contains the spatial data and related metadata for the roads and
an abridged set of key landmarks and locations in Manhattan. In this particular set of data,
roads, landmarks, and locations are represented as one or more shapes, so, for example,
Columbia University is actually spread over two rows of data, as shown by this query.
SELECT LandmarkID, Landmark.ToString() AS LandmarkText, Name
FROM dbo.LANDMARK
WHERE NAME = ‘Columbia University’
This SELECT statement returns the following data.
LandmarkID LandmarkText Name
36 POLYGON
((-73.960162 40.808155,
-73.961079 40.806824,
-73.963956 40.808001999999995,
-73.962054999999992 40.810631,
-73.959223 40.809402999999996,
-73.95966 40.808794999999996,
-73.960162 40.808155))
Columbia University
352 POLYGON
((-73.961079 40.806824,
-73.961603 40.806166999999995,
-73.96204 40.805558999999995,
-73.964894 40.806754,
-73.964392 40.807410999999995,
-73.963956 40.808001999999995,
-73.961079 40.806824))
Columbia University
So what can we do with this data? First, let’s see how big the university actually is.
SELECT SUM(Landmark.STArea()) AS LandmarkArea
FROM dbo.LANDMARK
WHERE NAME = ‘Columbia University’
SELECT LandmarkID, Landmark.ToString() AS LandmarkText, Name
FROM dbo.LANDMARK
WHERE NAME = ‘Columbia University’
LandmarkID LandmarkText Name
SELECT SUM(Landmark.STArea()) AS LandmarkArea
FROM dbo.LANDMARK
WHERE NAME = ‘Columbia University’

spatial data

Spatial Data Types
I was recently involved in a project involving law enforcement (this is not a euphemism for
being arrested), and, as part of the data architecture, we realized that it would be great if we
could identify certain types of information on a map of the area in question—items such as
locations of booted vehicles, sex offenders, or any number of other items.
Perhaps I’m working on a real estate system and want to be able to show in what school
district or congressional district a house is located. Or maybe I want to be able to fi nd retail
stores in relation to a house, or maybe the nearest airports.
And so, may I introduce to you the new spatial data types of SQL Server 2008. The
GEOGRAPHY data type is used to represent geodetic spatial data, items on the oblate spheroid,
or slightly fl attened sphere that is our planet Earth. The GEOMETRY data type represents
planar spatial data, or items on fl at surfaces. Both of these are implemented as system CLR
types, so, like HIERARCHYID, they do not require you to enable CLR in order to use it.
Note This topic could fi ll a sizable book on its own, so bear in mind that we are only scratching
the surface of spatial data in this introductory-level book.
DECLARE @TransactionToken varbinary(max);
SET @TransactionToken = GET_FILESTREAM_TRANSACTION_CONTEXT ();
--At this point, you would return these values to the client
SELECT @PathName, @TransactionToken ;
/*
4: Call the OpenSqlFilestream API to obtain a Win32 handle
by passing in the path and transaction token from above. Next
using the Win32 hanlde you got from OpenSqlFilestream,
you can call the various Win32 API functions such as ReadFile(),
WriteFile(), TransitFile(), and so on. Of course, be sure to
call CloseHandle() when you are done working with the file.
*/
--5: You must then COMMIT or ROLLBACK the transaction.
COMMIT TRANSACTION;
Chapter 3 Type System 105
Types of Spatial Data
As mentioned previously, there are two data types in SQL Server 2008 that can represent
spatial data, GEOMETRY and GEOGRAPHY. There are a total of 11 different spatial data objects,
although only 7 of them are instantiable: Point, LineString, Polygon, GeometryCollection,
MultiPoint, MultiLine, and MultiPolygon. Figure 3-6 shows the entire hierarchy of all 11 (the
instantiable ones are shaded dark gray).
Polygon
LineString
MultiSurface
MultiCurve
MultiPoint
MultiPolygon
MultiLineString
Curve
Surface
GeometryCollection
Point
Geometry
FIGURE 3-6 Spatial data type hierarchy
Working with the Spatial Data Types
To best show how these various instant types work, I am going to use the following shapes
(numbered 1 through 8):
1. Polygon: A square with a small hole in the middle.
2. Polygon: A square located within polygon 1.
3. Polygon: A square located in the hole of polygon 1.
4. Polygon: A pentagon that, although is within the outer boundaries of polygon 1, covers
parts of polygon 1 and the hole within polygon 1.
5. Polygon: A square that shares a border with polygon 1.
6. LineString: A line that touches polygon 1.
106 Introducing SQL Server 2008
7. MultiLineString: Two lines, one of which crosses polygons 1 and 5 and the other which
touches polygon 5.
8. MultiPolygon: Four squares, three of which are wholly contained within polygon 1 and
a fourth which lies outside of polygon 1.
Figure

Data and Backup Compression

Data and Backup Compression
SQL Server Enterprise (and thus Developer) edition now allows you to effortlessly implement
data and backup compression.
Data Compression
Data compression can compress table data, nonclustered indexes, and indexed views. Of
course, because a clustered index contains the table data in its leaf level, clustered indexes
are compressed when a table is compressed. There are no changes required to your code
to access data that is in a compressed state because the compression and decompression is
done behind the scenes.
Row Compression
Row compression uses variable length storage to reduce row storage requirements. Not all
data types can be compressed. For example, an INT of 4 bytes can be reduced such that only
the bytes needed for storage are used. So if an INT column has a value of 12,345, then it will
Chapter 2 Performance 47
use only 2 bytes (because a 2 byte integer can hold values from –32,768 to 32,767). The topic
“Row Compression Implementation” in SQL Server Books Online describes all the data types
and if and how much they can be compressed.
Note When compression is implemented, 0 (numeric) and NULL values do not take up any
space.
Page Compression
Page compression is actually a combination of three different compression types: row, prefix,
and dictionary. First a row compression operation is done, in an attempt to reduce the data
type storage.
Note When using page compression on a nonclustered index, the non-leaf nodes of the index
are compressed using only row compression. The leaf nodes use all of the three compressions
(discussed in this section).
Next, a prefix compression operation is performed. Prefix compression looks for patterns in
the start of the data and repeated prefix values in the page header. For example, review the
pieces of data shown in Figure 2-2.
Column 1 Column 2
Data abcdefg abcefg abcd abcdefg xxyyzzz xxyyz
FIGURE 2-2 Data before compression
Note The prefix and dictionary values are actually stored in a compression information (CI)
structure in an area just after the page header.
When prefix compression is applied, different initial patterns within the same column are
moved to the CI structure, and the data is changed to reference the values in the CI structure
(pointers are represented here by the equal border color), as shown in Figure 2-3.
48 Introducing SQL Server 2008
abcd xxyyz
Data 4efg 3efg [empty] 4efg 5zz [empty]
Column 1 Column 2
CIStructure
FIGURE 2-3 Data after prefix compression
A value of [empty] in the data means that data is an exact match and just a pointer to the
prefix value in the CI structure is stored. The value 4efg replaces the prefix of abcd (four characters)
and points to the prefix value in the CI structure. The value 3efg only uses three of the
characters from the prefix in the CI structure.
Now that the prefix compression has been completed, a third compression operation occurs.
Dictionary compression looks for repeated patterns throughout the data and replaces them,
as shown in Figure 2-4.
abcd xxyyz
Data [empty]

Policy-Based Management Objects
Policy-Based Management uses fi ve different objects to manage policies: facets, conditions,
policies, targets, and categories.
Facets
Facets are the base units of this framework. Facets are types of objects, such as a Surface
Area feature, server, logon, database, user, and so on. Each facet has a set of predefi ned
properties against which conditions can be created.
As of the Community Technology Preview 6 (CTP6) release, there are a total of 47 facets, with
a whopping 1,492 total properties. SQL Server Management Studio has a list of these facets
under the Facets node in Objects Explorer (found under Management, Policy Management).
Alas, if you want to see each list of properties, you need to open each facet’s properties individually.
If you want a quick list of all facets and properties, however, you can use the SQL
Server Management Objects (SMO) to iterate through all available facets and properties, as
shown here:
FacetInfoCollection fic = PolicyStore.Facets;
IEnumerable fic_sorted = from fic_i in fic
orderby fic_i.DisplayName
select fic_i;
Int32 pcount;
foreach (FacetInfo fi in fic_sorted)
{
Console.WriteLine(“FACET: “ + fi.DisplayName);
IEnumerable fi_sorted = from fi_i in fi.FacetProperties
orderby fi_i.Name
select fi_i;
pcount = 0;
foreach (PropertyInfo pi in fi_sorted)
{
if (pcount++ > 0)
Console.Write(“, “ + pi.Name);
else
Console.Write(pi.Name);
}
Console.WriteLine();
Console.ReadLine();
}
Console.WriteLine(“---End of List---”);
Console.ReadLine();
FacetInfoCollection fic = PolicyStore.Facets;
IEnumerable fic_sorted = from fic_i in fic
orderby fic_i.DisplayName
select fic_i;
Int32 pcount;
foreach (FacetInfo fi in fic_sorted)
{
Console.WriteLine(“FACET: “ + fi.DisplayName);
IEnumerable fi_sorted = from fi_i in fi.FacetProperties
orderby fi_i.Name
select fi_i;
pcount = 0;
foreach (PropertyInfo pi in fi_sorted)
{
if (pcount++ > 0)
Console.Write(“, “ + pi.Name);
else
Console.Write(pi.Name);
}
Console.WriteLine();
Console.ReadLine();
}
Console.WriteLine(“---End of L