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)

6
votes
2answers
969 views

What's a good relational structure for units and complex unit conversions?

My company is in the Energy industry and I need to come up with a good way to represent the conversion of units of measurement. I've done some searching and have yet to find a good article covering ...
4
votes
1answer
13k views

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...
7
votes
4answers
45k views

Easily show rows that are different between two tables or queries

Imagine you have two different tables/queries that are supposed to have/return identical data. You want to verify this. What's an easy way to show any unmatched rows from each table just like the ...
4
votes
2answers
1k views

why we can specify table names enclosing them with []

Is there a reason we can specify a tablename with [schema].[tableName] instead of just schema.tablename? is it JUST to allow spaces in a tableName?
5
votes
2answers
893 views

Restrict SQL Server 2008 R2 User to restore only one database (but not others)

It is my understanding that a user has to be a "dbcreator" in order to restore databases. However, what if I want a specific user to restore only one specific database, but not gain access or restore ...
2
votes
2answers
3k views

How to re-create the ##MS_PolicyEventProcessingLogin## principal

I'm getting a bunch of errors in my MS SQL Server logs about a missing ##MS_PolicyEventProcessingLogin## principal. I went to check the master database and yes, it is indeed missing. I don't know how ...
2
votes
3answers
520 views

SQL Server 2008r2 replication from multiple sources

I am currently rearchitecting an application so that each of seven businesses will have there own "live" SQL Server 2008 database and at scheduled times during the data the data from all seven ...
3
votes
1answer
327 views

Checking lock granularity

I understand in SQL Server there are various levels of granularity for locks...RID, Key, Page, Extent, Table, DB. Forgetting about lock escalation, I just want to check the default level of lock ...
5
votes
1answer
135 views

Find out which columns must be specified in insert-statement

Sometimes when inserting data in a table with many columns it could be useful to know which columns must be specified if the insert-statement shouldn't fail. I wrote this query to find out which ...
6
votes
3answers
871 views

SQL Server Table Size Discrepancy

Hopefully this has a simple answer and I've missed something obvious. I have a table where the number of rows * the row size is much smaller then the actual data space used by the table. If I run the ...
2
votes
2answers
484 views

Impact of changing instances' passwords

As part of a security policy, the company I work for has given me the task of changing the sa's and SQL Server Agent service accounts' passwords for several instances. What's the possible impact of ...
1
vote
0answers
89 views

Managing SQL 2008 database [closed]

We have a vendor application that creates a new database monthly for only few tables. Vendor doesn't have solution. Is there any other way besides partitioning as partitioning require enterprise ...
6
votes
2answers
4k views

Does SQL Server 2008R2 Express actually enforce the memory and CPU limit?

I've been testing SQL Server 2008R2 Express to see if it is limited to 1GB of RAM and 1 CPU. It certainly seems to be using more than one CPU, as I see spikes on all 4 CPUs in Task Manager when I ...
4
votes
2answers
883 views

Specify type on SQL Server Computed Column

I have just added a computed column to a table, and it is showing up as an int and I'd like to make it a bit is this possible? I'm having a hard time finding any info on this. Below is the computed ...
4
votes
5answers
3k views

Managing Transaction Log Size using a Maintenance Plan [duplicate]

Are there any best practices for configuring a Maintenance Plan in SQL Server 2008? Currently, I'm removing database backups and transaction logs greater than 40 hours old, then backing them up. ...
9
votes
3answers
6k views

3rd party dll in SQL Server CLR

I need to use a third party DLL in a trigger c# code in SQL Server CLR But when I try to add reference it just shows some DLLs from SQL Server. How can I add my third party dll to SQL Server?
2
votes
2answers
385 views

How to quantify TEMPDB usage in relation to other databases hosted on same hardware?

Working on a project to decide if configuration 1 or 2 is the best way to organise disks for my SAN: Config1 (sql 2008): 8 disks @ raid 10 data 2 disks @ raid 1 logs 2 disks @ raid 1 tempdb 2 disks ...
4
votes
2answers
3k views

Extracting a field from RESTORE HEADERONLY in sql server

