Most Popular
1500 questions
53
votes
8 answers
How do I shrink all files quickly for all databases?
In SQL Server (2008 in this case) how can I quickly shrink all the files, both log and data, for all databases on an instance? I could go through SSMS and right click each and choose Tasks -> Shrink, but I'm looking for something faster.
I scripted…
jcolebrand
- 6,354
- 4
- 42
- 67
53
votes
2 answers
selecting where two columns are in a set
This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following:
SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)
I want to select…
James
- 655
- 1
- 5
- 7
53
votes
1 answer
Is there an analog of GETDATE() that returns DATETIME2
According to MSDN, Getdate(), GetUtcDate(), and CURRENT_TIMESTAMP all return DATETIME. I ran a short test, which confirms that:
CREATE TABLE #t(T DATETIME2(7));
GO
DECLARE @i INT ;
SET @i=1;
WHILE @i<10000 BEGIN ;
INSERT #t…
A-K
- 7,244
- 3
- 32
- 50
53
votes
6 answers
How can I get the actual data size per row in a SQL Server table?
I found this script
sql-server-2005-reaching-table-row-size-limit
that seems to return the row size per defined data type lengths. I need a script that would give me all the rows in a table that their max data size is over the recommended 8024…
Anthony
- 633
- 1
- 5
- 5
53
votes
2 answers
How to create Unicode parameter and variable names
All of this works:
CREATE DATABASE [¯\_(ツ)_/¯];
GO
USE [¯\_(ツ)_/¯];
GO
CREATE SCHEMA [¯\_(ツ)_/¯];
GO
CREATE TABLE [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯] NVARCHAR(20));
GO
CREATE UNIQUE CLUSTERED INDEX [¯\_(ツ)_/¯] ON…
Brent Ozar
- 42,952
- 47
- 220
- 375
53
votes
8 answers
Get multiple columns from a select subquery
SELECT
*,
p.name AS name,
p.image,
p.price,
(
SELECT ps.price
FROM product_special ps
WHERE p.id = ps.id
AND ps.date < NOW()
ORDER BY ps.priority ASC, LIMIT 1
) AS special_price,
(
…
Sparctus
- 623
- 2
- 7
- 8
53
votes
4 answers
What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?
Many times I need to write something like the following when dealing with SQL Server.
create table #table_name
(
column1 int,
column2 varchar(200)
...
)
insert into #table_name
execute some_stored_procedure;
But create a table which…
Just a learner
- 2,682
- 7
- 34
- 53
53
votes
6 answers
How to update 10 million+ rows in MySQL single table as Fast as possible?
Using MySQL 5.6 with InnoDB storage engine for most of the tables. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. Server has 32GB RAM and is running Cent OS 7 x64.
I have one big table which contains around 10 millions +…
user16108
52
votes
2 answers
Best way to populate a new column in a large table?
We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL constraint to it).
If I understand Postgres correctly…
Collin Peters
- 745
- 1
- 6
- 9
52
votes
5 answers
When should you denormalize?
I think we are all familiar with database normalization.
My question is: What are some guidelines that you use when you want to denormalize the tables?
Richard
- 6,393
- 8
- 42
- 62
52
votes
8 answers
Calculating disk space usage per MySQL DB
I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can take minutes to calculate.
What is the quickest…
GoldenNewby
- 664
- 1
- 6
- 8
51
votes
3 answers
How to make MySQL table name case insensitive in Ubuntu?
I am using Ubuntu 13.10 and MySQL 5.6 and I know database name and table name are case sensitive in Ubuntu (and some other *nix environments) by default.
Now, I want to make MySQL work as case insensitive in Ubuntu.
Is it possible? If yes, how can I…
java baba
51
votes
5 answers
How to design a database for storing a sorted list?
I am looking to store a sorted list inside a database. I want to perform the following operations efficiently.
Insert(x) - Insert record x into the table
Delete(x) - Delete record x from the table
Before(x,n) - Return the 'n' records preceding the…
chitti
- 611
- 1
- 5
- 6
51
votes
7 answers
Why does Postgres generate an already used PK value?
I'm using Django, and every once in a while I get this error:
IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey"
DETAIL: Key (id)=(1) already exists.
My Postgres database does in fact have a myapp_mymodel object…
orokusaki
- 1,179
- 2
- 11
- 21
51
votes
5 answers
How export a sql server 2008 diagram to PDF filetype?
I want to have an export from my database diagram to PDF or image types. How can I do this?
I worked with SQL Server 2008 R2.
Hamid Talebi
- 615
- 1
- 6
- 8