SQL Scripts Archives - Page 284 of 318 - SQL Authority with Pinal Dave

SQL SERVER – 2005 – Last Ran Query – Recently Ran Query

January 3, 2008

How many times we have wondered what were the last few queries ran on SQL Server? Following quick script demonstrates last ran query along with the time it was executed on SQL Server 2005. SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest…

SQL SERVER – Remove Duplicate Characters From a String

December 30, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL String

Follow up of my previous article of Remove Duplicate Chars From String here is another great article written by Madhivanan where similar solution is suggested with alternate method of Number table approach. Check out Remove duplicate characters from a string Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL SERVER – TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed

December 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts

You might have listened and read either of following sentence many many times. “DELETE can be rolled back and TRUNCATE can not be rolled back”. OR “DELETE can be rolled back as well as TRUNCATE can be rolled back”. As soon as above sentence is completed, someone will object it…

SQL SERVER – 2008 – New DataTypes DATE and TIME

December 23, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DateTime, SQL Scripts

One of our project manager asked me why SQL Server does not have only DATE or TIME datatypes? I thought his question is very valid, he is not DBA however he understands the RDBMS concepts very well. I find his question very interesting. I told him that there are ways…

SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

December 22, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

Index Rebuild : This process drops the existing Index and Recreates the index. USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REBUILD GO Index Reorganize : This process physically reorganizes the leaf nodes of the index. USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REORGANIZE GO Recommendation: Index should be…

SQL SERVER – Enabling Clustered and Non-Clustered Indexes – Interesting Fact

December 21, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

While playing with Indexes I have found following interesting fact. I did some necessary tests to verify that it is true. When a clustered index is disabled, all the nonclustered indexes on the same tables are auto disabled as well. User do not need to disable non-clustered index separately. However,…

SQL SERVER – DISTINCT Keyword Usage and Common Discussion

December 20, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts

Jr. DBA asked me a day ago, how to apply DISTINCT keyword to only first column of SELECT. When asked for additional information about question, he showed me following query. SELECT Roles, FirstName, LastName FROM UserNames He wanted to apply DISTINCT to only Roles and not across FirstName and LastName.…