Questions tagged [sql-server-2012]
SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.
4,526
questions
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 ...
Jacky.Chueh
-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
--...
- 1
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 ...
- 77
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 ...
- 319
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 ...
- 319
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 ...
- 319
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 ...
- 319
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 ...
- 319
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 ...
- 33
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 ...
- 123
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. ...
- 319
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 ...
- 319
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 ...
- 151
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 ...
- 47
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.
...
- 319
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 ...
- 77
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 ...
- 77
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 ...
- 319
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 ...
- 319
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 ...
- 77
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 ...
- 13
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 ...
- 319
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 ...
- 319
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 ...
- 33
-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
...
- 11
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