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)

7
votes
3answers
763 views

Performance benefit of taking unused databases offline

I have several databases for legacy applications which run in VMs that are currently off until someone needs to look at some history. I have set each of the databases used by these applications to ...
3
votes
2answers
1k views

How to prevent SQL Server service account from registering / deregistering SPN?

The service account is a domain account. It is not a domain admin (nor is it a member of any group that is a domain admin). It has neither "write servicePrincipalName" nor "Write public information" ...
1
vote
1answer
107 views

Input requested on localization setup for DB

I am building an ASP.Net site that will pull dynamic data from SQL server. The site will be built with localization/globalization foundations in anticipation that in a year or so the site will start ...
1
vote
1answer
641 views

Connect to SQL Server using OLEDB or ODBC?

We have a desktop application written in Visual Basic .NET. One our clients needs to use ODBC instead of OLEDB to connect to the SQL Server 2008 R2 Database. The company has several databases ...
4
votes
1answer
3k views

Should I rebuild index after truncate/large insert?

I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.) Basic outline is very simple: ...
1
vote
1answer
173 views

How to store lots of small text in a SQL Server database?

I have a table with a varchar(max) column that stores text ~4-10KB in size. Data is just being added - never modified, it is a log. My problem is that a table grows too large in size - I would like to ...
11
votes
1answer
1k views

DELETE vs TRUNCATE

I am trying to get a greater understanding on the differences between the DELETE and TRUNCATE commands. My understanding of the internals goes something along the lines of: DELETE -> the database ...
3
votes
2answers
533 views

Is it possible to get schema ID in another database without using dynamic sql?

I start with an object ID and a database ID. Inside a user defined function, I want to get the full name and ID of the database, schema, and object. How can I get the schema ID without using dynamic ...
4
votes
2answers
13k views

Why is my database stuck in the Restoring state?

I have some unit tests that manipulates data in a database. In order to guarantee that the database state is always the same throughout all the tests, we're trying restore a database snapshot at the ...
9
votes
1answer
268 views

DB_ID context from farther up call stack

In SQL Server, is it possible to get the DB_ID from the context from farther up the call stack? My goal is to create some handy (and admittedly hacky) utility functions in a dev sandbox database ...
7
votes
1answer
871 views

SQL Server 2000 to 2008 to 2012

We are migrating from SQL Server 2000 to 2008 now. We are making lot of changes to code, scripts because there has been a lot of change since 2000 to 2008. While making these changes, What I want to ...
2
votes
1answer
87 views

SQL Server Profiler behavior?

I want to monitor only sp's. Does SQL Server Profiler listen to all events and then filters the desired data (e.g. sp's)? Or rather the sql emits only Profiler pre-configured events ?
1
vote
4answers
538 views

Recommendation for synchronizing SQL Server DBs

I have two databases, prod and dev. Each DB is about 500GB. At the end of each work day, the prod database gets several million new rows and several thousands of updates. After the data is loaded in ...
2
votes
1answer
2k views

Msg 7391, Distributed Transactions (DTC) on SQL Server

This may be DBA related or developer related, I honestly don't know. I have two Database servers: A: SQL Server 2005 (Trigger that uses DBMail on Server B) B: SQL Server 2008 R2 (DBMail configured) ...
4
votes
1answer
3k views

How to update one database from another?

I'm using SQL Server 2008. I have two databases called Adb and Bdb. There is a common table called commonTable. How can I update Adb database every 15 minutes using the data from the same table in ...
2
votes
1answer
1k views

Downgrading SQL Server 2008 R2 Standard to Web Edition

I have to downgrade a SQL Server running SQL Server 2008 R2 Standard Edition SP1 to 2008 R2 Web Edition SP1. I discovered this question on Server Fault: Can I downgrade SQL Server 2008 R2 ...
9
votes
3answers
658 views

Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?

The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
7
votes
2answers
2k views

How do Availability Groups work with cheduled jobs and SSIS packages?

