Monday, February 28, 2011

Thursday, February 24, 2011

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 Edit--- >IntelliSense --- > Refresh Local Cache



Wednesday, February 23, 2011

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

 

Tuesday, February 22, 2011

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.

Monday, February 21, 2011

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

Friday, February 18, 2011

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

Thursday, February 17, 2011

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.

Wednesday, February 16, 2011

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.

Tuesday, February 15, 2011

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'

Friday, February 11, 2011

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.


Tuesday, February 8, 2011

SQL Server: 20 Most Commonly Used DateTime Formats


Dealing with datetime data type and conversion of datetime data into many formats is everyday requirement. Here is a table of commonly used formats, to deal with datetime data.
Note: Use Convert function as follow
SELECT CONVERT(VARCHAR(20), GETDATE(), 108)
Format No
Output
101
U.S. mm/dd/yyyy
102
ANSI yy.mm.dd
103
British/French dd/mm/yyyy
104
German dd.mm.yy
105
Italian dd-mm-yy
106
dd mon yy
107
Mon dd, yy
108
hh:mi:ss
109
mon dd yyyy hh:mi:ss:mmmAM
110
USA mm-dd-yy
111
JAPAN yy/mm/dd
112
Yymmdd
113
dd mon yyyy hh:mi:ss:mmm(24h)
114
hh:mi:ss:mmm(24h)
120
yyyy-mm-dd hh:mi:ss(24h)
121
yyyy-mm-dd hh:mi:ss.mmm(24h)
126
yyyy-mm-ddThh:mi:ss.mmm
127
yyyy-mm-ddThh:mi:ss.mmmZ (With Time Zone)
130
dd mon yyyy hh:mi:ss:mmmAM (Hijri)
131
dd/mm/yy hh:mi:ss:mmmAM (Hijri)

Monday, February 7, 2011

SQL Server: How to Transfer All Objects of a Schema

Today, a colleague asked that “How he can transfer all objects of a schema to other one” First answer came in my mind that, “We must rename the existing schema” but I was not sure, because I never renamed a schema through t-sql in my carrier. But I was thinking that there should be a t-sql syntax to accomplish this task.
Unfortunately there is no such t-sql statement, using which we can rename a schema. We can only transfer objects of one schema to other using ALTER SCHEMA:
FOR EXAMPLE we need to transfer ‘Address’ table from ‘Sales’ schema to newly created ‘DailySales’
ALTER SCHEMA DailySales TRANSFER Sales.Address
And when, we need to transfer all objects of a schema (and there are dozens or hundreds of objects in a schema), it is really painful to write above alter schema line for every object. Following is the method which I like to use for such scenarios.
·   We need to transfer all objects from ‘Sales’ schema to our newly created ‘DailySales’
·   Execute follow query, and copy result set to a new query and execute to transfer all objects.
SELECT 'ALTER SCHEMA DailySales TRANSFER Sales.'+name
FROM sys.objects
WHERE type IN ('U','V','P','Fn')
AND SCHEMA_NAME(SCHEMA_ID) = 'Sales'

Saturday, February 5, 2011

SQL Server: When We Use Cross or Outer Apply Instead of Simple Joins

As me and my fellow DBAs are regular visitors of SQL Authority. In his last post, Pinal Dave introduced a query regarding wait stats and used CROSS APPLY in his query. A friend of mine asked, “What is The Difference Between APPLY Statements and Common Join Statements”.
APPLY statements are introduced in SQL SERVER 2005. I think most of us are use to with common JOIN statements. APPLY statements are just like these JOIN statements. CROSS APPLY is just like INNER JOIN and OUTER APPLY is just like LEFT OUTER JOIN.
Difference is very simple. When both joining members are simple tables, we use JOIN, but when one of joining member is parametrized table valued function, and there is no possibility to use ON clause, we use APPLY statements for joining.
Arshad Ali has explained it nicely with examples.

Friday, February 4, 2011

SQL Server: Mind Your Search Conditions In WHERE Clause

An index operation on the columns(s) in WHERE clause, can be performed or not, depends upon your search conditions.
Rule is simple. Exclusion searches generally prevent the optimizer from using an index on the columns, referred to in the WHERE clause. While Inclusion search conditions are also helpful for SQL Server to perform index seek operations.
Though every time it is not possible, still when ever it is possible, try to avoid using exclusion search conditions.
Here is a list of both exclusion and inclusion search conditions.
Inclusion Search Conditions
Exclusion Search Conditions
=
<>
>
!=
>=
!>
<
!<
<=
NOT IN
BETWEEN
NOT LIKE IN
LIKE operator with literal i.e. LIKE ‘literal%’
LIKE operator with  % sign first i.e. LIKE ‘%literal’

Wednesday, February 2, 2011

SQL Server: A Simple Method to Get Specific Part of String


SUBSTRING is a nice function to get a part of string, but when a string consists of many parts, getting specific part of it, is a bit painful task. Hopefully, following function will help you to achieve such tasks.




Hopefully, you will modify it to handle invalid sections i.e. in our example case if, only 4 section are in string but you provide if number 5 as section it will return you first part. Modify it, so it should return NULL for such cases. ;)

SQL Server: Indexes for GROUP BY Columns, a Good Choice


Every one of us knows that index on the columns referred to in an ORDER BY clause are helpful for optimizer to organize the output fast because columns values are already in sorted form.
Same way, columns which are part of GROUP BY benefit from indexes on the corresponding columns as GROUP BY clause columns are sorted first because sorted column values allow the adjacent matching values to be grouped quickly.
Indexes are also helpful for aggregate functions like MIN and MAX. Indexes on such columns are helpful for optimizer which need table/clustered index scan to find MIN or MAX values for a given column.