NULLIF

invert function "isnull()"
important "nullif" not support null value you make sure value is not null

table sample percent sql server

The rows retrieved are random and they are are not in any order
Example 1:
SELECT FirstName,LastNameFROM Person.Contact
TABLESAMPLE SYSTEM (10 PERCENT)
Example 2:
SELECT FirstName,LastNameFROM Person.Contact
TABLESAMPLE SYSTEM (1000 ROWS)

http://blog.sqlauthority.com/2007/05/27/sql-server-2005-limiting-result-sets-by-using-tablesample-examples/

Sequence install SQL Server and VS (Visual Studio)

You should install SQL 2008 Developer first, this will rule out the need for VS installing SQL which comes with it. Or you could do like others suggested and choose custom VS installation.

My favorite way is this:

1. SQL 2008 Developer
2. Visual Studio Professional 2008
3. Run Windows Updates
4. Install Resharper :-)
5. Install RedGate SQL ToolBelt

But it seems that to get some features to work the proper order is:

1. Visual Studio Professional 2008 (with SQL Unchecked)
2. SQL 2008 Developer
3. Run Windows Updates
4. Install Resharper/Redgate Tools

Resharper and Redgate in my opinion are far the best tools for developing in C#/SQL.

http://stackoverflow.com/questions/2473359/visual-studio-and-sql-server-correct-installation-sequence

Diff convert cast

Data type conversion has been one of the most used features of any programming language. Microsoft SQL Server users are familiar with the two functions: CAST() and CONVERT() and use them regularly, and more specifically, interchangeably. A few months ago, I had written a post comparing the performance of CAST v/s CONVERT, and was able to prove that there is no difference in performance of the two functions.You can read that post here: http://beyondrelational.com/blogs/nakul/archive/2011/01/03/cast-v-s-convert.aspx.

Recently, a colleague and I were discussing a particular design element and found that a data type casting was required. During this discussion, the following questions almost simultaneously came to our minds –

• If the performance and end results are the same, what is the difference between CAST & CONVERT aside from the fact that they have different syntax elements?
• Do we really need 2 data conversion functions?

Parameter	CAST	CONVERT
ANSI standard	Yes	No
Data-type coverage	Limited	Full (Date & Date Time values supported)
Performance	No difference	No difference
Microsoft SQL Server implementation	??	??
Books On Line Page	http://msdn.microsoft.com/en-us/library/ms187928.aspx	http://msdn.microsoft.com/en-us/library/ms187928.aspx

So, I ran a little test, and today, I will share with you my findings.

The CAST v/s CONVERT Test

The test is quite simple – we have two identical T-SQL statements, one using CAST, the other using CONVERT. We will use the Properties window of SSMS to analyze the execution plan and try to see what can be found about the underlying implementation.

Let’s take a look at our test statements:

01./***********************************************************

02.Press Ctrl+M (or go to Query->Include Actual Execution Plan)

03.************************************************************/

04.USE AdventureWorks2008R2

05.GO

06.--Use CONVERT

07.select CONVERT(VARCHAR(10),BusinessEntityID) FROM HumanResources.Employee

08. 

09.--Use CAST

10.select CAST(BusinessEntityID AS VARCHAR(10)) FROM HumanResources.Employee

1. Connect to your SQL Server instance using SQL Server Management Studio (SSMS)
2. Copy the T-SQL code above into SSMS Query editor window
3. Run the above T-SQL statements against your SQL Server instance
4. Change over to the Execution Plan tab in the Results pane
5. In the results pane, notice that both CAST & CONVERT have been implemented as “Compute Scalar” operators
6. 
7. Press the F4 key or go to View –> Properties to launch the properties window
8. Expand the “Defined Values” node

CONVERT implementation

CONVERT does not come up with any surprises, and has a straightforward internal implementation as demonstrated in the Properties window.

CAST implementation

When we move to the properties for the query using the CAST operation, we see that under the hood, SQL Server does take us for a ride. Internally, CAST is implemented as a CONVERT call. There is no difference between CAST & CONVERT besides the fact that CAST is an ANSI standard, while CONVERT is not. No wonder both CAST & CONVERT demonstrate the same performance.

Surprised? Try it for yourself - I was not prepared to see what I saw when I first ran through the test. I restarted the entire server and got a cup of coffee to make sure I was not dreaming!

Conclusion

I guess all I need to do now is to complete the little grid I had above:

