Hot answers tagged

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 ...


28

There are a lot of different ways to do this. I don't usually recommend inserting into a #temp table, since any tempdb load or autogrowth may impact the results, and I definitely don't recommend using a @table variable, since modifications to those are forced serial (no parallel plan can be used), which may change actual query times. Variable Assignment ...


24

You are ordering rows of data, not each column separately. The row (10, 1) comes before the row (1, 10) because of your ORDER BY clause. The Value2 only comes into play when there is a tie on the first column. As further explained by Hellion, as far as the database is concerned, the pair (10, 1) is an indivisible unit: it's not two values, it's one set (...


21

This statement is legal (in other words, no FROM is required): SELECT x = 1; SELECT x = 1 WHERE 1 = 1; -- also try WHERE 1 = 0; The trick is when you introduce a column name that clearly can't exist. So these fail: SELECT name WHERE 1 = 1; SELECT x = 1 WHERE id > 0; Msg 207, Level 16, State 1 Invalid column name 'name'. Msg 207, Level 16, State ...


19

This is documented in UPDATE (Transact-SQL): SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column. In your code example, sum is the (unwise) name of a column, not an aggregate. db<>fiddle demo


18

You can handle both INSERT and UPDATE with an EXCEPT set operator. The EXISTS will only evaluate to TRUE both if it's just an INSERT, or if it's an UPDATE with different values for any of these columns. IF EXISTS ( SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted EXCEPT SELECT First_Name, Last_Name, JobCoe, ...


15

XML is bonkers When you add the concatenated string, you lose the "path element". For example if you do this: SELECT t.type + '/' AS type FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type ) FOR XML PATH(''); SELECT t.type + '/' FROM ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type ) FOR XML PATH('type'); You get this back: <...


14

There are some errors which are so severe that the CATCH block is never entered. From the documentation Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY...CATCH will handle the error. ...


13

George's answer does solve your problem, but it leaves you wide open to SQL injection attacks. While converting an INT to a VARCHAR 11 is likely not going to cause any issues, sysname is the equivalent of an NVARCHAR 128, and you can jam a lot of extra code in there. To make your code totally safe, you'd want to do this: DECLARE @stringsvar NVARCHAR(1000) ...


12

