John Huang's Blog on SQL Server and .Net

Home, Home, Sweeter New Home

2011-09-30T15:51:00.000-07:00

Please visit my new home @ http://www.sqlnotes.info.

Upcoming Event -- SQL Saturday 92 @ Portland Oct 8th

2011-09-23T10:42:00.000-07:00

I am going to have a presentation on "Multi-threading TSQL" in Portland Oct 8th, http://www.sqlsaturday.com/92/eventhome.aspx. This session will tell you how to use pure T-SQL to implement parallel SQL processing with complex ordering, such as how to start 2 threads at beginning, synchrinoze them then start another n threads for the next bunch of SQL processes without having any physically

SQL Server Transaction Logs

2010-05-05T00:20:00.001-07:00

Transaction log is a very important component in SQL Server to keep transaction durable even in an event of system failure. It's generated while a modification taking place in most of data pages in the memory that eventually being written into data files within a database. Inserting, deleting and updating DML operations will generate transaction logs. DDL operations, such as alter table, create

Evaluating a Boolean Expression

2009-12-17T23:31:00.001-08:00

When you write a boolean expression, @a = @b or @a/@b = 2, do you think the all the part of express get evaluated by SQL Server. let's have a test below:declare @a int = 1, @b int = 0print 'if @a = @b + 1 or @a/@b = 2'if @a = @b + 1 or @a/@b = 2 print 'No Error'print 'if @a/@b = 2 or @a = @b + 1'if @a/@b = 2 or @a = @b + 1 print ' Error'goThis test illustrates that while SQL Server is

Merge Statement

2009-12-16T17:21:00.001-08:00

Merge statement was introduced by Microsoft in SQL Server 2005. It allows user to merge one table to another combining different ways of operation, inserting, deleting and updating, on the targeting table based upon the condition specified in the merge statement.When Matched: It means when the record in the source table and target table matched by key. In this case, you have option of, update

Accessing Invisible Tables

2009-10-23T23:45:00.001-07:00

In SQL Server, there are two kinds of tables are invisible. One is system protected tables, for instance, sys.sysrscols. You could access them by using Dedicated Administrator Connection (DAC) to connection to the server.Enable DAC: exec sp_configure 'remote admin connections', 1 reconfigureConnect using DAC: specify ADMIN:ServerName in connection string, then you can run select * from

Implement Sequence in SQL Server (3)

2009-09-18T22:34:00.001-07:00

In my previous post, I introduced supporting procedures and functions of Sequence. In this article, I will post one example for performance testing and another two for generating set of numbers. print '------use temporary table------'if OBJECT_ID('tempdb..#1') is not null drop table #1gocreate table #1(id int identity(1,1) primary key)set nocount ondeclare @i int, @d datetime2(3)select @i = 0, @d

Setup Procedure to Run Automatically When SQL Server Starts

2009-09-15T00:55:00.001-07:00

That's simple, setup a job and run the procedure and schedule it runs when SQL Server agent started. See the problem here? My title is "when SQL Server Start", not "When SQL Server Agent". SQL Server can start without agent. Procedure in SQL Server can be started automatically when SQL Server starts. In order to do that, you have to set "scan for startup procs" through sp_configure function, and

Implement Sequence in SQL Server (2)

2009-09-15T00:10:00.001-07:00

In previous article, I explained the way using identity to implement Sequence object. I also gave the source code of my CLR Sequence which can also be downloaded here. As a continuation of this series, in this article, I will explain how to install Sequence object in SQL Server and how to use it.Download the source code and decompress it.Run <> to create

Implement Sequence in SQL Server (1)

2009-09-12T23:45:00.001-07:00

Sequence is a type of object in Oracle for generating sequential number. A sequence can be seen from all sessions. Sequence is not transactional. Calling NextVal function to a sequence will always give you a new number. It is a very common use in Oracle to create one sequence object for one table object and use the value coming from it as table's primary key. The counterpart of Oracle sequence in

Run Dynamic T-SQL from a Function with Data Change

2009-08-31T17:45:00.001-07:00

This is today's topic – Run Dynamic T-SQL from a function which allows data modification…wait a second…in this post, a point has been laid out that "You can NOT do any data modifications within a function". Is the rule changed? Answer is no. Microsoft hasn't made any promise yet to make a function to be able to update tables. How can it be implemented? In my experiment, I use SQL CLR to make

