Questions tagged [sql-server-2012]

SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
2 votes
2 answers
55 views

SQL Server 2012 Standard Edition rebuild index

We use SQL Server 2012 Standard Edition. Is there a way to rebuild the index, trying not to cause data table locking, and reducing the impact on performance? When we rebuild the index, it causes the ...
user avatar
-1 votes
0 answers
46 views

Creating a Hierarchy by Text

I'm trying to create a hierarchy from a text field I have in my table. Visually it is relatively easy to see who is parent and child. For example: (see a screenshot of the table results) 1M -----1M5 --...
5 votes
2 answers
233 views

Varbinary update attempt

Is it possible to adapt this into update query which has to seek a free sequence of 3 bytes or (0x000000) and replace it with a new sequence if the data is split like this every 3 bytes? The issue is ...
0 votes
0 answers
17 views

Join remote tables with local tables in SQL Server 2012

If I have to join a local table to a remote table then I usually use the 4-part identifier. For example: select t.id, r.region_id, r.cost from localdb.dbo.product p join linked_server.remotedb.dbo....
  • 149
1 vote
1 answer
28 views

Filtering in Queries using Linked Servers in SQL Server 2012

My colleague told me that any query that uses a linked server first transfers the data from the remote server to the server the query is running on and then applies filters. For example, if I have the ...
  • 149
1 vote
3 answers
51 views

Alter a scalar UDF that is used in a check constraint in SQL Server 2012

I have a check constraint on a column that uses a scalar UDF. I have a requirement where I need to make changes to the UDF. Based on what I know, I need to drop the constraint, make changes to the UDF,...
  • 149
0 votes
0 answers
25 views

SQL Server: Spatial Indexing and Bounding Box

I have two tables each with a SHAPE Geom Column, and I'm working on getting the intersected area between every shape in the first table with the second table. The second table has millions of rows, ...
  • 101
7 votes
1 answer
741 views

Does With(NoLock) help with query performance?

Most of the select statements in our stored procedures use WITH(NOLOCK) and I was told by my co-workers that they use it to improve performance. Now, I know that this hint helps with locking but does ...
  • 149
0 votes
0 answers
56 views

Improve query performance in SQL Server 2012

I have a table with 150 million rows with a non-clustered index on userId. I have to write a query to filter on userId and return all the rows if the productType is not SIG and return only rows ...
0 votes
1 answer
37 views

Modify an existing non clustered index or create a new index in SQL Server 2012

I have a table with 150 million records and 8 indexes. One of the indexes is on userId. A lot of the current queries I have filter on userId and therefore this index works perfectly. For one of the ...
5 votes
1 answer
179 views

How to Retrieve (Debit, Credit and Balance) from daily Transaction tables (Customer, Invoice, Payment) Full and Date Range)

There are 3 Tables Customer, Invoice and Payment. Customer Link with Invoice and Payment table. Question is, need to retrieve statement wise Debit, Credit and Balance. main issue is opening balance ...
  • 63
0 votes
1 answer
47 views

Re-write a query in SQL Server 2012

I have 3 tables: order: contains the list of all the orders; product: contains the list of all products for each order; productVersion: contains multiple versions for each product. I want to write a ...
0 votes
1 answer
54 views

Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\‪D:\DB_backup\uni.bak'

I have 2 database backup files casa.bak (25 GB) and uni.bak (176 GB) in the same folder. I'm using the same script to restore the database for both files previously both were working successfully. Now ...
0 votes
1 answer
104 views

Convert an Int column to BigInt in a stored procedure in SQL Server 2012

We have a stored procedure that uses SUM on an Int column in the SELECT statement. We have a few cases where for one of the parameter values, the SUM(integer column) exceeds the Int range and ...
2 votes
1 answer
33 views

Forwarding SQL Server Error Logs to Another Server

I wasn't sure where to post this question. If this is not the right place, kindly point me in the right direction. We have SQL Server 2012 and 2014 on-prem. My intention was to forward the error logs ...
  • 123
0 votes
0 answers
26 views

Does enabling or disabling a trigger cause table statistics to go out of date (SQL Server)?

Whenever I modify a table (adding an index or adding or dropping a new column), I always run a sp_recompile on the table to make sure the statistics are updated. When a trigger is enabled or disable ...
0 votes
0 answers
38 views

How do I edit DataRows of DBF file via SQL Server Management with Query?

I have an old DBF file that I want to manipulate its data directly through SQL Server Management itself, for example, perform update, insert, delete operations. I tried using the following code from ...
2 votes
1 answer
275 views

Return the inserted timestamp after inserting a record [duplicate]