A common solution to this type of problem is given by Itzik Ben-Gan in his article The Last non NULL Puzzle: DROP TABLE IF EXISTS dbo.Example; CREATE TABLE dbo.Example ( id integer PRIMARY KEY, val integer NULL ); INSERT dbo.Example (id, val) VALUES (1, 136), (2, NULL), (3, 650), (4, NULL), (5, NULL), (6, NULL), (7, ...


12

Probably you're moving the row when you set Deleted=1 and reading it again with your FAST_FORWARD cursor. Use a STATIC cursor instead, which will iterate a copy of the data, and avoid mutating the data structure you are traversing. DECLARE st CURSOR LOCAL STATIC FOR . . .


12

If you are talking about solving this purely in Transact-SQL, you can cover all those cases in your example data with the following filter: WHERE FilteredField LIKE '%[a-zA-Z][0-9]' If you want to additionally stipulate that all the characters before the numeral must be Latin letters, you will need to get a little creative: WHERE FilteredField LIKE ...


12

is there any way to get that TOP N to not be so heavy? I think there's a little bit of a misunderstanding here about how execution plans work. That number is just the estimated cost, which is a model SQL Server uses to determine what the most efficient execution plan will be. It's not updated at runtime, so even if the operators used very little resources,...


11

I expected t-sql to optimize it out - on a block/record level, the task to do is very easy and linear, essentially a for loop ( O(n) ). That's not the query that you wrote. It may not be equivalent to the query that you wrote depending on some otherwise minor detail of the table schema. You're expecting too much from the query optimizer. With the right ...


11

Query 2 The issue is Auto parameterization. In your case the constant 2 gets replaced with a tinyint parameter @1 rather than the literal 2 - as this parameter could have the value 0 or 1 it wouldn't be valid for the query optimiser to assume the check constraint contradicts this. You can use the following query to get a plan that does use the ...


11

You could also use a table valued parameter type in the stored procedure and pass numbers through this tvp. Create the type CREATE TYPE GetNumbers AS TABLE ( Numbers INT ); GO Create the procedure CREATE PROCEDURE dbo.InsertNumbers @GetNumbers GetNumbers READONLY AS SET NOCOUNT ON; CREATE TABLE #Temp(Num int); INSERT INTO #Temp(Num) ...


11

One way to determine the logical order of joins is to replace the first inner join in your example with a left outer join: SELECT * FROM user_branch T1 LEFT JOIN dimcustomer2 T2 ON T1.BRANCH_CODE = T2.BRANCH_CODE INNER JOIN customer_guarantee T3 ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM Let us assume that some rows in T1 have no matches in T2. More ...


9

In general you should be able to remove Index2 without any major issues. Index1 can cover any queries that Index2 would currently be handling (assuming there aren't any included column differences between the two). If column C in Index1 is large, you might end up reading slightly more data into memory with Index1 for queries that would have used Index2 ...


9

On SQL Server 2012 and later you can use TRY_CONVERT to check to see if the input can be converted. If it can't, a NULL value is returned so then you can then do an COALESCE to get either the converted value or the fixed date. begin declare @result date set @result = COALESCE(TRY_CONVERT(date, @date, 111), '2012-01-01') return @result end You ...


9

The best way I have found to speed up leading wildcard LIKE searches is to use n-grams. I describe the technique and provide a sample implementation in Trigram Wildcard String Search in SQL Server. The basic idea of a trigram search is quite simple: Persist three-character substrings (trigrams) of the target data. Split the search term(s) into ...


9

You want WHILE @@FETCH_STATUS = 0 which means continue unless something isn't right. Using <> -1 means it will continue even if the row fetched was missing, or it's not performing a fetch operation, making it infinite unless you get -1 as a return value, since there are 4 return values for @@FETCH_STATUS. 0 The FETCH statement was successful. -1 ...


9

Like other execution plan warnings, this one is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about where to look for a possible cause. As a purely practical matter, that is pretty much the end of it. The precise conditions that trigger this optimizer warning ...


9

Benjamin. If I understood you correctly, you want something like this: SELECT TR1.Title AS SourceTitle, TR2.Title AS DestinationTitle FROM [Result Map] AS RM INNER JOIN Table_Result TR1 ON RM.Source_Id=TR1.Id INNER JOIN Table_Result TR2 ON tr2.Id=RM.destination_id; The query will return SourceTitle DestinationTitle ABC DEF


9

I'm afraid the phrase "logical execution" does not make much sense; query execution by definition is physical materialization of a result set. I think what you mean by "logical execution" is the query compilation, the phase where the query syntax and semantic meaning is analyzed and the query plan is prepared to implement said semantic meaning. Joined ...


8

ROW_NUMBER() is a ranking window function, and ranking window functions require a mandatory ORDER BY clause. If you try to write it without the ORDER BY you will get a syntax error. SELECT ROW_NUMBER() OVER() FROM (VALUES ('A'), ('B'), ('C')) AS X(Y); -- Msg 4112, Level 15, State 1, Line 1 -- The function 'ROW_NUMBER' must have an OVER clause with ORDER ...


8

This is by design. When you store a document using the XML data type it is compressed and organised into a structure that Sql Server can perform operations on efficiently. One of the steps that it goes through to do this is to generate the InfoSet. When it does this, it removes anything that it determines to not be necessary, in your example, whitespace: ...


8

If the computed column isn't persisted or indexed, it's only a virtual column (expression), and it's not taking up any "space".


8

The Object_ID belongs to the object the index belongs to, such as an indexed view or a table. When you look at the object_id in the sys.indexes documentation object_id - ID of the object to which this index belongs. If you want to uniquely identify the index you need the Object_ID + index_id columns or the index_id for a specific object_id. To verify ...


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