Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
28
votes
4 answers

How to change SQL Server collation

How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database? Is there a way to do it using visual interface of SQL Server Management Studio? In the Server Properties window (and in the…
rem
  • 1,555
  • 3
  • 20
  • 22
28
votes
3 answers

Code to simulate deadlock

I am testing my application I need some code that stable simulates the deadlock on database site (sql script if possible). Thank you. ADDED: Reproducing deadlocks involving only one table
garik
  • 6,722
  • 10
  • 43
  • 56
28
votes
2 answers

Why do we need to rebuild and reorganize Indexes in SQL Server

After searching the internet i couldn't find the reason for Why do we need to rebuild and reorganize indexes in SQL Server ? what does internally happens when we rebuild and reorganize ? An article on a site says : Index should be rebuild when…
Ram Mourya
  • 391
  • 1
  • 3
  • 6
28
votes
1 answer

Where are Statistics physically stored in SQL Server?

Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool? More specifically, is there a way to figure out the pages used by statistics using DMVs and/or DBCC? I own both SQL Server…
ivanmp
  • 2,323
  • 3
  • 24
  • 35
28
votes
6 answers

Changing the use of GETDATE() in the entire database

I need to migrate an on-premises SQL Server 2017 database to an Azure SQL database, and I'm facing some challenges since there's quite a bit of limitations to go through. In particular, since an Azure SQL database works only in UTC time (no time…
Lamak
  • 2,576
  • 1
  • 24
  • 30
28
votes
2 answers

What is a "Partial Matching Index"?

I'm trying to learn more about the "foreign key references check" query plan operator introduced in SQL Server 2016. There's not a lot of information about it out there. Microsoft announced it here and I blogged about it here. The new operator can…
Joe Obbish
  • 32,165
  • 4
  • 71
  • 151
28
votes
3 answers

SQL Server clears plan cache and execution stats periodically

After upgrading SQL Server 2014 to 2016, the server keeps resetting cached execution plans and dm* views (like dm_exec_query_stats) etc. every couple of hours As if someone executes DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS manually (except for…
jitbit
  • 755
  • 1
  • 7
  • 15
28
votes
2 answers

Handling growing number of Tenants in Multi-tenant Database Architecture

Handling a modest number of customers (tenants) in a common server with separate databases for each tenant's instance of the application is relatively straightforward and is normally the correct way to do this. Currently I am looking at the…
coddey
  • 481
  • 4
  • 4
28
votes
1 answer

Benefits of using WITH TABLOCK on an INSERT

Under some circumstances, doing an INSERT INTO (WITH TABLOCK) will be faster due to minimal logging. Those circumstances include having the database in the BULK_LOGGED recovery model. Is there any other potential performance benefit to…
Mark Freeman
  • 2,181
  • 5
  • 30
  • 53
28
votes
3 answers

Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?

Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is used wherever possible, and implies (without ever…
Mark Amery
  • 1,075
  • 2
  • 14
  • 24
28
votes
2 answers

CREATE TABLE AS vs SELECT INTO

PostgreSQL supports CREATE TABLE AS and SELECT INTO when do I use both? CREATE TABLE AS -- define a new table from the results of a query CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have…
Evan Carroll
  • 63,051
  • 46
  • 242
  • 479
28
votes
4 answers

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
Matt Fenwick
  • 1,318
  • 3
  • 12
  • 28
28
votes
1 answer

Easiest way to duplicate rows

The closest I can find to what I want to do is How to duplicate related rows, but my inexperience gets me lost. Basically, I want to copy a number of records, change one column and insert them back into the same table (so its almost a duplicate of…
fiprojects
  • 425
  • 1
  • 4
  • 9
28
votes
1 answer

SQL Server 2014: any explanation for inconsistent self join cardinality estimate?

Consider the following query plan in SQL Server 2014: In the query plan, a self-join ar.fId = ar.fId yields an estimate of 1 row. However, this is a logically inconsistent estimate: ar has 20,608 rows and just one distinct value of fId (accurately…
Geoff Patterson
  • 8,397
  • 2
  • 27
  • 53
28
votes
5 answers

Add article to transactional publication without generating new snapshot

Using SQL 2008 R2 transactional replication with pull subscribers, when we add an article, I'd like to avoid having to create an entire snapshot (the db is ~80 GB, so this takes hours). From this article, I've seen how to do this with a partial…
user175528
  • 381
  • 1
  • 3
  • 3
1 2 3
…
99 100