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)

3
votes
2answers
2k views

Procedure cache (plan cache) size

I'm developing an application with many, dinamycally-assembled, complicated SQL queries. I'd like to keep the query plans in the procedure cache for as long as it is possible. To prevent discarding ...
1
vote
2answers
198 views

XML Large DataBase is the best? [closed]

Is the best way to implement a large database is XML? Or is there a better way? 1 - reduce the time complexity 2 - Reduce the amount of traffic between the tables And other factors ... Which ...
1
vote
2answers
167 views

2012 Transactional replication not delivering new articles

I have added a new article to an existing push publication with the following code sp_addarticle @publication='statics', article='FlightBaggageMarkup_tbl',source_object='FlightBaggageMarkup_tbl' ...
3
votes
1answer
622 views

Profiler TextData output is null

I am doing a trace on a database and dumping the trace realtime into a table. The reason I'm doing a trace is I want to see all the queries that are being executed against the database. ...
5
votes
1answer
2k views

ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?

Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
11
votes
2answers
2k views

Deadlocks From Locks on Same Temporary Tables In Different Processes

I have found a deadlock that appears to show something I thought was impossible. There are two processes involved in the deadlock: 1. process8cf948 SPID 63 Performing an ALTER TABLE on temporary ...
3
votes
2answers
170 views

Select blocking issue [closed]

The database was not responding to simple select statements like select * from table I noticed in sys.dm_os_waiting_tasks that one session is blocking, blocking session was pointing to a stored ...
-1
votes
1answer
131 views

Covering Index isn't being used and I don't know why

We have this complicated query that I'm trying to make "better" until we move it to pull from a data warehouse. I need a solution that's "good enough" for now and I think I'm about 2-3 indexes away ...
0
votes
1answer
169 views

avoid insert xml duplicate data

how can to prevent duplicate inserts for every Execute? insert into IQ (XMLDT)values('<row>sdsdf</row>')
2
votes
1answer
363 views

Non-clustered Index Update not displayed in execution plan [duplicate]

Possible Duplicate: Nonclustered Index Insert I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution ...
5
votes
1answer
1k views

Performance tuning a cascading delete with many foreign keys

I have a delete query that is taking a long time. Looking at the execution plan, I see that most of the estimated cost in the delete query is in a section of the data model that had a lot of data ...
4
votes
1answer
3k views

ASPState database locking and growth problems

We use an ASPState database to persist .NET Session state on a SQL Server 2005 cluster. We are seeing some strange behavior during peak periods The DeleteExpiredSessions proc is run every minute ...
14
votes
1answer
10k views

How do I attach a database in SQL Server?

Some common questions relating to attaching databases in SQL Server: What does it mean to attach or detach a database? How do I detach a database? How do I attach a database? What ...
2
votes
1answer
528 views

What does it mean if the spt_monitor - table in SQL Server is empty?

What does it mean for my environment if I find that the spt_monitor table in SQL Server is empty? What is this table, how is it used and what populates it? I am using SQL Server 2005. My ...
4
votes
1answer
348 views

Expanding a partitioned table

I have a table that holds four years of data, where for each month I have a partition that is based on a filegroup with two files. Unexpectedly, I received eight more years of data and prepared the ...
2
votes
1answer
1k views

how to convert xml to database table

input: Declare @OUT xml select @OUT = N' <row> <kind>MainCat</kind> <sortid>1</sortid> <kind_id>1</kind_id> <PPoet>حافظ</PPoet> ...
2
votes
1answer
498 views

An unknown error occurred while processing the request on the server [closed]

I have an asp.net 4.0 application with a button that opens a SSRS report. This works fine on my development machine and I can open the report directly with report manager but when I try to access the ...
0
votes
0answers
314 views

Cannot restore Amazon RDS SQL Server database to given point-in-time using SQL Server Management Studio [closed]

I'm trying to restore one of my Amazon DRS SQL Server databases. Using SQL Server Management Studio, I can correct to my DRS database instance using the master account I created when creating the ...
2
votes
5answers
7k views

Cannot start SqlLocalDB instance with my Windows account

I'm the administrator and simply run the command: sqllocaldb start v11.0 Result: Start of LocalDB instance "v11.0" failed because of the following error: Error occurred during LocalDB instance ...
4
votes
1answer
1k views

Cannot restore Amazon RDS SQL Server database to given point-in-time using SQL Server Management Studio

I'm trying to restore one of my Amazon DRS SQL Server databases. Using SQL Server Management Studio, I can correct to my DRS instance using the master account I created when creating the database ...
1
vote
0answers
140 views

SQL server Load Balancing

SQL server user access cut down from 8pm - 9am. During this period our SSIS scripts would run to load data(from Oracle/mainframe/Access) to sql, large database(s) & too much data - mortgage ...
7
votes
3answers
746 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
103 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
635 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
172 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
524 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
265 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
848 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
531 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 ...
1
vote
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
638 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
105 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
216 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
790 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
97 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
134 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
649 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
5k 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: ╔══════════╦══════════╗ ║ ...