SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

6
votes
1answer
765 views

Including clustered index key in non-clustered index

We're running SQL Server 2008. In looking over the results of a query I ran on sys.dm_db_missing_index_* views, I see a curious suggestion for an included column. I have the following two tables: ...
3
votes
1answer
609 views

querying a linked PostgreSQL database in SQL Server 2008

We will be deploying an application with a Postgres back-end and need to provide report writing capabilities in SQL Server. I am exploring options for real-time or near real-time reporting in this ...
0
votes
1answer
140 views

Re-subscribing to a merge publication after edits made offline

Hypothetical Question - To help scenario planning. Let's say I have a 2008 R2 Merge Publication with 5 active subscribers. The publication is deleted and all subscriptions are removed. A week or so ...
3
votes
1answer
539 views

What is a parse tree object?

I have looked into sys.syscachedobjects table. I found some records have an object type of parsetree. Can anyone tell me in detail what a parsetree is? Is this a data structure used by SQL Server, ...
1
vote
1answer
246 views

how to pull data source information on data-driven subscriptions

I would like to know more about the data-driven subscriptions on our ReportServer. I am specifically looking for what shared data sources are being used, but the connection string would also suffice. ...
7
votes
2answers
193 views

In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key?

In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns? I am aware of the fact that PRIMARY KEY and ...
5
votes
2answers
172 views

Are existing entries immediately included into a newly created index?

Suppose I have a table Stuff with a column City and no indices including that column. I populate the table. Then I decide to create an index: CREATE INDEX [StuffOnCityIndex] ON [dbo].[Stuff](City ASC)...
13
votes
1answer
5k views

When should you specify PAD_INDEX?

So, you can use FILLFACTOR to leave space in leaf index pages. Specifying PAD_INDEX also leaves space in intermediate nodes. What scenarios should you specify PAD_INDEX, and what benefit does it ...
4
votes
2answers
2k views

Add column taking too long

I'm trying to add a column to a database. The query has been running for 25mins and it's locking web access to the table and breaking our website: alter table MyTable add MyNewColumn varchar(max) not ...
5
votes
2answers
2k views

Compare Microsoft SQL server performance between virtual and dedicated server

I have a dedicated windows 2008 r2 server at the moment and I am thinking about migrating to a virtual server because of the costs. Now I know that SQL should be on a dedicated server, but the ...
2
votes
2answers
2k views

Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008

If I give all RAM to SQL Server, it will use it's own pattern to determine what RAM to allocate for what. If I allocate portion of RAM as RAM Disk and put tempdb into that disk it will force RAM ...
7
votes
2answers
4k views

A good SQL sniffer

Can anyone point me to a good SQL sniffer where I can watch the SQL commands that run against my database in real time? If it's free, that would be great.
5
votes
2answers
2k views

how does SQL Server know to lock view objects?

Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow. In SQL Server 2008 I have a view V over tables A and B that looks roughly like ...
17
votes
3answers
6k views

Painless way to create a clustered index on a huge table?

So we have a customer site that is complaining about some seriously slow performance. I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some ...
2
votes
2answers
601 views

pull records that are inactive

I need help with this query. I have records in customer table Main table - M +---------+---------+----------+ | company | Account | Active | +---------+---------+----------+ | A | A123 | ...
3
votes
1answer
1k views

async_network_io issues with BIDS

This is related to my previous question drastically different runtimes in BIDS and the SSRS Web Portal. I am struggling with refreshing some reports in BIDS because they run forever. I have now ...
1
vote
1answer
2k views

Executing a stored procedure on remote database

I have 2 databases on the same server. DB-A and DB-B. DB-B has a user that has read and write access to database B. I would like to grant the user in DB-B execute permissions to 2 stored procedures in ...
4
votes
1answer
225 views

Can the table spool operator be parallelised?

Is there something inherently unparallelisable about the table spool operator in SQL Server? I've got several queries that use parallel operators throughout due to the size of the tables, but by ...
0
votes
1answer
129 views

