SQL Server does not cache the query results, but it caches the data pages it reads in memory. The data from these pages is then used to produce the query result. You can easily see if the data was read from memory or from disk by setting SET STATISTICS IO ON Which returns the following … Read more
You don’t say what version of SQL Server. If SQL Server 2008 you can use MERGE NB: It is usual to use Merge for an Upsert which is what I originally thought the question was asking but it is valid without the WHEN MATCHED clause and just with a WHEN NOT MATCHED clause so does … Read more
#tablename is a physical table, stored in tempdb that the server will drop automatically when the connection that created it is closed, @tablename is a table stored in memory & lives for the lifetime of the batch/procedure that created it, just like a local variable. You can only add a (non PK) index to a … Read more
I see this post is pretty old and there are some useful answers but this is a pretty crazy straight forward method… Select SUM(DISTINCT(n & 0x01)) + SUM(DISTINCT(n & 0x02)) + SUM(DISTINCT(n & 0x04)) as OrN From BitValues
It indicates a “nationalized” a.k.a. unicode string constant. http://support.microsoft.com/kb/239530 When dealing with Unicode string constants in SQL Server you must precede all Unicode strings with a capital letter N, as documented in the SQL Server Books Online topic “Using Unicode Data”. http://msdn.microsoft.com/en-us/library/aa276823%28SQL.80%29.aspx nchar and nvarchar Character data types that are either fixed-length (nchar) or variable-length … Read more
This is likely not a problem of indexing, but one of data design. Normalization, to be precise. The fact that you need to query distinct values of a field, and even willing to add an index, is a strong indicator that the field should be normalized into a separate table with a (small) join key. … Read more
WITH q AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY crew, dayType ORDER BY [date]) AS rnd, ROW_NUMBER() OVER (PARTITION BY crew ORDER BY [date]) AS rn FROM mytable ) SELECT MIN([date]), MAX([date]), crew AS name, dayType FROM q GROUP BY crew, dayType, rnd – rn This article may be of interest to you: Things … Read more
The query you have will give the error Msg 8156, Level 16, State 1, Line 59 The column ‘AnotherKey’ was specified multiple times for ‘tmpTable’. That is because you are using * in the using clause and AnotherKey is part of both table2 and table3. Specify the columns you need. Also there is no use … Read more
You want insert into . . . select: INSERT INTO [DB_A].[dbo.a_test](a,b,c,d,e) –ADDED A COLUMN select p.product_info, p.product_date, p.smth, pr.program_name, pr.program_smth FROM [DB_B].dbo.products p LEFT JOIN [DB_B].dbo.program pr ON p.program_name = pr.product_info; I also fixed the query to use table aliases, so it is much easier to read.
Using collations eg: if (‘a’=’A’ Collate Latin1_General_CI_AI) print’same 1′ else print ‘different 1’ if (‘a’=’A’ Collate Latin1_General_CS_AI) print’same 2′ else print ‘different 2’ The CS in the collation name indicates Case Sensitive (and CI, Case Insensitive). The AI/AS relates to accent sensitivity. or in your example SUBSTRING(author,1,1) <> LOWER(SUBSTRING(author,1,1)) COLLATE Latin1_General_CS_AI