Restrict Connections for a Process

2009-08-27T17:51:00.001-07:00

Application lock in SQL Server is very powerful as I mentioned in my previous post here and here. If you have a process, many clients are calling it, and you want to restrict only 10 requests can be processed simultaneously, other requests wait until any of the running requests finish. How do you do it? – haha, you got it, use application lock! I will have an example to explain who it works.

Backup and Recovery

2009-08-16T23:37:00.001-07:00

In a SQL Server database, whenever a modification happens, there are always 2 operations taking place which are log recording to the log file and data modification to the data file. So a database can be backed up in 2 ways –data backup and log backup. Data backup looks at all data files in database, moves data in the data files to the backup file then copy the logs which could make the data to be

Declare a Variable in a Loop

2009-08-14T00:21:00.001-07:00

When a new T-SQL variable is declared, null value will be assigned to it. What if I declare a variable within the loop? Will it be initialized every time when it's being declared? Let's have a test.set nocount onif object_id('tempdb..#1') is not null drop table #1gocreate table #1 (id int primary key, name varchar(10))insert into #1 values(1,'a')insert into #1 values(3,'b')insert into #1 values(4

Repair a Database with Status Suspect

2009-08-13T23:58:00.001-07:00

One of my SQL Server 2000, which hosts a 200GB+ database, was dead today due to the RAID failure. The log shows information below. one of the log:SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [d:\Microsoft SQL Server\MSSQL\data\blabla.MDF] in database [blabla] (7). The OS file handle is 0x00000400. The offset of the latest long IO is

Full Backup and Restore

2009-08-13T00:23:00.001-07:00

Any database systems have chance to suffer from severe system failure and hence causes database corruption, for instance, memory failure, hard drive failure, power failure and so on. Once the database gets corrupted, it's almost hopeless to recover it without backup. So it's very important to backup the databases for any database systems. There are two big categories in backup world – cold backup

Generating Random Password by a T-SQL Procedure

2009-08-12T14:34:00.001-07:00

For generating a complex password, we should not only get random value from the set of the characters but also guarantee the combination of upper case, lower case, numbers and special characters. In the procedure below, the password generated will include all 4 parts.if object_id('GeneratePassword') is not null drop procedure GeneratePasswordgocreate procedure GeneratePassword(@length int)asbegin

References Permission

2009-08-07T14:42:00.001-07:00

While you are assigning permissions to a table through user interface, permission "Refereneces" could be assigned. That's that for? Answer is if a table has References permission assigned to a user, the user can create a Foreign Key reference to all the "referencable" column(s) to this table even though he has no access to it. This permission could be assigned to column level as well, this means

Printing Message to Client without Waiting

2009-08-07T09:05:00.001-07:00

While I am writing SQL code, I often use print command to send message to the client application for information. One thing I noticed that this command could not return the message to client immediately due to performance concern over the network. Please see the code below.declare @i intselect @i = 0while @i<5begin print @i select @i = @i + 1 waitfor delay '00:00:01'endIn this example, the code

Bypass Merge Replication Triggers

2009-08-05T17:37:00.001-07:00

One of my projects is to replicate tables from central office to Point Of Sales system at branches using merge replication for a financial institution. The customer information is replicated to the branches where the customer has been to. When customer goes to a new location, the new location does not have customer's information so the branch staff has to download them from head office. The

Distinguish Records Entered by Replication

2009-08-05T16:51:00.001-07:00

When you write a trigger you may have thought to handle the business on the records shipped by replication differently than the records entered by front-end application. How do you distinguish them? There is a simple way you could do it – check the session property with value "replication_agent", this value is 1 when the DML is issued by replication agent, it is 0 when DML is issued by any

Generate Formatted Text Through XML Functions

2009-08-04T16:33:00.001-07:00

I play around xml functions those days and find out an easy way to aggregate string field(s), generate CSVs and fixed length files. This is not a newer topic over the internet, see an example here. The tricks are concatenate field with comma, and then use for xml path clause to generate xml file and finally use value() to convert the content to string for un-escaping the characters like "<", ">",

