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)

2
votes
1answer
48 views

Rowversion database lock performance

As the documentation says, rowversion data type is auto-updated every time a row is updated using a global auto-incremented number within the entire database scope. My question is the following: Say ...
1
vote
1answer
47 views

Does schema binding views affect performance?

Does schemabinding views affect performance in SQL Server or is just to prevent accidental changes?
1
vote
1answer
44 views

Disk usage not decreased after the full and log backup in SQL Server

I am using SQL Server 2012. I created the backup (full and transaction log backup) using maintenance plan and scheduled the job using SQL Server agent. After the success of the backup, I cannot see ...
0
votes
0answers
73 views

SQL Server not using all the memory

We have a server with 32 GB of RAM, Windows 2012, 64 bit and SQL Server 2012. SQL Server is not consuming all the memory available on the server. Max and min settings are in place. No resource ...
1
vote
1answer
71 views

Does Clustered Index on GUID create more fragmentation than Non Clustered Index?

I have a simple table: CREATE TABLE [dbo].[UserTestGroups]( [UserTestGroupId] [int] IDENTITY(1,1) NOT NULL, [Token] [nvarchar](100) NOT NULL, [TestId] [bigint] NOT NULL, [Group] ...
2
votes
1answer
37 views

Stuck task in management studio, how do you handle them?

After clicking on "Take database offline" in management studio this message stays hang and won't close if you click on close. What's a good way to deal with stuck jobs like these in management ...
4
votes
1answer
87 views

Subtracting sum of previous rows' data from current row's data in SQL

I want to create a table or view in SSMS which should has a column called qoh. In that column should be calculate sum values of the column rel_qty. i.e. The first data of qoh is the same of the ...
5
votes
4answers
220 views

Total duration “server” was in “In” state per day

I have data of a server start & stop time on a daily basis for one month. I need the result containing the first start time of the day, last stop time of the day, total time the server was started ...
0
votes
1answer
40 views

SQL Server stored procedure running in infinite loop

I am running this stored procedure that updates a column. It runs in an infinite loop. When I run the internal query separately, it completes just fine. When I cancel the execution of the stored ...
1
vote
0answers
33 views

SQL Server Management Studio creates 18456 login error using registered server group

I have been using "Local Server Groups" registration for quite a while now with SQL Server 2008 Management Studio. I have created a group with 5 registered servers. They are registered using SQL ...
2
votes
1answer
51 views

SQL Server MDW - Query for data collector retention period

I have data collectors active on many remote servers and I am trying to find a query that will capture the retention periods for all data collectors active on an instance. These are system data ...
2
votes
1answer
57 views

Troubleshooting memory pressure - how to avoid paging out SQL Server?

I have received the email below that states that an alert has been fired and a significant part of SQL Server process memory has been paged out should I raise the Minimum server memory to 1740 ...
0
votes
1answer
35 views

Capture the Query Text of a DML Change

It may not be possible, but without doing a JOIN from the inserted/updated from the records coming in and adding them to an audit table column-by-column, is it possible to just capture the full DML ...
7
votes
1answer
391 views

Why is a .bacpac file so small compared to a .bak file of the same database?

I've been doing backups of my SQL Server 2014 Express databases for import to other servers and noticed a difference in file size between .bacpac and .bak. Why is a .bacpac file so small compared to ...
4
votes
1answer
52 views

Verify the importance of statistics? Database with auto-create stats works?

Working on SQL Server 2005, I want to use the database engine tuning advisor to help me tune my webserver. First thing I do is to create a server side profile trace and run it for about an hour. ...
3
votes
1answer
32 views

SQL Server 2016 Audit Options

Often law or organization policies require an application to track changes to certain table rows in a database. The changed data is often also viewable through some application page. Some common ...
0
votes
1answer
14 views

will subscriber be available, if publisher or distributor is down?

We have to set up replication between two SQL Server databases residing on two different servers connected to the same domain. Our concern is that if the publisher is down, or distribution database is ...
0
votes
0answers
22 views

SQL Server - “…failed to retrieve text for this error. Reason: 317” error on FILEGROUP

I log in on my local SQL Server 2012 via Windows Authentication. If I try to execute: CREATE DATABASE MediaDB ON PRIMARY ( NAME = FileStreamDB, FILENAME = 'C:\temp\FS\MediaDB.mdf'), FILEGROUP ...
0
votes
0answers
19 views

Issue in pivot operator

Find the employee is in which shift and shifttype If he is shift 1 shifttype 1then the record comes from employee table if he is shift 2 shifttype 4 then the record comes from shiftscheduler table ...
1
vote
2answers
71 views

Reclaim Transaction Log space of SQL Server

I have a SQL Server databases for which log files have grown huge. I have log backup scheduled to run only once a day. But now all the disk space is used by these log files. Can anyone help me ...
2
votes
3answers
60 views

Vendors with Most Invoices in State

I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work: SELECT ...
2
votes
2answers
19 views

SQL Server 2014 - Add Node inherits wrong configuration

I'm setting up a simple 2-node failover cluster with SQL 2014 on Server 2012 R2 in Core mode (no GUI). I have the cluster configured with identical machines, and I have the first node configured and ...
1
vote
0answers
21 views

How can I create a Web-service to share data from SQL Server?

Our organization has an externally hosted website that displays data from a monitoring system. Data is currently provided by FTPing csv files. The data is stored in a SQL Server Database. The ...
2
votes
2answers
121 views

Blocking continues after enabling snapshot isolation

On database hosted on a SQL Server 2012 instance, I have enabled ALLOW_SNAPSHOT_ISOLATION and verified the state as ON using SELECT snapshot_isolation_state_desc,name from sys.databases However, in ...
3
votes
1answer
39 views

Handling sql column with mixed varchar and float data types

We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to: [Parcel # 1] [Building Address] 038-60-171-16-000 1750 W 4th St ...
22
votes
1answer
387 views

Logical reads different when accessing the same LOB data

Here are three simple tests that read the same data, yet report very different logical reads: Setup The following script creates a test table with 100 identical rows, each containing an xml column ...
2
votes
1answer
143 views

App Domain unloaded when using GDAL/OGR ogr2ogr to insert many Geometry records

SQL Server 2012 x64, Total memory allocated 2GB, set to low value to enable us to reproduce "AppDomain unloaded" issues much quicker Windows Server 2012, Total Memory on Server 16GB (in ...
0
votes
2answers
46 views

Create a copy of a SQL Server Job

Using T-SQL or Powershell, how can I create a stored procedure that performs the following: Copy a SQL Server Agent Job to another backup Job on the same server (with a new name) Disable the backup ...
1
vote
0answers
25 views

Best practices for deploying and updating SQL Server as part of code deployment

I am a (windows) developer trying to determine the best approach for publishing DB schema changes requiring data transformations as part of my code releases. I have tried googling but did not find ...
0
votes
3answers
73 views

Syntax on Changing return String by Concatenation?

I successfully created the scalar function FN_TotalBilled in SQL Server, which gives me the total amount invoiced to a company using tables Invoices(InvoiceID, InvoiceTotal,..) and Vendors(VendorName, ...
4
votes
2answers
49 views

Statistics for AlwaysOn Availability DBs

Can you please help me understand why there is a need for SQL Server to maintain statistics in tempdb for DBs that are part of an Always On Availability Group? Isn't "everything" that happens in the ...
0
votes
2answers
49 views

Check if the trigger was fired by INSERT or DELETE

I know this is the easiest question you can find on this website but I wasn't able to find its answer on the internet. How to check if the trigger was fired by an INSERT or DELETE? Example CREATE ...
0
votes
0answers
22 views

Bypass or Override SQL Server DB related error during WSUS Setup Installation?

When trying to install the latest WSUS release from here: Windows Server Update Services 3.0 SP2 We tried it once with the DB and then someone suggested detaching the DB and moving to another drive ...
0
votes
1answer
20 views

SQL Server FTS get tokens from string

What is the query to get the tokens in a string from SQL Server 2008 R2? I need it to highlight results of FREETEXT
1
vote
0answers
13 views

Synonims in SQL Server 2008 R2 FTS

The Search feature on my website is based on SQL Server's Full Text Search (I insert everything relevant to a single table and this table is in the FTS Catalog). We don't have money to buy a ...
7
votes
1answer
144 views

Two columns with and without index in where clause - How index works

I am executing a query on SQL Server 2008: SELECT col1 FROM table1 WHERE col2=val2 AND col3=val3 Here col2 has a non-clustered index, col1 is the PRIMARY KEY, and col3 does not have any index. ...
3
votes
1answer
66 views

Correlated subquery to find total larger than state average

I have tables Vendors (VendorName, VendorState,....) and Invoices (InvoiceID, InvoiceTotal,...). I want to get the Invoices (as InvoiceId) that are larger than the Avg state InvoiceTotal. I know I ...
3
votes
3answers
116 views

Why SQL Server uses Non-Clustered but not Clustered Index?

I have a table with 145 million rows CREATE TABLE [dbo].[RFTest]( [SnapshotKey] [int] NOT NULL, [SnapshotDt] [datetime] NOT NULL, [LoanNum] [int] NOT NULL, [GLSourceSystem] ...
1
vote
3answers
45 views

AlwaysON - Force users connection to use Readable Secondary Nodes

I'm implementing SQL Server 2015 Enterprise on my actual client, using Always On Availability Groups. Processes like Reporting, ETL (extraction of data), and read workload are being redirected to the ...
3
votes
2answers
76 views

Max date record for items for given set of dates

I am looking for some help to create an efficient SQL query (SQL Server 2012) that will identify the latest records (date) for all the items in the table (table 2 below) on each of a list of given ...
0
votes
1answer
19 views

What are the minimum requirements for running a dtsx package on a PC?

One of my clients has some very specific needs to get a spreadsheet run through some ETL processes I have created. I have developed an SSIS .dtsx package which imports an Excel spreadsheet into SQL ...
-1
votes
1answer
40 views

How to read the deleted value using a variable

I'm trying to get @OldValue = (select @fieldname from #del) to give me the value. Instead I get the column name for the result. Is there a way to write this so it can output the value. If I write ...
1
vote
1answer
36 views

Multitenant application: several tables or several IDs?

We are designing a common product to serve several companies. The most convenient way to do this, technically speaking, is to deliver a database to each customer. But we predict that, depending on ...
0
votes
1answer
25 views

Append Records to a fixed number of columns using dynamic data [duplicate]

I have a table with 22,000 records formatted like so: Input [ID],[Cost Center],[Function] 1,01A1001710,GF000001 2,01A1001710,GF000802 3,02R1012000,GF000001 4,02R1012000,GF000059 ...
-1
votes
1answer
29 views

Update log table with all table updates [closed]

I need to update a log table with old and new updated values. Any ideas how can we do this in a trigger? I need to log all inserts and updates and write a custom message in the column like.. "User ...
1
vote
1answer
29 views

Offsetting reporting Load from one server to another

I have a SQL Server 2008r2 database ( Let's call it "BIG_DB") at 200GB. This server gets hit hard. It is the back end for an application, BIG_DB is a source of a lot of reporting. There are about ...
15
votes
1answer
197 views

Multi-statement TVF vs Inline TVF Performance

Comparing some of the answers on the Palindrome question(10k+ users only, since I've deleted the answer), I'm getting confusing results. I proposed a multi-statement, schema-bound TVF which I thought ...
1
vote
1answer
43 views

AlwaysOn Synchronous Mode Possibilty

We have 2 SQL Servers at Production site and 1 SQL Server at DR site. Please correct below things as I am not getting clear idea about these sync mode in AlwaysOn: If I install SQL Server FCIs in ...
8
votes
4answers
217 views

Separate letters from digits in alphanumeric string

I have an alphanumeric string as input and I want to get two results out of it: a string where all the numerals are removed and an integer that is the sum of all the digits in the input string. ...
1
vote
2answers
53 views

Similar Jobs in two different Servers

Can anyone just help me with a script to find similar SQL jobs in two different instances/servers? By similar I mean jobs with the same name, which do the same task, with the same frequency and ...