I feel like this should be an easy problem. All I want to do is return the timestamp of the entry that was just inserted, but in the same call. All I can find in my searching is about returning the ...
0 votes
0 answers
54 views

How to get exact SQL output for below mention input

Input table data : itemid MfrCatalog MfrNameNorm RawDescription UOM Conv noun Modifier 420754 G322H ETHICON, INC SUT PLAIN 3/0 27IN EA 1 SUTURE ABSORBABLE 420754 G322H ETHICON, INC SUT PLAIN 3/0 ...
0 votes
1 answer
131 views

Performance benefit of dividing a frequently used stored procedure into multiple stored procedures in SQL Server

I have a stored procedure usp_appdata that is used by our application loading screen as well as a lot of other places in the application. This SP is very resource intensive and returns a lot of data. ...
2 votes
1 answer
499 views

SUM total amount of each column from two tables SQL Server

By joining two tables, the total amount obtained from Crowdfund_Users is incorrect. Sum of Amount in table Crowdfund_Users increased by an additional 60000 from ID 5. table Crowdfund_Users (ID is ...
0 votes
0 answers
38 views

In SQL 2012 - Export Data-tire Application Error: Database source is not a supported version of SQL Server : . (Microsoft.SqlServer.Dac)

I have asked to move Database from SQL server 2012 to azure SQL During the export data-tire Application Its shows Error : Database source is not a supported version of SQL Server (Microsoft.SqlServer....
0 votes
1 answer
239 views

Updates to the same table in concurrent sessions in SQL Server

Consider the following scenario: 10,000 users try to create a profile on our application concurrently at the same time and the data has to be inserted into the table, userProfile. The SP that handles ...
5 votes
3 answers
145 views

Is there a way to sort by an alphanumeric column? [duplicate]

I have a table units unitname unit 1 unit 2b unit 2c unit 2a unit 10 unit 3a unit 3c unit 11 I want to sort the same as unitname unit 1 unit 2a unit 2b unit 2c unit 3a unit 3c unit ...
2 votes
0 answers
64 views

Setting up Database Integrity Checks

I'm relatively new to SQL and, unfortunately, am definitely not at the level of a DBA - However, it's become my job to look into some SQL issues... There's a Database Integrity Check set to run for ...
  • 21
4 votes
1 answer
104 views

Official Source of What Time Zone an XEvent is Logged

I'm trying to prove that extended events (hence audits as well) are logged in UTC time zone and can't be changed but I can't find an official source. I've searched Microsoft's pages and SQL Server ...
0 votes
1 answer
78 views

SQL server web edition licensing [duplicate]

My hosting company says SQL Server web edition has 16 cores limit, but at same time they provide license of 24x2 for SQL Server web edition, which we are not sure if its utilizing all CPU cores or not....
  • 121
2 votes
1 answer
111 views

Have to regenerate SQL Server Master Key after every server reboot

We recently migrated our database over from SQL Server 2012 to a new SQL Server 2019 box. As soon as the database was migrated, I regenerated the master key, but whenever our SQL Server 2019 box ...
3 votes
1 answer
111 views

XML vs UDF performance in SQL Server

I have a situation where I have to decide between receiving XML as an input parameter to the stored procedure or a list of comma separated values and parse them using a multi-table valued function. ...
0 votes
1 answer
98 views

Migrate SQL Server 2012 databases on premises to Azure SQL

I have SQL Server 2012 databases on premises and I would like to migrate these to Azure SQL However the Application Vendor has not certified their Application on Azure SQL but the following versions ...
  • 1
0 votes
0 answers
631 views

Select Data 5 minutes interval

I have data of 800 different id which is almost 48232 rows. sample data is pasted below Id DateTime Value 2999 14/11/2022 9:02:43 0.84 2999 14/11/2022 9:03:14 ...
  • 1
5 votes
2 answers
437 views

Case-Sensitive Collation on a select query in SQL Server

Our SQL Server's collation is set to SQL_Latin1_General_CP1_CI_AS I have a requirement where we have the same values with different cases but SQL Server treats them the same since our collation is set ...
0 votes
3 answers
696 views

How to split a large table in SQL Server

We have a table that consists of 150 million records and the data goes back all the way to 2010. I'm considering splitting the table into two to improve performance - a history table with data prior ...
0 votes
0 answers
21 views

Local variables and their impact on execution plans in SQL Server Stored Procedure [duplicate]

If a parameter that is passed to stored procedure is assigned to a local variable and the local variable is used inside the stored procedure logic, will that affect the execution plan in any way? Is ...
0 votes
0 answers
279 views

Transactional Replication Error: Violation of Primary Key Constraint on value that doesn't exist at subscriber

I have transactional replication setup going to 3 subscribers. All 3 are randomly throwing the following error: Violation of PRIMARY KEY constraint 'PK_RecieverList'. Cannot insert duplicate key in ...
1 vote
1 answer
81 views

Creating a non-clustered index on clustering key with some columns included

I have 3 tables: test_productInfo: contains information about the product test_productCreator: contains all the users in our system test_productOwner: contains information about who is the owner of a ...
2 votes
2 answers
172 views

Should I test against warm cache or cold cache in sql server?

I'm tuning a query which runs slow the first time and fast on the subsequent runs. I understand that it is because SQL Server is using cold cache (reading from disk) the first time and using warm ...
0 votes
1 answer
49 views

Testing strategies when tuning a stored procedure

I'm tuning a stored procedure that is used by the first page that loads whenever a user logs into our website and it's very frequently used. When I run the procedure the first time it runs in about 10 ...
0 votes
1 answer
113 views

Pagination solution increases the logical reads but decreases execution time

I'm working on a solution for pagination and I came across this article by Aaron Bertrand: https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch. The article suggests a CTE ...
0 votes
0 answers
46 views

Cannot execute as the user 'username' AS IF it was an orphaned user. But user is WITHOUT LOGIN

I have restored a database to a different server. The following errors do not occur in the original database. I have a username WITHOUT LOGIN. Username is used in EXECUTE AS for a procedure. In the ...
  • 133
1 vote
2 answers
646 views

What happens to an open transaction when the session window is closed?

I executed the following query in SQL Server 2012: create table testTransaction ( id int name varchar(100) ) insert into testTransaction values (1,'ABC'),(2,'XYZ') /* Query1 */ begin try begin ...
2 votes
0 answers
370 views

How do I fix the Failed to retrieve data for attaching mdf file is which not primary database file

I'm using SQL Server 2019 in Windows 10 Enterprise and Administrator user I have an MDF file and I want to attach it, and it has full access in the form of Everyone Full Control , when I try to attach ...
-1 votes
2 answers
694 views

SQL Server: left join resulting in multiple rows, want each row result in separate column in result

I need some logic help with a complex join situation. Here is an overall idea of the 3 tables I am working with Table 1: ID Number Type 1 A1 A2 Table 2: ID Number Type 1 ID Number Type 2 A1 B1 ...
0 votes
1 answer
46 views

How to skip "read only" databases when a server has many AOAG?

For example, I want to run an index maintenance job on all databases. My server has many AOAG, and is often playing the primary and secondary replica role for different AOAGs at the same time. If I ...
  • 7,248
-1 votes
2 answers
671 views

Is there a way to dynamically update records based on values in one column and using that value to find a matching column name?

Id Codes Col_Code1 Col_Code2 Col_Code3 Col_Code4 Col_Code5 A283240 Code1 1 Null Null Null Null B490382 Code2 Null 1 Null Null Null B490382 Code4 Null Null Null 1 Null B449200 Code3 Null Null 1 ...
-1 votes
1 answer
40 views

SQL Server long term backup policy

I wish to implement a long term backup policy in SQL Server on VM, much like Retention policies in azure sql database. I have full backup once a week, diff once a day and log backup every 15 minutes. ...
  • 1
0 votes
0 answers
33 views

SQL Server audit per day

I need, for regulation reasons, to create a SQL Server audit files to monitor database actions. I want to somehow create a file per day so I can ship the file to Azure storage. I know the audit files ...
  • 1
0 votes
0 answers
25 views

SQL Server has encountered <X> of I/O requests taking longer than 15 seconds. It was transient, but how do I find out why?

This happened roughly a week ago and root cause analysis has become an issue, I suspect locking but I'm not sure how to progress. I'm reasonably sure it's not a dev ops issue, i.e. capacity / ...
0 votes
0 answers
65 views

Additional unused pages for partitions after migration from SQL server 2012 to SQL server 2019

After migrating from SQL server 2012 to SQL server 2019 one of the tables and only one on which we have partitions is using more space. From what i noticed SQL Server is reserving more pages for each ...
  • 61
1 vote
0 answers
262 views

spid locked in KILLED/ROLLBACK state

I have a SPID stuck in what appears to be a deadlock, in tempDB... sp_who2: sp_lock: KILL 81 WITH STATUSONLY: SPID 81: transaction rollback in progress. Estimated rollback completion: 0%. Estimated ...
  • 1,005

1
2 3 4 5
91