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)

4
votes
3answers
591 views

Most performant SQL query needed

I'm storing documents with fields that are set in a DataSource table. The document is written to the database as follows: Document table (ID, DataSourceID) Document fields (DocumentID, ...
3
votes
1answer
330 views

How Commit works in a transactional push replication with updatable subscribers

I want to know if in a transactional push replication with updatable subscribers, a commit is only done when all subscribers have succeeded to make it, or if it only guarantees the correct commit in ...
2
votes
1answer
231 views

Dropping then adding a table to a subscription with Identity option Not for Replication causes errors

We have a Transactional Replication environment that was initialised from a backup that replicates the data for several hundred tables. When a table is altered, say a new column added, I have created ...
2
votes
1answer
1k views

What are the names of SQL Server database statuses 24, 1024 and 65544?

using the following query: SELECT Name, Status FROM dbo.sysdatabases WHERE name IN ( 'db_name' ) I get in some cases status = 24, other times 1024 and other times 65544 I see that even when status ...
6
votes
1answer
203 views

DBCC TRACEON with the global flag

In the question Need Help Troubleshooting Sql Server 2005 Deadlock Scenario there was a suggestion of using DBCC TRACEON (1204, -1) to trace deadlocks globally. When reading about this command in BOL ...
1
vote
2answers
786 views

SQL Server AlwaysOn Failover Transparency

This is using RC0. I have set up a SQL Server 2012 AlwaysOn (or more accurately, highly-likely-but-not-always-on) Availability Group. I take the Primary offline. The Secondary takes up to 25 ...
2
votes
1answer
333 views

Accessing Service Broker from Different Databases

In my Application we have a lot of databases nearly 100 dbs. We are planning to use Service Broker for calling a Stored Proc Async. Instead of running Service Broker on all the Databases, Is it ...
12
votes
4answers
1k views

What are efficient ways to deal with database schemas that are shared between code branches?

Working on a project with multiple branches, where each branch is eventually merged back to the main branch, and in essence is isolated in order to develop a new feature. The database, which is MS ...
2
votes
3answers
1k views

Loading a FACT table per day

I have a database with 13 billion rows, per day I have around 20-30 mio rows. On top of this I have one cube, one of its dimensions is DateTime that goes down to milliseconds. To load the fact table I ...
3
votes
2answers
166 views

Failing SQL Job

I've been passed a sql server scheduled job that's recently started failing. It basically runs a stored proc that runs a query, the query joins a bunch of tables together and populates a derived ...
6
votes
2answers
2k views

Shared Lock issued on IsolationLevel.ReadUncommitted

I read that if I use IsolationLevel.ReadUncommitted, the query shouldn't issue any lock. However, when I tested this, I saw the following lock: Resource_Type: HOBT Request_Mode: S (Shared) What is ...
5
votes
3answers
3k views

Is there a way to see the access log of a table?

I am using MS SQL Server 2008. I have a table in a database, in which records are being inserted and deleted. E.g. Initially the table is empty. A Java program (probably) inserts few records in it. ...
3
votes
3answers
3k views

SQL table design for primary keys (best practice)

I have a User table that contains the following fields ID (Auto-generated GUID) UserName Password FirstName LastName Email AddressID (GUID) This is linked to the table Address (1-1 relation) which ...
0
votes
1answer
679 views

Make SQL Server Agent retry failed script with Try/Catch

I have a script that has a try catch in it so that if it fails I can roll back the transaction. It runs on a job scheduled through SQL Server Agent. But when it fails SQL Server Agent does not see ...
6
votes
3answers
2k views

Can you prevent more than X number of rows being updated in SQL Server?

Is there a way to configure SQL Server to limit the number of rows than an update statement can modify? Say I wanted the limit to be 30,000 rows and someone fired an update that would modify 45,000 ...
1
vote
1answer
644 views

How to setup daily SQL server export to csv text file?

We are exporting 100,000+ rows from a SQL Server 2005 DB to a text file. We have a web script setup to read all rows and then write the text file and it takes over 5 minutes to complete that script. ...
0
votes
1answer
11k views

