About SQL Server, Schema z, and Others
John H 
SQL Saturday is coming back to Vancouver this year. It is a great opportunity to get free SQL Server training, learn from people there, and network with other professionals doing the same thing you are doing. I am going to present a 2 hour session “Introduction to SQL Server Development ” and one regular session “Advanced SQL Certifications“. Come and join. See you there.
For many reasons, you may run into a situation that resource database of SQL Server must be moved to a different location. There are many articles you can find through Google and in BOL such as http://msdn.microsoft.com/en-us/library/ms345408.aspx telling you how to do it. After going through steps indicated in the article, you may sucessfully move your resource database, however, you may fail to do so. This happened to one of my customers. I am going to provide a new way to relocate resource database as a last resort and you can use this way when you are experiencing the same.
There are more demands from different areas within a business requires things getting done quickly. Once way is to optimize the process to let it go faster, and another way is to have more hands on it in which the loads get spreaded, each person works on few smaller parts of a bigger task, and finally getting task done in a desired time frame. Such approaches exist in the desing of SQL Server processes as well. There’re always ways to tune a procedure up, indexes, table structures, processing order, statistics, plan guides, and etc, however, you may finally find out that those do not always work as you expected. Splitting a monster process into multiple smaller independent tasks and running them in parallel come and play. In this series, I am going to talk about the ways to schedule those tasks and control the order of the executions by T-SQL. Error handling will not be discuss here since the code can always be written in the way that it handles the errors and run successfully
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.
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 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.
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.
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.