Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server]

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.

0
votes
0answers
48 views

SQL Default CONSTRAINT not working

I have the table below with some basic information with two defaulted columns CREATE TABLE [dbo].[INSPECTION]( [InspectionID] [int] IDENTITY(1,1) NOT NULL, [LocationID] [int] NOT NULL, [...
0
votes
0answers
40 views

how to collect the information provided by tokens in order to troubleshoot failing jobs quickly?

I was working on a way to take advantage of the SQL Server Agent Tokens and Macros because I work a lot with jobs and job steps and sometimes I have to notify different people depending on the outcome ...
2
votes
0answers
31 views

Windowed IDENTITY column definition? [duplicate]

Is it possible to create a windowed identity column that automatically initializes and increments in SQL Server 2017 or later? I would like the conceptual functionality of SelectedSubsetId below. ...
-8
votes
1answer
66 views

Can someone assist with update statement beginning to end? I am new to development so please do not expect me to understand everything [closed]

I have been teaching myself as I go and having trouble updating a varbinary column. The issue is the column has a different data type. I want to convert varbinary to bit. My code looks like: ...
0
votes
1answer
48 views

Methods for automating SQL CU patching?

For reasons that are not mine to address, we are looking for alternatives to WSUS and SCCM to automate SQL CU patching (particularly on SQL 2017+) There are some neat Powershell solutions like this ...
2
votes
2answers
456 views

Return row with most matching parameters

I'm trying to make a stored procedure who will return me the RowID with most matching parameters. Let's say we have a table: +-------+------------+------------+-----------+ | RowID | DocumentID | ...
3
votes
1answer
69 views

Why does sql server perform better after restart

We had a performance issue that we solved by restarting the sql server service. That's obviously not the way we want to continue handling our performance issues. When a sql server performs better ...
0
votes
2answers
66 views

Find a row with value zero in any column

I’ve 10,000 rows with 64 columns. If any column in any row has the value “0”, that row needs to be selected. Does anyone have a ready-made query? It would be a benefit to my project. The values can ...
5
votes
1answer
106 views

Why doesn't first execution plan use RowCount Spool?

Here is my repro that was tested on SQL Server 2008 R2, 2012, 2016. The second and the third queries do use RowCount Spool, why the first does not? create table dbo.t (id int identity primary key, v ...
2
votes
3answers
434 views

Struggling to create an efficient index for this query

I am struggling to create an index for a query and unfortunately I cannot change the query at all as it part of an ERP system. The problem is this query has over 1m reads and sometimes a duration of ...
0
votes
2answers
33 views

Run query against all databases on server containing specific string in name

If I have a query such as: SELECT convert(varchar, DateTime, 100) AS "Date and Time" ,Text2 AS Area ,Substring(PText7, 1,Charindex(':', PText7)-1) AS Username FROM the_database.dbo.AlgViewENU AS data ...
1
vote
0answers
19 views

Add signature to stored procedure using PowerShell SMO

I want to add a signature to a stored procedure using PowerShell SMO. The equivalent command in TSQL is: ADD SIGNATURE TO [myprocedure] BY CERTIFICATE [mycertificate] with password = 'CertificatePW' ...
2
votes
3answers
50 views

Select data from another database instance on the same server in sql server

I have a query shown below select count(*) as Count, datepart(yyyy, [LogDate]) as [Year] from ViewAssociate..Auth_Log where ActionCode = 12 group by datepart(yyyy, [LogDate]) order by [Year] Now ...
2
votes
2answers
42 views

Sum up counts in subquery

I am writing a query to check if I have got duplicates in a certain column. SELECT COUNT(thecol) FROM thetable WHERE thecol IS NOT NULL GROUP BY thecol HAVING COUNT(*) > 1 That will give me an ...
0
votes
1answer
45 views

SQL Server Management Studio cannot connect because of common algorithm

I just installed SQL Server Management Studio, and I'm trying to connect to SQL Server 2014. It shows an error message "A connection was successfully established with the server, but then an error ...
4
votes
1answer
36 views

SQL Server 2014 View any Live execution plan in Activity Monitor

I have a SQL Server 2014 Build(12.0.5205.0) running here. Would It be possible to view any Live execution plan (from queries being executed by other users) in Activity Monitor ? My problem is: ...
11
votes
2answers
1k views

Why does a DELETE query run in one format much longer than in another?

I have specific cleanup code that tries to remove some duplicates. This runs perfectly on many customer sites. The logs tell me that at least 1 sec up to 45 sec is consumed by this query: DELETE ...
0
votes
2answers
33 views

get-dbabackuphistory bug in high availability environment

I am very fond of dbatools.io for SQL Server DBA maintenance For some time now I have been using get-dbabackuphistory to check the backups of the last week on all databases / instances. Currently we ...
0
votes
3answers
62 views

Query where condition take from the list of value

/****** Script for SelectTopNRows command from SSMS ******/ SELECT DISTINCT ( SELECT ForeignCurrencyBal FROM [InsightWarehouse].[dbo].[v_Account] AS subA WHERE ...
0
votes
1answer
46 views

Should I place Kimball Data Warehouse in Microsoft Columnstore Index on Premise?

Are Columnstore tables now the vendor default method for Kimball Data warehouse tables with Dim and Facts for On Premise? I know in the cloud, Microsoft automaticaly recommends Azure Data warehouses ...
2
votes
1answer
36 views

Completely remove additional data file from SQL Server 2012 DB

Hope someone can help me on this one. I have created by mistake an additional data file in the incorrect file group, I need to either remove the file completely or move the file to the default file ...
1
vote
3answers
41 views

NTLM vs Kerberos authentication - questions

We have 4 servers, all are members of the same Domain: 1. SQL 2012 on Windows Server 2016 2. SQL 2012 on Windows Server 2012 3. SQL 2017 on Windows Server 2016 4. SQL 2017 on Windows Server 2016 I ...
1
vote
0answers
38 views

Connecting to SQL Express Server 2017 with SQL Server Authentication Remotely

Some information: Running SQL Express Server 2017 I have two systems that are on the same network. I am able to ping back and forth between these systems with their IP address and with their system ...
0
votes
0answers
28 views

SSMS 17.9 instalation issue “DACFramework.msi”

I have issue to install Microsoft SQL Server Management Studio 17.9 on Windows Server 2016, I got following error and I have tried 32 and 64 bit version of DACFramework.msi and none of them worked.
6
votes
1answer
365 views

Index help for bad Query on fairly big table (2m)

I am looking for some assistance please. I have a query that is running against a fairly big table (2 million records). I have been trying to get the indexes working efficiently. There are a few ...
6
votes
3answers
425 views

Does SQL Server Cache Aggregate Results When Duplicated Across Columns?

Suppose we have a table Orders containing the columns order_id, total, discount Then we write a query similar to the following SELECT COUNT(order_id) AS num_orders , SUM(total) / COUNT(...
1
vote
1answer
50 views

Can I query Microsoft GP database and save the result as MDF file?

I have a request as below for auditing purpose on Microsoft Dynamic GP database. Database files are required as .mdf and .ldf files for certain companies Only need the certain period time of data, ...
1
vote
1answer
43 views

Upgrade SQL 2008R2 to SQL 2016 on windows server 2012R2

I am checking for expertise opinion for IN PLACE UPGRADE from SQL 2008R2 to 2016. We have tested side by side which looks good in dev env's. Considering production can i have some opinions on how ...
-2
votes
2answers
70 views

Need to encrypt the database files and backup files [closed]

Our requirement is that the data at rest is to be encrypted. MDF/LDF files to be encrypted, backup files to be encrypted. I found 3 options. We use Sql Server 2016 (SP1) Always encrypted AppScan EFS (...
2
votes
3answers
198 views

Transaction deadlock during restore

I am working on PS script to restore database backups and I have following SQL script that I am running from PS Invoke-SQLcmd command: USE master GO ALTER DATABASE mydatabase SET SINGLE_USER WITH ...
-4
votes
1answer
45 views

Tsql joins on sql server [closed]

What type of joins would you use if you were trying to find missing data in two related tables? Would it be an inner join or an outer join?
2
votes
1answer
67 views

How to benchmark disk performance from within SQL Server?

I know of tools such as SQLIO and diskspd to benchmark IO/disk performance, but need something that can be run from the SQL Server instance itself via T-SQL, as I'm working on a Cloud-hosted ...
-3
votes
1answer
80 views

What is the difference between the date queries and which is more efficient? [closed]

Where tt.transactiondate >= getdate () -1 Where tt.transaciondate >= dateadd (day, -1, getdate()) Where dateadd (day, 1, tt.transactiondate) >= get date() Where datediff (day, tt....
1
vote
2answers
60 views

Warning in Execution Plan

I have a T-SQL query with four inner joins, I executed the query in SSMS with Actual Execution Plan selected. When I review the generated plan I saw the warning icon on Sort operator. What is the ...
0
votes
1answer
40 views

Why is a trigger preventing update?

I have a script that runs weekly. It updates multiple tables in my database. It turns out one statement attempts to update 150,000 rows on a table that contains 250,000 rows. That failed this week ...
23
votes
4answers
575 views

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded. The comparison is ...
-1
votes
2answers
43 views

Is there any possible solution to insert correct records and prevent incorrect records from being inserted by insert statement in SQL server? [closed]

I know that each statement (i.e: Insert , Update , Delete ) in SQL-Server is treated as one single transaction and I know that one of the main characteristics of a transaction is it's 'Atomicity' so ...
1
vote
1answer
36 views

How to remove job history for a TIME PERIOD in SQL Server

I am trying to purge / delete history for all jobs that ran for a week. How could I achieve this? Please note that I've read this article that talks about deleting history for a SPECIFIC JOB (I have ...
1
vote
1answer
48 views

When to Disable and Recreate Indexes in Data Warehouse ETL?

We are moving data from Staging Database into Kimball Data Warehouse DB. I am hearing different ideas regarding when to disable and rebuild indexes. Stage tables are truncated every day for each new ...
0
votes
0answers
24 views

Configure replication from SQL server 2014 Enterprise Ed. to SQL server 2017 Enterprise Ed

The query here is pertaining to Replication support between Publisher instance running on SQL Server 2014 Ent. Ed. and Subscriber instance being on SQL Server 2017 Ent. Ed. Currently all DB instances (...
0
votes
1answer
71 views

Sql select slow on filter for key, but not for primary key, how can I hint the priority

I have 2 select, the first is superfast, and the second is 10x more slower. It seems to be first join everything from the table, than running the startdate filter, and the first case it is filtering ...
1
vote
1answer
34 views

Suggestions - Enabling EFS on FileStream Data for SQL Server

If anyone has already tried enabling EFS on FileStream data for multiple databases around 500 GB+, please share your thoughts on feasibility or issues / considerations we should keep in mind. We are ...
0
votes
0answers
34 views

Is possible to select non consecutive lines of queries to execute in SSMS 2017

os: windows 10 ssms v17.9.1 Example If I want to run the first and the third query, the unique solution I find is select and comment(CTRL+K, CTRL+C) the second query and select the three query lines ...
1
vote
1answer
35 views

Should SSIS packages and SQL database be on same server?

I'm a bit new to SSIS. I have a lot of 'dev', building, query, tuning performance on SQL Server but am not the master of admin/ overall performance modeling. So here's my question --- Should SSIS ...
0
votes
1answer
15 views

How present the result table below the last result query table, SSMS 2017

os: window 10 SSMS 2017, v17.9.1 I watched youtube videos of SSMS running two different queries and the last result was displayed below the last table. Example that I am referring in youtube. Now ...
0
votes
2answers
70 views

What is the best way to remove large amount of data from large tables?

I have two large SQL Server 2008 R2 tables: table_header with 7 columns and ~ 2 million records table_detail with 22 columns and ~ 78 million records The common field between the two is called ...
2
votes
0answers
37 views

Complex query won't run after migration [closed]

I have migrated an old, ugly query from a 2008 SQL Server to a new 2016 instance. The query runs fine on the old database even when the query plan has to be recreated. On the new database the query ...
4
votes
2answers
455 views

Is there any limit for IN results in SQL Server?

Is there any limit for the content that the IN filter can handle? For example: SELECT Name FROM People WHERE Job IN (All the values goes here) Microsoft docs for IN says: "Explicitly including ...
0
votes
0answers
39 views

Ignoring Null (optional) Variables in Stored Procedure

I am looking for what is hopefully something easily achievable without a ton of coding. I have a stored procedure that does some work and has about 20 optional parameters: CREATE PROCEDURE dowork ...
-2
votes
2answers
60 views

Are lost updates completely handled in SQL Server or is manual intervention still required? [closed]

As lost updates occur when 2 different transaction try to update the same thing at the same time - different sessions update the same row; the last one overwrites the changes made by the first. We ...