27,531 reputation
663114
bio website
location United Kingdom
age
visits member for 2 years, 11 months
seen 1 hour ago

Email: stackoverflowmartinsmith@gmail.com


2d
comment Why does my query end up with two seeks instead of one and how do I fix that?
Can you upload the plan or at least provide SQL that runs and reproduces the issue? Current SQL is invalid/
Aug
26
comment SQL Server table scan when query parameter doesn't exactly match column
@cha - No it isn't. The order is in descending order of precedence. varchar has lower precedence than nvarchar.
Aug
26
comment SQL Server table scan when query parameter doesn't exactly match column
@cha that scenario isn't likely/possible at all. Because it will convert the side with the lower datatype precedence to match that of the side with the higher data type precedence. Meaning the varchar always gets converted to nvarchar. Not the reverse.
Aug
26
comment SQL Server table scan when query parameter doesn't exactly match column
@cha the collation issue mentioned in that article is for a varchar column being compared with an nvarchar parameter or literal. The OP here is claiming they see the issue with the reversed scenario.
Aug
25
comment SQL Server table scan when query parameter doesn't exactly match column
Please post the execution plan for the varchar version that gives a scan.
Aug
25
comment SQL Server table scan when query parameter doesn't exactly match column
@AaronBertrand Yes agree that there is no good reason not to have them matching. Would be good if the OP double checks the info they provided as seems something is missing!
Aug
25
comment SQL Server table scan when query parameter doesn't exactly match column
This isn't right. The parameter will get implicitly cast to nvarchar as that has higher data type precedence. The issue of unsargability arises when the parameter is nvarchar and the column varchar (and not for all collations)
Aug
14
comment Why is it @@SERVERNAME but not @@DBNAME?
The @@ ones used to be known as global variables and that syntax goes back to Sybase days. Same with @@IDENTITY vs SCOPE_IDENTITY
Aug
9
comment Script that alters all stored procedure on the database
You'd probably be better off using SMO for this. That easily differentiates the proc header from the body for example. You could even use TSQL100Parser.
Aug
6
comment Is there a way to grant a user access to only all non system views of database?
+1 To me the good thing about this solution over mine is that there is no need to assign permissions explicitly when new views are created.
Aug
1
comment Performance Hit using CAST() in TSQL (SQL Server)?
cast as date can do an index seek but still has problems compared with expressing as a range seek without a cast.
Jul
31
comment Does READ UNCOMMITTED have the same effect as AUTOCOMMIT=1?
Your starting assumption is wrong. Just because transaction T1 runs at read uncommitted doesn't mean that other transactions will see uncommitted changes from T1. It just means T1 sees uncommitted transactions from them...
Jul
29
comment What happens during “big” inserts on a table with a clustered composite key?
RE: Will SQL "move" the existing rows to maintain the clustering. No. It will get fragmented and you would need to reorganize/rebuild the index to regain it. Demo of that here
Jul
28
comment Change rowguidcol property to a different column
@wBob - Of course! That makes sense. Unfortunately my hasty DV is locked in unless this answer is edited. But it could do with editing anyway to acknowledge the existence of ALTER TABLE MyTable ALTER COLUMN Col1 DROP ROWGUIDCOL; ALTER TABLE MyTable ALTER COLUMN Col2 ADD ROWGUIDCOL;
Jul
28
comment Change rowguidcol property to a different column
This isn't the case. The ROWGUIDCOL can be removed from/added to columns.
Jul
24
comment How do I place leading zeros for numbers less than 10 without affecting those 10+?
@user44339 SQL Server doesn't have any such function.
Jul
23
comment Why is my select statement so slow?
What does SET STATISTICS IO ON;SET STATISTICS TIME ON; then running the query and waiting for it to finish show?
Jul
23
comment Why is my select statement so slow?
Whilst the query is running can you look in sys.dm_os_waiting_tasks to see if it is blocked?
Jul
21
comment Finding last time table was updated?
@SeanPerkins - See edited version
Jul
16
comment Is it possible for SQL Server to miss rows on a select * where PK = “value” query
@BrentOzar Craig Freedman has a similar demo for read committed here but don't think you could get a PK to return null with this type of issue.