I'm trying to use 'RESTORE HEADERONLY' to get the date when the backup I'm about to restore was made. The command: RESTORE HEADERONLY FROM DISK = '<path to .bak file>' works fine in query ...
2
votes
1answer
660 views

sys.sysprocesses versus DMVs

I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and ...
6
votes
1answer
2k views

What is the best practice for storing large data when nvarchar(4000) is sufficient 99% of the time?

I have a table with about 100MM rows that stores information about a user. CREATE TABLE [dbo].[UserData]( [UserDataID] [int] IDENTITY(1,1) NOT NULL, [UserID] [int] NOT NULL, ...
2
votes
2answers
1k views

How do you block ad-hoc distributed queries from specific sql server instances?

For one reason or another, a particular development SQL Server is unable to openrowset or opendatasource to production servers, but can hit other dev servers. I've tried sqloledb and sqlncli ...
1
vote
1answer
1k views

SQL Server Linked Server Permissions

On a SQL Server 7 server, I have a linked server to a Sybase database server. select * from openquery(SYBASE_SERVER, 'select * from DBA.TESTTABLE') works fine if I'm logged in as an SA. If I try to ...
2
votes
1answer
574 views

Can't create linked server SQL Server 2005

I'm new to doing linked server and I have this error. "The linked server has been created but failed a connection" On the same virtual machine I have two SQL Server instances one 2000 ...
3
votes
1answer
15k views

SQL Server 2008 R2 There is insufficient system memory in resource pool 'default' to run this query

I have a 32-Bit Windows 2003 Enterprise Edition SP2 Server running SQL Server 2008 R2 (10.50.1617.0) Enterprise Edition. The server is a dedicated SQL Server box for our SharePoint 2007 environment. ...
2
votes
0answers
287 views

SQL Server retrieval error under heavy load (200 concurrent users)

Currently I'm facing an issue in a SQL Server retrieval procedure. Stored procedure returns unexpected result when executed by many user (No. of users > 40) concurrently for the same parameter. For ...
0
votes
1answer
354 views

Affinity Mask and Affinity I/O Mask

Using: SQL Server 2008 R2 Under what circumstances would you set the Affinity Mask and Affinity I/O Mask options in sp_configure in SQL Server? We are currently running on a machine with 16 cores ...
2
votes
1answer
199 views

Why evaluates “IS NULL” in SSMS interactive mode to “TRUE” but the same statement being executed in a SP to “FALSE”? [closed]

Given DECLARE @IN__CUST_NO INT DECLARE @OUT__USER_ID INT SET @IN__CUST_NO = 1000006660 CASE 1: IS NULL evaluates TRUE Since no CUSTOMER_NUMBER with the value of 1000006660 exists in our dummy ...
2
votes
1answer
137 views

How to improve this query (using table variables or not?)

I'm having a bit of problems with these tables... TableCost ( TableCostId int, Cost numeric, MonthsId int ) and Taxes ( Percentt numeric, MonthsId int ) I have to calculate a new value by ...
3
votes
1answer
158 views

Is it possible to install a single instance of sql server on a server with 2 NICS and 2 different subnet?

I have a server that has 2 NICS and each is connected to 2 different subnet. Is it possible to connect to this Sql server in each of the different subnet? If so, what special procedure do I have to ...
3
votes
1answer
1k views

Clustered Index Update on Nonclustered Columns

Using: SQL Server 2008 R2 I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table. The issue here is that the columns that are ...
1
vote
2answers
1k views

How to add ansi null and ansi warning in connection string

Using RDO Connection Connection String Set RdoVisPay = rdoVISENV.OpenConnection("VISA", 1, False, "DSN=VISA;UID=sa;PWD=sa;APP=Visual Basic;WSID=" & ServerName & ";DATABASE=" & ...
0
votes
3answers
868 views

SQL Server restore sequence from multiple full, diff and log backups

Suppose I have a lot of backups (may include full, differential and log backups) of a SQL Server database. I want to write a program that can generate a SQL script to take the database to its most ...
1
vote
2answers
769 views

Deny insert/delete for all users in SQL Server 2005

In our database we have about 20 users, that is used be some program (and users are added with this program). Now there was new requirement to use 2 database and do not allow delete data from one ...
3
votes
3answers
2k views

Historical reporting from the database with Reporting Services?

We run a job tracking service as part of the company software. Reports are generated by SQL Server (Standard) Reporting Services usually daily but often as and when needed before meetings etc. As ...
3
votes
1answer
1k views

recover broken msde 2000 8.0 database

i have a msde (mssql) 2000 / v8.0 database that has one corrupted table. Microsoft SQL Server Management Studio Express can see the database STRUCTURE, but not the data. i can see the DATA of the ...
4
votes
2answers
2k views

Is it possible to assign sql logins into a group?

This may be a bit too elementary question for this site but is it possible to control SQL Server authenticated logins using groups? So is it possible to assign sql logins under groups and give rigths ...
1
vote
3answers
1k views

SQL Server query performance affected by rebuilding an un-fragmented index

I have a query within a stored procedure that the same input will periodically time out ( > 30 seconds) when it normally runs in milliseconds. The table has 2077748 rows, 59 columns, and 13 ...
5
votes
2answers
15k views

Why does simple ALTER TABLE command take so long on table with full-text index?

I have a large (~67 million rows) name-value table that has full-text indexing on the value column (DataValue). My question is: If I try to run the following sql command: ALTER TABLE VisitorData ...
6
votes
2answers
491 views

What can cause statistics to get out of line?

I've just worked through a problem at a clients site which it turned out was caused by the statistics being wrong which caused the Optimizer to time out. Running exec sp_updatestats fixed the problem ...
2
votes
2answers
918 views

How do I determine what object two SQL process are contending over?

When long running Query#1 exclusively locks an object (table, index or page), and then Query#2 is executed by another user and becomes blocked on the lock held by Query:1, how would I get the name of ...
3
votes
3answers
417 views

Semi-Annual archiving of SQL Server database

Twice a year we create a static, archived subset of our production database. The newly created database contains roughly half of the tables as the production db (log/audit tables, etc. are not copied ...
6
votes
2answers
3k views

sp_executesql with user defined table type not behaving correctly

Problem Is there a known issue with user defined table types as parameters to sp_executesql? Answer - no, I'm an idiot. Set up script This script creates one each of table, procedure and user ...
3
votes
2answers
92 views

FK on NON Primary Keys

Is there any performance problem / difference if I have one Foreign Key that points to another table Indexed Fields that is NOT the PK ? Something like DECLARE @T1 TABLE ( T1_ID INT ...
1
vote
2answers
7k views

Single User mode can't be turned off and no users are connected

I have a database that is in Single User Mode. When I run this: exec sp_dboption 'MyDb', 'single user', 'FALSE' It fails, telling me that there is still a user connected. But when I run both of ...
10
votes
2answers
6k views

Create View in specified database with dynamic sql?

I am writting a dynamic sql to drop and create view in different database. So I wrote: set @CreateViewStatement = ' USE ['+ @DB +']; CREATE VIEW ...
5
votes
1answer
941 views

Deadlocks in SQL Server

We're currently encountering a problem with out ASP.NET Application that results in deadlocks on the SQL Server database. A bit of background information I can provide is that we rely heavy on AJAX ...
4
votes
2answers
2k views

Does SQL Server have Date_Format function?

As title, does it? All I found is a list of predefined DateFormat that I can choose from, like this As I remember in MySQL (and PostgeSQL too?) you can defines your Date Format: i.e ...
5
votes
3answers
6k views

Are there any advantages in SQL Server Always On vs. SQL Server 2008 R2 Mirroring with only two nodes?

We are currently hosting a SaaS website on two Windows 2008 R2 / SQL Server 2008 R2 Servers. Would upgrading to SQL Server 2012 / Always On give us any advantages with respects to high availability? ...
5
votes
2answers
3k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
0
votes
2answers
1k views

Clustered index always better than Non-Clustered index?

When I have a clustered index on 'A', is it better to use this clustered index even if I have selection of 'A=constant'? If not, why? Thank you!