30,596 reputation
877128
bio website
location United Kingdom
age
visits member for 3 years, 6 months
seen 2 mins ago

Email: stackoverflowmartinsmith@gmail.com


2h
comment Any tips to watch out for when setting one of my databases to Read Only?
Statistics are only auto created or auto updated when a query needs them. If you happen to be running queries against the read only version that are different from what it previously saw and for which no statistics are available or for which the stored statistics were already stale it won't be able to create/update them.
4h
comment Any tips to watch out for when setting one of my databases to Read Only?
Readonly databases prior to 2012 can't auto create or auto update stats so you might want to ensure that stats are manually updated (and potentially any missing ones created) first to avoid sub optimal plans.
5h
comment In SQL Server would misc pages be pushed out when an object grows within a mixed extent?
If Object B grows it will get allocated 6 more pages in other, different, mixed extents. Then when the ninth page was needed a uniform statement will be allocated. I can't imagine there is much milage in trying to "optimise" this aspect either though. Even if your 1,000 objects were all one page and occupying full extents such that 7/8 pages in each were unused you would only be wasting 55MB
12h
comment How is the B-Tree created for a composite PK?
The index is logically ordered in exactly the same order as an ORDER BY PersonID, PersonAge would give. So PersonAge only makes any difference to ordering where rows are tied with the same id.
2d
comment Reset IDENTITY value
I assume you mean in SSMS. This will create a new table, copŷ all rows into it, drop the old table and rename the new one. And you need to do this twice to toggle the datatype back. It is using a sledgehammer to crack a nut.
Mar
28
awarded  sql
Mar
27
comment sql querying for a pattern
@AaronBertrand - Thanks!
Mar
27
revised sql querying for a pattern
added 29 characters in body
Mar
27
answered sql querying for a pattern
Mar
26
comment Is there a faster way of moving rows of data between SQL Server databases than using CTE's?
You might be better off creating a poor man's partitioning scheme with a table per day in your live database for your 4 most recent days and a partitioned view to consolidate them (can't use table partitioning on express). Then change the definition of the view to bring in a new day and remove an old, insert to the archive DB and drop the now surplus table.
Mar
24
comment Efficient way to transpose large categorical column to one-hot table row
Can you show a cut down example? (E.g using varchar(20))
Mar
18
comment No value to update but still an TABLE UPDATE Operator
Just use UPDATE sample SET name='cat' WHERE id=@id; - if no rows are found to update nothing will be updated.
Mar
17
comment Isolation of SQL Server UPDATE
@RossPresser - Yes to the first example, see the index intersection piece here blogs.msdn.com/b/craigfr/archive/2007/05/02/…. For the second I guess potentially it could if two different committed versions were available. Not sure it would be possible to engineer in practice.
Mar
15
awarded  Enlightened
Mar
15
awarded  Nice Answer
Mar
14
comment SQL Server membership in asp.net
Asp.net questions should be asked on StackOverflow.
Mar
14
comment Isolation of SQL Server UPDATE
@Tesh yes I don't think it would be possible to get any other result as that would all be on the same page and protected by a latch during the write.
Mar
14
comment Make SqlClient default to ARITHABORT ON
@HenrikStaunPoulsen - Unless you are stuck using 2000 (or 2000 compatibility level) it doesn't make any difference though. It is implied by ANSI_WARNINGS on in later versions and things like indexed views work fine.
Mar
14
comment Isolation of SQL Server UPDATE
@Tesh - I'm not sure what you are asking. This isn't the only similar scenario that is possible with NOLOCK I've added another one.
Mar
14
revised Isolation of SQL Server UPDATE
added 1047 characters in body