30,591 reputation
877128
bio website
location United Kingdom
age
visits member for 3 years, 6 months
seen 1 hour ago

Email: stackoverflowmartinsmith@gmail.com


10h
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.
2d
comment sql querying for a pattern
@AaronBertrand - Thanks!
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
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
13
comment Isolation of SQL Server UPDATE
@srutzky - The question only asks about rows though. Which are logical not physical so providing an example where two different physical objects were involved in stitching together the row seems valid?
Mar
7
comment SQL Server - how transactions and transaction log work (simplified)
@TheGameiswar yes, as long as you aren't using delayed durability.
Mar
6
comment SQL Server: Recursive dynamic SQL puzzle
@user16484 the KB article only mentions order by but the responses on the connect site indicate this is not supported or guaranteed ever. Some example quotes here stackoverflow.com/a/15163136/73226 especially Even without ORDER BY, we do not guarantee that @var = @var + will produce the concatenated value for any statement that affects multiple rows. The right-hand side of the expression can be evaluated either once or multiple times during query execution and the behavior as I said is plan dependent.
Mar
6
comment Average of a row or record across three columns
@shrx no sorry. I have rarely used sqllite.
Mar
5
comment Cursor doesn't print text when I tell it to
Yes you can. screenshot of it all working fine
Feb
28
comment PostgreSQL 9.x: a clustered index on column A is the same as creating a table ordered by A?
@ErwinBrandstetter juggling, sleight of hand, trickery apparently!
Feb
25
comment Create database unique Surrogate Keys without using GUID’s
On 2012+ you could use a sequence object if I understand you correctly. Why do you need this?
Feb
25
comment Check if query will cause duplicate row
Trailing spaces aren't significant and you don't need the join. The example 2 can be simplified to WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY LTRIM(PK) ORDER BY LTRIM(PK)) as rn FROM tab) DELETE FROM cte where rn > 1;
Feb
10
comment How to combine date and time to datetime2 in SQL Server?
SELECT CONVERT(DATE,'2015-02-06',120)+ CAST('23:59:59.999' AS TIME) gives "Operand data type date is invalid for add operator." for me. On 2012.
Jan
18
comment Design: how to store lots of booleans?
What RDBMS are you using? What type of queries do you need to do against this data?