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

What can become a reason of database failure? [closed]

I'm the new one with SQL Server, so, please don't beat me. It's known that one of the best ways to protect your database from the failure is to make regularly scheduled backups. But my question is: ...
1
vote
1answer
28 views

Difference between database backup and data-tier application export

I don't know if this is a bad question, but I don't understand what the difference is between normal DB backups and data-tier applications in SQL Server. I know backups can't be imported from SQL ...
1
vote
0answers
40 views

SQL Server Repeatable read locking [closed]

I'm trying to understand the way the repeatable read isolation level works. I've been reading many articles and posts and still can't find my answer. Everywhere that repeatable read is mentioned, ...
0
votes
1answer
18 views

Howto remind and restore recovery mode during SSDT deployment?

Due to deployment of a SSDT database project I would like to be able to switch the target database's recovery mode from FULL to BULK LOGGED and back after the deployment finished in order to do any ...
6
votes
2answers
122 views

Selecting Distinct Rows but Counting All Rows

I have the following table: ---------------------------------------------- | ID | interestingData | timestamp | ---------------------------------------------- | 1 | 400 | ...
2
votes
1answer
76 views

Grant access only to System databases [closed]

Is it possible give an SA like permission minus any access to user databases? I want people to be able to collect information about Sql Server from the system databases but I want to make sure the ...
-1
votes
2answers
39 views

Stored procedure execute another sp with random value of parameters [closed]

For example: exec SpFirst @param1 = 1, @param2 = 'sdad', @randomSP = 'nameOfRandomSP @n1 = 1 @n2 = "2"' In SpFirst i use "exec @randomSP" but don't know how send to SpFirst send ...
8
votes
2answers
84 views

SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index

I'm running a 350GB database on my PC with ~40 million rows. SQL Server 2014, Win7, AMD 8350 @ 4.8GHZ, 16 GB of RAM, and a 500 GB SSD (database is hosted on it's own 500 GB SSD, with a ...
1
vote
1answer
34 views

Normalizing a folder schema

I've created a schema to allow creation of folders for customers. Customers Id Firstname Lastname StreetLine1 StreetLine2 City CustomerFolders Id CustomerId ParentId (NULL) Name Now as you can ...
3
votes
1answer
44 views

What process would you use to determine references on a database that has no integrity?

Imagine you inherit a database which has a bunch of tables, no documentation and zero referential integrity. Now you're trying to find out what are the various relationships between different tables. ...
4
votes
1answer
257 views

'CONCAT' is not a recognized built-in function name

A client reported that they were running on SQL Server 2012, and we delivered some test queries for testing prior to a final delivery, however: 'CONCAT' is not a recognized built-in function name. ...
3
votes
1answer
62 views

Select data divided in groups evenly distributed by value

I would like to select into 4 groups the data from a table having the sum of a values in the groups as evenly distributed possible. I am sure that I am not explaining it clear enough so I will try to ...
1
vote
1answer
218 views

What does N'[Uncategorized (Local)] mean?

The following code is seen in several Q&A code snippets here (and elsewhere), I am trying to translate the section N'[Uncategorized (Local)] EXEC @ReturnCode = msdb.dbo.sp_add_category ...
3
votes
1answer
68 views

Where clause from OUTER APPLY using LEFT function

What would be a good alternative to improve my where clause that currently uses the LEFT function? Should I create a persisted computed column and an index to it? SELECT --columns-- FROM ...
5
votes
0answers
70 views

How to solve blocking on tempdb

I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2). Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all ...
2
votes
0answers
34 views

Cross database - Table valued Stored Procedure

I have the same table type in two databases (DB1 and DB2), DB1 has an stored proc (sp1) which has the above type as an input parameter. Now, I want invoke this SP from DB2. But, I am getting this ...
0
votes
2answers
43 views

Find database user name from sysprocesses

I know that this is a strange requirement but I need to find out database user name(not the login name) from sys.sysprocesses for all the currently running processes. For e.g. select spid,dbid, uid ...
-2
votes
0answers
11 views

