Think SQL...

Gopal, your password was successfully reset

noreply@blogger.com (Unknown) — Tue, 2 Dec 2014 09:25:00 +0300

Hi Gopal,

You've successfully changed your LinkedIn password.

Thanks for using LinkedIn!

The LinkedIn Team

When and where this happened:

Date:

December 2, 2014 11:55 AM

Browser:

Chrome

Operating System:

Windows

IP Address:

114.143.97.209

Approximate Location:

Mumbai, Maharashtra, India

Didn't do this? Be sure to change your password right away.

This email was intended for Gopal Chettri (Sr. Software Developer). Learn why we included this.

Gopal, your password was successfully reset

noreply@blogger.com (Unknown) — Wed, 17 Sep 2014 19:44:00 +0300

Hi Gopal,

You've successfully changed your LinkedIn password.

Thanks for using LinkedIn!

The LinkedIn Team

When and where this happened:

Date:

September 17, 2014 10:14 PM

Browser:

Chrome

Operating System:

Windows

IP Address:

122.179.177.17

Approximate Location:

Mumbai, Maharashtra, India

Didn't do this? Be sure to change your password right away.

This email was intended for Gopal Chettri (Software Developer). Learn why we included this.

Gopal, here's the link to reset your password

noreply@blogger.com (Unknown) — Wed, 17 Sep 2014 19:39:00 +0300

Hi Gopal,

You recently requested a password reset.

To change your LinkedIn password, click here or paste the following link into your browser: https://www.linkedin.com/e/rpp/65721660/mageshwaran%2Esubramanian%2Ethinksql%40blogger%2Ecom/6689409392919487395/?hs=true&tok=2WIGjFLK0DR6o1

The link will expire in 24 hours, so be sure to use it right away.

Thanks for using LinkedIn!

The LinkedIn Team

This email was intended for Gopal Chettri (Software Developer). Learn why we included this.

I'd like to add you to my professional network on LinkedIn

noreply@blogger.com (Unknown) — Mon, 15 Sep 2014 13:13:00 +0300

Mageshwaran Subramanian

Data Migration Consultant at Utopia

Bengaluru Area, India

I'd like to add you to my professional network on LinkedIn.

- Mageshwaran

Confirm that you know Mageshwaran

You received an invitation to connect. LinkedIn will use your email address to make suggestions to our members in features like People You May Know. Unsubscribe

Learn why we included this.

If you need assistance or have questions, please contact LinkedIn Customer Service.

creating an Identity column

noreply@blogger.com (kishore kumar P) — Tue, 1 Mar 2011 09:14:00 +0300

This will create an identity column in the following table.

CREATE TABLE dbo.MyTable (    ID INT IDENTITY(1,1)    ,MyShortDesc varchar(20)     ,MyLongDesc varchar(500)    );

Data file locations

noreply@blogger.com (Unknown) — Thu, 3 Jun 2010 14:13:00 +0300

Here's a query which displays the .mdf and .ldf location of all the databases in the server.

SELECT physical_name
FROM master.sys.master_files

Sequence number within a group

noreply@blogger.com (Unknown) — Sat, 10 Apr 2010 15:10:00 +0300

The following query can be used to generate the sequence numbers within a group.

select

GROUP_ID

, GROUP_COUNTER= ROW_NUMBER()

OVER ( PARTITION BY [GROUP_ID]

ORDER BY [GROUP_ID]

)

from GROUP_TABLE

This query can generate a output like the following.

GROUP_ID GROUP_COUNTER

-------------------------------------------

AAA 1

AAA 2

AAA 3

BBB 1

BBB 2

CCC 1

SQL Server Video tutorials

noreply@blogger.com (Thinking SQL Team) — Tue, 20 Oct 2009 14:31:00 +0300

There are some good video tutorials available here.Check it out.

An interesting article - Selecting Duplicate Rows in a table

noreply@blogger.com (Thinking SQL Team) — Sun, 18 Oct 2009 13:08:00 +0300

Hi All, we are back after a pause.Here's an interesting article which talks about the different ways of selecting the duplicate rows from a table.Hope it will be useful to you.

Article

Question of the day

noreply@blogger.com (Mohammad Shahanshah Ansari) — Fri, 25 Sep 2009 11:37:00 +0300

Can you instantiate a COM object by using T-SQL?

1. True
2. False

T-SQL best practices - 4

noreply@blogger.com (Thinking SQL Team) — Wed, 23 Sep 2009 23:59:00 +0300

- The dynamic SQL Performs slower because the optimizer has to create an execution plan for every execution.

-Use table aliasing, even if there is no joining in the query. Aliasing eliminates ambiguity, eases subsequent modification, and further reduces the chance of a typo not being recognized.

-When querying a table, prefix it with the owner (schema), typically dbo. There is a little performance improvement and query becomes more readable

Question of the Day

noreply@blogger.com (Thinking SQL Team) — Wed, 23 Sep 2009 07:27:00 +0300

What are the disadvantages of cursors?

1. Cursor results in a network roundtrip
2. Cursor requires more resources and temporary storage
3. Cursor results in more IO operations
4. 1 & 2
5. 1, 2 & 3
6. None

T-SQL best practices - 3

noreply@blogger.com (Thinking SQL Team) — Tue, 22 Sep 2009 22:02:00 +0300

Today's tips are not really related to T-SQL,but I hope these will improve the way you approach SQL SERVER.

- Get Certified

- Subscribe to News Groups and blogs to get regular updates

- Read White papers

T-SQL best practices - 2

noreply@blogger.com (Thinking SQL Team) — Mon, 21 Sep 2009 21:38:00 +0300

Today's best practice techniques are

- NOLOCK helps in concurrency and performance but can be dangerous for data integrity

