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
1answer
1k views

Where are queries against a linked server actually processed?

How is a query against a linked server processed? Does the linked server use its optimizer when it receives a query from a remote source? There are a few scenarios about which I'm wondering: Scenario ...
4
votes
3answers
3k views

SQL Server 2005 Tools makes SQL Server 2008 Express install fail

When I tried installing SQL Server 2008 Express, the install failed due to this line. How can I circumvent this, or fix the issue?
4
votes
1answer
117 views

can sql generate a good plan for this procedure?

I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
4
votes
2answers
218 views

Checking a wide table for nulls [duplicate]

Possible Duplicate: Test if any fields are NULL I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column ...
4
votes
2answers
11k views

A Script to insert dummy Data in all tables of Database

I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...
4
votes
3answers
8k views

Why so many logical reads?

I have a very simple query that is showing in Activity monitor and other statistics as having the most logical reads on my entire DB server. SELECT MAX(RESULT_DATE) FROM TABLEX mm WITH (NOLOCK) JOIN ...
4
votes
3answers
1k views

How can I make my SSIS process consume more resources and run faster?

I have a daily ETL process in SSIS that builds my warehouse so we can provide day-over-day reports. I have two servers - one for SSIS and the other for the SQL Server Database. The SSIS server ...
3
votes
1answer
48 views

Query Plan Error

I have an interesting situation. I use SQL2014 Enterprise version with SP1. The following query results an error message : The query processor ran out of internal resources and could not produce ...
3
votes
3answers
146 views

Choosing a PK: VARCHAR(2) or SMALLINT(2)?

Today we were discussing the following: Brazil has 27 states and each one has its own abbreviation (just like USA). So we've got RJ for Rio de Janeiro, SP for São Paulo, MG for Minas Gerais and so ...
3
votes
1answer
126 views

what would be a good choice of clustered index (and other indexes) for this situation involving a indexed view?

To help me improve the performance of some queries I have created the following indexed view: CREATE VIEW DBO.vwOrders WITH SCHEMABINDING AS ...
3
votes
1answer
316 views

SELECT COUNT(*) to html table

This may be a very simple but it's still not clear to me. I'm writing a SQL Agent job to generate output in html format. I need to get results of a query SELECT COUNT(*) FROM TABLE_NAME. I'm able to ...
3
votes
0answers
103 views

How to set ALL the replication to DEADLOCK_PRIORITY LOW?

I have just got this email here: Then looking at the sql server log I find this deadlock was caused by one of our procedures (that was the victim) against the replication. O.K. in this case in ...
3
votes
1answer
341 views

Hiding a SQL Server vs Turning Off SQL Browser

Can anyone explain the difference between turning off the SQL Browser service and Hiding an instance through the SQL Server Configuration Manager? I've always assumed they do the same thing and have ...
3
votes
3answers
2k views

Monitoring free space in SQL Server data files

I have manually resized mdf/ndf files to a big size to avoid autogrow operations on SQL Server databases. Since the files are bigger there is very little free space on disk partitions and the ...
3
votes
1answer
215 views

Grouping() equivalent in PostgreSQL?

I have a SQL query that runs in MS SQL Server and it has grouping(), rank() and rollup() functions / keywords in it. The query is given below. How can this be translated to PostgreSQL? select top ...
3
votes
1answer
495 views

Transactional Replication to DB on Same Server instance

We have a SQL Server 2012 instance that contains a database with data for many product lines. We are looking for a way to take subsets of this data into another database on the same instance. This ...
3
votes
2answers
2k views

How to properly increment an int identity in SQL Server if the Identity Increment isn't set?

When I use SQL Server with an int based identity column, I always use the auto increment. But I'm needing to insert into an existing table that doesn't have the property set, and I'm wondering what ...
3
votes
2answers
3k views

Execute Remote Powershell script from SQL Agent

A test SQL Server job hangs when it executes a Powershell script. It does work using my login and a Powershell window, but not using the Agent account and a job. The SQL Agent user has ...
3
votes
1answer
294 views

Single year column and multiple weeks columns into single year/week column with PIVOT

I have a table that looks roughly like this. (Pk int, Year int, Week1, Week2, .., Week53) The values in each "Week" column are sales data. What I want to do is create a new table from the first ...
3
votes
2answers
244 views

Determining the Actual Server Create Date

I am interested in determining the ages of all 70+ my servers. I would like to create a growth graph showing when servers and databases were added to the environment either/both monthly or annually. I ...
3
votes
1answer
140 views

Replicating a SQL Server database & Optimization

We got a database of approximately 100-120 GB on a server with 128GB ram, while the db is sitting on an 256 GB SSD drive. The bulk of the database space is spent on the tables below: Page_contents ...
3
votes
2answers
382 views

Restricting Database Access/Functions to User Groups?

We've been having issues with Databases being DROPed or RESTOREd WITH REPLACE with the incorrect backup etc... I'm developing a plan right now on how to restrict access and I have some questions. ...
3
votes
3answers
2k views

Track changes in database structure

There has been a change in our DB and one of the SPs has been deleted. We have no idea who did it or when. Is there any way to find it out? Or enabling an option to be able to track this in future?
3
votes
2answers
6k views

How to enable a SQL Server 2012 login to create, alter and drop the created databases?

I'm trying to assign a proper GRANT permission to CI login, that will be able to: Create test DB Alter it Drop this test DB (and only it) But it appears that SQL Server 2012 GRANT CREATE ANY ...
3
votes
1answer
357 views

Are there reasons to not use SCHEMABINDING on database objects?

Not using schema binding seems like turning off compiler warnings - you don't find out you broke something until runtime. The benefit is that schema bound objects protects the database from ...
3
votes
1answer
63 views

