Joe
  • Member for 5 years, 8 months
  • Last seen more than 1 year ago
  • NY, United States
Is it bad to have index space larger than data space?
5 votes

I like Brents answer and I upvoted it. I would like to add another perspective though. I have worked as a user, a developer and a DBA and feel that opinions are not relevant. I believe it is up to ...

View answer
Does alter column change datatype use tempdb version store?
4 votes

I believe that the db engine is creating a new temporary table and filling it. Looking at your execution plan might provide clues. You can see the quote in the following article but after searching ...

View answer
Date Conversion error
Accepted answer
3 votes

If you are using a 'literal date' you need to type the format differently. If you are using a variable or field you will need to use substring and parse into the correct format. I have a DateTime2 ...

View answer
Counting number of occurences in a time period
3 votes

Edited based on your sample data. For an item number to have a total quantity of two or more in a given date range: Select MachineRefNo, ItemNumber, Sum(Quantity) NumberOfItems From Table ...

View answer
How would i create a report involving multiple many to many relationships
Accepted answer
3 votes

I think it is a sound design, based on what is called an associative entity. To query: Select A.FieldList..., B.FieldList..., C.FieldList... From A Join AB On AB.AID = A.ID Join B On B.ID ...

View answer
Is adding the ‘tbl’ prefix to table names really a problem?
3 votes

Is “Do not prefix your tables with tbl” really a problem? Regarding the system? No. Regarding other people? Maybe. You can generate lotsa hate. I personally do not prefix tables but do so on other ...

View answer
Display both null and not null under same id but not both null or not null like XOR logic
2 votes

Select NullTest.ID, NullTest.Col1 From NullTest Join (Select ID From NullTest Where Col1 Is Not Null Group By ID) NotNulls On NotNulls.ID = NullTest.ID Join (Select ID ...

View answer
Structuring relational database tables
1 votes

Option 1 is the normalized way to structure the table with one addition. There should be a table of types with an id stored in the main table as a foreign key. That way when you add new types there ...

View answer
Combine addresses missing Unit/Suite #
Accepted answer
1 votes

This is not a clean problem. The following should work assuming that there will not be multiple street addresses with different suite numbers. Also the data will have to be formatted with the comma ...

View answer
Update Null Values With Other Values
1 votes

This will be faster than setting by hand if you have many companies. If there were more fields than 3 or 4 I would consider building a dynamic loop. Update R1 Set R1.power1 = R2.power1 From #...

View answer
Full Outer Join shows NULL when Group By is used
1 votes

You have cr.type in some of your group by. You need to remove this to avoid getting duplicates. I do not see this in the select lists so I dont think you need it in the group by.

View answer
Very slow insert performance on large table (EF)
0 votes

It appears that the dates you are clustered on are not being inserted sequentially. If your date field was recent purchases for example they would be inserted at the 'end of the table'. Because it ...

View answer
Prevent duplicate record counts in one to many query
0 votes

You need to group on teacher as well so that the counts roll up correctly and you will only get one record per teacher. Without your where clause, students can be counted multiple times, once for ...

View answer
Tryng to use and or queries
0 votes

You will need three variables or objects if building in a language like c# to specify what you are searching for based on your check boxes. Select * From Pets Where (CatChecked = 1 And 1Cat = ...

View answer
Why SQL Server uses Non-Clustered but not Clustered Index?
0 votes

A clustered index contains all data in the row. It is not possible to tell exactly why the index was chosen without the actual plan file. I can say that based in my experience the optimizer is ...

View answer