Tag: sql-server-2005

Parameter Sniffing (or Spoofing) in SQL Server

A while ago I had a query that I ran quite a lot for one of my users. It was still being evolved and tweaked but eventually it stablised and ran quite quickly, so we created a stored procedure from it. So far, so normal. The stored procedure, though, was dog slow. No material difference […]

Does T-SQL have an aggregate function to concatenate strings?

Possible Duplicates: Implode type function in SQL Server 2000? Concatenate row values T-SQL I have a view which I’m querying that looks like this: BuildingName PollNumber ———— ———- Foo Centre 12 Foo Centre 13 Foo Centre 14 Bar Hall 15 Bar Hall 16 Baz School 17 I need to write a query that groups BuildingNames […]

Select top 10 records for each category

I want to return top 10 records from each section in one query. Can anyone help with how to do it? Section is one of the columns in the table. Database is SQL Server 2005. I want to return the top 10 by date entered. Sections are business, local, and feature. For one particular date […]

best way to convert and validate a date string

I have a single char(8) variable formatted as ddmmyyyy in a stored procedure (quality and validity of this value is unknown and beyond my control). What is the best most efficient way to move the value into a datetime variable, and throw an error if it is not valid datetime. DECLARE @Source char(8) DECLARE @Destination […]

The multi-part identifier could not be bound

I’ve seen similar errors on SO, but I don’t find a solution for my problem. I have a SQL query like: SELECT DISTINCT a.maxa , b.mahuyen , a.tenxa , b.tenhuyen , ISNULL(dkcd.tong, 0) AS tongdkcd FROM phuongxa a , quanhuyen b LEFT OUTER JOIN ( SELECT maxa , COUNT(*) AS tong FROM khaosat WHERE CONVERT(DATETIME, […]

T-SQL: Opposite to string concatenation – how to split string into multiple records

Possible Duplicate: Split string in SQL I have seen a couple of questions related to string concatenation in SQL. I wonder how would you approach the opposite problem: splitting coma delimited string into rows of data: Lets say I have tables: userTypedTags(userID,commaSeparatedTags) ‘one entry per user tags(tagID,name) And want to insert data into table userTag(userID,tagID) […]

Concatenate values based on ID

I Have a table called Results and the data looks like: Response_ID Label 12147 It was not clear 12458 Did not Undersstand 12458 Was not resolved 12458 Did not communicate 12586 Spoke too fast 12587 Too slow Now I want the ouput to display one row per ID and the values from Label to be […]

SET versus SELECT when assigning variables?

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?

Do I really need to use “SET XACT_ABORT ON”?

if you are careful and use TRY-CATCH around everything, and rollback on errors do you really need to use: SET XACT_ABORT ON In other words, is there any error that TRY-CATCH will miss that SET XACT_ABORT ON will handle?

Is it possible to force row level locking in SQL Server?

I can see how to turn off row level and page level locking in SQL Server, but I cannot find a way to force SQL Server to use row level locking. Is there a way to force SQL Server to use row level locking and NOT use page level locking?

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.