All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

106
votes
8answers
86k views

What's the difference between a CTE and a Temp Table?

What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other? CTE WITH cte (Column1, Column2, Column3) AS ( SELECT Column1, Column2, ...
2
votes
0answers
93 views

Permon counters on SQL 2005 mirroring (user defined counter log file is empty) [closed]

I've been attempting to gather performance counters for mirroring using perfmon, over a period of time and saving this to a log file. I am able to do this without problem for general SQL Server ...
1
vote
2answers
458 views

dangers of using SHRINKFILE in sql server 2008 [duplicate]

I'm trying to shrink the file size of a massive log file and was wondering how I estimate the size of the log file and what the dangers in running this are
1
vote
1answer
106 views

backing up selected tables in a database

We need to transport database backups over a lower bandwidth connection. We only want the data of specific tables, not the whole database. In SQL Server 2008 R2, how can this be done? I am attempting ...
5
votes
3answers
4k views

How do I use `sys.dm_exec_connections` in combination with SUSER_SNAME()?

How do I use sys.dm_exec_connections in combination with SUSER_SNAME()? I want to see the username corresponding to each record in sys.dm_exec_connections.
2
votes
2answers
513 views

Scheduling a task that happens very frequently in Sql Server

I've just added some caching to my Sql server to speed up a particularly important SELECT query. This cache will be refreshed every ~10 minutes. The time between activations does not have to be ...
3
votes
2answers
2k views

Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights?

I am struggling with maintaining or re-gaining access after restoring an SQL Server database (2008 R2, if that makes any difference). Here is what I am trying: SQLServer login APPUSER is member of ...
2
votes
1answer
2k views

Access SQL Server from a different subnet

I'm creating a program which accesses MS SQL Server 2008, the problem is that I can run the program smoothly as an administrator account, even on different subnet computers and using terminal service (...
4
votes
2answers
5k views

sql 2008, select from a view as a different user, like owner

In sql server 2008, the system views sys.database_role_members and sys.database_principals, only returns the users and group that the current account is allowed to view. I need to check id a certain ...
3
votes
2answers
2k views

Installed SQL Server 2000 with wrong collation.. how to correct for entire server instance?

I installed SQL 2000 Developer (yes, old, don't ask) on my laptop ... with the wrong collation. Oops! It's showing Latin1_General_CI_AS, but it turns out I need to use SQL_Latin1_General_CP1_CI_AS ...
2
votes
2answers
4k views

Why the query scan all the partitions?

I have the following partitioned table create table T (Month char(6), ID int, .... primary key (ID, Month)) on psMonth(Month) The partition function is CREATE PARTITION FUNCTION [pfMonth](char(6)) ...
8
votes
2answers
3k views

Is there any collection of PowerShell scripts that help the task of SQL Server maintenance?

I've been studying PowerShell and I've been (slowly) getting it into my day-to-day DBA tasks. So far I've seen a bunch of disconnected PowerShell scripts for many disparate administration activities. ...
0
votes
2answers
165 views

How can I generate call stack on error on the fly

I have an issue with an index on a Sql2k server: Event Log shows the following errors happening: Date 2/14/2012 9:54:19 AM Log SQL Server (Current - 2/14/2012 10:26:00 AM) Source ...
6
votes
2answers
646 views

Why select max partitioned column is much slower than indexed one?

I have a big partitioned table (300+M rows, 70 active partitions). The table has clustered PK of (K0, K1) and it's partitioned on column K1. And K1 has very low selectivity. However the following ...
1
vote
1answer
552 views

Going from separate databases to shared databases and schemas (Multi Tenant Data Architecture)

I am eagerly trying to design the perfect multitenant data architecture for my database. I am getting inspiration from an article posted by Microsoft, based on scenario 3. I have been given all my ...
13
votes
4answers
3k views

Is it a good idea to use Synonyms to avoid creating a duplicate table?

We have 3 copies of the exact same database. All 3 databases have a Users table, and a User will always exist in all 3 databases with the exact same settings. Anytime we want to add or edit a User we ...
2
votes
2answers
3k views

MS SQL Server 2008R2, “Login failed for user” from time to time, IIS 7 , NET 4.0

I'm fighting with strange problem that that most probably is connected with Windows / MS SQL Server security/privileges setup. When external app (that runs with administrator privileges) polls the IIS ...
0
votes
2answers
442 views

Restore a database to different databases without losing any data

I work on a database structure that has a lot of data in it. This database structure is used by approximately 40 different servers. I want to change some of the table structures, by adding some ...
7
votes
2answers
1k views

Advantage of using a database snapshot for reporting purposes

What is the performance advantage of using a snapshot of a database for reporting purposes? The way I see it, it would probably downgrade performance since for every write in the original database, ...
7
votes
1answer
2k views

Msg 2534 : DBCC CHECKDB allocation errors

We recently moved our intranet production database from an SQL Server 2008 to an SQL Server 2008 R2 brand new server. The backup Job have been failing since migrating, due to DBCC Errors (Check ...
1
vote
1answer
749 views

Using View filters for tenants in SQL-Server 2008

I'm currently creating a Multi-tenant data architecture in a SaaS BI-product. The product are using shared database and shared schema, but with tenant view filters and tenant data encryption (...
1
vote
1answer
952 views

Merge Replication from SQL 2008 R2 to SQL 2005 Express: SQL Server version (10.50) is not supported

I'm setting up a (one way) pull, RMO merge replication via web sync from my main database (SQL Server 2008 R2) and clients running SQL Server 2005 Express or 2008 Express (I must support both). I've ...
2
votes
1answer
3k views

Database restoring taking too long

I have backuped sql database from my server and tried restore on my machine. Restoring window disappeared by saying successfully restored. In the database list my database is not accessible. ...
7
votes
1answer
7k views

Temporarily Disable Sql Replication

Due to a business need, I may need to disable transactional replication in my environment (Sql 2k -> Sql 2008). Functionally, I understand this to mean that I will need to drop subscriptions and ...
1
vote
1answer
769 views

Generate a new Sub Plan/Sequence ID in SQL 2005/8 Business Intelligence Development Studio

I have what is effectively a template SSIS package (well a maintenance plan actually) that is copied and used as the basis of new maintenance plans. In this package there is a Sequence (or a sub-plan)...
0
votes
1answer
357 views

Is it possible to tell the source of a backup?

I was wondering if it is possible to tell the difference between backups created manually and backups created through a maintenance plan. There does not seem to be any way to discriminate them in the ...
2
votes
1answer
416 views

Effect on performance on retrieval of image column from a table with half a billion records on remote server

I require to loop through different servers to collect data in a temporary table. The table accessed on these servers have about half a billion records. To retrieve the data from these tables, I ...
6
votes
4answers
598 views

Resources for understanding SQL Server locking and concurrency?

As demonstrated by a recent question of mine locking and concurrency are HARD. Can you suggest any good resources for intermediate-to-advanced SQL professionals to do a thorough study on these that ...
9
votes
4answers
2k views

How will large index INCLUDE fields effect system performance?

This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index. I’m trying to improve performance in an slow and unstable database application. In some ...
5
votes
4answers
1k views

Is it critical for a database to have a relationship table/diagram or not?

Got a question and wanted to get the best practices or thoughts on this. I have an application that was developed with a SQL Server back end (site is Entity framework, vb.net, asp.net). The original ...
6
votes
2answers
4k views

SQL Server Profiler is available for SQL Server 2008 Dev Edition?

I just upgrade from SQL Server 2008 Express with advance services to Dev Edition. I wonder whether SQL Profiler is available for the Dev Edition? Please let me know how to access that if it is ...
8
votes
1answer
30k views

Finding the last time a table was updated

The query: SELECT name AS TableName, create_date AS CreatedDate, modify_date as ModifyDate FROM sys.tables order by ModifyDate; ...will tell me the last time a table was created ...
18
votes
4answers
124k views

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 ...
0
votes
1answer
1k views

SET keyword in SQL Server

I have been using SET keyword to assign the values to parameters declared in SQL Server something like, Declare @StudentName Nvarchar(max) SET @StudentName='ABC' I'm not sure how to write this if ...
17
votes
5answers
287 views

Enforcing database integrity

Would this ever make sense to have application enforce the database integrity instead of having foreign keys, check constraints, etc? How much of a performance improvement one can expect for not ...
10
votes
6answers
18k views

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 ...
3
votes
2answers
2k views

Having a unique ID for 2 different tables

We would like to use two different tables, one will hold an object when it is 'Active', and the other will hold the object once it becomes'Non-Active'. The ID is therefore unique per both tables (...
5
votes
3answers
298 views

Is there a way to query SQL Server 2005/2008 for location of its log files?

I there a way to query SQL Server 2005+ for the file system location of its various log files. By that I mean the text log files, not database transaction log files? For example my SQL Agent, Error ...
21
votes
3answers
7k views

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, ...
1
vote
1answer
122 views

SQL Server function invocation syntax question

The following excerpt is extract from this page. CURRENT_USER (Transact-SQL) Returns the name of the current user. This function is equivalent to USER_NAME(). When I execute the two ...
6
votes
2answers
1k views

Why did Microsoft kill “English Query” on SQL Server?

Given the popularity of the natural language user interface Siri, which was acquired by Apple for $200 million, I've wondered if anyone knows why Microsoft killed "English Query" on SQL Server.
0
votes
1answer
440 views

Differences between CMS and MSX/TSX?

What is the differences between Central Management Servers and Master Server (MSX) / Targer Server (TSX) concepts in SQL Server? I know that MSX/TSX is related to SQL Agent and the basic idea is to ...
14
votes
3answers
3k views

Is there a reason to update statistics manually?

In SQL Server, statistics are updated automatically when Auto Update Statistics in True (which is the default). Is there a reason to update statistics manually and in what circumstances?
16
votes
2answers
5k views

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 ...
20
votes
1answer
14k views

Should I use many single field indexes, instead of specific multi column indexes?

This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection". I'm working with an existing SQL Server (2008) application that has a number ...
2
votes
2answers
3k views

Single Identity column and composite key, which to make primary?

I've got a table that's a working copy subset of a base table. The base table has a composite key with a clustered index. However I need a single column unique identifier for my framework to work ...
7
votes
2answers
1k views

What's a good relational structure for units and complex unit conversions?

My company is in the Energy industry and I need to come up with a good way to represent the conversion of units of measurement. I've done some searching and have yet to find a good article covering ...
4
votes
1answer
15k views

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...
14
votes
4answers
59k views

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 ...
4
votes
2answers
1k views

why we can specify table names enclosing them with []

Is there a reason we can specify a tablename with [schema].[tableName] instead of just schema.tablename? is it JUST to allow spaces in a tableName?