ErikE
  • Member for 9 years, 11 months
  • Last seen this week
  • Seattle, Washington, United States
Is adding the ‘tbl’ prefix to table names really a problem?
29 votes

comDon't verListen prepTo adjThose adjOther nouPeople. proYou auxShould advAlways verUse nouPrefixes prepWith proYour adjTable nouNames. proI auxHave advEven verStarted gerUsing proThem prepIn ...

View answer
Why are queries parsed in such a way that disallows the use of column aliases in most clauses?
Accepted answer
19 votes

Summary There's no logical reason it couldn't be done, but the benefit is small and there are some pitfalls that may not be immediately apparent. Research Results I did some research and found some ...

View answer
Should you design the database before the application code is written?
13 votes

I've had the luxury of designing several medium-complexity databases, all used in businesses, with various front ends including web, Access, and C#. Usually, I have sat down and worked out the ...

View answer
Does SQL Server read all of a COALESCE function even if the first argument is not NULL?
8 votes

I just wanted to mention a strategy you may not have considered. It may not be a match here, but it does come in handy sometimes. See if this modification gives you any better performance: SELECT ...

View answer
Is it possible in a view to reference a linked server implicitly rather than explicitly?
Accepted answer
8 votes

CREATE SYNONYM dbo.FooView FOR [LinkedServer].Database.dbo.FooView Then just use dbo.FooView in your SPs and they can be the same between production and dev. Note that this is only for SQL 2005 and ...

View answer
Form groups of consecutive rows with same value
7 votes

You can do this as a simple subtraction of ROW_NUMBER() operations (or if your dates are not unique, though still unique per id_type, then you can use DENSE_RANK() instead, though it will be a more ...

View answer
Inventory database structure when inventory items have varying attributes
Accepted answer
7 votes

Supertype/Subtype How about looking into the supertype/subtype pattern? Common columns go in a parent table. Each distinct type has its own table with the ID of the parent as its own PK and it ...

View answer
Sql Server fails to use index on simple bijection
6 votes

Don't project your enum as a case statement. Project it as a derived table like so: SELECT * FROM (SELECT [Extent1].[Number] AS [Number], enum.Name FROM [dbo].[MyEntities] AS [...

View answer
Is it a good idea to use Synonyms to avoid creating a duplicate table?
6 votes

Why do you have 3 databases with the same users? What happens if one database goes down now? I'm asking these questions because the Con of the user-source database going down preventing use of the ...

View answer
Understanding datetime formats in SQL Server
5 votes

According to SQL Server Books Online Write International Transact-SQL Statements you could wrap your date strings like so: { ts 'yyyy-mm-dd hh:mm:ss[.fff] '} Example: { ts '1998-09-24 10:02:20' } ...

View answer
Table view created with triggers, someone please explain why?
5 votes

The reason is because the skills for evaluating competence in any discipline are the same skills for competence itself. They thought this design was a good idea because they weren't very skilled in ...

View answer
What is the best practice for selecting from two tables with multiple relations?
4 votes

Please consider trying out this query, and let me know how it performs. WITH EnumerableItemNames AS ( SELECT PcsID = pcs.ID, v.ColName, ItemName = me.Name FROM dbo....

View answer
Merging two selects then sort by and remove duplicates
4 votes

UNION is definitely the way to "stack" rowsets on top of each other, while removing duplicates. (Use UNION ALL to keep duplicates.) Your queries appear to have some cleanup possible, so we'll do that ...

View answer
Easily show rows that are different between two tables or queries
4 votes

Here's a way to show what was asked for: SELECT Which = 'TableA', * FROM ( SELECT * FROM dbo.TableA EXCEPT SELECT * FROM dbo.TableB ) X UNION ALL SELECT 'TableB', * FROM ( ...

View answer
How to improve performance if index is not Most SELECTIVE?
4 votes

Without having much detail, I can't recommend much. One thing that does jump out at me is that it's very likely you can improve performance on the table by normalizing it! The presence of so many ...

View answer
how do I model this in database?
4 votes

Here's my idea: ResourceType: -- 2 rows: hotel, city --------- ResourceTypeID not null autoincrement primary key ResourceType not null unique key Resource: -- both hotels & cities --------- ...

View answer
Interpreting an execution plan
Accepted answer
4 votes

From your question I gather that your table is relatively small. As you put more rows in the table you'll find that the bookmark lookup stays about the same, and the scan takes longer and longer. ...

View answer
Quick way to validate two tables against each other
3 votes

If you have a primary key, this is sometimes a better way to examine differences because the rows that should be the same are shown together. SELECT ID = IsNull(A.ID, B.ID), AValue = A.Value, ...

View answer
query optimization: time intervals
3 votes

I can't answer your question as to the absolutely best way. But I can offer a different way of solving the problem, which may or may not be better. It has a reasonably flat execution plan, and I think ...

View answer
Checking a wide table for nulls
3 votes

Dynamic SQL: DECLARE @SQL nvarchar(max); SELECT @SQL = IsNull(@SQL + ' OR ', '') + QuoteName(Column_Name) + ' IS NULL' FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'YourTable' AND ...

View answer
Tables with hierarchy: create a constraint to prevent circularity through foreign keys
3 votes

Here is another option: a trigger that allows multi-row updates and enforces no cycles. It works by traversing the ancestor chain until it finds a root element (with parent NULL), thus proving there ...

View answer
Why evaluates "IS NULL" in SSMS interactive mode to "TRUE" but the same statement being executed in a SP to "FALSE"?
3 votes

Initialize @OUT__USER_ID with NULL, since it is probably being set to a non-null value earlier in your script somewhere.

View answer
How do I model data from arbitrary CSV's in a database?
Accepted answer
3 votes

The question is so wide open that it is hard to say much. And why are you using a database at all? Excel does everything you've described so far. Seriously though, avoid making an inner system in ...

View answer
Why are queries parsed in such a way that disallows the use of column aliases in most clauses?
2 votes

In Entity SQL, you CAN use aliases from expressions in other places in the query in some situations: select k1, count(t.a), sum(t.a) from T as t group by t.b + t.c as k1 Note that here you MUST ...

View answer
sp_executesql adds statements to executed dynamic script?
Accepted answer
2 votes

I discovered that the server doesn't put the injected script content into the batch, but into the cached execution plan. So, you can only see this happening in profiler if the executed dynamic SQL is ...

View answer
What will happen when a forum uses 24 char length as an URL ID?
2 votes

But notice: it's not 24 bytes (256-bit characters), it appears to be a number only. And a 24-digit decimal number can be stored in just 10 bytes (12 digits, 999999999999, uses 5 bytes, E8D4A50FFF, so ...

View answer
How to find out what is causing my Identity column to keep going up
2 votes

The identity value will be increased if you begin a transaction, insert a row, then rollback the transaction. I originally thought that Access databases could be a culprit in this, thinking that its ...

View answer
Remove nulls from TSQL connection string
Accepted answer
2 votes

SQL Server always returns NULLs in this case. I know of no way to globally (through server settings or connection strings) cause it to return empty strings or zeroes instead of NULLs. And I do not ...

View answer
Express union which is dependent on other results
Accepted answer
2 votes

I'm not sure what you mean by a UNION. Please explain! If you are referring to the UNION operator that combines rowsets "vertically" then this might do the trick for you: SELECT CASE X.Which WHEN ...

View answer
Use Netbackup 7.7 `WITH KEEP_CDC`
Accepted answer
1 votes

I just found this question answered by Veritas themselves. Though this answer is for 7.5 instead of 7.7, I doubt they added this in the meantime. www.veritas.com: Does Netbackup support the MS-SQL ...

View answer