All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

4
votes
2answers
30k views

How to Find the Foreign Key Associated with a Given Primary Key

All, I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via SELECT * FROM INFORMATION_SCHEMA....
2
votes
0answers
197 views

SQL Server : Transactional Replication Filter By Computed Column

There is a database that contains records for multiple groups of people. These records do not contain a column indicating which group the record is "owned" by (very frustrating). I need to setup ...
0
votes
2answers
940 views

SQL Server 2008: Diff table data on different servers with exact schema

I'm trying to find a good open source tool that simply compares data in a database on one server to a table (of the same schema) on another server. Then I want to generate INSERT statements for the ...
2
votes
1answer
246 views

Availability Groups and Partition Swapping

Is it possible to use SQL 2012 Availability Groups over two machines and perform partition swapping on the primary machine ?
0
votes
2answers
840 views

Remote Desktop Session's Machine ID for SQL Server Query

How to get machine name of a user, who is connected on an instance through Remote Desktop and used windows authentication login. I need it for each query she has executed. sys.dm_exec_connections just ...
2
votes
3answers
2k views

Verify all Stored Procedures

How can I verify all stored procedures in a SQL Server database? I need to verify if all stored procedures still work, after some tables/views etc. have been deleted or changed.
2
votes
0answers
372 views

Best Practice to update columnstore indexed tables

I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
3
votes
0answers
350 views

SQL Server Event Alerts Lack Enough Details

I setup SQL Server Alerts to send me emails whenever certain events (by severity) occur. Below is an example of en email I received for a "014 - Insufficient Permission" severity event of Type "SQL ...
3
votes
1answer
37k views

How do I add minutes to a time data type?

I have a stored procedure which inserts two records into a table, the difference between the records is that the time column of the second record is @MinToAdd after the first: CREATE PROCEDURE ...
2
votes
0answers
45 views

improving this goal query

