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)

0
votes
0answers
62 views

Trying to find a MIN() workaround for an indexed view

I'm trying to speed up a SQL Server query using indexed views and I have one slow element which I think is due to the use of GUIDs for the particular comparison column. The GUID can be mapped to an ID ...
3
votes
1answer
60 views

Inconsistent Execution Plan for Stored Procedure

We have a business-critical stored procedure that normally runs daily at 2am from a scheduled job (in an SSIS package) on the production DB server. The same procedure/package is called from a second ...
19
votes
2answers
1k views

When are computed columns computed?

When are the values for computed columns determined? When the value is retrieved? When the value is changed? Some other time? I'm guessing this is a novice question since I'm not finding anything ...
3
votes
0answers
21 views

Chance of skipping extended events when using an offset for the file target

I am exploring extended events and came across a question I can't solve using the Microsoft documentation : When reading events from the file target we can specify and initial offset so we can read ...
0
votes
1answer
17 views

sys.dm_exec_procedure_stats reset [duplicate]

I want to get stats from the sys.dm_exec_procedure_stats view. When I check the records from months ago, I see that after a point of time its stats reset daily, but there have been no server resets. ...
4
votes
2answers
367 views

Outer self join filters vs subqueries

I have a table storing information about tickets. There is one record when ticket is sold and another when ticket is used. There is a column called TransType which is set to either 'sold' or 'used' ...
-4
votes
0answers
30 views

what prerequisite are required to become a DBA?not overview but to the depth of course [duplicate]

what prerequisite are required to become a DBA?not overview but to the depth of course if any online link is there to learn or course to become DBA then let me know
6
votes
2answers
157 views

Does table size matter?

