Category Archives: SQL server

String concatenation gotcha

In SQL Server, there are a few ways to concatenate string values from a column in a table, e.g. using FOR XML TYPE method. The following example is another method we all use: USE tempdb; GO CREATE TABLE MyTestTable (id … Continue reading

Posted in SQL server | Leave a comment

T-SQL Suggestion: Support GROUP_CONCAT

MySQL has a handy aggregation function GROUP_CONCAT to concatenate string when grouping data. AFAIK, SQL Server lacks the similar functionality. So I created a MS connect item for this feature support. Please vote at: https://connect.microsoft.com/SQLServer/feedback/details/764820/support-group-concat-aggregation-function

Posted in SQL server | Leave a comment

Stamp records with consecutive dates

For the following data table, the requirement is to assign/stamp the same unique number to the Group column if EndDate/StartDate is consecutive for the same Medicine.ID    Medicine  StartDate       EndDate        Group1     1              19900601      19940630       02     1              19900712      19940606       03     1              … Continue reading

Posted in SQL server | Leave a comment

Set First Day of the Week Globally

The first day of the week varies in different culture, e.g. in US, Sunday is the first day of the week, in Great Briton, it is the Monday. SQL Server has a command to set first day of the week: … Continue reading

Posted in SQL server | Leave a comment

Shrink Database Log file

There are some misunderstandings about shrinking database log file. One mysterious is that sometimes you cannot shrink a log file though it is barely used.   We can use DBCC SHRINKFILE or Tasks–>Shink–>Files menu in SSMS to shrink database log … Continue reading

Posted in SQL server | 1 Comment

LEN and DATALENGTH with varbinary

varbinary data types store strings of bits. Binary constants have a leading 0x (a zero and the lowercase letter x) followed by the hexadecimal representation of the bit pattern, e.g. 0x2B1D. Both LEN and DATALENGTH can be used with varbinary … Continue reading

Posted in SQL server | 1 Comment

Linked server and XML

If you call a stored procedure in a linked server to return xml, you may not get what you expected.Let’s define a linked server first: exec sp_addlinkedserver ‘MyServer’, ”, N‘SQLOLEDB’, ‘MyRemoteServer’, ”,”,‘Northwind’ exec sp_addlinkedsrvlogin @rmtsrvname = ‘MyServer’, @useself = ‘true’ … Continue reading

Posted in SQL server | 2 Comments