Copy snapshot files from Publisher, Paste and Apply on subscriber in SQL Merge Replication

I read an excellent article about copying the snapshot files for Transactional replication from Publisher to Subscriber and Apply them on subscriber. http://blogs.msdn.com/b/repltalk/archive/2010/03/...
3
votes
3answers
2k views

Why am i getting deadlocks with 1 read and 1 write thread in SQL

if anyone could please help me understand why the following transactions deadlock? I am providing the 2 transactions and the indexes for each table: Transaction 1: SELECT blockId, blockData, ...
3
votes
2answers
763 views

SQL Server consuming all my CPU

I already have a question on gis.stackexchange.com assuming it may be geoserver the problem but as I go deeper into problem I found that I have a database bottleneck. My SQL server CPU usage is almost ...
1
vote
1answer
325 views

Update and select concurrently running on same set of rows but different indexes are referred?

In multithreaded application where update and select can go simultaneously assuming update is used within transaction. If table has non clustered index and clustered index, what are the chances of ...
2
votes
1answer
493 views

Can we use SQL Express to send messages to SQL Standard using the Service Broker?

We want to use SQL Express at our local sites but to still be able to push data back to our head office using the Service Broker. SQL Server at the head office is 2008 R2 Standard. Looking at the ...
1
vote
1answer
194 views

Overloaded Database - Batch Updates vs Atomic/Single Updates

We have on overloaded database which is now receiving failed transactions consequently. The proposed solution is to create a 2nd database that taskes all the (ecommerce) transactions initially and ...
4
votes
1answer
207 views

SQL Server - server-side tracing

I've successfully started some server-side tracing on a customer's SQL Server instance thanks to a few great posts out there, but since that's really my first ever in a "real-life" situation, I was ...
2
votes
1answer
103 views

Help on writing server-side trace

I need help on writing a server-side trace to check logins to try to determine if and when vendor accounts are used. The purpose is to remove sa access. These accounts sure have sa but I need to ...
3
votes
3answers
554 views

SQL JOIN Syntax in MS SQL

I have been taught in my MSSQL classes, this is how to join two tables select * from FirstTable A JOIN SecondTable B on A.ID= B.ID Now in my professional life, I came across JOIN queries like ...
1
vote
2answers
190 views

Get Total in a single line in SQL Server

I have a very simple SQL Query which is getting results in the following way COLUMN A COL-B WINDCHILL 99.998889 WINDCHILL 96.000000 Now how i can modify my query so that I get the ...
5
votes
2answers
559 views

Is there a setting to force an error when asigning a varchar longer than a variable?

I would like to get an error when assigning a longer varchar to a variable and no silent truncation. Are there any settings? This truncates silently: declare @code varchar(10) set @code = 'This is ...
3
votes
1answer
62 views

How do I create logins for all database users in a database that was just restored for a different server?

I was recently tasked with moving some databases to a new server. I created backups then restored the databases to the new server and it looks like the database users were retained, however there's no ...
2
votes
1answer
8k views

TSQL Get minute part from time data type

