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)

1
vote
1answer
4k 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
2k 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 ...
9
votes
0answers
4k views

Fast SQL query with * but extremely slow with specific columns? [closed]

We have a query that we're running on SQL Server 2008 in the Management Studio. I can't give the exact query details, but let's say it's equivalent to this: SELECT * FROM MyView a (nolock) INNER ...
0
votes
1answer
258 views

Write Access Client App for SQL Server 2005 Backend [closed]

I'm writing an inventory/payroll system, and I'm storing all the records on a remote server running SQL Server 2005 on Windows Server 2008 R2. I was wondering how I could start writing forms(very ...
5
votes
1answer
589 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: ...
15
votes
3answers
3k 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 ...
6
votes
4answers
2k views

Do I really need to keep .LDF files?

Each month we make an end of month snapshot of our production databse. These month end snapshots are strictly for reporting purposes, there are no inserts, updates or deletes ever done on them. Each ...
3
votes
1answer
2k views

SQL Backups - Full vs Differential vs Log Shipping

I'm looking to refine our SQL backups utilizing Full, Differential, and Log Shipping. Currently, I am only performing full backups once at night. This process is taking so long that it is running ...
22
votes
4answers
22k views

When is it OK to shrink a Database?

I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I ...
1
vote
1answer
1k views

Index Strategies on Text or NVARCHAR(MAX) Fields

I have the following query (simplified for question) I'm trying to speed up for a read only DB ... SELECT [sysid] ,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101) ,[From]=[from_addr] ...
3
votes
1answer
231 views

Optimizing queries against a set of tables that contain partitioned data

I have a third party program that creates and populates a series of tables to hold some historical data: data_33 -- contains data for 12/01/11 - 12/05/11 data_34 -- contains data for 12/05/11 - ...
3
votes
1answer
6k views

SQL Server 2008 R2 on Windows 7 x64 Professional - what are the downsides?

I need to minimize the cost of the platform, that will run SQL Server 2008 R2 Standard Edition. I have a special licensed version of this SQL server to work with like 20 users (it's an 1C special ...
3
votes
3answers
329 views

How do you ensure accuracy of your query

I am have a table with about 100,000 rows. Lets suppose the primary key is some sequence of numbers like a barcode for example. I am doing checks like SELECT count(*) FROM TABLE WHERE ...
3
votes
2answers
4k views

What are the limitations of Toad for SQL Server freeware? [closed]

I just downloaded the freeware version of Toad for SQL Server. Toad's download site says: "the Freeware edition has certain limitations, and is not intended to be used as a TRIAL for the Commercial ...
2
votes
3answers
1k views

ETL: extraction strategy for 200 source databases

What is the best extraction strategy for approximately 200 SQL Server 2005 source databases (same schema) for a daily load into a staging area in preparation for data warehouse cleansing, ...
10
votes
2answers
37k views

Returning a result set with multiple rows based on max date

I have a child table that is something like this: [Cust Date Table] | Customer ID | Some Date | Balance | +-------------+------------+---------+ | 1 | 2012-04-30 | 20.00 | | 1 ...
1
vote
1answer
2k views

Weak entity and one-one or many-many relationship

In Entity Relationship Model, can the supporting (or identifying) relationships of the weak entities be many-many or one-one rather than many-one? Suppose I have a customer entity (C), a video entity ...
7
votes
2answers
21k views

How to add a user with access to a single view?

I'm working with MSSQL Server Management Studio 2008 and I need to expose a view to a third party for their data reconciliation. I have created the appropriate view but I'm having trouble creating a ...
3
votes
1answer
543 views

Changing Query Results?

I have a query that I am running and I just noticed that every time I run it, it is returning a different amount of records (usually less by 1 or 2 rows). My question is how can this be possible? I am ...
3
votes
1answer
328 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
2answers
956 views

Backup database and clean old backup question

Right now we have a maintenance plan which looks like this: backup all databases clean old backups (> 24 hours) This job runs every day. Every now and then this script crashes during backup step ...
2
votes
2answers
3k views

Will altering order of columns in GROUP BY affect performance?

I have a SQL query like this: SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D) FROM SomeTable GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END) On a huge dataset the actual execution plan ...
4
votes
5answers
3k views

Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

Is it possible to have SQL Server 2008 R2 Standard automatically execute a stored procedure in any given database that is restored or attachced to the instance? I've got close to a solution by ...
3
votes
2answers
489 views