Goal joining the two tables: tbl_quickSearchParam (fulltext search results) against tbl_product ( active products : prod_status = 1, group by prod_image_name) create table tbl_product ( `...
3
votes
1answer
2k views

How much of a view is persisted when you create an index?

Say I have a view defined by the following SQL: SELECT t1.id, t2.name, t3.address FROM Table1 as t1 INNER JOIN Table2 t2 ON t1.id = t2.tID INNER JOIN Table3 t3 ON t1.id = t3.tID From what I ...
2
votes
1answer
429 views

How much overhead is involved with querying a DB on the same server?

History The company I work for had two databases, DB A and DB B, on two different servers. A contains information pulled from a COTS application database in addition to information from our own ...
0
votes
2answers
961 views

Is there a SQL Server equivalent to “OVERRIDING USER VALUE”

While googling I found about the OVERRIDING USER VALUE parameter for an INSERT for IBM's iSeries. Is there an equivalent command for Microsoft SQL Server versions 2005 or newer to allow you to ...
1
vote
1answer
5k views

Find fulltext indexes in SQL Server 2008

How do I find a list of tables that have a FULLTEXT INDEX applied to them on a given database? For example, you can enable and disable a FULLTEXT INDEX like this: ALTER FULLTEXT INDEX ON [dbo].[...
1
vote
1answer
6k views

Possible Causes of a Slow Sql View When Filtered

I have a view in Sql Server 2008 that when performing just a: SELECT * FROM [Table] Will return roughly 40000 rows by 130 columns. Here are 6 runs with client statistics on (roughly 7 seconds ...
1
vote
3answers
337 views

SQL Server 2000 performance

I have a SQL server with about 150 tables and the 5 tables that are used the most consist of over 10 million rows each. I'm trying to see why the performance of the server has been diminishing day by ...
9
votes
4answers
11k views

cannot connect to sql server in single user mode

I have started my SQL Server 2008 sqlcst02\inst01 in Single User Mode by appending the ;-m to the end of the startup parameters for this instance in SQL Server Configuration Manager. I have stopped ...
4
votes
3answers
3k views

Remote queries and linked servers

I have 2 servers with SQL Server 2008 R2: INT & CRM. I need to run an SSIS package on INT, which uses data from both an INT database and a CRM database. It's painfully slow. I tried this to ...
3
votes
1answer
625 views

Does sp_droparticle @force_invalidate_snapshot=1 invalidate the entire snapshot?

Forgive this simple SQL Server question, but if I just want to remove a single stored procedure from snapshop replication (2008 SQL Server R2), if I call sp_droparticle, will it invalidate the entire ...
0
votes
1answer
755 views

Stored procedure encryption and master service master key, SQL Server 2008

Is the Service Master Key (SMK) used to encrypt stored procedures in SQL Server 2008? I know it is used for data, but what about database objects? If the Service Master Key is not used, then I would ...
1
vote
1answer
1k views

Sql Server rollback to some point in time

This seems like a fairly common scenario, yet I'm not sure how to do this. I have a very large database (think a lot of terabytes), which makes it impossible to create a full backup. At some point, I ...
1
vote
3answers
6k views

How do I use SSMS to compare Database 1 Table 1 against Database 2 Table 1?

I have two database - Database1 and Database2. Both database contain a table that have similar structure as follows: =================================================================================...
2
votes
2answers
113 views

Advice about Backup Solution

Our database Server is being hosted by another company. They provide a file level backup that occurs once per day. They also allow for Log Shipping. My company is taking advantage of their ...
2
votes
2answers
134 views

Migrating SQL Server 2000 to SQL Server 2008

I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause? Thanks.
1
vote
1answer
588 views

How does disabling a network protocol affect SQL server performance

The SQL server 2000 process info monitor displays about 100 processes that use a mix of Named Pipes/TCP-IP and (integrated authenticated) users. I was wondering if: disabling Named pipes - or - TCP-...
1
vote
1answer
1k views

How to determine SQL server version install history? I.E. When it was updated last

I have various SQL server installs across several machines, and a different IT staff will sometimes randomly update each machine with different service packs and cause version issues. Is there a way ...
2
votes
1answer
2k views

SQL Server recommended MAXDOP settings for NUMA

I have an OLTP SQL Database running on SQL 2008 R2 Enterprise Edition that may be fairly busy (5000 batches/sec) in next couple of months after going live production. Looking into server configuration ...
2
votes
2answers
2k views

Oneway sync of two SQL Servers via FTP

I need to replicate data from a ‘master’ SQL Server out to a number of replicas. Luckily the data only flows from the ‘master’ to the replicas. I need to replicate both schema and data. Data size ...
2
votes
4answers
2k views

Update many-to-many relationship table

I have three tables: task, taskmembers, members. Each task may have up to five people associated to it. Currently, when someone updates a task I delete all the current members and insert them as a ...
6
votes
4answers
6k views

SQL Server script to delete accounts no longer in Active Directory

We have a SQL Server 2000 that will shortly be migrated to SQL Server 2005. It has years of Windows Authentication accounts created that no longer exist in Active Directory, which prevent the Copy ...
4
votes
4answers
8k views

Restore SQL Server database using Windows Powershell 3.0

I'm trying to restore a SQL Server database with a PowerShell script, but I'm having problems. Here is the error I'm getting: Exception calling "SqlRestore" with "1" argument(s): "Restore failed ...
4
votes
2answers
1k views

Right Trimming Binary Data in SQL Server

Scenario: I am inserting a string into a binary field (CONTEXT_INFO) and then later attempting to pull it out and convert it back to a string. When I do, the resulting string has a length of 128 ...
2
votes
1answer
3k views

SQL Server - how transactions and transaction log work (simplified)

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model. Now, everything ...
1
vote
1answer
180 views

SQL Server 2005 Blocking Issue

I am experiencing a blocking issue in sql server 2005. An end user executes a query (e.g. select statements) and seems to get into a never-ending blocking until i kill the process e.g. for hours on ...
4
votes
3answers
255 views

Easiest Way to Move 100 Databases

I need to move about 150 databases from one server to another server. SQL Server 2008 Web Edition SQL Server 2012 Preview (in a different datacenter - East Coast Azure) I was planning on moving ...
8
votes
1answer
868 views

Equality query on NVARCHAR column yields multiple results in SQL Server 2012

I am in the process of moving a pet project from PostgreSQL (9.2.2) to SQL Server (2012 Standard). I've noticed an interesting phenomenon when querying unicode words. Given the definition: CREATE ...
3
votes
1answer
765 views

Why SQL server unload CLR assembly every midnight

Every midnight not a specific time, the sql server will unload the CLR assembly. The reason is that due to memory pressure, the assembly is marked with unload and will be unloaded soon. How to make ...
2
votes
2answers
8k views

Multiple triggers vs a single trigger [closed]

Scenario: Each time data is inserted/updated/deleted into/in/from a table, multiple unrelated items of business logic need to be executed. Question Given that the solution is to use database ...
2
votes
1answer
259 views

can you run sql server 2005 and sql server 2008 r2 in mirror mode?

Must all db servers be running the exact same version to enable mirroring without a witness server? How about with a witness server in place?
2
votes
2answers
7k views

SQL Server drop and create all users in every DB

I would like to drop and recreate every user in a database without having to manually do this for 500 users. I have this so far: USE [Maintenance] GO IF EXISTS (SELECT * FROM sys....
1
vote
3answers
854 views

Point in time recovery from the same database - what does it mean?

I have two questions. I have a database in full recovery mode. Lets call transaction log an LDF file for short. SQL Server allows me to restore to point in time in the past when restoring from ...
5
votes
1answer
9k views

What is the difference between NORECOVERY and RECOVERY when restoring database?

Can anyone explain the main differences? Any help appreciated.
8
votes
2answers
3k views

SQL Server - contents of transaction log file in more detail

I have a question regarding transaction log (let’s just call it LDF for short) contents. I am assuming a database with full recovery model. I have read that LDF file contains (logs) each and every ...
0
votes
2answers
294 views

How can I make only *one* user controlled database

How can I system admin let it be me, but another users and admins must be cannot access my database. My database located on customer server. I deleted all sql users, there's only sa user. This persons ...
1
vote
3answers
937 views

Support both stored procedures and functions with same TSQL code base?

We have a need for both Microsoft SQL Server 2008 functions (ITVFs) and stored procedures to provide the same interface and functionality to end-user reporting frontends. Given the following ...
2
votes
2answers
648 views

Advantages of Database Snapshot

Can someone please highlight the advantages of Database Snapshot in SQL2008. I read some where that "When a Database Snapshot is created, SQL Server doesn’t allocate space on disk equivalent to the ...
2
votes
0answers
425 views

SQL Server 2008 R2 - Deadlock Trace, Need Help Understanding It

I really need help on understanding this deadlock, i've already replaced the values, mind if anyone helps me interpreting where my deadlocks are coming from? What does e_waitpipegetrow mean? btw, i'm ...
12
votes
1answer
10k views

How to manage 3.1 billion rows of data?

I am currently tasked with implementing a storage schema for a relatively large amount of data. The data will primarily be accessed to determine a current data point value, but I am also required to ...
2
votes
0answers
102 views

SQL Server Online Training [closed]

I recently took an intro class for MySQL from www.oreillyschool.com and it was very helpful. The next class they offer is administering MySQL but I currently work with SQL Server. The first class was ...
8
votes
2answers
600 views

SQL Server Select Count READ_COMMITTED_SNAPSHOT QUESTION

I seem to be getting a lot of deadlocks when doing select count(*) on a particular table. I have already changed all the required parameters and made them into row only locking. I've also changed the ...