Galin Iliev's blog

Software Architecture & Development

T-SQL Number Generator

Here is some more T-SQL fun: number generator Smile I found this code in comments of this blog post and it is so cools so I decided to save it here for reference:

-- use number generation
WITH Numbers3( n ) AS ( SELECT 1 UNION SELECT 0 ),
     Numbers2( n ) AS ( SELECT 1 FROM Numbers3 n1 CROSS JOIN Numbers3 n2 ),
     Numbers1( n ) AS ( SELECT 1 FROM Numbers2 n1 CROSS JOIN Numbers2 n2 ),
     Numbers0( n ) AS ( SELECT 1 FROM Numbers1 n1 CROSS JOIN Numbers1 n2 ),
     NumbersBase ( n ) AS ( SELECT 1 FROM Numbers0 n1 CROSS JOIN Numbers0 n2 ),
     Numbers ( Number ) AS 
         SELECT n
         FROM NumbersBase ) D ( n )
         WHERE n <= 50

T-SQL: Get Date and Hour

I am back in T-SQL fun and had interesting problem to solve – group rows by date and hour but eliminate minutes, seconds and milliseconds. Thus here is a handy function to do that:

DECLARE @Date datetime= getdate();

SELECT DATEADD(ms, -1*DATEPART(ms, @date), -- remove milliseconds
    DATEADD(SECOND, -1*DATEPART(SECOND, @date), -- remove seconds
    DATEADD(MINUTE, -1*DATEPART(MINUTE, @date), -- remove minutes

I hope you know how to create function from this code Smile.

Have fun!

SQL Server 2008 Management Studio – Saving changes is not permitted error.

I’ve been away from database side for a while but these days I am designing one. And I had a strange error while working in SQL Server Management Studio 2008. I made some changes and while trying to same the diagram I was shown this message:

“Saving changes is not permitted. The change you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enable the option Prevent saving changes that require the table to be re-created.”


While message explains the issue well I couldn’t spot this option right away. So here is it: Tools->Options->Designers

Uncheck the box and that’s all..:


Hope this helps.

SQL on Saturday

I am attending a very interesting even called SQLSaturday at the newest MS building complex The Commons. This is a three track event about SQL server and has very interesting topics. Speakers are MVPs form northwest and MSFTs.

This event is held in different locations in USA. It is highly recommended for DB devs and admins who want to learn some tips, trick and get some advises learned hard way.

Maintain Database Indexes

It is wide known that creating index on table column can speed up queries that has this column in it's Where clause. Table indexes are binary trees in most cases and they are stored in pages similar to stored data itself. Over time data changes which cause index changes and it require some sort of maintenance to keep database optimized and running as fast as possible.

Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.*

This can be fixed by either rebuilding index (by dropping existing and create new one) or reorganize it (or defrag it).

Rebuild indexes

Rebuilding indexes can be done by either one of these

I won't cover the details as you can look them up on MSDN. Just my favorite way is like following:



The advantage is this operation is online - meaning you can query table during index rebuild.

Reorganizing indexes

Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.

Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space.*


There are two ways to perform index reorganization:

Again my preferable is this:


This is also online operation.

Note: Although both operation stated above should be online I've applied it on big tables (above 140M records on ~60 GB in two tables) and of course it was pretty I/O intensive which caused some delays in performed queries. Having in mind that default CommandTimeout in .NET Class Library is 30 seconds and application writing at least once per minute creates very challenging DB to maintain. Possible solution would be using MS SQL Server 2008 Resource Governor. Unfortunately the server was MS SQL 2005...

How to detect fragmentation

In order to apply techniques described above fragmentation should be detected. For this comes a new DMV (Dynamic Management View) sys.dm_db_index_physical_stats - that gives us fragmentation in percent (avg_fragmentation_in_percent).  

These are the recommendations depending on returned value in avg_fragmentation_in_percent column:

avg_fragmentation_in_percent value

Corrective statement

> 5% and < = 30%


> 30%



Using sys.dm_db_index_physical_stats could not be very useful when used by itself so I prefer using it together with system tables sys.tables and sys.indexes:

---=== get index fragmentation
SELECT a.index_id, as TableName, as IndexName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL) AS a
INNER JOIN sys.indexes AS i ON a.object_id = i.object_id AND a.index_id = i.index_id
join  sys.tables t on t.object_id=i.object_id
ORDER BY avg_fragmentation_in_percent DESC


The result is like this (executed in AdventureWorks):


 sys.dm_db_index_physical_stats can take time to execute so if you want to view all indexes with the table name this can be used:

select t.object_id, as TableName, as IndexName, i.type_desc as IndexType  
from sys.indexes i 
join  sys.tables t on t.object_id=i.object_id
where i.object_id >1000 
order by t.create_date asc

Which return following result:


And it can be used to generate detailed T-SQL queries for reorganizing indexes one at time:

select  'ALTER INDEX ' + + ' ON ' + + ' REORGANIZE;'
from sys.indexes i 
join  sys.tables t on t.object_id=i.object_id
where i.object_id >100 
order by t.create_date desc




So far we took a look at following

  • Detect index fragmentation
  • Rebuild indexes
  • Reorganize indexes
  • Use T-SQL to generate T-SQL to maintain indexes.

I hope this helps.

* quoted from MSDN article Reorganizing and Rebuilding Indexes.

Rule &quot;Previous releases of Microsoft Visual Studio 2008&quot; failed

I downloaded MS SQL Server 2008 Dev Edition this morning and was eager to install new SQL Server Management Studio with IntelliSense support. Unfortunately I hit a showstopper:


The message says: "Rule "Previous releases of Microsoft Visual Studio 2008" failed", "A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008". (for search engines:) )

I noted that prior starting I was asked to install following:

  • .NET Framework 3.5 SP1 (does this mean it has been released?!)
  • Windows Installer 4.5 - wow! That's new too. I missed that one...

As VS 2008 SP1 is still on beta I don't want to install it on my primary dev box. I am looking for alternative solution... I hope I will find one :)


After some research I found that:

  1. There is no workaround for this yet!
  2. The only good workaround would be to wait for VS 2008 SP1 :). I really don't want to dig into MSI database with Orca.
  3. And of course there are other guys with same issue:
    1. SQL Server 2008 RTM Requires/Installs ...
    2. Visual Studio 2008 SP1 may be required for SQL Server 2008 installations
    3. SQL Server 2008 On The Horizon