What is the difference between Failover Clustering and AlwaysOn Availability Groups in SQL Server 2012 [duplicate]

I would like to know the differences between Failover Clustering and AlwaysOn Availability Groups in SQL Server 2012
6
votes
3answers
86 views

SQL Server Agent won't start

When I'm trying to start SQL Server Agent, I got this error: TITLE: Microsoft SQL Server Management Studio Unable to start service SQLAGENT$SQL_NAGA on server NAGA. (mscorlib) ADDITIONAL ...
-1
votes
2answers
60 views

Browse for Servers does not display local instance

I just installed SQL Server and SQL Server Management Studio but it doesn't show anything in servers list and I don't know how to connect to it. This shows that I have installed SQL Server: ...
5
votes
1answer
98 views

Datawarehouse indexing

I have a table with nearly 6m records. The business key that identifies a unique row is quite large. Our update processing is now taking a lot longer since I added this new table for a new cube. I ...
3
votes
1answer
55 views

SQL Server 2014 Batch Transaction Log Management [closed]

I have recently taken over a system which is going live in March on SQL Server 2014 after running on Sybase for years. The system is 24 x 7 OLTP and heavy batch at night. Prior years during batch in ...
2
votes
1answer
61 views

SQL Server 2014 Minimum Memory configuration - VMware

I am taking over a SQL Server 2014 environment which has been built and configured by a consultant. They have set a minimum value for memory which I usually set to 0. According to the consultant ...
9
votes
1answer
125 views

Why can my index seek estimate the right number of rows and the sort operator can't?

I've a query that uses a function on the predicate, something like this: commentType = 'EL' AND commentDateTime >= DATEADD(month,datediff(month,0,getdate()) - 13,0) I have a filtered index on ...
3
votes
0answers
29 views

How to PUSH data between a Local DB Server to Cloud DB Server?

