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)

16
votes
11answers
6k views

Why aren't primary key / foreign key matches used for joins?

As far as I could find out many DBMSs (e.g. mysql, postgres, mssql) use fk and pk combinations only to constrain changes to data, but they are rarely natively used to automatically select columns to ...
0
votes
1answer
205 views

Tips for using DML Statements [closed]

I've recently began learning T-SQL and have found that there are some rules to using single table queries. For instance: GROUP BY If aggregate and non-aggregate expressions are used together in a ...
6
votes
4answers
4k views

Can I be automatically notified of prolonged blocking in SQL server?

About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a ...
2
votes
1answer
655 views

Correct SQL Server linked server connection settings to MySQL that will allow inserts

I'm in linked server hell! I've created a linked server to a remote MySQL database. I'm able to query and do updates. However when I attempt an insert I get an error: "[MySQL][ODBC 5.1 ...
2
votes
4answers
975 views

Remove sub-string from WHERE clause

I have a stored procedure that is using the SUBSTRING function in the WHERE clause. SELECT DISTINCT ColumnBB, ColumnCC FROM TableAA WITH (NOLOCK) WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA; ...
6
votes
2answers
316 views

How to query the names of encrypted stored procedures?

What query will identify the names of stored procedures which are encrypted? Here's what I got so far - select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE = 'PROCEDURE' -- need ...
5
votes
1answer
453 views

What is the impact of large varchar data types on indexing?

We have some tables in our database that have a data type of varchar(8000) where the longest actual value in that column is 75. When we place an index on the column we get the expected warning that ...
4
votes
2answers
376 views

Computed column with less size than the “referring column”

I have a column with data type nvarchar(max) and I wold like to index this. However, since it's too big it's not possible. So I figured I could create a persisted computed column based on that column ...
3
votes
3answers
9k views

How do I preserve the leading zeros when I insert a number into this table? [closed]

I have inserted two records into a table. create table num(id int) insert into num values(0023) insert into num values(23) select * from num When I query them, they all display as 23. It means that ...
1
vote
0answers
397 views

Keep original column names in SSRS report designer 3.0?

I have queries returns a lot of (over a hundred) columns which names contain important underscores, >, < and Uppercase/lowercase characters. However, when creating the reports, SSRS designer (3.0) ...
4
votes
1answer
335 views

SSRS set datasource of report model

I created a Report Model which will be consumed by the End users to create ad hoc reports using Report builder tool. The Report Model can be created using the Report Model Project in Business ...
2
votes
1answer
304 views

Oracle Discoverer like app native for SQL Server?

Can someone point me at some web based apps that work like Oracle Discoverer but native for SQL Server. We already the SQL connector running to actually use disco, but was wondering what other ...
1
vote
1answer
460 views

DATETIME2 and IDENTITY as composite key

I am currently developing a new database schema with an opportunity to do things right. The purpose of the development effort is to collect data (events) from various sensors that will be recorded ...
2
votes
1answer
628 views

Can a clustered index with low cardinality cause deadlocks?

I'm looking at a deadlock, which happens when two processes execute an UPDATE statement on the same table. Both transactions are implicit, meaning that they span only the current statement only, which ...
8
votes
1answer
8k views

SQL Server 2008 Change length of varchar on live prod table

I have a MS SQL Server 2008 R2 DB server currently in use with a production app. A new enhancement to the app now requires a varchar(100) column in a table to be increased in length. Can the ...
6
votes
3answers
16k views

How do I get a list of all the partitioned tables in my database?

How do I get a list of all the partitioned tables in my database? Which system tables/DMVs should I be looking at?
6
votes
1answer
665 views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
2
votes
3answers
4k views

What's the difference between functions and stored procedures? [closed]

I heard that the difference between functions and stored procedures in SQL Server is that functions are not stored in the database while stored procedures are. Is that true? If not, what are the ...
1
vote
1answer
712 views

Cursor doesn't print text when I tell it to

I have used WAITFOR combined with a cursor in the following script: create table orders(orderId int primary key,productId int,productName varchar(20)); insert into orders values(1,11,'book'); insert ...
5
votes
2answers
1k views

Dropping and recreating indexes for performance reasons?

I'm maintaining Somebody Else's Code. There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive. But it's ...
5
votes
1answer
209 views

Triggers and the Transaction

We recently made a change to create constraints based on logic previously used in stored procedures, and part of that included the use of INSTEAD OF triggers to centralize logic. The logic is ...
2
votes
1answer
1k views

Why is SQL Server 2008R2 using more memory than the “max server memory” setting? [duplicate]

Possible Duplicate: Microsoft SQL Server 2008 Memory I've explicitly set the Maximum Memory value in SQL server 2008R2 to 13,000 MB, however when I check task manager I see it is using ...
9
votes
1answer
6k views

CREATE DATABASE vs. CREATE ANY DATABASE permissions

In Microsoft SQL Server, what is the difference between the CREATE DATABASE and CREATE ANY DATABASE permissions? I am unable to find an authoritative answer. The best I can infer is that either (a) ...
0
votes
2answers
248 views

Merging Results while avoiding Key Constraint Error?

I have a table with a primary key which is two integer fields, each a foreign key to a different table. (Multi to Multi table) In one of the foreign tables two rows are merged and this table is ...
10
votes
1answer
945 views