With the time data type (I'm using Microsoft SQL Server 2008), is there any way to take only the minute part out of it? I tried to pass the time to datepart and datediff function but both refused to ...
1
vote
1answer
161 views

Which should be used after messing up clustered index? Update statistics, Reindex or Reorganize

If I have a clustered single column index, e.g FooId in SQL Server 2008, 2012 and I know I have messed up the order of the rows to not have sequential order of the FooId column anymore, which command/...
2
votes
1answer
2k views

Error attaching database after deleting NDF file

I have deleted .ndf file by mistake from a SQL Server 2008 database. While trying to attach a previous copy, SSMS raises this error: Attach database failed for Server 'MDSWIN34'. (Microsoft....
2
votes
2answers
83 views

Data Backup and Restoring the same if requried in SQL SERVER 2008

In Multi-tenancy , Shared Database Shared Schema. How to take Tenant level backup and restored the tenant data when required? Can any one guide me or point me to the right direction. Thanks!!! NB: ...
1
vote
1answer
2k views

FILESTREAM is enabled, but I get a message that it's not. Anyone able to help?

I have been given the task of setting SharePoint Foundation 2010 up in work. I have hit quite a few problems (see other questions I have posted), so I decided to start from scratch again. I have ...
1
vote
1answer
994 views

SQL Server 2008 R2 - VLDB Backup Strategies

I have a 200GB database running on SQL Server 2008 R2 Enterprise Edition. I just recently partitioned it in order to archive 11 months of history, so it was pushing 400GB prior to archiving. Our ...
2
votes
2answers
2k views

SQL Server 2008, cpu 100% until recreate an index

I have a 4000000 lines table (SQL Server 2008). This table has a private key composed of two int columns. One corresponds to a PK in another table of the same database. There is a relation between ...
3
votes
2answers
1k views

Select total number of records from common table expression

We are using common table expressions to temporarily save the search results. WITH Results ( ------ ) AS ( select --- ) SELECT * FROM ...
2
votes
2answers
6k views

Access Linked Server error “The provider did not give any information about the error.”

A little information. I'm running SQL SERVER 2008 R2 64bit. I have Microsoft.ACE.OLEDB.12.0 provider installed. On my production server I've setup a linked server to the access db using the provider ...
2
votes
1answer
329 views

How to determine the size of a SQL Server database for a specific date in the past?

Microsoft SQL Server 2008 R2 Standard What I would like to find out is what the size of the DB was at specific dates in the past. I don't have backups going back that far. The current log may or ...
2
votes
1answer
4k views

How can I move LOB_DATA ( varbinary/image/text) to different Filegroups

LOB_DATA : varbinary(max),varchar(max), image , text columns. I have 4 tables with large amount of LOB_DATA in it. I would like to move LOB_DATA in these tables to different File group in a different ...
5
votes
3answers
2k views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
2
votes
2answers
387 views

Can I store half of a record on one page and half of the record on another page?

I'm watching Paul Randal's MCM video from http://technet.microsoft.com/en-us/sqlserver/gg313756.aspx From 27:11 of the video, the author said a record must be saved in one page, you can't have half ...
1
vote
2answers
208 views

Web application using SQL Server 2008 starts hanging

We have a custom web application that uses SQL Server 2008 (10.50.1600). It's worked fine for years, but lately web pages that return data "instantaneously" have begun showing the "endless swirly blue ...
-2
votes
2answers
1k views

How can I remove an index scan from a SQL Server SELECT statement?

This select statement: select * from tab1 inner join tab2 on tab1.col1 = tab2.col2 where tab1.col3 in ('<some conditions>') In the execution plan I see two indices used. But the second ...
2
votes
2answers
3k views

how to use database mail from SSIS

I try to use gmail in database mail. I have no problem to send out email from it. But how I can configure my send mail task to send out email by using database mail. Thanks
7
votes
3answers
467 views

Prevent Data Sources from being deployed outside designated folder

Before I just go ahead and put a really awful check constraint on the Catalog table, I wanted to solicit some better ideas first. I want to ensure that all shared data sources on our report server ...
3
votes
4answers
4k views

migrating tables from Postgres to SQL Server 2008

(I fear what I wish to accomplish shall not be easy, but a lot of my googling around about it yielded rather dated results, so here it goes...) I have a Postgres 8.4.x database with over 100 tables. ...
2
votes
1answer
2k views

Activity Monitor Showing Long Duration for WAITFOR

Using Activity Monitor, I am consistently seeing the following query showing an average duration of over 300 seconds: WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, ...
1
vote
1answer
309 views

SQL Data type for “Reference” fields in table - SQL Server

I am learning SQL Server. I need a bit of advice regarding data type for fields that will be referred to but will not actually themselves require values. I don't know the correct term to use(edit ...