I have an implementation doubt regarding replicating data between SQL Server 2014 database servers (Replicating data from Local Premises SQL Server to Cloud DB Server, which are not in same domain or ...
2
votes
1answer
32 views

String parsing: Extracting the filename from a URL

This is a link I have in a column of a table: http://earthexplorer.usgs.gov/browse/etm/1/11/2000/LE70010112000130KIS00.jpg There are like 1000's of rows like this. Is it possible to extract the ...
-1
votes
0answers
28 views

SQL Server 2005 patching issue

We are trying to patch SQL Server 2005 server with SP 4 cumulative hot fix 5324 for the first time in the server. Our patching wizard got hung for about 2 hours at this stage: Installing ...
3
votes
0answers
50 views

SQL Server Incremental Backups Split

I am trying to recover a database where the incremental (log) backups are split between two methods (Carbonite/Zmanda and SQL .bak files). When I try to use the restore wizard I get the following ...
0
votes
1answer
40 views

Copy A Database

Is it possible to copy a SQL Server 2008 Express without launching SSMS? Little more back-story: my internal drive was removed and is now used as a USB drive (upgraded to a larger hard drive) and ...
9
votes
5answers
268 views

Getting a scan though I expect a seek

I need to optimize a SELECT statement but SQL Server always does an index scan instead of a seek. This is the query which, of course, is in a stored procedure: CREATE PROCEDURE dbo.something ...
0
votes
1answer
30 views

Generic stored procedure for results sets comparison

Some of the tasks in my team are related to stored procedure optimization. I am mainly a .NET developer, but I thought of writing a generic procedure for data comparison while procedures are being ...
7
votes
3answers
368 views

Risks involved in upgrading to SQL Server 2008 R2

We have quite a number of SQL Servers that need to be upgraded from version 2005 to 2008 R2. Work is planned before the middle of the year as Microsoft is ending its support for the same. The 2005 ...
-4
votes
1answer
53 views

Unable to create a table type on SQL Server 2005

I have created a type defined as following: CREATE TYPE [dbo].[TypeListingDatePrice] AS TABLE( [RowID] [int] NULL, [ListPrice] [int] NULL, [SoldPrice] [int] NULL, [PendStatusDOM] ...
8
votes
1answer
79 views

SQL Server - Export large table without primary key

I need to sync a large table ~500 millions rows without a primary key between SQL Server and MySQL. The table has only a clustered composite non-unique index. I do have a ODBC connection between the ...
5
votes
1answer
88 views

First-In and Last-Out times for Night Shift Employees

I can get FirstIn - LastOut out of Day shift employee using min and max: select UserId, [date], min(convert(char(5), [Login], 108)) as FirstIN, max(convert(char(5), LogOut,108)) as ...
1
vote
0answers
52 views

Query with join to a table on a different database need an index

I'm creating a query that one of its joins is made to a table from a different database. The query analyzer is suggesting the creation of an index, however I don't have permission to add objects to ...
6
votes
3answers
210 views

Updating a view on multiple joined tables

Since MSDN does not say much, what happens exactly if I execute the following query? update claims set status='Awaiting Auth.' where status = 'Approved' Can I use the ClaimStatusName column of the ...
0
votes
2answers
62 views

Get no. of trips in SQL Server [closed]

I have a table structure like this. id zoneid status datetime 1 35 IN 2016-02-02 11:36:39 2 35 OUT 2016-02-02 11:36:59 3 36 IN 2016-02-02 11:37:35 4 36 IN ...
0
votes
2answers
18 views

Create new instance MS SQL Server 2014

I have installed MS SQL Server 2014 , but after installation there is no instance created. There is only the default instance. How make a new instance in existing MS Sql Server ? I found a lot of ...
1
vote
2answers
167 views

How to use SET operation in CASE clause in SQL?

I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error: Incorrect syntax near SET Query: DECLARE @tbl_Result TABLE ( RowID INT ...
-2
votes
1answer
39 views

I have created a zip file using 7z for backup but its taking time to open [closed]

I have created a zip file using 7z for backup but its taking time to open. Any possibility of making fast. Please suggest me.
0
votes
0answers
37 views

AG Automatic Failover when SQL Server service shut down

I set up an availability group listener, and am able to failover manually without data loss. If I reboot the host OS, it fails over automatically. However, when I stop the SQL Service on the node ...
33
votes
5answers
4k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
3
votes
1answer
43 views

How to enable query store on the SSMS object browser with Sql Azure?

I am following this guide: Monitoring Performance By Using the Query Store I am using SSMS 2016 CTP3.2 and Sql Azure V12 I have enabled the query store on the database properties. I can use the ...
2
votes
0answers
32 views

Create File,secondary FileGroup then move a table and restore database with 'Primary'

i created secondary FileGroup , linked a File then moved a table there and restore database with 'Primary' however the table name still there and when I use the select statement I got an error that it ...
3
votes
2answers
68 views

How can I extract the perfmon counter “Memory: Pages/Sec” using t-sql?

I've googled this and so far, no luck so turning to all you awesome gurus for help. I'm trying to figure out if there's a way to get the perfmon counter "Memory: Pages/Sec" using t-sql as I'm trying ...
2
votes
2answers
186 views

Use Distinct With Sum?

I want to sum a few values using case statements. My problem is compiler complains if I do not add a group by to the end of the query, which then that negates my Distinct, hah. For example, in my ...
4
votes
2answers
159 views

How can I tell what database/procedure is using a linked server?

I have 3 servers, one of which has a linked server configured that points to the other - I'll call this Server A. Server A has over 100 user databases for various purposes. Server B is running SQL ...
2
votes
0answers
47 views

SQL Server Check Constraint Lock Order

I'm having problems with SQL Server 2012 filetable. Filetable defines an check constraint on a path_locator field with definition as: /*ParentsAreDirectories*/ ...
4
votes
1answer
49 views

Why are transactions running as distributed (DTCXact)?

I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask. I repetitively run the following query ...