Single statement or batched delete?

I need to delete 6.3 million records from 25 tables in production. I have the following two queries, but I don't know which one will be best: delete jc from Jtable jc join J1Table j on j.JobId = ...
3
votes
1answer
554 views

Getting each status change in a table

I have a table that holds status changes per item. The item can go in and out of a status multiple times. ItemTable (ItemId int, Status varchar(50), DateOfChange date) I need to pull the date of ...
3
votes
1answer
713 views

Utilizing a single core to 100% doesn't show in perf mon as single cpu being fully utilized

Ever since Windows 2008 I've noticed the following behavior: I'm using a windows 2008R2 VM with two vCPUs but this also works on a physical server. I'm using SQL server 2008 R2 for this example. ...
3
votes
1answer
1k views

In Powershell Retrieving Printed Message From SSMS

Our DBA team doesn't verify the backups using the below TSQL (which can easily be done post-backup and takes almost no time, so I don't get why not): RESTORE VERIFYONLY FROM DISK = ...
3
votes
2answers
4k views

“Restore With Replace” or drop/restore with Instant Initialization on?

What exactly does the restore argument "With Replace" do? I'm looking at needing to restore a database back to a beginning point on a regular basis, and I've been trying to figure out if there are any ...
3
votes
2answers
3k views

SQL Server 2012 High Availability on VMware virtualization

We're going to host a large SQL Server 2012 under a VMware virtualization and we need it to be highly available. These are the three options I found supported, but couldn't find a lot of info and ...
3
votes
1answer
895 views

Distributed - across multiple databases or servers - Transactions

I have some doubts related to transactions in SQL. I perform below steps. Connection 1 open for DB1 database Begin transaction for Connection 1 Run the commands on Connection 1 Connection 2 open for ...
3
votes
3answers
13k views

overwrite table with data from another table - SQL

Our users want to refresh a QA database from production but want two tables (lets call them T1 and T2) to retain the original QA data. So I copied two tables from QA (DB1) to a temp QA database (DB2). ...
3
votes
1answer
1k views

Page Lookups Percentage - SQL Server performance monitoring

I'm currently doing some work on monitoring SQL Server performance. I've found the following script[1] which calculates 'Page Lookups Percentage' - the suggestion being that a "good" value is ...
3
votes
5answers
680 views

Extract data to another computer

I have SQL Server 2008 R2 Express edition, and I want to export the data in that database to another computer so I can import it again in that computer. How please? What I have tried Right-click ...
3
votes
2answers
712 views

Data sharing between client and server two ways

One of my Silverlight applications is running. This Application is using one central database (SQL Server 2008) and three other client databases (SQL Server 2008) in server other locations. Our ...
3
votes
2answers
2k views

Can I trust the Database Engine Tuning Advisor recommendations?

Our database has become large, and we are trying to optimize performance. Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
3
votes
4answers
9k views

How to find all positions of a string within another string

How can I find all the positions with patindex in a table or variable? declare @name nvarchar(max) set @name ='ali reza dar yek shabe barani ba yek ' + 'dokhtare khoshkel be disco raft va ali ...
3
votes
1answer
2k views

Database locked

I canceled a query which took too long time. After that, the SQL-Server database was locked. After I restarted SQL Server, the mode of my database was "in recovery". Now I can't access my database ...
3
votes
0answers
830 views

SSIS Recordset Destination error

I have an SSIS package targeted at SQL Server 2012. I have it deployed into the Stored Packages in Integration Services and then have a SQL Server Agent job which executes it. The first part of the ...
3
votes
1answer
291 views

Creative workaround for granting edit rights to individual jobs for a group?

We have two developers that say they can not function without full rights to a few jobs on our database server. They both want rights to the same jobs. Given that it seems that MS doesn't seem to be ...
3
votes
2answers
11k views

ORDER BY clause is allowed over column that is not in SELECT list?

In theory, every statement in SQL Server create virtual table passed to next statement. In other words, when FROM and Where clause are finished, next step is SELECt columns from virtual group created ...
3
votes
1answer
31k 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 ...
3
votes
4answers
3k views

limiting sql server backups inside .bak file

I have a job scheduled to run a few times everyday. The job backups my databases, like: BACKUP DATABASE [databasename] TO DISK = N'd:\sqlserver\backup\databasename.bak' I can track the backups in ...
3
votes
2answers
2k views

Does adding page compression to a table also compress existing indexes?

I am creating a series of new tables that will have page level compression enabled. Will this affect the indexes as well, or will I have to also specify compression when creating the indexes? create ...
3
votes
1answer
5k views

Unknown database corruption: Data error in cyclic redundancy check

I have a server that is throwing an error message when I try to backup a database. The error is: 10 percent processed. 20 percent processed. Msg 3271, Level 16, State 1, Line 1 A nonrecoverable I/O ...
3
votes
1answer
264 views

In-memory SQL Server log file

I have a SQL Server instance that is used solely for the purpose of reporting and also for preparing data to be loaded into Analysis Services. Its data comes from another SQL Server instance which is ...
3
votes
1answer
968 views

Why do we have to use SWITCH in table partitioning?

I'm a beginner in SQL. I'd like to know what is the importance of SWITCH in table partitioning? I've created one table partitioned month-wise, with a total of 12 partitions. The ranges are: ...
3
votes
2answers
10k views

Why database diagrams are not working when domain account is attached to database owner?

I was trying to use Database diagrams is SQL Server and got following error: Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first ...
3
votes
2answers
878 views

SQL Server 2005: Full-text search space requirements

Is there a way to calculate the physical drive space required for a full-text search catalog? Space is cheap, but I'd like to know what I'm getting into. I'm looking at a table of about 200 articles ...