MSDN Subscrpition says it cleary :(

SQL Server 2008 RTM Available for Download

English downloads are available now and additional languages will be added on a daily basis. Visual Studio 2008 users will need to download and install Service Pack 1 which will be available here after August 11, 2008.

So we wait...

Update 2: If you ran out of patience you can try one trick:

rename the following registry key:

This was found on Guy Barrette blog. More especially on comment by Daniel. As Daniel says:

No guarantees, but SQL 2008 will install and run.


Maybe it is better to wait :)

Update 3: The wait is over :). VS 2008 SP1 is here!

SQL Server 2008 (aka Katmai) is Released

The newest version of popular database server from Microsoft is released. The list with new features and improvements is very impresive:

     - Transparent Data Encryption (encryption while data is 'still' on disk, transparent to applications)
     - External Key Management (Consolidation of key management, integration with external products)
     - Data Auditing (1st-class 'AUDIT' objects; DDL support; audit objects, principals, data, etc.; support for multiple logging targets)
     - Pluggable CPU support
     - Enhanced Database Mirroring (compression of mirror streams, enhanced performance, automatic page-level repair for principal/mirror)
     - Data compression (easy to enable/disable online, more efficient data storage (this is NOT traditional data compression))
     - Backup stream compression (server level control or backup statement control, all backup types)
     - Performance data collection (single, common framework for data collection, reporting, and storage/warehousing)
     - Improved Plan Guide support (plan freezing, pull plans directly from plan cache, SSMS integration, etc.)
     - Resource Governor (create pools and groups to govern, define classifications based on built-in functions, segment resource utilization amoung groups)
     - Policy-based management framework (manage via policies vs. scripts, enterprise-wide support, automated monitoring/enforcement, etc.)
     - Integrate with Microsoft System Center
     - Extended Events (high perf lightweight tracing infrastructure, NOT sql trace, integrated with ETW, unprecidented insight into goings-on)
Development Enhancements
     - Improved datetime datatypes (100th nanosecond precision (7 digits past second), time-zone datetime offset, date only, time only)
     - HierarchyID datatype (hierarchical-aware data type, ORDPath values, built-in functions, methods, etc.)
     - Entity Data Model support (develop 'business entities' vs. tables, model complex relationships, retrieve entities vs. rows/columns)
     - LINQ
     - Sql Server Change Tracking (Change Data Capture, get 'diff' data changes WITHOUT a comparible value (i.e. datetime, timestamp, etc.))
     - Table Valued Parameters
     - MERGE statement ('upsert' data, also includes deletion functionality)
     - Large UDT's (no more 8000 byte limit on CLR-based UDTs, no more 8000 byte limit for UDA's)
     - Spatial data (GEOMETRY and GEOGRAPHY data types, built-in spatial function support, spatial indexes)
     - XML enhancements (support for lax validation, office 12 support, xs:dateTime support, lists/union types, LET FLOWR support, etc.)
     - Inline initialization and compound assignment
