Hot answers tagged

681

Contents Caveat This answer discusses "classic" table variables introduced in SQL Server 2000. SQL Server 2014 in memory OLTP introduces Memory-Optimized Table Types. Table variable instances of those are different in many respects to the ones discussed below! (more details). Storage Location No difference. Both are stored in tempdb. I've seen it ...


104

The MERGE statement has a complex syntax and an even more complex implementation, but essentially the idea is to join two tables, filter down to rows that need to be changed (inserted, updated, or deleted), and then to perform the requested changes. Given the following sample data: DECLARE @CategoryItem AS TABLE ( CategoryId integer NOT NULL, ...


73

Let's start with the basic scenario. If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP. First, let's consider the whole set from Production.TransactionHistory for a particular ProductID: SELECT h.TransactionID, h.ProductID, h.TransactionDate FROM Production.TransactionHistory h WHERE h.ProductID = 800; ...


61

You can not not use transactions in SQL Server (and probably any other proper RDBMS). In the absence of explicit transaction boundaries (begin transaction ... commit) each SQL statement starts a new transaction, which is implicitly committed (or rolled back) after the statement completes (or fails). Transaction simulation suggested by the person who ...


60

If you are using SQL Server you can use the REVERSE() function to check? SELECT CASE WHEN @string = REVERSE(@String) THEN 1 ELSE 0 END AS Palindrome; Including Martin Smith's comment, if you are on SQL Server 2012+ you can use the IIF() function: SELECT IIF(@string = REVERSE(@String),1,0) AS Palindrome;


53

Here are a few methods you can compare. First let's set up a table with some dummy data. I'm populating this with a bunch of random data from sys.all_columns. Well, it's kind of random - I'm ensuring that the dates are contiguous (which is really only important for one of the answers). CREATE TABLE dbo.Hits(Day SMALLDATETIME, CustomerID INT); CREATE ...


52

Use BCP utility bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T The -c argument specifies character output, as opposed to SQL's native binary format; this defaults to tab-separated values, but -t , changes the field terminator to commas. -T specifies Windows authentication ("trusted connection"), ...


51

Before answering when to use it and why, it's first paramount in understanding exactly what GO is, and what it isn't. The keyword GO is used by SQL Server Management Studio and SQLCMD in order to signify one thing and only one thing: The end of a batch of statements. In fact, you can even change what you use to terminate batches to something other than "...


47

The typical way to do this in SQL Server 2005 and up is to use a CTE and windowing functions. For top n per group you can simply use ROW_NUMBER() with a PARTITION clause, and filter against that in the outer query. So, for example, the top 5 most recent orders per customer could be displayed this way: DECLARE @top INT; SET @top = 5; ;WITH grp AS ( ...


46

•Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE. Reference http://msdn.microsoft.com/en-us/library/ms181765.aspx This is standard SQL behaviour: A CASE expression evaluates to the first true condition. If there is no true condition, it evaluates to the ELSE part. If there is no true condition and no ...


46

This might be the cleaner approach you're after. Basically, check if the variable has been initialized yet. If it hasn't, set it to the empty string, and append the first city (no leading comma). If it has, then append a comma, then append the city. DECLARE @col nvarchar(MAX); SELECT @col = COALESCE(@col + ',', '') + city FROM dbo.tbl WHERE state = '...


44

Well, identifiers are always Unicode / NVARCHAR, so technically you can't create anything that doesn't have a Unicode name 🙃. The problem you are having here is due entirely to the classification of the character(s) being used. The rules for regular (i.e. non-delimited) identifiers are: First letter must be: A letter as defined by the Unicode ...


43

The following query uses the same idea as in this amazing answer by ypercube: SELECT x.* FROM (SELECT NULL AS SomeCol) AS dummy CROSS APPLY ( SELECT ID, SomeCol AS MyTest FROM dbo.Customers ) AS x; It works like this: if dbo.Customers has a column named SomeCol, then SomeCol in SomeCol AS MyTest will resolve as dbo.Customers.SomeCol; if the ...


42

Great question, Paul! I used a couple different approaches, one in T-SQL and one in CLR. T-SQL quick summary The T-SQL approach can be summarized as the following steps: Take the cross-product of products/dates Merge in the observed sales data Aggregate that data to the product/date level Compute rolling sums over the past 45 days based on this ...


40

There are a few things I'd like to point out based more on particular experiences rather than study. As a DBA, I'm very new so please do correct me where required. #temp tables by default use the SQL Server instance's default collation. So unless otherwise specified, you might run into problems comparing or updating values between #temp tables and database ...


40

The UCS-2 encoding is always 2 bytes per character and has a range of 0 - 65535 (0x0000 - 0xFFFF). UTF-16 (regardless of Big Endian or Little Endian) has a range of 0 - 1114111 (0x0000 - 0x10FFFF). The 0 - 65535 / 0x0000 - 0xFFFF range of UTF-16 is 2 bytes per character while the range above 65536 / 0xFFFF is 4 bytes per character. Windows and SQL Server ...


39

Easy: GRANT EXECUTE TO LowlyDBA Or, I guess in this case it'd be grant execute to lowlydba Take your pick of variations on this. In all likelihood you may be able to test this now against your current system, but any number of small changes in the database over time could invalidate your testing. The character string could change, someone could create ...


38

If the procedure just returns one result set and the ad hoc distributed queries option is enabled. SELECT * INTO #T FROM OPENROWSET('SQLNCLI', 'Server=(local)\MSSQL2008;Trusted_Connection=yes;', 'SET FMTONLY OFF;EXEC sp_who') Or you can set up a loopback linked server and use that instead. EXEC sp_addlinkedserver @...


36

No, unfortunately table value parameters are read-only and input only. This topic in general is covered very well in How to Share Data between Stored Procedures, which presents all the alternatives. My recommendation would be to use a #temp table.


36

Yes, it will influence initial plan compile time as the optimizer will have many extra access paths to the data to consider. Since you're on SQL Server 2017, loading once, and running reports, why not just use a clustered column store index instead? That seems to be the ideal solution to your need to index every possible column combination. Columnstore ...


33

There is obviously some communication going on between SQL Server and the client that directly is dependent on the number of statements executed. Yes, there is. By default, SQL Server sends a TDS DONE_IN_PROC message after every statement in a stored procedure. The message communicates status and row count information for the completed statement to the ...


33

It determines the logical tables involved in the join. With a simple example SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName FROM #widgets1 w1 LEFT JOIN #widgets2 w2 ON w2.WidgetID = w1.WidgetID JOIN #widgetProperties wp ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b' ORDER BY w1....


33

One common method is to use the VALUES clause, and CROSS APPLY the two columns aliased as a single column, then get the MIN and MAX of each. SELECT MIN(x.CombinedDate) AS least, MAX(x.CombinedDate) AS greatest FROM dbo.Users AS u CROSS APPLY ( VALUES ( u.CreationDate ), ( u.LastAccessDate )) AS x ( CombinedDate ); There are other ways of writing it, for ...


32

GROUP BY A.* is not allowed in SQL. You can bypass this by using a subquery where you group by, and then join: SELECT A.*, COALESCE(B.cnt, 0) AS Count_B_Foo FROM TABLE1 AS A LEFT JOIN ( SELECT FKey, COUNT(foo) AS cnt FROM TABLE2 GROUP BY FKey ) AS B ON A.PKey = B.FKey ; There is a feature in SQL-2003 standard to allow ...


32

The performance will be the same. The optimizer will recognize this and create the same plan. On the other hand I wouldn't say they are equal. The first form in the question is far more readable and generally expected. For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query. You ...


32

If you look at the FROM clause syntax diagram you will see that there is only one place for the ON clause: <joined_table> ::= { <table_source> <join_type> <table_source> ON <search_condition> ... } What you find confusing is simple recursion, because <table_source> in <joined_table> above can be another <...


32

It is being used as a Modulo Operator; returning the remainder of a number divided by another. In your example, the WHERE clause is limiting the results to only those where the Col1 value divided by 3 leaves a remainder of 1. (e.g. 4,7,10, etc.)


31

That is just documented behavior. I don't think anyone messed with the settings. See data type precedence on MSDN. When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. As noted in the ...


30

The MSDN documentattion page about ALTER TABLE explains these: ALTER TABLE: modify the table's structure (and some of the possible actions/modifications are): CHECK CONSTRAINT ..: enable the constraint NOCHECK CONSTRAINT ..: disable the constraint There are also additional, optional steps to do while creating/enabling/disabling a constraint: WITH CHECK: ...


29

They're integers. Try: select 1.0/2.0; Or: select CONVERT(NUMERIC,1)/CONVERT(NUMERIC,2);


Only top voted, non community-wiki answers of a minimum length are eligible