Monthly Archives: February 2011

>SQL Server: What I Have Learned In February 2011

>

Dedicated to Tara Kizer., who is on this planet, just to help others.
February was a tough month as I switched my company and also because of my final term exams. Here is a list of topics which I have learned and shared in February 2011.   
Data Migration
TSQL Tips and Tricks
Performance Tuning
Database Design
SQL Server Management Studio
 
Advertisements

>SQL Server: How to Refresh Intellisense Cache

>

Intellisense in SQL Server 2008 is a time saving feature. But sometime writing queries it start underlining some objects names or some columns names which are already added in database and these are correct but intellisense mark them as a MISTAKE. In long queries it creates confusion. I have seen my fellow DBAs, turning off the feature to avoid confusion and mental stress;).
Also, sometime intellisense is not helping enough for auto-complete feature. 
Simple solution for this problem is to refresh local cache by using  Ctrl + Shift + R. So it should start marking only real mistakes. You can also refresh local cache from FILE — >IntelliSense — > Refresh Local Cache

>TSQL Challenge 49: Identify overlapping time sheet entries of field technicians

>

I am a big fan of http://beyondrelational.com/and specially its challenges. Recent quiz (TSQL Challenge NO.49) was an interesting and very useful for DBAs and developers. Everyone must visit and try this challenge.

 http://beyondrelational.com/blogs/tc/archive/2011/02/07/tsql-challenge-49-identify-overlapping-time-sheet-entries-of-field-technicians.aspx

 Here is an idea to solve this challenge.But it would be better if you try it yourself. (Base table structure, data and expected result can be found on above mentioned link)

SELECT  BaseTable.Technician,
        BaseTable.Grade,
        CASE WHEN ( BaseTable.StartTime >= LeftOuterTable.StartTime
                    AND BaseTable.StartTime <= LeftOuterTable.EndTime
                   
                  ) THEN BaseTable.StartTime
             WHEN ( BaseTable.StartTime <= LeftOuterTable.StartTime
                    AND BaseTable.EndTime <= LeftOuterTable.EndTime
                    AND BaseTable.EndTime >= LeftOuterTable.StartTime
                  ) THEN LeftOuterTable.StartTime
             WHEN ( BaseTable.StartTime <= LeftOuterTable.StartTime
                    AND BaseTable.EndTime >= LeftOuterTable.EndTime
                  ) THEN LeftOuterTable.StartTime
        END AS CStartTime,
        CASE WHEN ( BaseTable.StartTime >= LeftOuterTable.StartTime
                    AND BaseTable.StartTime <= LeftOuterTable.EndTime
                    AND BaseTable.EndTime <=LeftOuterTable.EndTime
                  ) THEN BaseTable.EndTime
              WHEN ( BaseTable.StartTime >= LeftOuterTable.StartTime
                    AND BaseTable.StartTime <= LeftOuterTable.EndTime
                    AND BaseTable.EndTime >=LeftOuterTable.EndTime
                  ) THEN LeftOuterTable.EndTime
              WHEN ( BaseTable.StartTime <= LeftOuterTable.StartTime
                    AND BaseTable.EndTime <= LeftOuterTable.EndTime
                    AND BaseTable.EndTime >= LeftOuterTable.StartTime
                  ) THEN BaseTable.EndTime
             WHEN ( BaseTable.StartTime <= LeftOuterTable.StartTime
                    AND BaseTable.EndTime >= LeftOuterTable.EndTime
                  ) THEN LeftOuterTable.EndTime
        END AS CEndTime,
        LeftOuterTable.Technician AS OverlapedWith