Cursor (6) – Fetch Multiple Rows at a Time

2009-08-01T10:50:00.001-07:00

I have talked about many the properties of a cursor in this series; you may already figure out that T-SQL fetch only supports fetching one record at a time. If you want to have fetch to retrieve more than one record at a time, you will have to use API cursor. You could get more information at here about API cursor. See the demo below.use AdventureWorksset nocount ongodeclare @handle int exec

Why Truncating Table (2)

2009-07-25T22:34:00.001-07:00

This is a continuation of my prior post. Last time, I compared results from truncating and deleting table. According to the log records, we could clearly see that deleting table goes to every data page and marks every record to be deleted. As we know, while modifying data on a page, the page is read to the memory by buffer manager; database engine modifies the page in the memory and then buffer

Two ways to Pass “Parameters” to Triggers

2009-07-24T15:34:00.001-07:00

Triggers are the codes behind the views and tables (or system for the DDL triggers). It is always being executed as long as the data manipulation statement, insert, delete and update, applied to the table except it's been disabled. In some cases, trigger logic can be applied to the most of circumstances but in some particular condition, you want the same trigger behave differently. There are

Why Truncating Table

2009-07-22T13:44:00.001-07:00

There are many differences between truncating table and deleting table. Truncating table cannot have "where" clause; it reseed the identity column if any; the truncating operation is logged as one operation; whereas deleting table can have "where" clause; it does not reseed the identity column and it log every delete and when a table is big, it will become problematic. I would not be surprised

Another Example of SQL Server Application Lock

2009-07-17T16:30:00.001-07:00

I have talked about using application lock in previous post -- ensuring a block of code being entered once at a time. I want to give another example of use of application locks. Let's say, you have a process (or many process) running, proc1, they are complicated and not running within a transaction. This process might also be fired up by multiple sessions. Let's say you have another process,

How to Make a Block of SQL Code Enter Once at a Time

2009-07-16T13:20:00.001-07:00

As a multi-threading application, SQL Server accepts multiple connections and allows them to run same or different SQL statements simultaneously especially in multi-CPU environment. SQL Server implements lock mechanism to prevent resource from being modified while other processes are not expecting it to be modified, i.e. while one process is modifying one row, if another process wants to modify

Cursor (5) – Concurrency

2009-07-14T17:53:00.001-07:00

Continue on yesterday's topic, I am going to talk about cursor's concurrency. There are 2 concurrency modes SQL Server cursor provides – "SCROLL_LOCKS" and "OPTIMISTIC". If you don't specified any one of those options, the optimistic is selected automatically which is also called default. These 2 options could not be applied to read only cursors which include static cursor, cursor with read only

Cursor (4) – Other Properties

2009-07-13T23:55:00.001-07:00

I have talked about how to manipulate a cursor and Cursor's storage, let's have a glance on other properties of SQL Server cursor. A static cursor is also called insensitive cursor, which was the name a static cursor being called before having static key word. While opening a static cursor, it grabs all the records to the tempdb as I mentioned in my prior post so that there will be a big or small

Microsoft Certified Master – SQL Server 2008

2009-07-11T17:34:00.001-07:00

It was my hardest time this year; I attended the training of Microsoft Certified Master – SQL Server 2008 in a rainy season – the middle of March 2009. When I was guided to the small room that I booked in HomeStead, which was the closest hotel to the classroom, I felt I was stepping into a different world as I knew I would have totally different life with those 3 weeks. As everyone knows that

Implement Multi-threaded T-SQL Application

2009-07-10T23:55:00.001-07:00

In addition to Capture Multiple Result Sets within T-SQL Statement, combining this CLR procedure, which could also be downloaded here, and Service Broker together, you could very easily implement multi-threaded T-SQL application. The goal of it is when you run a dynamic SQL; you can get return immediately without waiting SQL statement to be finished. The session calling this dynamic SQL will not

Cursor (3) – Cursor Type

2009-07-08T22:26:00.001-07:00

Continue on last post, now we know how to operate a cursor, open, close and fetch. If you are asked to write a C# application to iterate records read from database. How do you do it? There are many approaches you could do it. The most common one is to read all rows from database to you C# application and then build a loop to go through every record. In this way, whatever changes to the set of