Working example of OPENDATASOURCE command using SQL Authentication

I need to run some ad-hoc queries on several hundred servers that are not part of a domain. Each server has a low-privilege SQL user account that has read-only access to a few tables of interest. My ...
7
votes
4answers
3k views

SQL Server Partitioning - what to use for partition key?

I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued ...
1
vote
2answers
2k views

How can I check the status of transactional replication?

How can I check if there are commands waiting for being executed in the subscribers and find if there is any error occurring in my publication. I want to be able to do this programmatically so the ...
1
vote
2answers
1k views

Any security related reasons to prefer Windows Authentication over Sql Server Authentication?

This is a rather short question: When using Microsoft SQL Server (version 2005 and newer), are there any security related reasons to prefer Windows Authentication over SQL Server Authentication? ...
7
votes
5answers
1k views

Over use/correct use of schemas?

Having asked This question on Stackoverflow, I wondered where what I have done is correct/best practise. Basically, every object that I create is going into a schema with the schema name reflecting a ...
4
votes
4answers
299 views

SQL Server Optimization/Configuration for Inflexible Application

I'm responsible for overseeing an application which uses SQL Server as its backend storage solution. Unfortunately, the application has no concept of how a RDBMS should be utilized and seems to use it ...
8
votes
3answers
7k views

Why are NULLs sorted first?

Why is it that when we have a NULL value in a column and we order by the value ascending, the NULLs are sorted first? select 1 as test union all select 2 union all select NULL union all select 3 ...
2
votes
1answer
881 views

Can I get similar Full Text Search functionality without using Full-Text Search?

Normally, I wouldn't post this kind of question here, but I can't seem to find anyone who has actually used SQL Server Full Text Search, so I'm posting here. I'm creating a solution for a client that ...
33
votes
3answers
2k views

Is the concept of a clustered index in a DB design sensical when using SSDs?

When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made ...
3
votes
3answers
768 views

Restoring SQL Server databases - tips and tricks?

Anyone have any tips and tricks when it comes to restoring SQL Server databases? I recently restored a .bak from one server/instance to another, using SQL Server 2005 on both, and have run into the ...
7
votes
2answers
5k views

What is the “Chaos” Isolation level and when should it be used?

ADO.NET documentation shows the possibility of setting the transaction level for a SQL transaction to Chaos. It sounds unpleasant, but if the feature is there, presumably it has some legitimate use. ...
2
votes
4answers
1k views

SQL Server 2008 transaction log growing since SP3 Installed

On our production db (~117gb) we quite a few tables being replicated. Historically our maintenance plan has kept the transaction log fairly small. Since we installed SP3, the transaction log is ...
5
votes
1answer
106 views

Possible pitfalls of condensing 2 SQL Server instances into 1

I currently have 2 SQL Server 2000 database instances (each running on a different physical server), each containing around 12 databases (not all are active). We are planning an upgrade to SQL Server ...
11
votes
2answers
22k views

SQL Server commands to clear caches before running a performance comparison

When comparing the execution time of two different queries, it's important to clear the cache to make sure that the execution of the first query does not alter the performance of the second. In a ...
2
votes
2answers
157 views

Want to know details and meaning about the trace of SQL Server

I referred to this article: sp_trace_setevent (Transact-SQL). And trace my SQL Server on production machine for months. EventId of most of the traces were: 10,12,14,17 The MSDN says: 10 ...
1
vote
2answers
371 views

Difference in Size of SQL Server 2008 R2 backup on Local disk and on Remote Network Share

I wanted to take backups of all SQL Server 2008 R2 databases on a network share, I already have a backup schedule for backup on local disk. I have tried two methods: using SQLBackupAndFTP Software ...
3
votes
2answers
637 views

Can an article in Sql Server replication be based upon a query?

I am looking to replicate the results of a query, is that possible or do I need to rep the tables themselves?
2
votes
2answers
439 views

