Hot answers tagged

11

TOP 100 with no ORDER BY means it is undeterministic which 100 rows from the remote table end up participating in the join. This is execution plan dependant and can vary. If it is a one to many relationship it may be the case that one batch of 100 rows has more matches on the other side of the join than another different batch of 100 rows. You should ...


5

We found that this error occurred because forced parameterization was on at the database level. For other readers who want to reproduce it, use Kenneth's sample code in a database with this setting on: create database filterme; GO alter database filterme set parameterization forced; GO For parameterized queries, SQL Server doesn't want to cache an ...


4

A computed column would solve the issue and probably be better than having triggers: CREATE TABLE #Moofasa ( gotitdate DATE, dunitdate DATE, delayed AS COALESCE( DATEDIFF(day, gotitdate, dunitdate), 0) ) ; Tested at: rextester.com: INSERT INTO #Moofasa (gotitdate, dunitdate) VALUES ('2016-07-01', '2016-07-05'), ('2016-07-01', '2016-07-04'), ...


4

There are several ways of doing it. Changing the data model may also be a better option if you can do it. 1. Comma separated parameters If you want to stick with the comma separated string, you can find many string split functions online using CLR, CTE, XML, ... I am not going to put them all here and I won't benchmark them. But you must know that they all ...


3

This is pretty easy to do. Open two query windows in Management Studio. Run something like this in the first one (choose a test table that nobody is using, because this will block them, and make sure it has at least one row in it): BEGIN TRANSACTION SELECT * FROM sometesttable WITH (TABLOCK, XLOCK, HOLDLOCK) Then in the second query window: SELECT * ...


2

BEGIN TRANSACTION SELECT * FROM YourTable WITH (TABLOCKX, HOLDLOCK) WAITFOR DELAY '00:05:00' -- 5 minutes ROLLBACK TRANSACTION And then in another Query Window: SELECT * FROM YourTable


1

Try this: go create function dbo.fn_GetCountForPhrase (@phrase varchar(500)) returns int as begin declare @count int declare @localphrase varchar(8000) set @localphrase='"' + @phrase + '"' SELECT @count=COUNT(*) FROM table a WHERE CONTAINS(table.description, @localphrase) return @count end go SELECT phrase, dbo....


1

You could also fire this off in a few concurrent sessions. Only one session will be able to get the exclusive lock at any one time. BEGIN TRANSACTION; EXEC sp_getapplock @Resource = 'FooBar', @LockMode = 'Exclusive'; WAITFOR DELAY '00:00:30'; EXEC sp_releaseapplock @Resource = 'FooBar'; COMMIT TRANSACTION;


1

To add a default is easy: CREATE TABLE dbo.t ( tid INT PRIMARY KEY, ggid UNIQUEIDENTIFIER NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' ) ; Then the following insert will work: INSERT INTO dbo.t (tid) VALUES (1), (2) ; Test at rextester. If you want NULLs to not raise errors but be converted to the default, I think the easiest ...


1

One option that entails minimal change in the calling code is to pass in simplistic XML. This is very similar to Option 2 in @JulienVavasseur's answer, except that it keeps the two input parameters separate. The thought here is that it should be easy enough to convince the developer to change the existing String.Join(',', _SomeIntArray) to instead be ...



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