Add OPTION (RECOMPILE) to the end of the query. SQL Server doesn’t “sniff” the values of the variables so you will be getting a plan based on guessed statistics rather than one tailored for the actual variable values.
I learned something new, just like you as well I believe the difference is between a “String Sort” vs a “Word Sort” (ignores hyphen) Sample difference between WORD sort and STRING sort http://andrusdevelopment.blogspot.com/2007/10/string-sort-vs-word-sort-in-net.html From Microsoft http://support.microsoft.com/kb/322112 For example, if you are using the SQL collation “SQL_Latin1_General_CP1_CI_AS”, the non-Unicode string ‘a-c’ is less than the string … Read more
Yes, SQL Server rounds time to 3.(3) milliseconds: SELECT CAST(CAST(‘2009-01-01 00:00:00.000’ AS DATETIME) AS BINARY(8)) SELECT CAST(CAST(‘2009-01-01 00:00:01.000’ AS DATETIME) AS BINARY(8)) 0x00009B8400000000 0x00009B840000012C As you can see, these DATETIME‘s differ by 1 second, and their binary representations differ by 0x12C, that is 300 in decimal. This is because SQL Server stores the time part … Read more
MERGE was introduced in SQL Server 2008. If you want to use that syntax, you’ll need to upgrade. Otherwise, the typical approach will depend on where the source data is from. If it’s just one row and you don’t know if you need to update or insert, you’d probably do: UPDATE … WHERE key = … Read more
Try this WITH cte AS ( SELECT *,COUNT(1) OVER(PARTITION BY cnt) pt FROM ( SELECT tt.* ,(SELECT COUNT(id) FROM t WHERE data <= 10 AND ID < tt.ID) AS cnt FROM t tt WHERE data > 10 ) t1 ) SELECT id, [when], data FROM cte WHERE pt >= 3 SQL FIDDLE DEMO OUTPUT id … Read more
The best way to strip the time portion of a datetime field is using datediff and dateadd functions. DateAdd(day, datediff(day,0, MydateValue), 0) This takes advantedge of the fact that SQL Server stores dates as two integers, one representing the number of days since day “0” – (1 jan 1900), and the second one which represents … Read more
Is the user an SA, if so it will not work, according to the documentation SA users are always defaulted to the dbo schema. The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of … Read more
EXCEPT treats NULL values as matching. This query: WITH q (value) AS ( SELECT NULL UNION ALL SELECT 1 ), p (value) AS ( SELECT NULL UNION ALL SELECT 2 ) SELECT * FROM q WHERE value NOT IN ( SELECT value FROM p ) will return an empty rowset. This query: WITH q (value) … Read more
With MSSQL you can do something like this: declare @result varchar(500) set @result=”” select @result = @result + ModuleValue + ‘, ‘ from TableX where ModuleId = @ModuleId
If the two databases are on the same server, you should be able to create a SQL statement something like this: UPDATE Test1.dbo.Employee SET DeptID = emp2.DeptID FROM Test2.dbo.Employee as ’emp2′ WHERE Test1.dbo.Employee.EmployeeID = emp2.EmployeeID From your post, I’m not quite clear whether you want to update Test1.dbo.Employee with the values from Test2.dbo.Employee (that’s what … Read more