Advanced search engine or server for relational database

In my current project we are storing big volume of data in relational database. One of the recent key requirements is to enrich application by adding some advanced search capabilities. In the Project ...
5
votes
3answers
214 views

How to monitor how much data is transfered in a linked server?

I have a linked server to a remote computer and doing some insert, update and delete operations between local and remote databases. I want to know how much data is transfered when operation is doing. ...
7
votes
2answers
10k views

SQL 2008 R2 creates user/schema when Windows user creates tables

We added a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity: USE master go CREATE LOGIN ...
9
votes
5answers
880 views

Copying 500 GB SQL Server database from Production to Dev instance

We need to copy 500 GB database from our PRODUCTION server to a DEV server. We only have a window of 5 hours. What would be the quickest way to achieve this?
0
votes
1answer
475 views

Coalesce Function Returning Duplicates

I have a many-to-many relationship setup between three tables. CompanyNotices (M-M) CompanyNoticesLocations (M-M) Locations I would like to retrieve all rows from the CompanyNotices table for ...
1
vote
3answers
556 views

What's the solution for corrupted MDF files?

We are working on an MDF file that has been corrupted, because a virus deleted 2 MB from the header of the file. We also have another backup of this MDF File from 3 months ago. Both MDF files have ...
0
votes
2answers
4k views

why user with db_owner & db_BackupOperator database role is unable to restore database?

I'm in sysadmin server role on the server . All Programmers are in dbCreator Server role , So they can create databases for themselves and they have in db_owner & db_Backupoperator database role ...
2
votes
2answers
1k views

How to change DB file location with Replication

I need to move few DB files to another location on same server due to low disk space. Replication is present with that DB and this scenario is on subscriber server. Is there any way to do this without ...
1
vote
1answer
2k views

OPENROWSET can't import an excel spreadsheet into SQL Server 2005 Express

I am trying to run the following query to select data from an excel spreadsheet: SELECT FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_ADR3,FT_CP,FT_VILLE,FT_TEL,FT_FAX,F10 FROM OPENROWSET ...
28
votes
7answers
9k views

Why should I create an ID column when I can use others as key fields? [duplicate]

Possible Duplicate: Why use an int as a lookup table's primary key? So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not ...
3
votes
1answer
187 views

Why SQL Server installs multiple services for AS and RS, but only one for IS?

When I install multiple SQL Server instances on a same computer, every instance gets its own SQL Server Service for Analysis Services and Reporting Services. But I get only one service for Integration ...
3
votes
1answer
110 views

Is it possible to create a database called C that is a view into all tables in database A + B?

Using SQL 2005 or 2008, is it possible to create a database called C that is a view into all tables in database A + B ? Databases A, B, and C are on the same local server. I am wanting to be able ...
1
vote
2answers
2k views

Allow db_owner to add/view existing logins to database

On our development server (SQL 2008 R2) we want local dbo's to be able to add existing logins on the server to the database. The problem is they can't view them, they can only see themselves in the ...
2
votes
1answer
249 views

Do I trace an Active Directory group based database login the same way as an AD user based login on SQL Server?

We are currently undergoing an PwC audit on our database logins. Some of our user accounts have sysadmin privs, which of course is a major security breach. I have successfully done traces on ...
0
votes
1answer
104 views

What is the rationale for requiring a column list when inserting into a table with identity column?

When inserting into a table with an identity column from some similar staging table, we have to set IDENTITY_INSERT to ON and specify a column list. Why do we have to specify a column list in this ...
4
votes
3answers
1k views

How can I make my SSIS process consume more resources and run faster?

I have a daily ETL process in SSIS that builds my warehouse so we can provide day-over-day reports. I have two servers - one for SSIS and the other for the SQL Server Database. The SSIS server ...
2
votes
3answers
3k views

How to programmatically remove a push subscription?

I have 4 databases, a publisher and 3 subscribers (transactional replication). In case of an error in the publisher I want to: delete all subscriptions create a new publisher make the two remaining ...