FROM    TC49 AS BaseTable
        LEFT OUTER JOIN TC49 LeftOuterTable ON ( BaseTable.StartTime >= LeftOuterTable.StartTime
AND BaseTable.StartTime <= LeftOuterTable.EndTime
OR ( BaseTable.EndTime >= LeftOuterTable.StartTime
AND BaseTable.EndTime <= LeftOuterTable.EndTime
) OR ( BaseTable.StartTime <= LeftOuterTable.StartTime
AND BaseTable.EndTime >= LeftOuterTable.EndTime
)
WHERE   BaseTable.Technician <> LeftOuterTable.Technician
ORDER BY Grade

 

>SQL Server: Why We Should Prefer Database to Implement Data Integrity

>

Recently, I had explored a client database, in which not a single domain or referential constraint was used to implement data integrity. And such integrities are implemented through code on application side.
At database side following domain and referential constraints can be used to implement data integrity.
  • FOREIGN KEY
  • CHECK
  • DEFAULT
  • NOT NULL
Out of these four constraints, DEFAULT has less importance as compare to other three. Developers mostly like to implement domain integrity constraints through code on application side, and like to avoid implementing these through CHECK constraints, on database side. But one should keep in mind that implementing such business rules in the database using CHECK constraints are always helpful for optimizer to generate efficient execution plans.
For remaining two, one should totally depend on database and these (FOREIGN KEY and NOT NULL) constraints should be implemented on database side. Because, through database you are not only using domain and referential constraints to implement data integrity but also TO FACILITATE THE OPTIMIZER TO GENERATE EFFICIENT QUERY PLAN FOR YOU.

>SQL Server: When We Should Use Read Only Cursors

>

Everyone of us knows that processing of data through cursors, is a worst choice, because SQL Server are designed to work best with sets of data and not one row at a time.
Still processing through cursors can be made faster by making little changes. Like, if we need to use cursor to process one row at a time and we don’t need to update base table through this cursor, we MUST use read only cursor. As read only cursors are non-updateable so no locks are required on the base table. Only shared locks are held. And due to this phenomenon read only type of cursor are considered FASTER and SAFER.
Syntax is almost same to ordinary cursor and only keyword of READ_ONLY is added.
DECLARE YourCursorNameHere CURSOR READ_ONLY
      FOR SELECT columnNameHere
            FROM  tableNameHere
            WHERE filterConditionHere
OPEN YourCursorNameHere
FETCH NEXT FROM YourCursorNameHere INTO @parameterName
WHILE @@FETCH_STATUS = 0
      BEGIN
            YourImplementationHere
            .
            .
            .
      FETCH NEXT FROM YourCursorNameHERE INTO @parameterName
      END
CLOSE YourCursorNameHere
DEALLOCATE YourCursorNameHere

>SQL Server Performance: Not All Fast Queries Are Innocent

>

To, find out costly quires, a majority of DBAs like to visit SQL Profiler. Queries with higher CPU and READ/WRITE are marked as costly quires. I have observed that most of the time quires with less CPU and READ are ACTUAL BIG PROBLEM. Cumulative effect of multiple executions of these commonly considered well performing quires normally put more pressure on system as compare to occasionally executing costly quires. Simple if a query giving result in 10 milliseconds but being executed 10000 time, with in a short time, then definitely it’s a REAL costly query.
To get optimum performance, identification of such COSTLY quires is necessary. This can be achieved by creating history of trace data for peak and off peak hours of your database. Follow given steps for this task.
  • Open profiler, and select following columns
    • EventClass, TextData, Duration, CPU, Reads, Writes
  • Create a filter on your required database
  • Start your trace, and later save this trace data to some trace output file.
  • Load trace data from trace file to trace table by using following query
SELECT * INTO Trace_Table
FROM ::fn_trace_gettable(‘C:\YourTraceFile.trc’,default)
  • Once the trace data is imported, use following query to find quires with high CPU and READ/WRITE values
SELECT COUNT(*) AS TotalExecutions,
      EventClass,
      TextData,
      SUM(Duration) AS TotalDuration,
      SUM(CPU) AS TotalCPU,
      SUM(Reads) AS TotalReads,
      SUM(Writes) AS TotalWrites
FROM Trace_Table
GROUP BY EventClass,TextData
ORDER BY TotalReads DESC
Why order by TotalReads and not CPU, read this

>SQL Server: How to Migrate Data From Top Speed Database Files

>

I had never heard about Top Speed Database. But, my today’s task was, “to migrate data from TPS files to SQL Server2008”. I start collecting information but unfortunately there was no detailed information regarding these TPS files. But thanks to my team lead who always have some solution for such interesting tasks.
TPS Database File is an ISAM type file developed by the TopSpeed corporation primarily for use with the Clarion Development platform which is currently developed and distributed by Soft Velocity www.softvelocity.com.
To shift data from tps files, first we have to open these files, reformat some columns (Date and Time) and then we can shift data to some flat files.
Top Speed Database Scanner is used to open these files
Click on FILE – – >OPEN, to open a single tps file. Once the file is open you can select column of your choice from VIEW. Columns with data types date and time are stored separately in tps files. And before shifting we have to convert these date and time columns to some proper format other wise these date and time will look like simple integer.
For this purpose click on  COLUMN and then FORMAT DATABASE COLUMN, in picture field use @D17 or @D18 to format date column data, and then click OK button. Next select time column (if exists) and again select FORMAT DATABASE COLUMN. This time in picture field use @T3. Press OK button and you are done with formatting.
To export to a text file, move your pointer to FILE, and click EXPORT. Quote Strings is checked by default, unchecked it and also use a CHARACTER as separator. In my case I used pipe | sign. Now, for “Export File” provide name and path to valid text file and click OK button to export.

On SQL Server side, use import utility to import, use Flat File Source as data source and make necessary changes according to following screen shot, at choose a database source page.

>SQL Server: Data Type DATETIME or Only DATE

>

SQL Server 2008 introduced a new data type “DATE” to store date data only. Before this we have only DATETIME data type which stores both date and time. I have observed that many database developers and DBAs still prefer DATETIME to store data, even for columns where they don’t need to store time. For example, to store date of birth we need only date, and not the time.
SELECT Emp_Name, Emp_SSN, Emp_DOB
FROM Employee
WHERE CONVERT(VARCHAR(20), Emp_DOB, 101) <= ’06/30/2005′
Problem occurs when we need to retrieve such data, and we need different conversion functions to separate date from time. And if such conversion functions are part of WHERE clause, then it prevents the optimizer from choosing the index on the column. Resultantly, a poor query performance, because indexes are not being used.
Simply select DATE data type for such columns, especially when you don’t need the time portion. Even, if most of queries use conversion functions to separate date or time. Save such data separately in two different columns with data type DATE and TIME respectively.

>SQL Server: How to Delete or Update Rows with JOIN Clause

>

A common question asked in every Database Developer interview is that “How to Delete or Update Rows using JOIN Clause to filter effected rows
Not all update and delete quires are as simple as we think. Sometime, we need to update or delete records on the basis of complex WHERE clause. And sometime we can only do this complex FILTERING through joining multiple tables.
If multiple tables are in join clause then following is the simple method to delete or update rows.
UPDATE:
UPDATE [target table]
SET [target column] = [new value]
FROM    [table1]
        INNER JOIN [table2]
ON [table1.[joining column] = [table2].[joining column]
WHERE   [condition]
USE AdventureWorks
GO
UPDATE Person.Address
SET City = ‘NEWCITY’
FROM    HumanResources.EmployeeAddress eAdd
        INNER JOIN Person.Address pAdd ON eAdd.AddressID = pAdd.AddressID
WHERE   pAdd.City = Cambridge
AND         eAdd.EmployeeID = 19
DELETE
DELETE  [target table]
FROM    [table1]
        INNER JOIN [table2]
ON [table1.[joining column] = [table2].[joining column]
WHERE   [condition]
USE AdventureWorks
GO
DELETE  HumanResources.EmployeeAddress
FROM    HumanResources.EmployeeAddress eAdd
        INNER JOIN Person.Address pAdd ON eAdd.AddressID = pAdd.AddressID
WHERE   pAdd.City = Cambridge

>SQL Server: How to Avoid Data Loss During Migration From MS Excel

>

While importing data from excel sheet, keep in mind that, SQL Server export/import utility look into first  row of your data in excel sheet to guess data types of different columns.  Problem occurs when we want to import columns, which contains both numeric and character data.
In above screen shot of ms excel data sheet, we have three columns, first one contains only numeric data, but second and third columns contains mix (numeric and character data), which in future we like to import as varchar or nvarchar.  If data type of cells in Excel is GENERAL (that is default). During data migration on column mapping page we can see that, utility made guess for data types according to data in first row. For example first cell of second column contains numeric data, that’s why utility guessed float as data type for whole column.

Check out the results, after import is complete. We lost the data for some rows in second and third columns. Because utility imported data that fits into data type criteria and left other. Problem still exists even if we change the data type during mapping.
Best way to resolve this problem is that we should change the data type of each cells, explicitly in Excel Sheet i.e. for columns which are supposed to contain numeric , data type should be number and for character it should be text. After changing data type explicitly in Excel sheet, mapping page will look like as follow.
Now check the results.