Most Popular
1500 questions
26
votes
1 answer
Logical reads different when accessing the same LOB data
Here are three simple tests that read the same data, yet report very different logical reads:
Setup
The following script creates a test table with 100 identical rows, each containing an xml column with enough data to ensure it is stored off row. In…
Paul White
- 83,961
- 28
- 402
- 634
26
votes
1 answer
See if an JSON array in MySQL contains an object whose key holds a specific date
i'm trying to find out if there is a row which contains a specific date inside a JSON array
Let's say my data looks like this:
Table applications:
id | application_id | data
# Rows
1 | 1 | [{"data" : ["some", "data#1"], "date": "2016-04-21"},…
Klemen
- 591
- 1
- 4
- 7
26
votes
7 answers
What problems are solved by splitting street addresses into individual columns?
We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the size of our organization and how the needs or our…
Greg Burghardt
- 363
- 3
- 10
26
votes
1 answer
Insert results from a stored procedure into a table variable
I have a stored procedure that stores values in a table variable. I select these values and return them when the procedure is called.
I am trying to set these return values in another table variable but I can't figure it out.
Stored procedure
ALTER…
ThunD3eR
- 397
- 1
- 3
- 8
26
votes
1 answer
Index not used with = ANY() but used with IN
Table t has two indexes:
create table t (a int, b int);
create type int_pair as (a int, b int);
create index t_row_idx on t (((a,b)::int_pair));
create index t_a_b_idx on t (a,b);
insert into t (a,b)
select i, i
from generate_series(1, 100000)…
Clodoaldo
- 1,135
- 2
- 8
- 22
26
votes
6 answers
How to remove line breaks in SSMS?
I am dealing with SQL that looks as follows
create procedure
as
begin
Notice the large gaps e.g. between as and begin.
How can I remove these? I used SQL formatter but this is not working.
Anand
- 305
- 1
- 3
- 5
26
votes
3 answers
MongoDB MMAPv1 vs WiredTiger storage engines
In mongoDB3 appeared a new storage engine: WiredTiger. Yet, MMAPv1 is still the default choice in Mongo.
One might not be better than the other, it's often a matter of use case and choosing the right tool for the job. But which engine is right for…
Buzut
- 365
- 1
- 3
- 9
26
votes
3 answers
How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?
When I run the following command I get an error, however one of my scripts requires it.
SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'
Evan Carroll
- 63,051
- 46
- 242
- 479
26
votes
2 answers
Why does LEN() function badly underestimate cardinality in SQL Server 2014?
I have a table with a string column and a predicate that checks for rows with a certain length. In SQL Server 2014, I am seeing an estimate of 1 row regardless of the length I am checking for. This is yielding very poor plans because there are…
Geoff Patterson
- 8,397
- 2
- 27
- 53
26
votes
3 answers
Best way to design a database and table to keep records of changes?
I need to setup a history feature on a project to keep track of prior changes.
Let's say I have two tables right now:
NOTES TABLE (id, userid, submissionid, message)
SUBMISSIONS TABLE (id, name, userid, filepath)
Example: I have a row in notes and…
Schwarz
- 375
- 1
- 4
- 6
26
votes
6 answers
How to add new sysadmin account when no sysadmin accounts exist
During testing I deselected the sysadmin rights for my login and now can't re-add it (because I don't have sysadmin rights).
There are no other sysadmin accounts for the instance except the [sa] account.
I was set up for just Windows Authentication…
Mark D Jackson
26
votes
2 answers
How to store time series data
I have what I believe is a time series data set (please correct me if I'm wrong) that has a bunch of associated values.
An example would be modeling a car and tracking its various attributes during a trip. For example:
timestamp | speed | distance…
guest82
- 363
- 1
- 3
- 4
26
votes
1 answer
Is there any difference between 'LIMIT 0, 1' and 'LIMIT 1'?
I recently stumbled upon example codes, which differed by these notations.
SELECT * FROM table LIMIT 0, 1
SELECT * FROM table LIMIT 1
The first argument should be considered as the offset if I'm not wrong, the purpose of those two queries is to…
Gimu
- 363
- 1
- 3
- 5
26
votes
6 answers
Index performance on ON versus WHERE
I have two tables
@T1 TABLE
(
Id INT,
Date DATETIME
)
@T2 TABLE
(
Id INT,
Date DATETIME
)
These tables have a non-clustered index on (Id, Date)
And I join these tables
SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON
t1.Id =…
Erik Bergstedt
- 387
- 3
- 9
26
votes
2 answers
Optimization issue with user defined function
I have a problem understanding why SQL server decides to call user defined function for every value in the table even though only one row should be fetched. The actual SQL is a lot more complex, but I was able to reduce the problem down to…
James Z
- 2,219
- 13
- 22