Parameter	CAST	CONVERT
ANSI standard	Yes	No
Data-type coverage	Limited	Full (Date & Date Time values supported)
Performance	No difference	No difference
Microsoft SQL Server implementation	CONVERT	CONVERT
Books On Line Page	http://msdn.microsoft.com/en-us/library/ms187928.aspx	http://msdn.microsoft.com/en-us/library/ms187928.aspx

What would I recommend?

Although we now know that to Microsoft SQL Server, data type conversion only happens via CONVERT, I would still prefer to go with CAST unless I am converting dates or date-time values. The reason being CAST is an ANSI standard, CONVERT is not. CONVERT is implementation specific, and therefore may change in terms of the number of parameters or underlying processes. To the calling T-SQL statement, it is safe to assume that CAST would remain unchanged (unless the standard changes) and therefore, it would be the responsibility of Microsoft SQL Server to translate the CAST to a CONVERT implementation.

Do share your thoughts on the whole CAST v/s CONVERT myths and controversies floating around in the SQL Server world. I will be more than happy to research more and share my findings with you.

Until we meet next time,

http://beyondrelational.com/modules/2/blogs/77/posts/11334/cast-vs-convert-is-there-a-difference-as-far-as-sql-server-is-concerned-which-is-better.aspx

Get Quarter Number ( use datename function )

SQL DATENAME Function - Year, Quarter, Month, Day, Hour, Minute and Milisecond.

It return specifies the part of the date name and the DATENAME for Date Time such as Year, Quarter, Month, Day, Hour, Minute and Milisecond.

SQL DATENAME SyntaxDATENAME ( datepart , date )

Example

SELECT GETDATE() = 2007-05-30 23:13:38.763

SELECT DATENAME(year, GETDATE()) 
SELECT DATENAME(yy, GETDATE()) 
SELECT DATENAME(yy, GETDATE()) 
- It will return value = 2007

SELECT DATENAME(quarter, GETDATE()) 
SELECT DATENAME(qq, GETDATE()) 
SELECT DATENAME(q, GETDATE()) 
-It will return value = 2 (because 1 quarter equal to 3 month,Detail see below table)

Month	Quarter Value
January - March	1
April - June	2
July - September	3
October - December	4

SELECT DATENAME(month, GETDATE()) 
SELECT DATENAME(mm, GETDATE())
SELECT DATENAME(m, GETDATE()) 
- It will return value = May

SELECT DATENAME(dayofyear, GETDATE()) 
SELECT DATENAME(dy, GETDATE()) 
SELECT DATENAME(y, GETDATE()) 
- It will return value = 150 (this is calculate total day from 1 jan 2007 until 30 may 2007)

SELECT DATENAME(day, GETDATE()) 
SELECT DATENAME(dd, GETDATE()) 
SELECT DATENAME(d, GETDATE()) 
- It will return value = 30

SELECT DATENAME(week, GETDATE())
SELECT DATENAME(wk, GETDATE())
SELECT DATENAME(ww, GETDATE())
- It will return value = 23 (this is 23rd week from 1 jan 2007)

SELECT DATENAME(hour, GETDATE())
SELECT DATENAME(hh, GETDATE())
- It will return value = 23 (time for 24 hour)

SELECT DATENAME(minute, GETDATE())
SELECT DATENAME(mi, GETDATE())
SELECT DATENAME(n, GETDATE())
- It will return value = 13 (minute)

SELECT DATENAME(second , GETDATE())
SELECT DATENAME(ss, GETDATE())
SELECT DATENAME(s, GETDATE())
- It will return value = 38 (second)

SELECT DATENAME(millisecond , GETDATE())
SELECT DATENAME(ms, GETDATE())
- It will return value = 763 (milisecond)

http://sqltutorials.blogspot.com/2007/05/sql-datename.html

concat string many row to single row

SubjectID       StudentName

----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

Select Main.SubjectID,
       Left(Main.Students,Len(Main.Students)-1) As "Students"
From(Select distinct ST2.SubjectID, 
           (Select ST1.StudentName + ',' AS [text()]
            From dbo.Students ST1
            Where ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            For XML PATH ('')) [Students]
     From dbo.Students ST2) [Main]

http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

เอาค่าหลายๆ record มาต่อกันเป็น record เดียว

update join sql server

UPDATE
    Table
SET
    Table.col1 = other_table.col1,
    Table.col2 = other_table.col2
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id

http://stackoverflow.com/questions/2334712/sql-server-update-from-select