December 2011 – John Huang's Blog

Merry Christmas and Happy New Year

December 24, 2011 by John H

My blog will not be updated in the rest of year 2011. I received 2 very thoughtful and cute SQL Post Cards. I would like to share them here to wish you all Merry Christmas and Happy New Year. Christmas Tree on the Page. and Merry Christmas

Truncate

December 23, 2011 by John H

What are the differences between TRUNCATE and DELETE? You may immediately point out that:

Truncates reset the identity value
Truncates can’t have where clause
Truncates can’t be run on a table which is referenced by other tables
Truncates do not fire triggers.
Truncates can’t be run on a table which has depending objects such as indexed views.
Truncates can’t be run on a table which has published by merge or transactional replication publication
Truncates need db_owner and db_ddladmin permission.
Truncates need locks on the table and schema but do not need locks on rows of the tables

Are Cursors Bad In Perfromance

December 21, 2011 by John H

The performance of cursors in SQL Server has been blamed for years. Many developers rather to use a temp table to save the record set from the query and then use while statement to loop the records over the temp table. Is this a good choice? Do cursors realy perform badly? In this article, you will figure the loop will only perform well in one particular case actually. In most of the cases, cursor performs better.

Generating Random Password by a T-SQL Procedure

December 19, 2011 by John H

To generate 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.

Fetch Multiple Rows Through Cursor

December 16, 2011 by John H

Fetch statement allows you to retrieve one records at a time. If you need multiple records returned, you will have to call fetch multiple times to get multiple sets. Each returned sets has one record in it. It would be useful if a cursor can return multiple rows by one fetch. This can be implemented by API cursor.

Open a Cursor over Queries, Procesures, and Dynamic SQL

December 14, 2011 by John H

Cursor is a built-in mechanism SQL Server provides to iterate through records returned frome a query even a stored procedure. Before using a cursor, the cursor has to be defined with a unique name in a scope, local and global. Cursor structure is built when it’s opened. Once the cursor gets opened for a set of rows, you can use fetch command to retrieve one row at a time and save it into a variable, return to client, or insert them into a table structure.

SQL Server Tracing System (2) — 2 Ways to Receive Trace Data

February 4, 2012December 12, 2011 by John H

There are one way to setup a trace programmatically but 2 ways to acquire and consume the trace data. To setup a trace, you need to to call trace supporting procedures to define events and columns. If you specify the location of the trace target, SQL Server will write trace data to files. By this approach, the SQL Server instance which reads the data files need to have file access permission to them. The another approach is that you don’t specify the target location. By this approach, the trace data will be “streamed” to the client directly.

SQL Server Tracing System (1) — DMVs

February 4, 2012December 9, 2011 by John H

SQL Server tracing system is used very often by DBAs and programmers for pinpointing issues in the databases. It’s supported by few system objects that describes itself including list of events, columns of each event might return, start and stop event, retrieve event data, status of each event sessions, etc. SQL Profiler utilizes those objects to retrieve and show the trace information on the screen. From debugging perspective, it’s enough for a SQL developer. DBAs, however, prefers to create trace programmatically and save the trace in the database or file system for alerting and later performance analysis. You can use SQL Profiler to trace the SQLs issued by anbother SQL Profiler to generate the trace procedure sequences. Alternatively, you can also browse trace related DMVs to find out all trace related metadata.

Rows|Range – Another Layer of Partition within Over Clause in SQL Server 2012

March 1, 2012December 7, 2011 by John H

Rows|Range enhances partition functionality of Over clause in SQL Server 2012. In the Over clause before SQL 2012, you can group rows with the same attributes together, which is called partition. You can also order the rows within each of partitions, which is implemented by Order By. The aggregation functions in front of Over clause will be are partition and order based. In SQL Server 2012, a lower level “partition”, which is called windows fram, range of rows, can be defined under the partition. A window frame can be static like partition clause but can also be dynamic – moving window. This provides great power on data analytics, for instance, you no longer need joins for calculating moving average, instead, one SQL with moving window can give you the result.

Auto Adjust Memory Configuration When Failover/Failback Happens

February 4, 2012December 5, 2011 by John H

SQL Server failover clustering is widely implemented in the world. One of the DBA’s tasks is to adjust memory when failover happens. When that happens, 2 or more SQL instances are running on one physical node, then max memory for both instances should be adjusted. When the failed nodes are up, failback will perform, then DBAs need to do memory adjustments again on all affected instances. It would be more productive if this process can be automated. In this post, I will give you my ideas and the implementation of it.