Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
27
votes
4 answers

Find objects linked to a PostgreSQL role

Some times ago I created a PostgreSQL user named user1 (PostgreSQL 9.4.9). I want to drop this user. So I first revoke all permissions on tables, sequences, functions, default privileges and ownership too: ALTER DEFAULT PRIVILEGES IN SCHEMA public…
Nicolas Payart
  • 2,368
  • 5
  • 27
  • 35
27
votes
4 answers

How can I add a rowversion column to a large table with minimal downtime

Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply ALTER TABLE [Tablename] ADD Rowversion [Rowversion] NOT NULL Then the table is unavailable for updates for too long. What strategies can I…
Michael J Swart
  • 2,059
  • 5
  • 22
  • 31
27
votes
5 answers

How to do a case sensitive search in WHERE clause?

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings. Any idea on how to do a case sensitive search in SQL query?
Somnath Muluk
  • 1,104
  • 3
  • 13
  • 15
27
votes
1 answer

Splitting Snapshot files with MaxBCPThreads for Transactional Replication

I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm expecting it to split the files so the threads on the…
b1kjsh
  • 271
  • 2
  • 6
27
votes
1 answer

How do I split a long PL/pgSQL line of code over multiple lines?

Is there a way to split a long line of PL/pgSQL code over multiple lines? My context is a trigger function where I log inserts into a table as per: INSERT INTO insert_log (log_time, description) VALUES ( now() , 'A description. Made up of 3…
dw8547
  • 937
  • 3
  • 10
  • 24
27
votes
1 answer

How do I restore a plain text postgres .backup file

I exported a postgres db from pgAdmin in a plain format because I wanted to search the whole db text. I then tried to restore the database back but it gives an error saying that the archive file is not valid archive file. How do I restore the db…
yankitwizzy
  • 391
  • 1
  • 4
  • 7
27
votes
5 answers

SARGable WHERE clause for two date columns

I have what is, to me, an interesting question on SARGability. In this case, it's about using a predicate on the difference between two date columns. Here's the setup: USE [tempdb] SET NOCOUNT ON IF OBJECT_ID('tempdb..#sargme') IS NOT…
Erik Darling
  • 40,781
  • 14
  • 130
  • 456
27
votes
3 answers

Managing concurrency when using SELECT-UPDATE pattern

Let's say you have the following code (please ignore that it's awful): BEGIN TRAN; DECLARE @id int SELECT @id = id + 1 FROM TableA; UPDATE TableA SET id = @id; --TableA must have only one row, apparently! COMMIT TRAN; -- @id is returned to the…
ErikE
  • 4,305
  • 4
  • 28
  • 39
27
votes
2 answers

What does the position of the ON clause actually mean?

The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, not found in tutorials and I have not found any…
boot4life
  • 1,279
  • 1
  • 11
  • 19
27
votes
7 answers

Pros/Cons of using multiple databases vs using a single database

I was working on a new project which has the requirement to use 7 databases, arguing that performance, stability, optimization are more easily implemented. While I don't agree, I'm having trouble collecting good arguments to use a single database…
rdkleine
  • 391
  • 1
  • 3
  • 7
27
votes
5 answers

Do empty columns take up space in a table?

I have table that holds from very basic info. Just a title and a few date fields. There's one field called comments which is varchar(4000) Most of the time we leave it blank, but some times will enter a large amount of data here. Is this a really…
aron
  • 595
  • 2
  • 6
  • 8
27
votes
3 answers

Should I use UUID as well as ID

I've been using UUIDs in my systems for a while now for a variety of reasons ranging from logging to delayed correlation. The formats I used changed as I became less naive from: VARCHAR(255) VARCHAR(36) CHAR(36) BINARY(16) It was when I reached…
Flosculus
  • 381
  • 1
  • 3
  • 6
27
votes
2 answers

Could a null column be part of a primary key?

I'm developing a SQL Server 2012 database and I have a question about a One-to-Zero-Or-One relationship. I have two tables, Codes and HelperCodes. A code could have zero or one helper code. This is the sql script to create these two tables and their…
VansFannel
  • 1,853
  • 5
  • 23
  • 36
27
votes
7 answers

Changing sa password

I'm trying to change the password of the sa account using SQL Server Management Studio 2012. I've followed the steps below but the password is still the same. Login into the SQL Server Management Studio, Select Database Engine, \SBSmonitoring,…
lobna
26
votes
1 answer

Composite Primary Key in multi-tenant SQL Server database

I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will have TenantId (Guid / UNIQUEIDENTIFIER) field.…
Reynaldi
  • 361
  • 3
  • 5
1 2 3
…
99 100