Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

2
votes
1answer
1k 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
3k 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
1k 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
2k 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)) ...
7
votes
2answers
2k 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
148 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
524 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
439 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 ...
11
votes
4answers
2k 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
2k 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
350 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
961 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
2answers
1k 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 ...
0
votes
1answer
618 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
843 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
1k 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. ...
6
votes
3answers
4k 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 ...
0
votes
1answer
656 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 ...
0
votes
1answer
189 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 ...
1
vote
1answer
325 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
530 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 ...
7
votes
4answers
1k 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 ...
4
votes
4answers
692 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
2k 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 ...
6
votes
1answer
16k views

Finding last time 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 and modified (from a ...
8
votes
3answers
34k 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
800 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
263 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 ...
7
votes
3answers
10k 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
1k 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
255 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 ...
18
votes
3answers
4k 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
116 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
328 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 ...
12
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?
15
votes
2answers
3k 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 ...
14
votes
1answer
6k 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
2k 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 ...
6
votes
2answers
904 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
12k 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] ...
7
votes
4answers
42k 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
925 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?
5
votes
2answers
844 views

Restrict SQL Server 2008 R2 User to restore only one database (but not others)

It is my understanding that a user has to be a "dbcreator" in order to restore databases. However, what if I want a specific user to restore only one specific database, but not gain access or restore ...
2
votes
2answers
2k views

How to re-create the ##MS_PolicyEventProcessingLogin## principal

I'm getting a bunch of errors in my MS SQL Server logs about a missing ##MS_PolicyEventProcessingLogin## principal. I went to check the master database and yes, it is indeed missing. I don't know how ...
2
votes
3answers
511 views

SQL Server 2008r2 replication from multiple sources

I am currently rearchitecting an application so that each of seven businesses will have there own "live" SQL Server 2008 database and at scheduled times during the data the data from all seven ...
3
votes
1answer
307 views

Checking lock granularity

I understand in SQL Server there are various levels of granularity for locks...RID, Key, Page, Extent, Table, DB. Forgetting about lock escalation, I just want to check the default level of lock ...
5
votes
1answer
133 views

Find out which columns must be specified in insert-statement

Sometimes when inserting data in a table with many columns it could be useful to know which columns must be specified if the insert-statement shouldn't fail. I wrote this query to find out which ...
6
votes
3answers
817 views

SQL Server Table Size Discrepancy

Hopefully this has a simple answer and I've missed something obvious. I have a table where the number of rows * the row size is much smaller then the actual data space used by the table. If I run the ...
1
vote
2answers
450 views

Impact of changing instances' passwords

As part of a security policy, the company I work for has given me the task of changing the sa's and SQL Server Agent service accounts' passwords for several instances. What's the possible impact of ...