How do I count rows with two properties in one index scan in SQL?

I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it. I need to output the following: for each day I need to ...
2
votes
1answer
196 views

database connection time expired issue

I have a server machine and four client machine for database connectivity. I installed sqlexpress 2008 in server and one machine and all remaining systems, they have connected the database through ...
2
votes
1answer
408 views

Greater than operator ignoring a nonclustered index?

I have a pretty simple query in SQL 2008 R2 and I have a nonclustered index on the "sDate" datetime column. When I look at the execution plan for the following query it seeks the nonclustered index. ...
12
votes
1answer
4k 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 ...
5
votes
3answers
395 views

Restoring SQL DB from down SQL Server (on domain) to existing SQL server (not on domain)

If and when my SQL server goes down and takes too long to get back up, can I restore my DB on another SQL server and run my software from there (I can point which SQL server to run from)? Will all ...
4
votes
2answers
1k 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 ...
2
votes
1answer
4k views

Using a foreign key on multiple fields in one table

Simple question probably. For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does ...
5
votes
2answers
3k views

Find the disk size of specific rows

I am trying to figure out the disk size a number of rows in various tables take up. I can define this rows by a query. How can I find out the actual disk size that corresponds to the result set in ...
5
votes
1answer
587 views

SQL Server falling below 5000 free pages

I have set alerts on two SQL Server 2005 instances I have in production. One of those alerts is sent whenever the Free Pages performance counter falls below the 5000 pages mark. Both servers normally ...
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 ...
9
votes
0answers
3k views

Deploying dacpac application to SQL Server 2008 R2 [closed]

I cannot get dacpac working on my installation of MS SQL Server 2008 R2. I am using Visual Studio 2010 Shell with MS SQL Server Data Tools to build a project into a dacpac file. However, when I try to ...
2
votes
1answer
496 views

Configure sp_send_dbmail to send emails ONLY to certain recipients

For our environment test we need to configure sp_send_dbmail to send emails only to certain recipients. Let's say a stored procedure executes the following code: EXEC msdb.dbo.sp_send_dbmail ...
6
votes
3answers
655 views

Single query sending and retreiving duplicate data on MS SQL Server?

Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes ...
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.
4
votes
3answers
4k views

PageIOLatch_Sh waits noticed after tuning an index

Lately, a lot of queries to the database are timing out. While investigating I found that queries are in the suspended state with wait_type as PageIOLatch_Sh. I have not noticed so many queries being ...
-1
votes
1answer
322 views

Important distinctions between DBMSs [closed]

DBA is a career I'm strongly considering after school, but my limited experience mostly involves some PL/SQL Oracle web programming, some basic PHP/MySQL web pages, and some queries in SQL Server. ...
5
votes
2answers
230 views

Does Restore “From database” restore from the most recent backup of the database or the actual live database?

In SSMS 2005, I want to use the GUI to copy a database by restoring in to a new database. I was using the "From database" radio button for the source, but then the databases in the list disappeared ...
2
votes
1answer
544 views

unreachable SQL Server for a group of computers

I'm deploying SQL Server 2008 Express in my company. I already installed, did a application, tested, gave permission to user. All desktop users run the program without any problem. But the notebook ...
8
votes
3answers
2k views

Referencing database programmatically via T-SQL

I am writing a stored procedure that takes a database name as an argument and returns a table of that database's indexes and their fragmentation level. This stored procedure will live in our DBA ...
4
votes
1answer
206 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 ...
21
votes
2answers
3k views

As a DBA, how would I go about transitioning from Oracle to SQL Server?

I'm an Oracle DBA that also has Sybase experience. What are the major architectural and conceptual differences between the two RDBMS platforms? An answer similar to the SQl Server->Oracle question ...
0
votes
1answer
95 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. ...
2
votes
1answer
163 views

Do auto created statistics indicate that you should build an index?

In a blog post from 2008 the famous Kalen Delaney wrote: What is the difference between statistics and indexes? Do auto created statistics indicate that you should build an index? Check out ...
3
votes
2answers
641 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 ...
3
votes
3answers
1k 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
867 views

List all database containing table and value

in one of our SQL Servers we have several databases attached and most of them contain a little table, let's say dbo.DBType and that table will always contain 1 record with two columns, first column ...