Most Popular
1500 questions
29
votes
7 answers
How do long columns impact performance and disk usage?
In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on.
In reality, how much does it really matter? How good is this optimized? What is the impact of just…
Lars Corneliussen
- 393
- 1
- 3
- 6
29
votes
2 answers
SQL Server returns "Arithmetic overflow error converting expression to data type int."
When I run this command with SUM()
SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total]
FROM dbo.t1 AS t
WHERE t.Id > 0
AND t.Id < 101;
I'm getting,
Arithmetic overflow error converting expression to data type int.
Any idea on what is…
Evan Carroll
- 63,051
- 46
- 242
- 479
29
votes
2 answers
Empty blocking process in blocked process report
I'm collecting blocked process reports using Extended Events, and for some reason in some reports the blocking-process node is empty. This is the full xml:
Tom V
- 15,670
- 7
- 63
- 86
29
votes
6 answers
How do I run a large script with many inserts without running out of memory?
Question:
I have a script with around 45 thousand insert from select statements. When I try and run it, I get an error message stating that I have run out of memory. How can I get this script to run?
Context:
Added some new data fields to make an…
spaghetticowboy
- 425
- 1
- 4
- 9
29
votes
1 answer
What date/time literal formats are LANGUAGE and DATEFORMAT safe?
It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language:
yyyyMMdd -- unseparated, date…
Aaron Bertrand
- 180,303
- 28
- 400
- 614
29
votes
5 answers
Why does my SELECT DISTINCT TOP N query scan the entire table?
I've run into a few SELECT DISTINCT TOP N queries which appear to be poorly optimized by the SQL Server query optimizer. Let's start by considering a trivial example: a million row table with two alternating values. I'll use the GetNums function to…
Joe Obbish
- 32,165
- 4
- 71
- 151
29
votes
1 answer
What are Objective Reasons to Prefer SQL Server 2016 over Earlier Versions?
As Microsoft makes SQL Server version upgrades more frequently since the SQL Server 2005 or 2008 days, a lot of companies find it difficult to determine when an upgrade is a "must have!" and when an upgrade is a "nice to have"
In the spirit of a few…
Mike Walsh
- 18,173
- 6
- 47
- 72
29
votes
6 answers
Preferred way to store DateTime
We can store Date and Time information in a couple of ways. What is the best approach for storing DateTime information?
Storing Date and Time in 2 separate columns or one column using DateTime?
Can you explain why that approach is better?
(Link to…
Julian
- 452
- 1
- 4
- 11
29
votes
4 answers
Why does the estimated cost of (the same) 1000 seeks on a unique index differ in these plans?
In the queries below both execution plans are estimated to perform 1,000 seeks on a unique index.
The seeks are driven by an ordered scan on the same source table so seemingly should end up seeking the same values in the same order.
Both nested…
Martin Smith
- 84,644
- 15
- 245
- 333
29
votes
4 answers
SQL Server "empty table" is slow after deleting all (12 million) records?
I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a new data set.
However, commands that once ran…
TheDramaLlama
29
votes
3 answers
If positive, sum all items. If negative, return each one
I am needing to find a way to SUM() all of the positive values for num and return the SUM() of all positive numbers and an individual row for each negative number. Below is a sample DDL:
Create Table #Be
(
id int
, salesid int
, num…
user2676140
- 960
- 3
- 18
- 28
29
votes
3 answers
When are computed columns computed?
When are the values for computed columns determined?
When the value is retrieved?
When the value is changed?
Some other time?
I'm guessing this is a novice question since I'm not finding anything in my searches.
Shelby115
- 487
- 4
- 7
29
votes
2 answers
Convert Postgres TIMESTAMP to TIMESTAMPTZ
I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller.
Stupidly, when I initially designed the thing I had all the fields as TIMESTAMP…
GTF
- 393
- 1
- 3
- 6
29
votes
2 answers
Ready-to-Use Database models example
Where can I find ready-to-use database models ?
I don't need a database with data in it, but only schemas (UML diagrams). Perhaps something like the data models at this link, but much more complex and real world.
tasmaniski
- 1,175
- 4
- 13
- 16
29
votes
2 answers
How to get the ID of the conflicting row in upsert?
I have a table tag with 2 columns: id (uuid) and name (text). I now want to insert a new tag into the table, but if the tag already exists, I want to simply get the id of the existing record.
I assumed I could just use ON CONFLICT DO NOTHING in…
Oliver Salzburg
- 392
- 1
- 4
- 12