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

learn more… | top users | synonyms (2)

3
votes
1answer
6k views

moving large number of tables to different filegroups

An existing database has a large number of tables stored in the PRIMARY filegroup. I want to move theses tables and their indexes on different filegroups automatically, depending on the "prefix" of ...
5
votes
2answers
894 views

varchar performance impact

This question has been asked before ( speed impact of using varchar in MSSQL 2008 ), but the answers are not detailed enough to my satisfaction. Admitedly this is not a current issue and mainly for ...
10
votes
3answers
5k views

TSQL performance - JOIN on value BETWEEN min and max

I have two tables in which I store: an IP range - country lookup table a list of requests coming from different IPs The IPs were stored as bigints to improve lookup performance. This is the table ...
1
vote
2answers
77 views

Retrieve free data

Goal: Review data Problem: Where can I get a database with free data for SQL server? I need to review data to improve my ability as data analyst.
8
votes
3answers
2k views

Is it better to populate variables using SET or SELECT?

What is the better way (with regards to performance) to set a value to variable? By SET command: DECLARE @VarString nvarchar(max); SET @VarString = 'john doe'; SELECT @VarString; By SELECT ...
4
votes
1answer
88 views

Issue with CAST using linked servers (possible bug?)

So I've got a some SQL Server 2008 DBs arranged in a star schema. Server A is the one users submit queries to. On Server A there is a DPV called Detection that's basically a UNION of table Detection ...
5
votes
1answer
3k views

SQL Server memory usage

I'm trying to understand more about the memory usage for SQL Server. I can't seem to figure out what's exactly stored in the memory buffer. My setup is a Windows Server 2008 x64, 8 GB RAM, ...
15
votes
3answers
4k views

Anybody using HierarchyId in production? Is it reliable?

Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
5
votes
2answers
7k views

Grouping records based on intervals of time

I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the ...
2
votes
1answer
757 views

Disabling Indexes and Auditing for large data import

Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are: 1. Is it ...
0
votes
1answer
527 views

Stored Procedure is not returning complete result

I am using the query as mentioned in the answer accepted in the question located at Stored Procedure doesn't show result in SQL Server Now when I run this SP with the values provided in the ...
1
vote
1answer
157 views

Why is replication attempting to distribute NULL values into Identity columns

We are sporadically getting this error: Explicit value must be specified for identity column in our replication environment. I have checked the transaction command that is reporting the error and it ...
1
vote
1answer
1k views

how to set/change the font style in database?

This question may be duplicate with another but my question is this. I want to set or change the font style in header of table (like hindi font or any other) of database. I am using sql server 2008 ...
4
votes
1answer
840 views

How to query EndDate value from ReportServer..Subscriptions.MatchData

In SQL 2008 R2, I'm putting together some administrative queries on SSRS metadata to check proper configuration, and other sorts of proactive things. While querying the ReportServer db, I've been ...
2
votes
1answer
114 views

Associate a piece of data with a session

I think I want to associate a piece of data with each session. The association must have the following properties: The data being associated is provided either as a part of connection string (if ...
1
vote
1answer
353 views

Send a trigger to an application upon a specific SQL Server server usage pattern

I've been dealing with a vendor's unconfigurable application which is, unfortunately, the most feature-complete in its niche. I'm looking to augment its capabilities with on-top screen display. Its ...
4
votes
1answer
210 views

Filtered index statistics refresh threshold

We have filtered indexes in our production environment. While doing some research about them, I came across this article "Filtered indexes and filtered stats might become seriously out-of-date" ...
2
votes
1answer
750 views

Stored Procedure doesn't show result in SQL Server

I have created a stored procedure (to be used in SSRS) that has parameters whose values are more than one. When I execute this stored procedure in SSMS by providing the values for that parameter, SP ...
3
votes
1answer
4k views

Memory utilization per database - SQL Server

I am trying to find the memory utilization per database(using dmvs) in a sql server instance, ie (Buffer Cache + Procedure Cache) over a period of time. I have the query to get the buffer cache ...
8
votes
5answers
7k views

Does SQL profiler affect server performance?

I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load. I have found on the internet that ...
2
votes
1answer
4k views

SQL Server 2008 R2 64bits repair error

I had to reinstall the default server instance on one Windows 2008 R2 server. I checked that the sql groups were cleaned and the registry keys cleaned before reinstalling. After reinstalling, the ...
44
votes
4answers
14k views

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other. Our ...
4
votes
3answers
15k views

Slow insert in SQL Server 2008

Our products' database contains an audit trail table: CREATE TABLE gn_AuditTable( gn_ObjectId int NULL, gn_Action smallint NULL, gn_Time datetime NULL, gn_UserId int NULL, gn_Login ...
4
votes
1answer
2k views

Sum values at a group level rather than detail

I have an invoice report I'm developing for some project billing. The main tablix has several groups, in this order: ProjectGroup TicketGroup NoteGroup Details (actual billing details) The data ...
1
vote
1answer
783 views

Can you rename or name Reporting Services jobs?

In Report Manager you can create schedules, either shared or one-of's. When you create these schedules a job is created in SQL Server with names that look like guids. IS it safe to rename these jobs ...
1
vote
1answer
5k views

Clarify - Need to add Multiple foreign key constraints

I am not sure if I am thinking about this process the wrong way. Just to Surmise if I create a table(TeamList) whose sole purpose is to capture the coaches team list of players and positions for a ...
3
votes
3answers
3k views

View foreign key constraints so I can delete a table

I have copied a database I was working on so I could change some key elements of its design for my new version. I want to delete totally some tables from the database however I cannot due to some ...
1
vote
1answer
741 views

Why do I see sp_OACreate, sp_OASetProperty in sp_WhoIsActive?

I've seen this happen a few times: sp_WhoIsActive shows various sp_OA% sprocs, with my user name, but I'm certainly not running them directly. Does this reflect some kind of query rewrite process? ...
2
votes
1answer
504 views

Can you rename replication jobs?

I have snapshot replication running on three different servers, two publishers and one subscriber. The replication processes have associated jobs (either building the snap shot or grabbing the snap ...
6
votes
1answer
713 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
574 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
137 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
438 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
223 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. ...
6
votes
2answers
189 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
156 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 ...
12
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
1k 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
3k 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 ...
1
vote
0answers
1k views

Installing over a SQL Server 2008 Express default instance named “SQLEXPRESS”

We have a system in which there a SQL Server Express 2008 default instance called "SQLExpress". Normally the SQL Server Express default instance is called "MSSQLSERVER". Now we have an application ...
17
votes
3answers
5k 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
513 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
218 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
117 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. ...
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, ...