jmoreno
  • Member for 9 years, 10 months
  • Last seen this week
  • Garner, North Carolina, United States
Identity column re-seed: when it is necessary?
9 votes

Reusing an identity value, should in general be discouraged. Either the value is used entirely internally, in which case it’s actual value is immaterial, or it is also used externally in which case ...

View answer
How is this SQL Server PK violation possible?
Accepted answer
6 votes

Your theory is wrong. Reading from your "identity" table doesn't lock it, the insert into it locks it. While they are part of a single compound statement, they are still seperate actions. Two (or ...

View answer
Why do people recommend not using the name "Id" for an identity column?
5 votes

Basically for the same reason you don't typically name parameters parameter1, parameter2...it's accurate, but not descriptive. If you see TableId, then you can probably safely assume that it's used ...

View answer
DELETE command not completing on 30,000,000 row table
4 votes

The easiest solution is to simply not do that -- do a smaller delete, which can be more easily processed. In this case I would have recommended trying sequential deletes of the form: DELETE FROM `...

View answer
Why aren't primary key / foreign key matches used for joins?
3 votes

The reason is that there is the LANGUAGE, and then there is the underlying principals. The language is sparse and lacking in many features that you would expect to see in a general purpose language. ...

View answer
CAST throwing error when run in stored procedure but not when run as raw query
2 votes

You are obviously getting different query plans and your cast is being tried against values which are filtered out by the where clause, I wouldn’t bother trying to figure out why — whatever the cause, ...

View answer
ORDER BY usage in subquery (derived table) SQL standard allowed or not
Accepted answer
2 votes

A cursor is not a table, it refers to a table or a view of that table. The cursor has an order only to the extent that the query that defines the cursor has an order by. A subquery within that ...

View answer
Why changing the column in the ORDER BY section of window function "MAX() OVER()" affects the final result?
1 votes

There is something you need to keep in mind for windowing functions: they are performed on every row. Thus windowing functions like lag and lead apply to the previous and next rows. By adding an ...

View answer
Does relational design discourage splitting tables unnecessarily?
1 votes

Reducing the number of tables isn’t a goal, either in design theory or in practice. Reducing tables can help or hurt performance, which is why in practice people increase or reduce tables (regardless ...

View answer
Which columns should be indexed when all may be used in different search queries?
1 votes

You are optimizing for size*, on an extremely small table. Since this basically a bridge table, it doesn’t really need a primary key, and even if it did, it wouldn’t need to be a bigint, the movies ...

View answer
How do I prove that the act of removing foreign keys doesn't corrupt existing data?
1 votes

TL;DR -- explain how db's work Long version: You can't show that removing a FK can not cause corruption, because that is not actually true -- theoretically it could. What you can show, is that it ...

View answer
How do I place leading zeros for numbers less than 10 without affecting those 10+?
1 votes

You can use a case statement for this, if it must be formatted in the database and you can't persist it. Select case when len(facilityCode) = 1 then '0' else '' end + facilityCode as ...

View answer
How to query a database for empty tables
1 votes

I generally just create a query that creates the query that I want and then execute that manually, but if you want it all in one go... declare @sql nvarchar(max) ; set @sql = ';with cte as (' + (...

View answer
Is "SET IDENTITY_INSERT" required to insert arbitrary GUID into [uniqueidentifier] column?
0 votes

Identity inserts only apply to identity columns in sql server trying to create non numeric identity columns will produce this error: Identity column 'id' must be of data type int, bigint, smallint, ...

View answer
Best way to automatically reset sequence/identity once a year
0 votes

As an alternative to the identity/sequence column, a column with a default value (say -1) can be used to identify records to update inside a trigger. This allows full control of the value, with the ...

View answer
Adding Users after Database Restore
0 votes

Create a table in your test database with the users that should have access, create a restore script that copies that table into a temp table, do your restore, recreate the table in your test database,...

View answer
Find procedures that haven't been called in <n> days
0 votes

The problem isn't that DMV's are unreliable, it's that they don't capture the information that you want. Create a job that runs periodically that uses them to capture the information that you want -- ...

View answer