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
What are the differences between TRUNCATE and DELETE? You may immediately point out that:
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.
Continue reading »
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.
Continue reading »
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.
Continue reading »
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.
Continue reading »
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 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 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.
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.