I have a table like this (with more columns though): CREATE TABLE [dbo].[MyTable]( [SnapKey] [int] NOT NULL, [SnapDt] [smalldatetime] NOT NULL, [Company] [varchar](4) NOT NULL, ...
3
votes
0answers
48 views

LOB_DATA, slow table scans and some I/O questions

I have a rather big table with one of the columns being an XML data with an average size of XML entry being ~15 kilobytes. All other columns are regular ints, bigints, GUIDs etc. To have some concrete ...
1
vote
2answers
13 views

Is there a list what datatype corresponds to the system_type_id in Sys.columns

Is there a list what datatype corresponds to the system_type_id in Sys.columns. I am looking for an easy way to examine the columns we have in the database and i would like to avoid the ...
1
vote
0answers
35 views

Is there ANY risk in granting a superuser CREATE and ALTER procedure permissions in a specific schema?

I have a developer who has created a set of procedures/functions/views he uses to perform configuration analysis in a large application (provides much higher productivity than using the formal ...
11
votes
1answer
109 views
+100

Keeping (Partial) Backups Small when using SQL Server FILESTREAM

I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the ...
0
votes
1answer
22 views

OLEDB provider for linked server reported change of schema version error

I am trying to run a report but its giving this message: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'ParamYear'. ...
1
vote
2answers
86 views

sys.dm_sql_referencing_entities not returning results

No doubt this is syntax or typo...but when I query sys.dm_sql_referencing_entities for a parent table, I get zero results. No error, just no results. USE [source]; GO SELECT * FROM ...
0
votes
0answers
16 views

Backup and restore specific filegroups [duplicate]

I've seen this Stack Overflow question: Backup and restore SQL Server database filegroup. However, it didn't do what I needed. I have a database with three filegroups: primary, reporting, and ...
-1
votes
0answers
34 views

normalize table data

Maybe my question is duplicate or asked many times, but it's rare, and I really need this. I have a table, which contents looks like: Id points 3 20 3 42 4 40 4 60 6 20 6 30 5 10 ...
2
votes
1answer
117 views

What is the best way to import this non-standard text file into SQL Server?

I have a text file that is generated daily. It is a fixed width format with one frustrating exception. The third column (NAME) has a variable width. The column is always exactly as long as the longest ...
0
votes
1answer
66 views

Identity column jump in sql server restart

I had many research on identity column jump in sql server restart. I know the reason why this happen as it explained here. I know the solution how to prevent this using either manually perform ...
3
votes
3answers
121 views

SQL Logins in db_owner role on database cannot create stored procedures

I started locking down permissions in our test environment by removing sysadmin permission from generic accounts. The first account, used by several developers, is a local SQL login. I gave the ...
0
votes
0answers
24 views

Upgrade SQL Server 2008 R2 [closed]

I have SQL Server 2008 R2 with SSMS version 10.50.1600.1. Can I upgrade this version to SQL Sql server 2008 R2 with SSMS version 10.50.4000.0? While restoring the database there comes an error. So ...
0
votes
0answers
10 views

End user configurable mailers based on queries [closed]

We are required to send out a variety of mailers containing database rows (basically work items assigned to users) Ideally I would want to buy a solution that gives as much control as possible to ...
0
votes
0answers
27 views

Sql Server Stored Procedure calling table-valued function - Performance

I'm using Sql Server 2012 and I have a stored procedure that does about 15 left joins and one of those joins is a CROSS APPLY to a table-valued function. The first 14 left joins are not gonna restrict ...
4
votes
2answers
92 views

The index columns in sys.index_columns different from what are in the index

When I run the following I get 3 indexed columns: SELECT b.name, c.name FROM sys.index_columns a INNER JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id INNER JOIN ...
0
votes
1answer
48 views

Why can't I update or delete certain records in SQL Server?

I am having a weird issue in SQL Server 2014. I cannot update or delete certain rows from a table. I can insert rows fine, and select them, but when I try to update or delete a row, I get a timeout ...
0
votes
1answer
49 views

Duplicates when checking for two different column values based on a key that is not unique in table

I want to check to see if two orders were placed for one person within an hour of one another. Example data PERSONID | proc_code | TIME 123 | 1 | 4/25/2016 00:01:00 123 | 2 | 4/25/2016 00:01:00 123 ...
0
votes
0answers
12 views

SQL Server - DENY new server role to view any logins, server roles, or system databases

Wondering if the following is possible, to satisfy a requirement for login to be able to connect to an instance via SSMS but only be able to select from a view in a particular user database, without ...
0
votes
1answer
30 views

Sql service account password change for environment on Availability groups 2012

My current environment is on Availability groups sql 2012 with 1 Synchronous and 1 Asynchronous replica. Soon we need to change the sql service account passwords. Can any one suggest to me do I need ...
7
votes
2answers
263 views

SQL Server not using all memory

I have SQL Server 2014 with max memory set to 6GB (physical memory is 8GB). The Target Server Memory is sometimes 6GB and then drops back to Total Server Memory (approx 5.3GB, never reaches 6GB). I ...
0
votes
0answers
24 views

Missing database on a Linked Server

I have a MS SQL 2014 (web ed) that's on the Azure cloud (Server A). I have linked this server to an onsite SQL 2014 server (server B). On server A, I can see MOST of server B's databases but not all. ...
0
votes
0answers
28 views

best practice to remove leading zeros when writing data to a destination

I have a query like in my other question DBA Question with Source Code of Select statement And i want to remove the leading zeros of the customer Number (KUNDE) with the answer of this question ...
1
vote
2answers
39 views

I'm confused about High Availability/Always On

is High Availability something to describe Log Shipping and Always On ( replica )? is Always on something to describe Log Shipping and High Availability? I'm studying to get my certification 40-762 ...
0
votes
0answers
49 views

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

Good morning, I have a select statement that separate wheel of my application, or is a threading which seeks in the bank and send emails. But a few days it is me that is generating the error in ...
0
votes
0answers
15 views

Connect MSSQL DB with SSAS

I am trying to build a BI system using SQL Server 2008. I have installed and created the database. I have also installed SSAS but I can't seam to find a way to connect my database as a Data Source ...
1
vote
0answers
41 views

Having 2 identities in a table

I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly. I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', ...
3
votes
4answers
103 views

Is it enough to have only full backup and transaction log backup only for a good backup plan?

If Full backup is scheduled for every Sunday at 12 pm and differential back up is scheduled for for every 6 hours and transaction log backup is scheduled for every 15 minutes. My problem is what is ...
5
votes
3answers
141 views

Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4. After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad ...
1
vote
0answers
12 views

Transactional replication using articles filters and delete operations

The problem with transactional replication using articles filters and delete operations. I have a problem with my transactional replication when I use articles filters and delete operation. When I ...
0
votes
2answers
53 views

How can I return top 10 rows from each state [duplicate]

How can I return the top 10 rows for each category? Say I have a table named xyz. It has a column called state, in the political unit sense of the word. I want the top 10 rows from each state. Do I ...
1
vote
0answers
20 views

Solr with SQL Server or NoSQL

I am working on a POC of using Solr with SQL Server. I have a very complex data model with SQL server which required a bunch of joining and scalar functions to strip some mark up and other stuff. ...
0
votes
0answers
19 views

my log files are 65gig with 1% free space [duplicate]

my log files are 65gig with 1% free space so shrinking the file can't shrink any smaller. what do I do? should I delete it to have it create a new one?
1
vote
1answer
89 views

Most efficient way of finding rows in a table that don't exist in another — SQL Server

What is the best method of finding rows in table A that don't exist in table B based on unique key of the tables, when both tables are huge (A over 300 million rows, B over 500 million rows) and the ...
1
vote
0answers
27 views

Problem inserting into table from trace file consumes all space in tempdb

I'm working with SQL Server 2008 SP3 Enterprise Edition (64-bit) I'm trying to import a trace file of about 3.5 MB (4667 rows) into a table as following: INSERT INTO DBTRACE with (rowlock) SELECT * ...
3
votes
2answers
98 views

How to rewrite a query with MAX in WHERE clause

I have a query with a max in the where clause, which is slow. select count(*) from TableName tbl1 where tbl1.id = ( select max(tbl2.id) from TableName tbl2 ...
11
votes
1answer
301 views

CASE expression returns wrong value when using CEILING

I've run into an issue where a CASE expression does not return what I expect. As a test, I added a decimal variable and ran the same CASE expression against it and it works fine, returning the ...
1
vote
1answer
43 views

Limit the number of records depends on the part of primary key

I created database for football matches. I have 2 tables with a many-to-many relationship, so I also created a junction table: Referee CREATE TABLE referee ( id_referee INT IDENTITY PRIMARY KEY, ...
0
votes
1answer
24 views

Powershell invoke sqlcmd

How is it possible to execute a query as a applicationintent=readonly using powershell? I have an cluster alwayson environment. I want to call a procedure with invoke sqlcmd to the readonly database ...
3
votes
1answer
61 views

Finding hash match aggregate

After reading following blogs, I understand that hash match aggregate causes blocking. Using appropriate indexes it can be made as stream aggregate. Blocking/non-blocking aggregate operators The ...
0
votes
1answer
26 views

Sql server covering index and join

I understand that covering index will speed up read operation if all the columns in select list are covered in the index. But what if I select and filter on columns in other tables as well? Would ...
1
vote
2answers
16 views

Setting up a listener for sql server alwayson availability groups without computer object rights in the AD

Is there a way to create a sql server alwayson listener in an active directory (AD) environment where I don’t have create computer object rights? The AD admin created a computername for me. Is there a ...
0
votes
1answer
37 views

Copying data from two tables into one using INSERT in SQL 2012

I've been trying to copy data from two tables into one using the INSERT statement. The tables are from the same database and have the same numeric types and I only need to copy one column from each ...