Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

learn more… | top users | synonyms

0
votes
0answers
9 views

Dynamically REVOKE CONNECT FROM guest in SQL Server 2005

I wrote this simplistic script (I'm not very familiar with TSQL and dynamic SQL) to revoke all connections from guest accounts: DECLARE @database_id int, @database_name nvarchar(100); DECLARE ...
1
vote
2answers
76 views

What is the best way to get all data for a date range, plus the last event just before the range?

For this question assume the following: There is a table of events [Event] There is a table of employees [Employee] There is a table that relates the two [EventEmployee] (EventID + EmployeeID) Each ...
0
votes
0answers
72 views

Row_Number() not working with OVER PARTITION BY

I have a query that I am "rolling up values". Lets say I have two rows that are the "same", save for one field: [CNumber] [PNumber] [date] [FullName] [Type] [P] [S] ...
5
votes
1answer
74 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
69 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
1answer
46 views

Using CHECKSUM on where clause

I'm going through a long stored procedure and I found this, as part of a long query: SELECT .... FROM --- WHERE condition AND CHECKSUM(r.roomprovider_id) <> ...
0
votes
1answer
42 views

Name for SELECT equivalent of UPSERT

UPSERT means INSERT and\or UPDATE, is there a standard name for a sproc that will INSERT if necessary and then SELECT? e.g. I want to get the IS of a row that matches fields and give me the ID, if ...
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
177 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
33 views

Find actual data size from a query?

Is it possible to find the size of data contained in a particular query? e.g. I can use the following to find the number of rows per date: select mydatecolumn, count(*) from MyTable where ...
0
votes
0answers
84 views

How can I get rid of the “No Join Predicate” warning

I have this fairly straight-forward snippet of a query: DECLARE @ClientGUID numeric(16) = 9000011916900200; SELECT ch.GUID, c.LastName, c.FirstName, mrn.ClientIDCode AS MRN FROM Client c INNER ...
1
vote
2answers
58 views

SQL Query with optional WHERE clause parameter for date range?

I have a procedure that takes, among other things, start and end date parameters. These two parameters are optional inputs and default to NULL. What I would like to do is modify one of the current ...
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
83 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 ...
1
vote
1answer
122 views

stored procedure with multiple statements does not execute all statements when run from job

** see updates below I have a stored procedure (SQL Server 2012) that deletes old records from a table, then inserts new records from a view to synchronize the table with the view. When I execute the ...
1
vote
1answer
37 views

Difference between sql agent account logged in as NTLM and UPN?

Can someone please tell the difference between a sql agent account logged in as NTLM and UPN? What is the difference if the sql agent account is logged in as: domain\username and username@domain.com? ...
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
55 views

Comparing INT and NVARCHAR

T-SQL newbie here. I am sure you guys can help me with a little problem I'm having. Here's my situation: I want to do a select from a certain table. And with that select I want to display the count ...
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 ...
0
votes
4answers
92 views

T-SQL: Complete Database in one table

at a relational data model every logical relation is a single table with values and so on. I know someone who is this too much work and he wants to create only one table which contains all. This could ...
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
22 views

Result set to eventvwr

I'm looking for a solution to the following case: I have a trace(only captures queries above 5000ms duration, which is about 20 per hour) running in MSSQL which I can query by using: SELECT ...
3
votes
1answer
47 views

SQL Server 2008 R2: query RECENT locks, NOT current locks

The database had average lock wait time of 30 seconds for a period of about a minute. I didn't run my lock query in time to see what was happening. I can't seem to find anything that shows recent ...
3
votes
1answer
46 views

Timestamp on SSIS update on tsql

I have a couple of SSIS packages that copy the content of several tables to another db. One of the columns updated in the process is a timestamp datatype. From what I read this datatype can't be ...
0
votes
1answer
42 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
91 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
107 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
107 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
507 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
117 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
71 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
47 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
62 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
148 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 ...
1
vote
2answers
130 views

Expose temp tables to SSRS in stored procedure

Problem: Compare data of a given date range to the same data of up to 3 previous years, and display the tabular data and a graph in an SSRS report. However currently, my stored procedure that I have ...