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)

1
vote
0answers
35 views

what if all the non clustered indexes on my table were filtered indexes?

I have a table that is highly hit on my database. this table has 370,370 records. when I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [...
2
votes
1answer
51 views

Recompile not working for DELETE statement

I want to optimize a deletion from my tables. I have a table-variable input for my usp of following type: CREATE TYPE [AddressInfoParts] AS TABLE( ( [TcpInfoId] [BIGINT] NULL INDEX ...
1
vote
1answer
26 views

Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

I received the error that is in question, but do not have a north where to start investigating the cause of this error, ie not a North where to start. I'm getting worried about the situation. can ...
1
vote
1answer
28 views

location of DTSX package

I need to know the location of a DTSX package which is stored in "file system", It is visible under Stored pacakges-->filesystem-->Package1 in Integration services . I tried to do a windows search ...
3
votes
3answers
946 views

How much RAM should SQL Server have [duplicate]

I have a database which takes up 500gb, I have noticed that my ple is low even though I have 100gb of RAM. I have searched in google to find reasons of that and I found some interesting queries. I ...
0
votes
0answers
47 views

Why the same execution plan causes LOB reads in some cases?

I have a spatial index and the following query: SELECT DS2.[ID] ,DS2.[InstanceID] ,DS2.[Location].STDistance(@Location) FROM [dbo].[DataSource01] DS1 INNER JOIN [dbo].[DataSource02] DS2 ...
2
votes
1answer
57 views

Calculating a percentage discount in SQL [on hold]

Basically just started using sql and having a minor problem. Working out the percentage of a number. Example: What would I need to type in to work out: 2.75 - 5% Drink_price= 2.75 After 5%...
1
vote
2answers
21 views

Prevent inserts to a non existing Synonym

I have a Synonym called VirtualTable which points to RealTable1. A lot of inserts are happening to VirtualTable - about 50 every second. Very often I drop the synonym and re-create it to point to ...
1
vote
0answers
59 views

Logical read when use Offset in SQL

When I execute this script SET STATISTICS IO ON SELECT *, ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO] FROM vwTblTest WHERE ([Factor] >= 139501 AND [Factor] < ...
0
votes
2answers
53 views

SQL Server mdf file too large

I have a .mdf file that is 10gb. This is the max file size. How can I reduce the file size? I do not need the data, I just need to be able to write new data to the database. I have deleted a ton of ...
2
votes
2answers
39 views

Restore Database from Database with Different Name

I have 2 databases, DB1 and DB2. DB1 has production/live data. A script runs daily (takes about 3 hours) to fill DB2. The goal is to restore DB1 with DB2 so that DB1 is down for as little time as ...
0
votes
1answer
29 views

SSIS: Import Excel file to SQL Server

Is there a Excel version for this article? I have been searching for this past days but there is no luck. https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-the-foreach-loop-container/
1
vote
2answers
29 views

Updating dbi_dbccLastKnownGood

I have a VLDB and I offload the DBCC CHECKDB to another server. Is it possible to update the dbi_dbccLastKnownGood on the VLDB server so it passes any scripts that check that date like sp_Blitz?
3
votes
1answer
61 views

Why does the “Date Created” for the master database show such an old date (8 April 2003) in SQL Server 2008 R2?

Why does the "Date Created" for the master database show such an old date? I created the new instance today, not 2003.
2
votes
3answers
37 views

select from many to many by matching field within a column

Having following tables: Order table: OrderID 1 2 3 4 5 CustomerOrderLink table: CustomerID OrderID 1 3 1 4 2 2 2 4 3 3 4 4 Customer ...
0
votes
1answer
63 views

Incorrect syntax near the keyword 'WHERE' - SQL Server 2014

The error I get is Incorrect syntax near the keyword 'WHERE'. Script: SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[msp3_account_login] @LOGINID nvarchar(50), ...
1
vote
1answer
49 views

Matching empty string is not working

I have the following query SELECT Ice.IceId, Ice.Code, Box.Ice, LEN(Box.Ice) AS IceCharLength, DATALENGTH(Box.Ice) AS DataLength, Box.Ice, ASCII(Box.Ice) AS ASCII, CAST(Box.ice AS ...
0
votes
2answers
60 views

Same query with less data takes more time [on hold]

I have a same query to find MIN(download_id) but different data ranges in where clause. The criteria which has more number of rows takes less time but query result which has relatively less number of ...
0
votes
1answer
42 views

How do I run a query only asking for a max date of attendance?

I'm new to SQL and the school I work for wants to run a query on students' transcripts. We want to find out the most recent school the student attended prior to coming to ours. I think I need to use a ...
1
vote
0answers
24 views

How to design efficient queries and structure for data logging table (numerical stats)

I'd like to create a logging table that logs numeric values (.NET application). Being a programmer and not really a database person, I'd like to ensure myself with the advice of more experience ...
0
votes
0answers
27 views

Execution time for CONTAINSTABLE/WHERE query

I am facing an issue for a seemingly trivial database query on SQL Server 2012 accessing a full-text index: SELECT MyTable.ID FROM MyTable INNER JOIN ( SELECT [KEY] FROM CONTAINSTABLE(MyTable,...
0
votes
1answer
16 views

ACCESS_METHODS_DATASET_PARENT Problem

We had an AlwaysOn Async replica we had some problems so we deleted the availability group After that the database and CPU seem to work abnormally It's very slow and we have waittype = LATCH_EX , ...
0
votes
0answers
37 views

pros and cons of partition or not-partition this sql server table that is highly hit?

I have a table that is highly hit on my database. this table has 370,370 records. when I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [...
2
votes
1answer
46 views

Make index work on join to table variable

I a table with several columns, but the most importants are: DocumentId(bigint), StatusId(int) and ReceiveDateTime(datetimeoffset(3)). CREATE TABLE [DocumentInfo]( [DocumentId] [bigint] NOT NULL, ...
0
votes
0answers
9 views

Multi Purpose Witness/Master Data Services SQL Server

I currently have a Master Data Services SQL Server instance that I use to execute SSIS packages, moving and consolidating data from a few OLTP servers to a Data Warehouse. It currently just runs its ...
0
votes
0answers
17 views

repeat headers not working in Microsoft Visual Studio 10.0 report builder

I added a tablix to my report grouped by location, set the page break between each group, set repeat column header to true, and selected the static button of the header and set repeat on new page to ...
0
votes
2answers
34 views

Need to create a “summary grid” query?

I have multiple transaction records in table A like this: TXN# DATE CUSTOMER 1001 10/05/2015 Cust 1 1002 02/03/2016 Cust 2 1003 02/06/2016 Cust 1 1004 03/25/2016 Cust 3 1005 03/26/...
0
votes
1answer
44 views

EXEC xp_dirtree

I developed a stored procedure with a call to this extended SP EXEC xp_dirtree @path, 1, 1 Database: SQL Server 2008 R2 Express I run the store procedure from difrentes sources: ASP. Net with ...
1
vote
0answers
32 views

What do you disable, uninstall, remove from your OLTP SQL Server to keep it as clean as possible?

I'm looking for answers on all the services you disable, anything that you disable, uninstall, etc. The goal of this topic/question is have a running checklist of things that do NOT need to be on a ...
0
votes
1answer
29 views

Select 1 Row Per Day For Each Database

I have a table with over 60 million rows. Snapshots taken at multiple times a day for all databases across all instances. I am trying to reduce the rows in my select by only returning 1 snapshot per ...
0
votes
2answers
37 views

Need demo scripts and sample corrupted databases for corruption databases troubleshoot [closed]

I am looking for a demo for recovery of corruption databases in different ways. Is there any link for those examples to practice in LAB machines.
7
votes
2answers
357 views

select rows having condition met for group (without temporary table)

Having the table with 3 columns: ID category flag 1 A 1 2 A 0 3 A 0 4 B 0 5 C 0 I want to select all rows that have flag = 1 at least ...
0
votes
0answers
36 views

Linked Server directly to database

Say I have a query like this: select * from openquery ([linkedservername],'select * from [databasename].dbo.person') Is there a way of omitting [databasename].dbo.? I have an enormous query I want ...
4
votes
1answer
76 views

Copy Millions of Records from one table to another with the same schema, in the same database

I am trying to find the best way to copy a large amount of rows ( ~70million) from one table to another with the same schema inside the same database. Our system currently runs with a set of A/B ...
0
votes
0answers
37 views

Looking for additional ideas on transaction log waits

We are seeing high Log Waits on our SQL Servers. Our Production server can be in the hundreds of milliseconds. We are running SQL Server 2008 R2 on Win 2008 R2. The server is virtual using VMware. ...
-1
votes
0answers
25 views

Issues when try to open VS editor from Script task from BIDS

I am getting below issues from SQL Server Data Tools. I reinstalled Data Tools on the system a lot of times & repaired VS a lot of times. Even then I am not able to fix the issues. Can you ...
0
votes
0answers
29 views

concatenate/using string doesn't work while query for clearing table [closed]

My boss required me to write a query that clear tables... and i searched and found some code, put them together, at the end, for simplify and remove any mistake, put the code in variables... but it ...
3
votes
3answers
261 views

Long running delete query

I have a table with roughly 11 million rows, defined as: CREATE TABLE [sko].[stage_närvaro]( [datum_fakta] [datetime] NULL, [person_id] nvarchar NULL, [läsår_fakta] nvarchar NULL, ...
0
votes
1answer
33 views

Deselect CPU in affinity setting for OS Only use?

A memory recommendation is to reduce MaxMemory by 4GB or 10% of RAM (equally divisible by cores) to dedicate that memory for the OS. Most questions on CPU affinity deal with splitting them out ...
3
votes
2answers
72 views

“Sniff” SQL queries being sent out from my computer

I'm a student working on pulling certain information from an SQL server here at work. I don't have sysadmin privileges and my attempts at receiving them have not been met with success. It's a big ...
0
votes
1answer
44 views

Rebuild system databases to a different location

Firstly, forgive my poor question title. I couldn't think of the best way to summarise this. Essentially I'm putting together some scenario based DR documentation, with this specific scenario being ...
0
votes
0answers
46 views

Unrecoverable error occurred during Flush operation

We have been backing up our on premise SQL instances (3 different servers) to various azure storage accounts for the past several months without issue. Starting from July 14th, we have been ...
1
vote
0answers
14 views

Cannot connect to WMI provider, SQL Server Configuration manager

I cant connect to SQL Server Configuration manager. I have installed SQL Server 2014 x64 Standard edition and SQL Server Management Studio ,which I can open and work with. Just to point out, that I ...
1
vote
1answer
60 views

is there a term for databases which are not system databases? [closed]

Is there a term for all databases in SQL Server that are not system databases (such as master, model, tempdb)?
4
votes
3answers
95 views

Assign db_owner to all users at once

I need to assign db_owner to all users within an instance. There are a few hundred logins and same number of databases within the instance. I would like each user to get db_owner within the database ...
12
votes
2answers
244 views

How to track blocking that happen for less than a second - SQL Server

I am trying to troubleshoot a blocking issue which happens for less than a second. The OLTP application is very sensitive and has to have an response time of less than 200ms for some transactions as ...
0
votes
0answers
20 views

How to return columns when using Dynamic Sql

In my project, i want to search input values and i think i should create a SP using dynamic Sql. When i call this SP in MVC, it return "int".I want it return columns as ProductName,ProductCat ... . ...
1
vote
0answers
33 views

ms sql database in suspect mode when we upgrade from developer to standard edition [duplicate]

sql database in suspect mode when we upgrade from developer to standard edition 2012 version. in log file there is two error occurred 933 and error 905. following error occurred when we try to get ...
0
votes
1answer
45 views

Query to Find Out How Many Days Until Database Is Full

Using my central MDW database I am trying to make a query that will be able to calculate the number of days until all my databases free space will be full. Currently the query I have is overly ...
2
votes
3answers
206 views

Use of IMPERSONATE permissions in SQL Server?

During some reading, got to know about the Impersonate permissions. From what I've read, it is more like creating a copy of the user with all the permission levels under a different name. I understand ...