There are several considerations when writing a query using the IN operator that can have an affect on performance. First, IN clauses are generally internally rewritten by most databases to use the OR logical connective. So col IN (‘a’,’b’,’c’) is rewritten to: (COL = ‘a’) OR (COL = ‘b’) or (COL = ‘c’). The execution … Read more
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders JOIN LineItems ON LineItems.LineItemGUID = ( SELECT TOP 1 LineItemGUID FROM LineItems WHERE OrderID = Orders.OrderID ) In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY: SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description FROM Orders CROSS APPLY ( SELECT TOP 1 LineItems.Quantity, LineItems.Description FROM LineItems WHERE … Read more
First of all, it’s just bad practice. Input validation is always necessary, but it’s also always iffy. Worse yet, blacklist validation is always problematic, it’s much better to explicitly and strictly define what values/formats you accept. Admittedly, this is not always possible – but to some extent it must always be done. Some research papers … Read more
Take a look at SQL Server – Set based random numbers which has a very detailed explanation. To summarize, the following code generates a random number between 0 and 13 inclusive with a uniform distribution: ABS(CHECKSUM(NewId())) % 14 To change your range, just change the number at the end of the expression. Be extra careful … Read more
For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version. To check if a table exists use: IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘TheSchema’ AND TABLE_NAME = ‘TheTable’)) BEGIN –Do Stuff END
Add a column with a default value to an existing table in SQL Server