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
547 views

How to prevent big performance impact during index creation

I have an existing database that is missing some indexes. When I tried creating some the database server really suffered in CPU usage during index creation interfering with the regular workload. Is ...
5
votes
2answers
425 views

Get NULL statistics for a column in MS SQL Server

In MS SQL Server 2005, how can I extract statistical information about NULL fraction (or NULL count) for a given column? Same information is available in Oracle and PostgreSQL, and I heard that MS ...
5
votes
1answer
460 views

SQL 2008 Merge Replication and table-level data compression

What would happen if you enable data compression (either row or page-level) on a replicated table (merge replication) where the publisher is SQL 2008 Enterprise (Supports data compression) and the ...
3
votes
1answer
212 views

Connect tables in a cube based on historical data

So this may be the wrong place, and may seem pretty basic, but I'm new to building up cubes, so please forgive me. Imagine a setup described by the following: CREATE TABLE [dbo].[Orders] ( ...
4
votes
1answer
5k views

How can I monitor the call count per stored procedure per timespan?

In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get ...
3
votes
1answer
1k views

sp_whoisactive keeps returning create procedure statements

When I run sp_whoisactive on my server it often shows create statements being run for stored procedures but this doesn't make any sense since the procedure already exists. See the following example: ...
11
votes
2answers
413 views

To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS

I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run ...
4
votes
3answers
553 views

What are the pros and cons of using my customer codes as a primary key?

I am designing a database for one of my sales teams. Right now, the goal is to get a master list of customers and their mailing addresses, but the DB will eventually expand to track some other ...
3
votes
3answers
949 views

Restore script in SQL server doesn't work in W7

I need a little help with a restore process, I can't find specific help in other similar questions, sorry if my question does not correspond. I have this script in my system: RESTORE DATABASE ...
10
votes
2answers
913 views

Optimization: Moving variable declarations to the top of your procedure

While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity. One thing the DBA mentioned was I should ...
0
votes
1answer
232 views

How to find equivalent of Oracle's privileges in SQL Server?

I have a user in Oracle for which the permissions given are CONNECT and SELECT_CATALOG_ROLE. How do you find the equivalents when setting up a similar user in SQL Server?
1
vote
2answers
856 views

Restore System Databases from MDF/LOG files

We recently experienced a hardware failure that caused our SQL 2005 server to take it in the dump. The good news is that we have the MDF and LOG files for all the databases, the bad news is that we ...
4
votes
1answer
781 views

Property Owner is not available for Database 'DBNAME'

Property Owner is not available for Database 'DBNAME'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo) I am ...
5
votes
2answers
155 views

Is is possible to manage SQL Server 2008 databases via R2 Management Studio?

Is it possible to manage SQL Server 2008 databases using SQL Server 2008 R2 Management Studio?
4
votes
4answers
687 views

Can two servers access the same database files?

I'm designing an application and considering some options regarding the database system since I'm not familiar with Microsoft SQL Server. I would like to know if it's possible to have 2 servers ...
3
votes
3answers
2k views

Are there ways to only replace SQL Server stored procedures when the definition has changed?

I know the following two patterns to write scripts for creating stored procedures, which can be executed repeatedly without throwing errors. if object_id('my_proc') > 0 drop procedure my_proc go ...
2
votes
2answers
287 views

SQL Server recovery with LVM snapshot as base

Is it possible to fully recover a database from a snapshot plus transaction log backups? By snapshot I mean a crash-consistent clone of the Server - in this case an LVM snapshot that is shipped ...
4
votes
1answer
2k views

How to deploy SSRS reports build through Report Builder?

I would be building or correcting the already build SSRS reports through report builder. Once the change is done in Development side, then how to publish the reports on test server/UAT or on ...
1
vote
2answers
691 views

Are there any reasons not enable SQLCMD-mode as default in SSMS?

I don't consider "just to ensure that sqlcmd-mode is not required by a script" a valid reason. Any things to think of before giving the order to enable this setting to my coworkers?
2
votes
1answer
384 views

