5
votes
1answer
71 views

SMS Suggesting covering all columns in an index?

I'm still a bit newer to indexes and database works, so I may very well be missing something obvious. SMS suggested adding a covered index to one of my tables that Indexes on the column I believe ...
1
vote
1answer
53 views

How do I solidify my SQL Server / T-SQL Knowledge? [closed]

I am a C# developer and not a database admin, but I have been using SQL Server for application developnment for over 4 years now and was really interested in getting a more complete understanding, and ...
1
vote
1answer
66 views

Return every instance of a string contains search

I have this sample table and data CREATE TABLE [dbo].[SampleTable]( [Id] [int] IDENTITY(1,1) NOT NULL, [TextColumn] [nvarchar](1000) NOT NULL, CONSTRAINT [PK_SampleTable] PRIMARY KEY ...
-1
votes
0answers
56 views

CTE Hanging with Larger Data Set

I have a CTE that creates a hierarchy and looks for circular references. The table it searches lookes like this: Parent_IndicatorID | Child_IndicatorID | SortOrder My CTE is the following: ;with ...
1
vote
2answers
49 views

SQL Server select columns which contain data

I'm working for a client to import data out of a generic off the shelf application database. If you can imagine a database that is designed by software developers that have no concept of database ...
3
votes
1answer
176 views

How do I delete unique rows from a table with duplicate keys?

I have the following data: road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ...
0
votes
1answer
32 views

Take query results and insert into new table

So I have the following query: DECLARE @tbl TABLE(value int) *--The same function but with the number of the random numbers ;WITH Nbrs (n) AS ( SELECT 1 UNION ALL SELECT 1 + n FROM ...
3
votes
1answer
82 views

Inserting a 30 million row table from a linked server

I have a large table that needs to be updated on a nightly basis from a linked server. Initially I had simply truncated the table and inserted all of the records but this was taking too long. I'm ...
0
votes
4answers
87 views

SQL Where Clause Efficiency

Say I have a SQL statement: SELECT * FROM MyTable WHERE KeyColumn1 = 1 OR KeyColumn1 = 2 OR KeyColumn1 =3 OR ... KeyColumn1 = 10 And another: SELECT * FROM MyTable ...
0
votes
1answer
36 views

Nightly reload of tables from a linked server

I have a number of tables that need to be reloaded nightly from a linked server. My current method of doing this is to create a nightly job to pull the data using something like the following: ...
0
votes
1answer
26 views

SQL Server sp_addrolemember in stored procedure for different database

I have a stored procedure to restore the latest backup of a prod database to an acceptance database. Once it's restored I need to grant a user read access to the database. Use statements are not ...
1
vote
1answer
59 views

How to transform this result set?

I have the result set above that I stuffing into a table variable @ApptTemp, and trying to transform it into something similar to the one below. Note that of the 4 rows that have a description of ...
2
votes
3answers
49 views

Query databases on different servers

How can I create a query that will find all the databases on different servers? I have four servers, PRDSVR, UATSVR, DEVSVR, TRNSVR; with either copies of the same database on each server or ...
0
votes
0answers
63 views

SQL Server and Entity Framework

We are currently experiencing a problem with Entity Framework which generates a sp_executesql statement. This statement is below. The statement runs fine in SQL Server however through the front end ...
4
votes
2answers
141 views

Update Statement takes forever to complete [closed]

I have table around 1250000 records, and 80 columns Table Structure CREATE TABLE [dbo].[Table] ( [Column1] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL, [Column2] [int] NULL, [Column3] [int] ...
0
votes
1answer
41 views

How to retrieve whether database has any tables?

Knowing to use SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' to query for a list of tables, I'm currently finding no way to retrieve a list of non-system tables only. I.e. ...
2
votes
1answer
39 views

SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users

Related Question: SQL Server script to delete accounts no longer in Active Directory Whilst the above question points out the rather handy sp_validatelogins procedure, i'd like to extend this ...
0
votes
0answers
42 views

incorrect value from SUM Function

Has anyone seen this before? When doing a simple sum on a table: select sum(column) from table where column is not null I'm receiving the data 1558.39999999999 However, investigation of the ...
3
votes
1answer
89 views

Frequent Delete & Insert, impact on performance

In my application, we have very few updates. Most of the data gets deleted and inserted. My question is, how this will impact the performance? We are using SQL Server 2012. Table statistics (will ...
0
votes
2answers
106 views

why stored procedure performance is very high compared to plain select query

I have a table with more than 2 million rows, with needed indexes. When I run a simple query it takes so much time so I decide to stop executing it; while when I wrap the query in a stored procedure, ...
2
votes
2answers
106 views

Reduce DB size - SQL Server, SSMS

One of my databases was growing in size a few gigs every day. That was very unusual since it is still in development phase. After small research i discovered that the Even Log table was almost the ...
11
votes
1answer
506 views

GO After every T-SQL statement

What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
0
votes
2answers
54 views

Which one is better way to insert data? [closed]

I have two cases case create proc createEmployee @firstName varchar(255), @lastName varchar(255) = null as insert into Employee_Tbl values (@firstName, @lastName) case create proc ...
2
votes
1answer
78 views

Avoiding Deadlocks

We currently have a trigger-based system in place that does the following: When records in certain tables are modified, a trigger fires that makes a field NULL in a varying number of records in ...
0
votes
2answers
115 views

Store output of sp_who2 in a table

I am looking for something like this, where I could store the output of sp_who2 in a table, without having to first create the table: create table test as exec sp_who2
1
vote
1answer
79 views

Performance of dynamic SQL Server reporting queries?

I have to build an application that allows the end user to select what columns they want in their report. For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns ...
1
vote
1answer
67 views

How to dynamically generate rows based on value in a column

I'm attempting to write a query to denormalize some data for integrating to our data warehouse, specifically around generating some time slots. In my source system, I have table that has a start time ...
0
votes
1answer
63 views

semaphore in SQL Server

I wrote a stored procedure in sql to take a number from table. it has lots on records which has only column. whenever a call it , is should take the first record and return it after that delete the ...
1
vote
0answers
46 views

SQL command in stored procedure continue on error

To perform Checkdb on all database of server i am using a stored procedure which loops over a view which contains list of databases in server and the stored procedure performs checkdb on all databases ...
0
votes
1answer
60 views

SQL Case Sensitive Query

I have two databases which return different results based on the case of the query. On the first database I run the query FROM [Temp].[Team].[CLASS] WHERE [TABLE_NAME] like '%Link%' and on ...
1
vote
1answer
34 views

Case when missing line logic

I have a case when statement in a extract query which doesn't seem to be returning what i'm expecting DECLARE @Val1 int, @Val2 int,@Val3 int, @Val4 int, @Val5 int SELECT @Val1 = 5 , @Val2= 2,@Val3= ...
5
votes
1answer
145 views

How to avoid a race condition with cross db proc calls? and other concerns

I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a ...
3
votes
2answers
105 views

Multicolumn statistics on large table yielding poor estimations

I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the ...
3
votes
1answer
73 views

Mapping of multiple server logins to one database user

This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology). Is it possible to log multiple ...
5
votes
1answer
396 views

Use of FROM keyword on DELETE statement

The BOL and many other sources point out: FROM Is an optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited. I'm used to ...
2
votes
2answers
97 views

View scripts ran by a specific user?

I read in a stackoverflow post that you can restore scripts from the past 24 hours by using this query: Use <database> SELECT execquery.last_execution_time AS [Date Time], execsql.text AS ...
3
votes
2answers
142 views

T-SQL - Enforce Atomic Operations x Locks

I have a database model that works similar to a banking account (one table for operations, and a trigger to update the balance). I'm currently using SQL Server 2008 R2. Table OPERATIONS: VL_CREDIT ...
1
vote
1answer
72 views

TSQL Query - Efficient?

I want to output data that will display various results depending on Type and/or Quantity values. Each query calls the same table and returns the same columns. I was thinking of creating a UNION on ...
3
votes
1answer
78 views

How to replace “set rowcount”

Currently on SQL Server 2008/R2. I just read here that "set rowcount" is being deprecated in some post 2012 release: http://technet.microsoft.com/en-us/library/ms188774.aspx We have some legacy code ...
2
votes
1answer
51 views

Getting each status change in a table

I have a table that holds status changes per item. The item can go in and out of a status multiple times. ItemTable (ItemId int, Status varchar(50), DateOfChange date) I need to pull the date of ...
2
votes
1answer
115 views

SQL Server TDE stuck encryption state 4

I'm trying to create a robust script that runs backups, backs up current certificate, creates a new certificate, backs up new certificate and regenerates database encryption keys with the new ...
2
votes
1answer
60 views

Get identity value of all rows inserted

I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement? Example insert ...
4
votes
2answers
97 views

Why can't I view the SQL Server database created by a web application?

I have an ASP.NET MVC web application deployed to IIS on a Windows Server 2012 machine. When the application first starts, it will create the database and tables it needs if necessary through Entity ...
0
votes
1answer
48 views

Identity Column Behaviour After Replication

Say we have two Servers: Server A and Server B. Data is being replicated (vanilla Transactional Replication) from A to B. For one of the Tables, we have data as follows: Server A - Server B ...
2
votes
1answer
109 views

Performance issue with TOP (1) on a nullable column

I need help to identify the reason for a performance issue. Actually following query sits in a scalar-valued-function but that is not the reason for the issue since it needs the same time as query: ...
2
votes
1answer
53 views

Getting Information on active Restore in TSQL / SQL Server Management Studio

On my server a database is currently restoring according to the SQL Server Management Studio In the history SELECT * FROM MSDB..RestoreHistory WITH (nolock) WHERE destination_database_name = 'myDB' ...
0
votes
1answer
115 views

SQL Server Full-Text Search: combining proximity term and thesaurus

Scenario I am developing a full-text search over a products table. The full-text indexed field of th table contains a brief description of the product, the name of the category and the product code. ...
5
votes
2answers
111 views

In the SQL Server RESTORE statement, in what time zone is the STOPAT argument?

SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value? Is it UTC? Is it local for the server the backup was taken? Is it local for ...
3
votes
4answers
185 views

How to rollback changes made to the database by a specific user?

In mssql, is it possible to rollback changes made by a specific user to the database? For example if that user ran an update query and did not write begin transaction before, is it possible to revert ...
2
votes
1answer
106 views

Should I run a delete first before I execute index rebuild to a large table about 15 Million records

I need help for a better approach on how to approach this. Currently we have a table that is huge about 15 million records. We plan to create an index on this table however due to large size, we ...