Resume Inactive Transactional Replication Agent in SQL Server 2000

2009-07-08T15:20:00.001-07:00

I am creating transactional replication from a production server which is a SQL Server 2000 server acting as a publisher in one city to a development server which is a SQL Server 2008 server working as subscriber in another city. There are about few thousand kilometers between those 2 cities. The network speed is not so fast about 1mbps. The initial snapshot is about 50 GB. I compressed it in to

Cursor (2) – Operating a Cursor

2009-07-07T22:42:00.001-07:00

Before getting into this topic, let's recap some cursor commands from last post and talk few more commands.Command DescriptionDeclare CursorDefine a cursor. The cursor structure will be created, including cursor columns definitions and cursor meta-data.Open CursorOpen a cursor so that data could be retrieved from it. When this statement runs, the SQL specified in Declare Cursor will be

Cursor (1) – How to Use Cursor

2009-07-06T22:52:00.001-07:00

Cursor is a built-in mechanism that SQL Server provides to access a set of records which can be a return set from a table, query, and even a stored procedure. Before using a cursor, the cursor has to be defined with a unique name in a scope, it then could be opened. Once the cursor gets opened against a set of rows, T-SQL code can use fetch command to retrieve one row at a time and put it into a

Allocating Pages in a Table

2009-07-05T18:24:00.001-07:00

Now, I have finished explanation of all the page types in SQL Server. What will be happening in SQL Server when a table is created and a record is entered? When a table created, SQL Server only create the table meta data in system tables without allocating any space and IAM pages for that table(you will see both root page and IAM page in system internal allocation table). When the first record is

Storage Internal (7) IAM

2009-07-05T16:10:00.001-07:00

I have been lazy those days. After finishing few articles for the topic other than Storage Internal, I would like to go back to this topic little bit more. As a continuation of last post, Storage Internal (6) Other Pages, this article is about IAM – Index Allocation Map. As the name of the page, it's is an allocation map for an "index". In SQL Server, the term index including almost everything –

Capture Data Changes to Data Warehouse

2009-07-04T00:11:00.001-07:00

Currently I am working on a data warehouse project. We have to bring relatively big amount of data from operational database to data warehouse initially and do incremental process frequently afterwards. The data in the operational database is rather volatile. We could not just go back few months and then re-process it due to limited time window. In order to mitigate the load on the operational

Capture Multiple Result Sets within T-SQL Statement

2009-07-02T23:15:00.001-07:00