Error When Restoring Log

Here is the situation. I have restored a database to replace an existing version (need to get it back to yesterday at a certain time). So I do a RESTORE DATABASE ... WITH NORECOVERY, REPLACE, etc... ...
0
votes
2answers
1k views

Create an Audit trail on SQL Server 2008 to trace all privilege users

I need to create a trace of all the commands issued by members of SysAdmin or SecurityAdmin server Roles. I figured, I could use Profiler to do that but it will not comprise all the added members. I ...
2
votes
2answers
163 views

SQL Server Timeout: Gathering server side information

We use SQL Server 2008 for our database. We use a combination of Classic ASP and .NET to connect to said database. We get random timeouts on queries that are extremely simple and when we run said ...
16
votes
3answers
14k views

Efficient INSERT INTO a Table With Clustered Index

I have a SQL statement that inserts rows into a table with a clustered index on the column TRACKING_NUMBER. E.G.: INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC) SELECT TRACKING_NUMBER, COL_B, ...
5
votes
1answer
2k views

Remote queries at SNAPSHOT isolation level

I'm experimenting with snapshot isolation level. Whenever I try to access a linked server, I get an error: Remote access is not supported for transaction isolation level "SNAPSHOT" I wasn't able ...
3
votes
2answers
323 views

switch TOP clause between percentage and rows by parameter

For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows. I have two ideas Calling two different sub sproc based on passed param. if ...
1
vote
4answers
6k views

How do I archive and restore selected tables using SQL Server?

How do I archive and restore selected tables using SQL Server?
1
vote
3answers
2k views

Can I Simply Change A Transaction Log From Unrestricted to Restricted?

I've got a database with a data file size of 674 MB and a transaction log of 6.1 GB. The transaction log is about 10 times bigger than the data file.The transaction log settings can be seen in the ...
1
vote
1answer
72 views

Any general reasonable performance tests for DBs on application server and DBs on separate server?

My company are planning to move the DBs (SQL Server 2008 R2) from the application server to another server (random distance). All servers are Windows 2003 x86. Are there any general performance tests ...
4
votes
3answers
5k views

How to make conditional ordering for two or more columns

IN MSSQL 2005 I am writing one query with conditional sort and my problem is I do not know how I can sort condidional using two columns. if i wrte code like this it is working normaly select * from ...
1
vote
1answer
3k views

How can I query the SQL Server Compatibility Level into a variable?

I know to query the current Compatibility Level of a database with declare @dbname sysname select @dbname = db_name(0) exec sp_dbcmptlevel @dbname But this seems to return its result via print and ...
5
votes
3answers
2k views

Run several commands against data in text file (SQL Server 2008 R2)

I'm new to this website so forgive me if I'm asking a rudimentary question and/or one that's been asked 100 times before. I wasn't able to find an answer during my searches here. I'd like to have my ...
1
vote
1answer
921 views

Set statistics time showing different elapsed_time than dm_exec_query_stats view

When I exec a SQL statement after setting set statistics time on and running dbcc freeproccache and dbcc dropcleanbuffers I get the following output: DBCC execution completed. If DBCC printed error ...
1
vote
7answers
1k views

Implement Database Security?

I want to implement a database security on the database i created so no one can access it from SQL Server Management Studio, and that it will be only accessible through code. Is that possible? and if ...
2
votes
1answer
1k views

Maintenance Plan fails but the query it generates Runs

I have an SQL Server 2005 Enterprise Edition whose Maintenance plan fails constantly with the error: backup MYSERVER (MYSERVER) Backup Database on MYSERVER Databases that have a compatibility level ...
6
votes
1answer
415 views

Does sql server calculate views every time when someone query the views?

I wonder whether sql server calculate views every time when someone query the views. It is because everytime I query the views, it take such a long time. And when I tried to shorten the time by ...
1
vote
1answer
335 views

Fail to connect to server

