Most Popular
1500 questions
26
votes
4 answers
How to get a group where the count is zero?
I'll try to make a graph from the data from my SQL server database. I'll have all streets with the count of the users who are living in this street even the count is zero.
For this I've tried this query:
Create table Streets(
ID int IDENTITY …
H. Pauwelyn
- 930
- 6
- 18
- 35
26
votes
6 answers
Why the term "relation(al)"?
In english, we might talk about the relation between, say, Bob and Tim. Perhaps they're cousins. The term "relation" in this context makes sense to me.
In the context of relational databases, I understand what the term refers to, but I don't…
Adam Zerner
- 371
- 2
- 7
26
votes
2 answers
Why does a subquery reduce the row estimate to 1?
Consider the following contrived but simple query:
SELECT
ID
, CASE
WHEN ID <> 0
THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE)
ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2)
END AS ID2
FROM X_HEAP;
I would expect the final row estimate…
Joe Obbish
- 32,165
- 4
- 71
- 151
26
votes
1 answer
Why does searching for LIKE N'%�%' match any Unicode character and = N'�' match many?
DECLARE @T TABLE(
Col NCHAR(1));
INSERT INTO @T
VALUES (N'A'),
(N'B'),
(N'C'),
(N'Ƕ'),
(N'Ƿ'),
(N'Ǹ');
SELECT *
FROM @T
WHERE Col LIKE N'%�%'
Returns
Col
A
B
C
Ƕ
Ƿ
Ǹ
SELECT…
Martin Smith
- 84,644
- 15
- 245
- 333
26
votes
7 answers
Storing IP address
I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column?
Should I support IPv6 as well? If so, what is the maximum length of IP address?
Cleankod
- 515
- 2
- 5
- 9
26
votes
4 answers
Not using foreign key constraints in real practice. Is it OK?
Not using FK constraints is my company's untold rule. FK constraints are used only when designing ERD and not used when creating tables.
According to my senior, in real practice, those are very time consuming obstacles when we are dealing with…
user2652379
- 519
- 1
- 5
- 12
26
votes
1 answer
Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity
I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week.
I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual core and 4GB memory and 40GB SSD.
The log errors…
TheVyom
- 363
- 1
- 3
- 6
26
votes
7 answers
Composite indexes: Most selective column first?
I’ve been reading about composite indexes and I’m slightly confused about ordering. This documentation (little less than half way down) says
In general, you should put the column expected to be used most often first in the index.
However, shortly…
Eric
- 373
- 1
- 3
- 5
26
votes
1 answer
How to change the schema of stored procedure without recreating it
I have some Stored procedure with "SalesTraining" schema.
I want to change all SalesTraining schemas to "Sales".
Is there any better way than recreating the stored procedures?
thnx
user113602
26
votes
1 answer
PostgreSQL DELETE FROM fails with `Error: attempted to delete invisible tuple`
The error
Trying to delete tuples containing invalid timestamps with
DELETE FROM comments WHERE date > '1 Jan 9999' OR date < '1 Jan 2000' OR date_found > '1 Jan 9999' OR date_found < '1 Jan 2000';
ends in
ERROR: attempted to delete invisible…
Kai
- 781
- 5
- 10
26
votes
4 answers
Limit results to the first 2 ranking rows
In SQL Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from rank 1, 2, 3......999. But I want only up to 2 RANKs in each…
UB01
- 937
- 2
- 9
- 18
26
votes
1 answer
Is it safe to rely on the order of an INSERT's OUTPUT clause?
Given this table:
CREATE TABLE dbo.Target (
TargetId int identity(1, 1) NOT NULL,
Color varchar(20) NOT NULL,
Action varchar(10) NOT NULL, -- of course this should be normalized
Code int NOT NULL,
CONSTRAINT PK_Target PRIMARY KEY…
ErikE
- 4,305
- 4
- 28
- 39
26
votes
6 answers
Delete all data in Postgres database
I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a staging server.
pg_dump -h localhost -U adminuser…
uberrebu
- 451
- 1
- 6
- 12
26
votes
4 answers
How to limit maximum number of rows in a table to just 1
I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added. I have opted to use a trigger for this, as…
Dib
- 447
- 1
- 6
- 13
26
votes
2 answers
What is the purpose of a Row_GUID column?
I've been digging around in the AdventureWorks2012 database and see Row_GUID used in several tables.
There are 2 parts to my question:
When should I include a Row_GUID column?
What are the uses and benefits of a Row_GUID column?
SQLSuperHero
- 477
- 1
- 4
- 10