When you perform an insert in a row the DB, it will be inserted in order relative to the other PKs in the table. With a normal guid, this could be anywhere in the table. A newsequentialid() will always be added to the end of the table. So the performance of inserts is improved. This … Read more
Escape single quote with an additional single as Kirtan pointed out And if you are trying to execute a dynamic sql (which is not a good idea in the first place) via sp_executesql then the below code would work for you sp_executesql N’INSERT INTO SomeTable (SomeColumn) VALUES (”John””s”)’
There’s a general consensus that you should reorganize (“defragment”) your indices as soon as index fragmentation reaches more than 5 (sometimes 10%), and you should rebuild them completely when it goes beyond 30% (at least that’s the numbers I’ve heard advocated in a lot of places). Michelle Ufford (a.k.a. “SQL Fool”) has an automated index … Read more
You don’t mention if the columns are nullable. If they are and you want the same semantics that the AVG aggregate provides you can do (2008) SELECT *, (SELECT AVG(c) FROM (VALUES(R1), (R2), (R3), (R4), (R5)) T (c)) AS [Average] FROM Request The 2005 version is a bit more tedious SELECT *, (SELECT AVG(c) FROM … Read more
CASE AlarmEventTransactions.DeviceID should just be CASE. You are mixing the 2 forms of the CASE expression.
You are just missing the words “primary key” as far as I can see to meet your specified objective. For your other columns it’s best to explicitly define whether they should be NULL or NOT NULL though so you are not relying on the ANSI_NULL_DFLT_ON setting. CREATE TABLE #tmp ( ID INT IDENTITY(1, 1) primary … Read more
One way to do it if tagID values are known upfront is to use conditional aggregation SELECT TimeSeconds, COALESCE(MAX(CASE WHEN TagID = ‘A1’ THEN Value END), ‘n/a’) A1, COALESCE(MAX(CASE WHEN TagID = ‘A2’ THEN Value END), ‘n/a’) A2, COALESCE(MAX(CASE WHEN TagID = ‘A3’ THEN Value END), ‘n/a’) A3, COALESCE(MAX(CASE WHEN TagID = ‘A4’ THEN Value … Read more
There are situations where a person may not want well formed XML – the one I (and perhaps the original poster) encountered was using the For XML Path technique to return a single field list of ‘child’ items via a recursive query. More information on this technique is here (specifically in the ‘The blackbox XML … Read more
This is the footprint of parameter-sniffing. See here for another discussion about it; SQL poor stored procedure execution plan performance – parameter sniffing There are several possible fixes, including adding WITH RECOMPILE to your stored procedure which works about half the time. The recommended fix for most situations (though it depends on the structure of … Read more
The set operation you are looking for is called MINUS, but in SQL Server the keyword is EXCEPT SELECT … // all documents EXCEPT SELECT … // active documents I believe that the EXCEPT set operation became available in SQL Server 2005.