I have 2 SQL Servers set up with a database in an AlwaysOn Availability Group. I also have jobs set up which run against the database. During a failover how do I ensure the jobs will continue to run ...
2
votes
1answer
106 views

Where to put utility (not administration) stored procedures in SQL Server? [duplicate]

Possible Duplicate: Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use? I have some simple user-defined functions ...
3
votes
1answer
222 views

Lost databases in SQL Server 2012

Had three databases connected to SQL server 2012, unfortunately, now I can't see any of them under the Databases part of the tree in SQL Server Management Studio. The files (.mdf) are still on the ...
6
votes
2answers
814 views

Conditional compilation of SQL Server stored procedure

Short version: Is there some way to conditionally compile chunks of TSQL code in a SQL Server Data Project using SQL Server Data Tools for Visual Studio 2010? I'm using SQL Server Data Tools in ...
3
votes
2answers
98 views

Is there a way to speed up an ALTER statement in SQL Server

I have an existing table with millions of rows in it where I have a column which is the SMALLMONEY data type and I want to ALTER it to be MONEY. To make things a bit more complicated it has an index ...
4
votes
2answers
4k views

Why won't SQL Server optimize the UNIONs?

Consider these queries (SQL Fiddle): Query 1: SELECT * INTO #TMP1 FROM Foo UNION SELECT * FROM Boo UNION SELECT * FROM Koo; Query 2: SELECT * INTO #TMP2 FROM Foo UNION SELECT * FROM Boo UNION ALL ...
2
votes
1answer
3k views

exist (select * from my table)

My colleague suggests that Query 1 should run faster than Query 2. If it's true, please anyone explain me the reason. Thanks. Query (1): IF ( SELECT COUNT(UnitTrustCounterId) FROM ...
3
votes
2answers
135 views

Why is this key lookup required?

I have an admittedly-complex query that I'm optimizing and I'm stuck on the next step. I have pinpointed the bulk of my problem to one part of my query that's doing the following Key Lookup but I'm ...
11
votes
2answers
666 views

What is shared between two instances on the same server?

We want to install multiple instances of SQL Server on a single Windows server. We cannot use VMs because the server is bare metal Windows 2008 R2. For security purposes we need to ensure that the ...
7
votes
2answers
3k views

why is io_stall_writes_ms so much higher for tempdb?

We have the user and system data files on the same disk drive. The ( io_stall_write_ms / ( 1.0 + num_of_writes ) ) is below 2 for the user files but the tempdb files are typically over 400. I see ...
5
votes
1answer
1k views

Kerberos authentication not working with Linked Servers in SQL Server 2012

I am setting up a DEV/TEST environment using 2 SQL Servers running SQL Server 2012 on Windows Server 2012. We are moving from SQL Server 2005 on Windows Server 2008, where we already have this ...
6
votes
3answers
6k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
0
votes
1answer
440 views

Problem with SQL Server connection from a Linux machine [closed]

On a Linux machine, I run a java application that accesses a SQL Server instance on a Windows machine. When I run this application I get the below error: 07-Dec-2012 15:09:40 ...
3
votes
2answers
484 views

Get the windows account which runs the query?

I'm using sql server 2008 r2. I logged in to ssms which in turn connects to a remote sql server machine. im writing a query which writes a file. I need to know - which windows permissions I should ...
3
votes
1answer
1k views

SMO v11 (SQL Server 2012) not scripting key definitions, indexes, and constraints

I have a .ps1 PowerShell script that runs on a Central Management Server. The script goes out to all databases in our environment, and scripts out all of of objects. For tables scripts, the output ...
1
vote
4answers
2k views

How to prevent SQL Server database server from running out of disk space?

