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

Removing duplicates values with join

I have a query that looks like this: SELECT B.Company, COUNT(*) AS QTY FROM Table1 A INNER JOIN Table2 B ON A.CompanySk = B.EDWCompanySK WHERE A.StartedDate >= '20171128' GROUP BY B.Company ORDER ...
1
vote
1answer
72 views

Best way to speed up requests using date columns

Our database is receiving the following requests: select * from [schema].[table] (nolock) where (Date_A > Date_B and Archive = 0) This is a normal request that is made by our application. On ...
0
votes
1answer
40 views

Checking update value before update

First time posting so apologies if this has been asked already. I have a table in SQL Server that is being updated from many different locations with data that I don't want in the table. I have come ...
0
votes
1answer
40 views

Index Seek Plan turns to Index Scan after adding another Join condition [closed]

I had a query passing from Entity Framework which is a very expensive query with a terrible performance. while optimizing this query I found the problem in this part: SELECT TOP 20 * FROM [...
0
votes
1answer
53 views

SQL Server - drop user triggered silent revoke of privileges

I encountered following scenario: A user X granted a ton of permission in the SQL Server database. For example it granted SELECT permission on table Y to user Z. A user X was dropped with simple ...
1
vote
1answer
31 views

Storing Routes and Locations in Graph using SQL Server 2017

I'm very new to Graph database, so please ignore my mistakes. My scenario described in following picture: I have different locations connected to each other with a route between them. Then I have ...
0
votes
0answers
57 views

Kimball Modelling One Column Linking to Many Dimensions

We are conducting Quality inspections, Appraisals, and Ratings on furniture. Many different types of furniture exist, chairs (1), shelves (2), couches (3), table (4). The respective DimProductType is ...
4
votes
1answer
68 views

Eliminating a intra query deadlock, can I avoid restricting parallelism?

I have an update statement that is generating intra query parallelism deadlock. Based on this link I believe my two options to resolve this issue is to force SQL Server to avoid parallelism via an ...
-1
votes
1answer
32 views

Incorrect syntax near 'Word, Another Word'

I'm at a loss on what to do. I'm trying to fix an old broken query that was messing something up. If I use the database2 script, it populates what I want. However, when used with the Update, it gives ...
0
votes
1answer
40 views

WHEN MATCHED clause fails if there's a NULL in the ON columns [duplicate]

I'm using a query with merge that works fine if all columns in the ON clause have a value but WHEN MATCHED omits if there's a NULL mark in one of the columns: MERGE PEPS AS peps USING (Lots of Cols.. ...
2
votes
1answer
628 views

Do primary keys change when indexes are rebuilt or reorganized?

I have a database containing tables that each have auto-increment int columns serving as the primary key column. When I do a rebuild or reorganize operation on my indexes, does either operation ...
2
votes
1answer
64 views

Backend Table/Row Locking Structure using CTE Queries

This question is an extension of a previous question that I asked. Table Locking When using Unions With the same query we are using that is causing blocking the various queries used in these UNIONS ...
1
vote
1answer
37 views

Difference between request and session in sp_blitzwho?

I'm starting to use sp_blitzwho, and I was wondering about a few of the columns. There are a set of columns that start request_ such as request_writes, and then a set of columns that start session_, ...
0
votes
2answers
48 views

Timing issues for backups during recovery

Let's say we did a full database backup of the PRIMARY filegroup at 10:20 am. It finished at 10:45 am. We also took log backups every 5 minutes, hence 10:15, 10:20, 10:25, 10:30, 10:35, 10:40, 10:45,....
2
votes
2answers
45 views

Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key

When we are trying to deploy an assembly with PERMISSION_SET=UNSAFE or EXTERNAL_ACCESS, we are getting following error message by SQL Server: CREATE ASSEMBLY for assembly 'System.ServiceModel....
-2
votes
1answer
38 views

How to add operator and email notification on all the SQL jobs?

To be more proactive and responsive to the job issues or failures I want to set an alert, add operator and send the notification(to me) when a job fails. I know Operator and notification can be added ...
2
votes
2answers
83 views

Assign a student to a Classroom based on course and scheduled hour

Each year we have to manual assign students to classes that were not included in original assignment either they are new or the class was cancelled etc, we are talking 100s of students where we have ...
0
votes
1answer
28 views

SQL Server Transaction Rollback: does log shrink or grow?

When cancelling a transaction, be it e. g. via failure or manually when it runs too long, what is the effect on the transaction log and its size? Do all the entries generated during and related to ...
0
votes
2answers
53 views

Efficient way to paginate in sql server with 50M plus data (sql server 17)

I was looking for efficient way of pagination in sql server 2017 so I came across following code: select id, col_1, col_2, col_3 from table_name where deleted_at is null order by id offset @pagesize ...
0
votes
1answer
19 views

Custom Deletion for Ola Hallengren Scripts

I am trying to find a way to delete backups being taken by Ola's native SQL backups. I am trying to keep only 2 weeks of transaction logs, 5 weeks of diffs, ,14 weeks of full backups, 13 monthly full ...
2
votes
1answer
68 views

SQL Server 2019 CTP 2.1 on Ubuntu fails after powering off

I've installed Microsoft SQL Server 2019 (CTP2.1) - 15.0.1100.94 (X64) on my VirtualBox Ubuntu 16.04.5 LTS. Everything seemed to work properly until I've powered off the machine. Since then SQL Server ...
5
votes
3answers
259 views

Getting SQL server to recognise a date column

I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed. My table looks like this orders ( order_id INT ...
2
votes
1answer
34 views

How to deal with id gap when your query is like from id > XX to id < XXX

I have a large table of around 50M data. I am trying to get data with something like select id,name,address,city from table_name where id > 50000000 and id < 50000050 order by id; But, this ...
0
votes
2answers
26 views

What happens when one of the filegroup is full within SQL server

Just a basic question due to my lack of understanding on how below works: For one of the databases around 1 TB in size we have 8 file groups with multiple files added to them. Now for one of the FG ...
0
votes
0answers
46 views

Application Failed to Connect after Failover [closed]

We have 2 nodes Alwayson Availability group , SQLServer 2016 Enterprise edition. It was working fine until recently if failover occurred most of the time the application fail to connect to the ...
0
votes
1answer
27 views

How to best manage a queue of external processes

My scenario is a web app that creates jobs that need to be processes by an R script. We are currently have the web app execute a SQL stored proc that executes the R script. However, we quickly ...
1
vote
1answer
64 views

Table Locking When using Unions

When multiple selects are used in a UNION/UNION ALL are the table locks for each individual SELECT statement handled individually or are all table locks held until all SELECT statements are completed? ...
1
vote
1answer
84 views

Difference in behaviour between `DBCC TRACEON` and `QUERYTRACEON`

I am using SQL server 2014, but my database version is SQL server 2012. So I am trying to use trace flag 2312 to force the query optimizer to use cardinality estimator v120. I have noticed through my ...
2
votes
2answers
53 views

Is there an easy way to have a non-IDENTITY, auto incrementing column based on another column?

+----+-------------+-----------------+-------------+ | ID | ProductName | ProductCategory | ProductCode | +----+-------------+-----------------+-------------+ | 1 | Item 1 | 1 | ...
-2
votes
1answer
47 views

DROP and CREATE versus ALTER [closed]

QUESTIONS: Is is better to ALTER an object definition (i.e. function, stored procedure) in a production environment, rather than IF EXISTS (...) DROP (...) CREATE...? Does SQL Server treat the two ...
0
votes
1answer
40 views

SQL, creating a new table with auto generated passords from other tables

i have this sql below which i have retrieved from our student database. It retreives all that i need, but i would like to create a new table which includes all the tables plus one column with auto ...
0
votes
1answer
47 views

SQL server security

I have requirement that I need to take backup from production and need to restore it in development. I have taken all users and permissions from production by generating scripts, but two users are ...
1
vote
1answer
37 views

is there a way (clean or unclean) to eliminate a filestream data file on a production SQL Server database?

Is there a way (clean or unclean) to eliminate a filestream data file on a production database? I forced a garbage collection on an empty filestream : sp_filestream_force_garbage_collection @dbname =...
0
votes
1answer
65 views

Move Large number of rows

I am trying to move close 88101840 rows from one table to another. To handle log growth on the server looking at doing 10000 rows at time. Please have a look at the query below. Just want to find if ...
1
vote
1answer
35 views

Custom backup deletion Ola Scripts

I am trying to find a way to delete backups being taken by Ola's native SQL backups. I am trying to keep only 14 days of transaction logs, 1 month of diffs, and 12 months of full weekend backups. Is ...
0
votes
3answers
166 views

Create Slowly Changing Dimension table from Repeated Data

I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. Many ...
0
votes
2answers
75 views

Create a View from Multiple Databases

Database X - tableCust - Column-CustNumber Database Y - tableCust - Column-Custnumber Datbase Z - tableCust - Column-Custnumber I'm trying to create a view in database H from those three databases ...
-1
votes
0answers
29 views

Random Nth Groups with Equal Distribution

I would like to pick three Random Nth groups out of a pool with different keys but each group to have the keys equally distributed. Can you please help. Need to select 3 random groups of 9 records ...
2
votes
2answers
119 views

Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys

I'm trying to figure out what the optimal solution is in regards to size and performance when using GUIDs as a primary key. The main reason for wanting GUIDs is that it's more appropriate in our micro-...
0
votes
1answer
58 views

How are other computers able to connect to my database

I have installed the software on my clients system where sql server was already installed. I have installed another named instance. I am able to login to the SQL server from another computers, when I ...
3
votes
1answer
112 views

Any way to tune / optimize this query?

Actual Execution Plan here: https://www.brentozar.com/pastetheplan/?id=H1fgOQTRX Non Clustered Indices did not seem to change much. SELECT DISTINCT TOP ? Object1.Column1 AS Column2, Object1....
1
vote
2answers
166 views

Performance of IN with OR operator

I have common clause in most of the procedures like Select * from TABLE A + Joins where <Conditions> And ( -- All Broker ('True' = (Select AllBrokers from SiteUser where ID = @...
0
votes
1answer
30 views

How to secure SQL Agent Job that INSERTS to a linked server

What is the proper way secure a T-SQL agent job that moves data between linked servers? Setup: Microsoft SQL Server 2012 (SP3) Enterprise Edition SQL Server Agent running as NT Service\...
0
votes
1answer
63 views

Maximum concurrent connections to SQL Server Express 2017 installed on Windows Server 2016 Essentials

If I install SQL Server Express 2017 on Windows Server 2016 Essentials, what is the maximum number of users that will be able to connect concurrently across the network to the SQL Server Express ...
2
votes
1answer
29 views

SQL Server 2008 Version of rows between… preceding and current row

Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008 As you can see, The SUM column for July 2017 for example is equal to the following: August to Dec 2016 + ...
0
votes
2answers
45 views

How to efficiently handle querying for different combinations of optional parameters

Consider the following Tables: Table: Parts part_id INT PK part_number VARCHAR Table: Jobs job_id INT PK job_number VARCHAR Table: Job_Parts part_id INT FK PK job_id INT FK ...
0
votes
1answer
41 views

Find if Stored Procedure Error

Is there a method in T-SQL to see if error occurred in Stored Procedure, without the use of @Output or try catch? We have a lot of legacy code 200+ stored procedures. I have seen @Output method and ...
1
vote
1answer
47 views

SSIS column cannot convert between unicode and non-unicode string data types

I am transferring a table from SQL Server to PDA Server. This is my data flow: ADO NET Source ---> OLE DB Destination All of the columns in my source table are varchars and so when I created my ...
0
votes
1answer
30 views

SQL command mode - unable to run command

I just answered this question but never tried to run it. So I tried it.. https://stackoverflow.com/questions/53521410/sql-command-mode-sql-server-unable-to-print/53521635#53521635 and I thought if ...
0
votes
1answer
20 views

Has anyone connected to Oracle from SSIS using Oracle Driver ( 12.1.0 ) from VS 2017 Data Tools SSIS

I have installed Oracle Driver ( 12.1.0 ) and I have VS 2017 Data Tools SSIS . The connection to Oracle Service from local machine via ODBC 64 bit on my machine is working. Connection to Oracle ...