As I mentioned in my last blog, I am going to give you some examples of using CLRRemoteExecute procedure. Before the example, let me wrap it up and make it easy of use. In this example I set show detail message on. We can see how the script to be run. create procedure dbo.Ex( @Server varchar(128), @Database varchar(128), @SQL varchar(max), @ConnectionTimeout int = 30, @CommandTimeout int = 3600

Run Your SQL Differently (2)

2009-06-30T23:54:00.001-07:00

This is a continuation of previous post. Once you explicitly begin a distributed transaction, user transaction will be enlisted in the MSDTC anyways. There is almost no way to get around that except calling a CLR procedure. I wrote this CLR procedure and figured out that it brings in more features exceed my expectations. Let's see what will be involved in writing a CLR to run a SQL statement

Run Your SQL Differently (1)

2009-06-29T23:30:00.001-07:00

There are a lot of ways you could run a SQL Statement for example Exec() and sp_executesql. If you want to run SQL Statement on a remote server or access data from a remote server, there are many options linkedServer.Database.Schema.sp_executesql, OpenQuery, OpenRowset, OpenDataSource. All mechanisms that SQL Server provided are good; however, you are not able to run a statement with the its own

Storage Internal (6) Other Pages

2009-06-28T19:57:00.001-07:00

The page 4 and 5 that following the first SGAM page, are not used. Page 6 is Differential Changed Map page; page type is 16. It is a bit map that indicates that which extents have been changed since last full backup. Some extents are always marked changed even after a full back, for instance the extent at page 0. The interval of DCM is same as GAM and SGAM. The page address of DCM is always the

Storage Internal (5) GAM and SGAM

2009-06-26T22:21:00.001-07:00

As I mentioned before, the fist page, page 0 in a data file is File Header Page, and the second page, page 1 is PFS page. Now, let's have a glance on next 2 pages, the third page – GAM, Global Allocation Map, page type 8 and the fourth page – SGAM, Shared Global Allocation Map, page type 9. Just as PFS page, GAM and SGAM are used to keep track of state of allocation as well, however, they track

Code Formatting

2009-06-26T00:31:00.001-07:00

I had a very hard time to format the code in my blog. At first, I just copy /paste the code from SQL Server management studio to my Microsoft Word application and then post the blog. I thought it was perfect. I really appreciated the work that the SQL Team and Office programmers have done. However, My blog just looked horrible due to the truncation of the html code from Blogger. After using

Storage Internal (4) Page Free Space

2009-06-25T23:15:00.001-07:00

Continue on previous post File Header Page. PFS, Page Free Space, page type 11, consists of an 8088 byte byte-map mainly keeping track of the free space on data file pages. Every byte in this page indicates allocation status of a page. Every PFS page can manage 8088 pages. Each data file is conceptually split into PFS intervals of approximately 64M, starting with page zero in the file. The first

Transparent Data Encryption

2009-06-24T22:08:00.001-07:00

Transparent Data Encryption is a new feature introduced in SQL Server 2008. It is designed for protecting the data on the media such as drives, backups, and tapes from being stolen or being attached / restored on un-trusted servers. It provides the ability to comply with laws, standards, and regulations and so on. However, it could not protect the server from being stolen.Let me borrow the

Storage Internal (3) - File Header Page

2009-06-22T22:14:00.001-07:00

File Header Page is page 0 on every data page (row page) in a database. (I will only focus on data page now. In the later post, I will talk about the logs). It records information about the file, backup status, formatting size, encryption information and so on. Now, let's dump page 0 and have a closer look. (As too many fields I don't know and could not find any articles about it from internet, I

Storage Internal (2) – Page Structure (2)

2009-06-22T00:07:00.001-07:00

Continue on last post, Page Structure (1). A page includes a 96 byte header. What is in it? Before answer this question, I would like to introduce a DBCC command DBCC Page. This command can help print the content of a page.dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]) [ WITH TABLERESULTS ]{'dbname' | dbid}:Database Name or Database ID. – Database id can be retrieved by

Storage Internal (2) – Page Structure (1)

2009-06-21T12:28:00.001-07:00

In last post of this topic, Storage Internal (1) -- Page and Extent, I talked about the basic concept of page and extent. In this post, I will focus on the structure of a page. There are different types of pages in SQL Server. No matter what types of pages are, the layout of the page is the same. A data file consists of numbers of 8k-pages. A Page includes 8192 bytes. First 96 bytes are used for

Run dynamic T-SQL statement from a function

2009-06-18T22:02:00.001-07:00

Are you able to run dynamic SQL from a T-SQL function? Answer is no. Let’s have a quick test. create function dbo.ExecDynamicSQLTest() returns int as begin exec('print 1') return 1 end go Msg 443, Level 16, State 14, Procedure ExecDynamicSQLTest, Line 5 Invalid use of a side-effecting operator 'EXECUTE STRING' within a function. Ok, let’s have another try then create

Storage Internal (1) -- Page and Extent

2009-06-17T22:20:00.001-07:00

In previous posts, I talked about Database file groups and files. From now on I am going to spend some time to describe storage internal. The first question you may ask is why I need to know internal structures. Well, it helps to understand the storage engine, troubleshoot problems and make decisions. So, what is in the file and how the data is organized in each database files? This is not a

Convert comma separated text to table structure

2009-06-16T23:25:00.001-07:00

I have worked with many customers who need to exchange information between systems. Some of them use csv file as the media for the data exchanging. Usually when we design the system, for auditing purpose, we always save the original message either in the file system or in a table after it’s been parsed and processed. There are many approaches converting a csv file to a table, for instance, BCP

Database file groups and files

2009-06-15T22:28:00.000-07:00

An SQL Server instance can host maximum 32,768 databases. Every database in a server can have up to 32,767 operating-system files. The maximum size of each file is 16TB. (Click here for more detail). Database files cannot be shared among databases, however, if all the files of a database are set to be read-only by OS, the database can be attached to same or different database names in the same