What are the best preventive approaches that we can do to prevent the SQL Server database server from running out of disk space ? Is there a way to set the maximum database file size (both the ...
0
votes
1answer
91 views

SQL Server Installation choices

I'm installing SQL Server 2008 R2 which would be helpful in A. querying database. B. Analysis Services C. Reporting services and BIDS (SSRS) D. Powerpivot in Sharepoint During installation I get ...
4
votes
1answer
1k views

How to link users and logins in an Availability Group?

I've got an Availability Group going and need to map some logins to database users on an active secondary replica. (Node2) Unfortunately, when I try to exec sp_change_users_login or alter user with ...
1
vote
2answers
651 views

“In Use” memory on a SQL Server box

I have a SQL Server 2012 database server with 64GB of RAM. A process called DataPumpService runs on the same box and loads around 400 million records from 2700 flat files. I want to understand what ...
2
votes
2answers
902 views

Moving database file locations in SQL Server 2008

I have SQL Server 2008 Express edition installed on my machine. I had changed the location of some folders from one drive to another (from F: to E:). Now when I connect to SQL Server using SSMS, it ...
6
votes
2answers
707 views

case versus isnull versus coalesce

I am just reading through the documentation for the SQL Server 2012 exams and I saw the following point: case versus isnull versus coalesce Now, I know HOW to use each one but I don't know WHEN ...
9
votes
2answers
504 views

Bulk insert into SQL Server from VMWare guest using distributed switch

This is mostly likely not a SQL server issue but the setup seems to only be affecting BULK INSERTS to SQL Servers. We have recently moved VM Hardware and all the guests that were moved had their ...
1
vote
1answer
198 views

SQL Server - Ignoring object dependencies to copy a DB (despite some column referenced in a sproc no longer existing)

First of all apologies for the novel but there's a lot to explain... We have over 100 databases for our product, 1 per server, each of which has variations on other ones (ideally they should all be ...
3
votes
1answer
92 views

Large inserts breaking indexed queries

Very similar to Large insert/update in table causes timeout while SQL Server re-arrange indexes only I am not using SSIS with only occassional large (predominently annual) inserts from another staging ...
2
votes
3answers
160 views

Determining if indexes are redundant

I have a table with the following structure: Id (int identity PK) FooId (int FK) BarId (int FK) QuxId (int FK) Other fields The following indexes have been defined: PK (Id), clustered Index1 ...
3
votes
0answers
111 views

How to Audit for Access with Cross Database Ownership Chaining

I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for ...
1
vote
1answer
1k views

Can't grant role permissions under sysadmin login

I'm trying to execute this script from Management Studio: GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdaptAdmin] AS [AdminSapr] Execution fails with this message: Msg 15151, Level 16, State 1, ...
4
votes
3answers
1k views

Logging query text without Profiler

I'm in the process of porting an old, semi-broken ASP.NET/SQL Server application to PHP/PostgreSQL. Among the torments^H^H^H^H^H^H^H^Hchallenges I'm faced with is that the reporting is done by a ...
5
votes
1answer
577 views

Why isn't partition-level lock escalation the default?

In SQL Server, locks are normally escalated from row or page -> table. Starting with SQL Server 2008, a new level of lock escalation was added - partition level. However, this isn't automatically ...
3
votes
0answers
267 views

MS DTC errors on SQL Server 2008 R2 but not SQL Server 2000 running on difference instance

I am receiving errors when I try to run a distributed query against different SQL Server 2008 R2. At first I thought this might be cause the servers have been set up on a VM using a clone but after I ...
-2
votes
1answer
343 views

Missing quotation - openquery to oracle [closed]

I am not able to successfully run a code with openquery to a oracle server. Please do not take into account field names/data types, as I had to present only a part of the whole procedure. It's quite ...
5
votes
1answer
311 views

Multi part identifier in Microsoft SQL Server

I'm migrating a SQL Server 6.5 database to SQL Server 2012. Migration went well but one of the software using this database performs a strange query: UPDATE myTable SET Field1='',Field2=0, ...
5
votes
1answer
2k views

Appending backups while overwriting expired sets

I've been reading up on this, but the online documentation is a bit difficult to understand and seems awkwardly written. For example the INIT, NOINIT. One says it will try to overwrite everything. ...