- Use XML datatype to pass table valued parameters to procedures.

- Use SQL Server Management Console with integrated Source Control to do the development

T-SQL best practices - 1

noreply@blogger.com (Thinking SQL Team) — Sun, 20 Sep 2009 15:45:00 +0300

Hi All!...we decided to write some posts on the best practices in writing T-SQL,which we think helpful to the newbies.We will list three best practices each day for the next seven days and hope this series will be helpful to all the readers.So today's best practices tips are,

Document your database

–Use Extended properties of the table structures to document the database.

–Use tools to generate the documentation and correct where necessary

–Maintain the script for populating the extended properties.

Use Cursors only when

–You cannot find a set based solution in 4 Hours

–You want to utilize already existing logic in other SP

–You are writing a database maintenance task

Use STATISTICS IO ON

–This will reduce the logical reads

We need your comments on this post and share the best practices that you are using in the script(related to the above 3 techniques).

Left Padding(LPAD) using REPLICATE

noreply@blogger.com (Thinking SQL Team) — Sat, 19 Sep 2009 14:30:00 +0300

Here's a SQL UDF to do the left padding using REPLICATE.

create function [dbo].[leftpad]

(

@csrting nvarchar(4000)

, @nlen smallint

, @cpadcharacter nvarchar(4000) = ' '

)

returns nvarchar(4000)

begin

declare @nleftlen smallint, @nrightlen smallint

declare @length smallint, @lenpadcharacter smallint

select @length = datalength(@csrting)/(case sql_variant_property(@csrting,'basetype') when 'nvarchar' then 2 else 1 end)

select @lenpadcharacter = datalength(@cpadcharacter)/(case sql_variant_property(@cpadcharacter,'basetype') when 'nvarchar' then 2 else 1 end) -- for unicode

if @length >= @nlen

set @csrting = left(@csrting, @nlen)

else

begin

set @nleftlen = @nlen - @length

set @csrting = left(replicate(@cpadcharacter, ceiling(@nleftlen/@lenpadcharacter) + 2), @nleftlen)+ @csrting

end

return (@csrting)

end

Working with Hijri Date in SQL server

noreply@blogger.com (Unknown) — Sat, 12 Sep 2009 15:00:00 +0300

Here's an example to convert the Hijri Date to Gregorian date in SQL Server

1430/09/23 is today's Hijri date.Lets see how to convert this into Gregorian date

SELECT CONVERT(DATETIME, '23/09/1430', 131)

It would return 2009-09-12 00:00:00.000

The vice-versa,conversion from Gregorian date to Hijri date would be,

SELECT CONVERT(NCHAR, GETDATE(), 131).And this returns 23/09/1430 2:52:17:920PM

Top 10 SQL server Performance tuning tips - 10

noreply@blogger.com (Unknown) — Sat, 12 Sep 2009 04:00:00 +0300

Use of performance monitoring tools:

SQL server ships with many tools like Profiler,Database Engine Tuning Advisor,Index Tuning Wizard,PerfMon,SysMon.Make use of these tools to moniter the performance of your query and take the nessesary actions,if required.

Well, with this post we come to an end of our series of Performance Tuning Steps.I hope its helpful to you to you and we will discuss each steps in detail in coming days.

Top 10 SQL server Performance tuning tips - 9

noreply@blogger.com (Unknown) — Fri, 11 Sep 2009 04:00:00 +0300

Use of NOLOCK hint

NOLOCK hint is useful to improve the query performance when selecting the rowset from a table which is being accessed by multiple transaction.But this leads to dirty read.A dirty read is where a query reads data from the database without lock protection. Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running.

Top 10 SQL server Performance tuning tips - 8

noreply@blogger.com (Unknown) — Thu, 10 Sep 2009 04:00:00 +0300

Reduce the NULLs

Reduce the use of NULLs in the database. The use of NULLs in a database can greatly reduce database performance, especially in WHERE clauses.Highly normalized database can achieve this.

Top 10 SQL server Performance tuning tips - 7

noreply@blogger.com (Unknown) — Wed, 9 Sep 2009 04:00:00 +0300

Remove unwanted columns from the SELECT statement

In queries, don't return column data you don't need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column. In addition, using SELECT * may prevent the use of covered indexes, further potentially affect query performance.

Finding long running queries in SQL server

noreply@blogger.com (Unknown) — Tue, 8 Sep 2009 22:58:00 +0300

Here's a query to find the longest running queries.This works in SQL Server 2005 and 2008.

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0)AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC

Top 10 SQL server Performance tuning tips - 6

noreply@blogger.com (Unknown) — Tue, 8 Sep 2009 04:00:00 +0300

Set Based Approach

Even though procedural approach is easier for most of the programmers,it (Use of Cursor, or, UDF to process rows in a result set) should be avoided when working with large result set.Most of the times cursors can be replaced by CTE(Common Table Expression). Avoiding Cursors with SQL Server 2005

Top 10 SQL server Performance tuning tips - 5

noreply@blogger.com (Unknown) — Mon, 7 Sep 2009 04:00:00 +0300

Avoid cursors

Avoid using cursors.It may result in Deadlocks.Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. During this kind of situation, SQL Server identifies the trouble and ends the deadlock by automatically choosing one process and aborting the other process, allowing the other process to continue.

Top 10 SQL server Performance tuning tips - 4

noreply@blogger.com (Unknown) — Sun, 6 Sep 2009 05:00:00 +0300

Use of Indexes

NON CLUSTERED index should be created on a highly selective field.
Order of the fields in the WHERE clause should be same as the index order.
Every table in the DB should have a CLUSTERED index.
Primary Key may not be suitable for clustered index if the table is extensively used for SELECT.