I am getting this error when attempting to connect to a SQL Server 2008 R2 machine: Cannot connect to JUPITER. A network-related or instance-specific error occurred while establishing a ...
11
votes
4answers
740 views

Inaccurate 'Actual' row counts in parallel plan

This is a purely academic question, in so much that it isn't causing a problem and I'm just interested to hear any explanations for the behaviour. Take a standard issue Itzik Ben-Gan cross-join CTE ...
2
votes
2answers
4k views

Access Linked Server error “The provider did not give any information about the error.”

A little information. I'm running SQL SERVER 2008 R2 64bit. I have Microsoft.ACE.OLEDB.12.0 provider installed. On my production server I've setup a linked server to the access db using the provider ...
5
votes
1answer
264 views

Where is the information about what files each database stored for SQL Server 2008 R2

I need to find where SQL Server 2008 R2 stores what files back each database. The kicker is that the server has completely crashed (hardware failure). I've managed to recover all the .mdf/.ldf files. ...
3
votes
2answers
4k views

What is SQL Server Parallel Data Warehouse?

What is SQL Server Parallel Data Warehouse solution? What is the benefit of PDW compared to normal DW architecture based on SQL Server and SSIS, SSAS and SSRS?
1
vote
1answer
775 views

Exporting from SQL Server 2005 for import into ER Studio 9.0

I am new to ER Studio and need to create an ERD for a remote system. That system can only be accessed by LogMeIn remote control and by FTP. The remote system is running SQL Server 2005. The local ...
7
votes
1answer
10k views

Receiving “The SELECT permission was denied on the object” even though it's been granted

I'm a programmer, not a dba... I know just enough to be dangerous. I've inherited a database with a legacy user that is a db_owner for the database. We can't adjust this user's permission for ...
3
votes
3answers
7k views

How to avoid index scans in SQL Server 2005?

I have a table named Cell which has columns like Cell_ID, CellValue and CellRow. Cellrow can have values maximum 1 to 5. There is a clustered index on Cell_ID field. This table contains 100000 rows. ...
14
votes
5answers
8k views

Does restarting SQL Server speed it up?

I have noticed that some DBAs restart SQL Server very frequently, sometimes even nightly. I believe they do it to free up some memory, or perhaps to speed up queries too. I know that after a restart ...
2
votes
1answer
6k views

Comparing DISTINCT, GROUP BY and ROW_NUMBER() in SQL Server 2008 with data warehouse workloads

I recently heard the advice: "If you're using DISTINCT, I challenge you to fix your code." However, I'm wondering what considerations are behind this advice, and whether it means that I should ...
0
votes
2answers
3k views

The OLE DB provider “SQLNCLI” for linked server X reported a change in schema version between compile time X and run time X for table [tablename]

The below error starts happening once a week at the same time every week. The OLE DB provider "SQLNCLI" for linked server X reported a change in schema version between compile time X and run time X ...
4
votes
1answer
10k views

Local login impersonation not working with a linked server

I have created a linked server on my server using the following: exec sp_addlinkedserver @server=N'MyOldDB', @srvproduct=N'', @provider = N'SQLNCLI', @datasrc=N'MYSERVER', ...
4
votes
3answers
1k views

Which options to enable to guarantee full recovery in SQL Server Express 2005?

I am using SQL Server 2005 Express Edition. I noticed that Recovery Model is set to Simple, by default. I changed this option to Full but want to know which other options to enable to guarantee ...
7
votes
5answers
7k views

Auto-Commit in SQL Server and Oracle

When I worked on Oracle 8 many years before, I used to execute COMMIT command manually after each bulk INSERT. In SQL Server, Auto-Commit is ON by default which has advantages as well as hazards. I ...
6
votes
2answers
4k views

Recover accidentally deleted rows from a table

My client has accidentally deleted all the records of a table from a test database. This test database is in SQL Server 2005 Express and we don't have a backup. Is it possible to restore the deleted ...