Most Popular
1500 questions
54
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
- 765
- 1
- 6
- 9
54
votes
2 answers
Optimizing bulk update performance in PostgreSQL
Using PG 9.1 on Ubuntu 12.04.
It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:
UPDATE table
SET field1 = constant1, field2 = constant2, ...
WHERE id = constid
(We're just overwriting…
xyzzyrz
- 661
- 1
- 6
- 8
54
votes
6 answers
What does the GO statement do in SQL Server?
The GO statement from SQL Server caused me great curiosity and I don't really know how to use it properly.
I noticed that queries with or without GO don't return errors and seem to work the same, so what is the purpose of it and why should I use…
Danilo Matrangolo Marano
- 651
- 1
- 5
- 7
54
votes
2 answers
Is it a bad practice to have several mutually exclusive one-to-one relationships?
Say, a table car has one-to-one relationship to tables electric_car, gas_car, and hybrid_car. If a car is electric_car, it can no longer appear in gas_car or a hybrid_car, etc.
Is there anything wrong with such design? Some problems that may occur…
Arthur Tarasov
- 811
- 1
- 9
- 15
54
votes
2 answers
Convert right side of join of many to many into array
When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row.
Example with 3 tables:
CREATE TABLE items (
id serial primary…
Ced
- 754
- 2
- 7
- 10
54
votes
1 answer
Indexes: integer vs string performance if the number of nodes is the same
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is searching for very specific attributes on a model.
I…
Chris Cirefice
- 643
- 1
- 5
- 7
53
votes
3 answers
How do I get back some deleted records?
I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side.
Is there any way to get back these records?
user755
53
votes
3 answers
Huge slowdown to SQL Server query on adding wildcard (or top)
I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not wanting to swamp the results page I did:
select…
stovroz
- 631
- 5
- 6
53
votes
2 answers
Does a re-index update statistics?
I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is:
Does a re-index update the statistics?
We found discussions online arguing both that it does and that it doesn't.
Thor Erik
- 645
- 1
- 5
- 6
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,376
- 4
- 43
- 67
53
votes
5 answers
Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling
I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes. My problem comes to figuring out which other tasks I want…
Josh
- 693
- 1
- 6
- 7
53
votes
5 answers
Data obfuscation in SQL Server
What is the best practice for Data Obfuscation in SQL Server?
We'd like to use masked Production data in our UAT system.
If we want to do it quickly, and with a higher level of obsfucation, what approach should be taken? I'm thinking about…
Sky
- 3,744
- 18
- 53
- 68
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
- 43,325
- 51
- 233
- 390
53
votes
3 answers
How do you grant execute permission for a single stored procedure?
Normally when I create a stored procedure I use the following as a template of sort
Create procedure
<@param1 , @param2 , etc..>
as begin
end
Is there a way to include granting execute permission on only…
DaneEdw
- 633
- 1
- 5
- 6
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,082
- 7
- 36
- 57