Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
20
votes
2 answers

Why is IDENTITY_INSERT ON only allowed on one table at a time?

It is the case that IDENTITY_INSERT can only be set to ON in one database table at a time, but why? Since IDENTITY columns aren't globally unique I can't think of any dangerous situation that could be caused by inserting identities into more than…
Ben Brocka
  • 2,063
  • 6
  • 28
  • 38
20
votes
3 answers

using *tables* as Table-Valued Parameters (TVP)

MS SQL 2008 supports TVP: a useful feature for bulk uploading data to a stored proceedure for processing. Rather than create a user-defined type, is it possible to leverage an existing table definition? For example, is it possible to create a…
Pressacco
  • 463
  • 1
  • 4
  • 10
20
votes
4 answers

Easily show rows that are different between two tables or queries

Imagine you have two different tables/queries that are supposed to have/return identical data. You want to verify this. What's an easy way to show any unmatched rows from each table just like the example below, comparing every column? Assume there…
ErikE
  • 4,305
  • 4
  • 28
  • 39
20
votes
4 answers

Effect of an index on update statements where update column is not in an index

I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute. While tuning my database to improve performance, I keep coming across this situation that seems to contradict…
Ryan
  • 325
  • 1
  • 2
  • 7
20
votes
3 answers

Huge data and performance in SQL Server

I've written an application with a SQL Server backend that collects and stores and extremely large amount of records. I've calculated that, at the peak, the average amount of records is somewhere in the avenue of 3-4 billion per day (20 hours of…
Brandon
  • 598
  • 2
  • 6
  • 17
20
votes
1 answer

Service broker was backed up, now receiving, but doesn't seem to be processing

Having a problem with Event Notifications. On the machine/drive/database that the messages are sent to (receiver), the drive filled up when nobody was looking, so it's been backed up all day. Now that we freed up space on the drive, it's…
mbourgon
  • 433
  • 3
  • 9
20
votes
2 answers

SQL Server VARCHAR Column Width

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255), when VARCHAR(30) will probably do. I consistently see agreement that there's a…
Eric J.
  • 468
  • 3
  • 12
20
votes
9 answers

Version control for database objects

The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be responsible for db changes only. So we need a source…
garik
  • 6,722
  • 10
  • 43
  • 56
20
votes
2 answers

Why scalar valued functions need execute permission rather than select?

I am wondering why, for scalar valued function, that I have to grant the user to execute rather than just a select? meanwhile a table valued functions works just fine with only select permission or db_datareader membership. to be more clear here is…
BobNoobGuy
  • 413
  • 3
  • 7
  • 11
20
votes
2 answers

Latin1_General_BIN performance impact when changing the database default collation

I have set the database collation to Latin1_General_BIN, to make string comparisons case-sensitive. Will this have an impact on performance? Will it have any impact on DML or DDL operations in the database? The database already exists with tables in…
Rakesh
  • 303
  • 1
  • 2
  • 6
20
votes
4 answers

Call a stored procedure from a trigger

I have created a stored procedure in mysql using the following syntax. DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- AC - AllCount DECLARE AC INT DEFAULT 0; …
Mark D
  • 1,110
  • 4
  • 15
  • 27
20
votes
2 answers

Is there a cool way of performing CU updates for SQL Server on hundreds of machines?

My company has grown from 50 SQL Servers to over 200 overnight in a merger acquisition. HELP! My questions are: How can I perform CU updates on this many servers and still have a life and keep my sanity? SCCM doesn't seem to be able to perform CU…
Russ Starksen
  • 201
  • 1
  • 2
  • 3
20
votes
7 answers

How can I take backup of particular tables in SQL Server 2008 using T-SQL Script

I want to take a backup of particular tables available in my database in a .bak file, and all these should be done using a T-SQL script.
Firoz Tennali
  • 325
  • 1
  • 3
  • 6
20
votes
5 answers

Azure SQL Database "Login failed for user" in application, but works fine in SSMS

I wanted to try out the contained database users feature on Azure SQL Database V12, but I'm having a problem authenticating that seems odd to me. I created a database called Classifier. I added my IP to the firewall rules so I could connect to…
Ben Collins
  • 303
  • 1
  • 2
  • 9
20
votes
3 answers

Trace Flag 4199 - Enable globally?

This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server. For those that have used it, under what circumstances did you experience query regression? It certainly seems…
FilamentUnities
  • 681
  • 1
  • 7
  • 16
1 2 3
…
99
100