Service Broker
     - New UI and Tools for working with (add/drop/edit functionality within SSMS, Diag tools, )
     - Conversation Priority (set message ordering, send/receive impact, 1-10 levels)
Data Storage
     - Data compression (see above)
     - FILESTREAM attribute (get the 'best of both' functionality from BLOBs in the DB vs. BLOBs on filesystem, no more "to blob or not to blob")
     - Integrated Full Text Search (FTS fully integrated into DB engine, no external storage, no external service, more efficient and reliable costing)
     - Sparse columns (more efficient storage for 'wide' tables with many columns that repeat and don't contain data)
     - New index types (spatial indexes, hierarchical indexes, FILTERED indexes (indexes on filtered values within columns), etc.)
Data Warehousing/ETL
     - Partitioned Table Parallelism (no more thread limit per partition)
     - Star Join support (no special syntax, optimizer based, full backward syntax support)
     - Data compression (see above)
     - Resource Governor (see above)
     - Persistent Lookups in SSIS (no more re-querying for lookup operators, cache lookups in multiple ways, persist lookups to disk)
     - Improved thread scheduling in SSIS (shared thread pool, pipeline parallelism)
     - Change Data Capture (see above)
     - MERGE statement (see above, great uses with slowly changing dimensions)
     - Scale-out analysis services (read-only storage supports multiple AS servers)
     - Subspace computations
     - New Tools for Cube design
     - Best Practice Design Alerting
     - Backup cubes with better scalability
     - Data-mining add-ins for Excel
     - IIS Agnostic Reporting Services Deployment (no IIS required to run RS any longer)
     - Rich-text support
     - Enhanced visualization (graphing)
     - New Word rendering (render reports to Microsoft Word)
     - Many 'old' features ARE REMOVED/GONE (those that have been deprecated for some time - 60/65/70 compat modes, nolog / truncateonly syntax, etc.)

(source SqlStuff blog on MSDN)


See full list by editions at MSDN: Features Supported by the Editions of SQL Server 2008.


Training Materials

Microsoft is put a lot effort to create supporting documentation and training materials that would facilitate adoption of new things. The training materials and blogs. As a trainer I've used some of them I find them very useful:

and of course some videos:

Download locations:

MS SQL Server 2008 Beta Exams - Open Invitation

It's been a long time since I went on MCP exam. I've been pretty busy these days but now is time to update my certification. Let's start with MS SQL Server 2008 exams. The best thing is these exams on beta stage now so this is a chance to be certified one idea before official release (or at least when the exams are released).

Gerry O'Brien posted an open invitation for two MS SQL beta exams that are extended to July 31, 2008 so hurry up.

The exams are:

NB: You need promo codes in order to register for these exams. These codes can be found on Gerry's blog post.

P.S. Note that you won't get exams results on exam completion as the criteria /scoring/ is not clear :). Setting scores is one of the goals of beta exams. Results will be announced in about 2 months from the end of beta period or around  exam release date.

Good luck!

How bad is SQL Injection

I have been presenting IIS (Internet Information Services) for a while and there is one slide in my deck which says that there is No critical security patch since RTM for IIS6.

Recently there was some news about 500k web pages was exploited with SQL Injection hack(more info here and here).

Although this could put some shadow on IIS security it has to be clear that this is not an IIS exploit. This is application exploit. Any application could suffer SQL Injection (video: Length: 6:01 - Size: 6.37 MB ).

It is not like uploading harmful file on the server and execute it, isn't it?

So it has to be clear: Do not use such code:

public bool Login(string userName, string password)
    string command = string.Format("SELECT COUNT(*) FROM User WHERE UserName='{0}' AND Password='{1}'",
        userName, password);

    using (conn)
        SqlCommand cmdLogin = new SqlCommand(command, conn);
        int res = cmdLogin.ExecuteScalar();
        return res == 1;

Do you know why?!

Because if you get as password the following string ' OR 1=1 '; drop table Users; you will drop the table from DB and apparently the application will stop working.

Do it this way:

public bool Login(string userName, string password)
    string command = string.Format("SELECT COUNT(*) FROM User WHERE UserName=@UserName AND Password=@Password",
        userName, password);

    using (conn)
        SqlCommand cmdLogin = new SqlCommand(command, conn);
        cmdLogin.Parameters.AddWithValue("@UserName", userName);
        cmdLogin.Parameters.AddWithValue("@Password", password);

        int res = cmdLogin.ExecuteScalar();
        return res == 1;

It is much safer...

Hope this helps!