DBCC CHECKDB firing every 20 - 60 seconds

I have a dev environment that is rapidly approaching deployment into production and have noticed in the logs that roughly every 20 seconds I see the message: Starting up database 'dbname' CHECKDB for ...
2
votes
1answer
192 views

Views - Indexes/Keys/Execution Plans

I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server. There is a ...
3
votes
1answer
116 views

Synchronising databases / database technologies

I need some advice before we engage in some costly development work We have two SQL Server database servers on the same network. One runs an in house database and the other a proprietary database ...
2
votes
1answer
181 views

If i issue a rollback when recovery mode is bulk logged, will my bulk operations be rolled back?

If my recovery model is BULK LOGGED, I'm in a transaction, and I've issued some statements that are minimally logged like SELECT * INTO, will a ROLLBACK undo those minimally logged statements?
0
votes
2answers
166 views

Which SQL Server system database is more important: master or resource? [closed]

Which SQL Server system database is more important: master or resource? If the resource database is damaged, can the database engine work? Can the system work with only the master database? If not, ...
2
votes
2answers
378 views

why does my clustered index scan have ordered = false

When I look to at my query plan, I see plenty of clustered index seeks (yay!) and scans (not quite so yay!) but in some of them ordered = False. I will mention that we have parallelism involved, ...
5
votes
1answer
1k views

What's difference between standard view and indexed view in SQL Server [closed]

I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't? Indexed view needs to be ...
4
votes
3answers
2k views

Test if any fields are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
2
votes
2answers
1k views

Move (Restore) SQL Server 2005 database which does not exist before

I did not ready about this but found out after trying to restore a database from remote server and it wont restore if the database did not exist before. What I would do is create a .bak file ...
5
votes
1answer
676 views

Are foreign keys better than indexes?

I have a replicated copy of an application database. I am exploring how to change the constraints and indexes to better serve our reporting requirements. Because this is a copy of the application ...
2
votes
2answers
1k views

Adding Timestamp to Exported Filename in SQL Server Management Studio

I am exporting to a CSV using a job with xp_cmdshell. I need the filename to include a timestamp. The code I currently have is... EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout ...
2
votes
2answers
1k views

SQL Server Management Studio .bak files

I'm new to SQL SMS and need help. I have been asked to check that .BAK files restored be checked using SELECT queries in SQL SMS. I have tried to create a new database but get the error "CREATE ...
0
votes
3answers
496 views

Is it possible to edit sys.messages texts?

Is it possible to add a new language to SQL Server 2008 and edit SQL error messages with message_id less than 50000?
3
votes
3answers
1k views

Natural (numeric) sort on string

I've got a table of data like so: Process_Id Stage_Id Step_Id P1 P1.5 P1.5.1 P1 P1.5 P1.5.10 P1 P1.5 P1.5.11 P1 P1.5 P1.5.12 P1 ...
2
votes
1answer
4k views

How to drop variable in SQL Server [closed]

I have declare a variable in SQL Server: Declare @variable How can I drop or enable the variable when I don't need it?
2
votes
2answers
187 views

Connecting to SQL Server 2008 on windows server 2008

Hell all I am new to windows server 2008 and we are trying to transfer our website from hosting to virtual server. I installed SQL Server 2008 Express edition and I did the things like opening port ...
13
votes
3answers
3k views

Can you explain this execution plan?

I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects ...
6
votes
3answers
2k views

How to identify columns order in a table

I need to list columns from a table in the table definition order: select * from syscolumns where id = object_id('MyTable') --order by colid By examining syscolumns tables two columns look ...
0
votes
2answers
461 views

Recommended plugin for SQL server 2008 [closed]

Is there any recommended plugin for SQL server 2008 to use when you are working as database administration, Business Intelligence developer or Database developer?
2
votes
1answer
127 views

Lock Pages in Memory for SQL Server

What is a recommendataion regarding turning on Lock Pages in Memory for 64 bit SQL Servers. Is there any implication on turning on lock pages in memory setting?
0
votes
1answer
95 views

Transfer ERD between Databases on SQL Server

I've a development database on SQL Server 2008. I want to copy the production database back to my development server to get the latest data for testing. Unfortunately the production database is ...
2
votes
4answers
4k views

decrypt/get sql user password

I have a sql server 2005 database server. I have administrative access(sa access) however I need the password to a specific account on the server. I can't really reset the password to this account ...
5
votes
2answers
690 views

How to automatically stagger transaction log shipping to minimize bandwidth usage peaks?

The following question pertains to Microsoft SQL Server transactional log shipping (TLS). We're using SQL Server 2008 R2 SP1, although the question is probably relevant for all recent editions. ...
9
votes
1answer
1k views

Interpreting SQL Server's Showplan XML

I just rolled out a feature on my site http://sqlfiddle.com that allows users to view the raw execution plans for their queries. In the case of PostgreSQL, MySQL, and (to some extent) Oracle, looking ...
31
votes
4answers
12k 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 ...
8
votes
3answers
220 views

Nonclustered Index Insert

Say I have a table like this: create table SomeTable ( id int identity(1, 1) not null primary key clustered, SomeString1 varchar(50) not null, SomeString2 varchar(50) not null ) go ...