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

learn more… | top users | synonyms

-1
votes
1answer
56 views

Find day values in bigint date?

This query is working good. select DATENAME(DW,getdate()) AS [Weekday] My Problem is suppose I give input values like select DATENAME(DW,InvoiceDate) AS [Weekday] from store; is not working in ...
2
votes
1answer
134 views

How to improve this queries performance by indexing properly?

Hi here i have a query and i need to improve the performance of this query.Can anyone suggest me like how to convert the clustered index scan to index seek. DECLARE @now DateTime; DECLARE ...
2
votes
2answers
138 views

How do I safely drop a list of tables, when i have the table names listed in a column?

Assuming I have a database column containing table names, how do I safely delete all of the tables it lists? The tables themselves are fairly simple - they may have indeces, but definitely don't ...
3
votes
4answers
204 views

How to find duration in a dataset

I have a dataset of the following structure: Target, PollTime, Value ------------------------ 1. A, 2013-12-13 13:31:44.000, 15.00 2. A, 2013-12-13 13:32:44.000, 16.00 3. A, 2013-12-13 13:33:44.000, ...
2
votes
1answer
71 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
11
votes
3answers
543 views

Get streak count and streak type from win-loss-tie data

I made a SQL Fiddle for this question if that makes things easier for anyone. I have a fantasy sports database of sorts and what I'm trying to figure out is how to come up with "current streak" data ...
0
votes
1answer
78 views

SQL Select query to include each row a number of times

This is probably easy to do (I hope), but my experiments with OUTER JOIN and CROSS JOIN etc do not give the results I am after, so here's the question: I need to do do a projection on fields in a ...
0
votes
1answer
83 views

Keyword Search a single SQL Server table for multiple search terms and create a new table from results?

I am not a database administrator. Most of my experience is with MySQL. However I think what I want to do is in the realm of possibility. I want to text query a single large table in a SQL Server ...
0
votes
1answer
85 views

How to create a versioning DDL/DML change script for SQL Server?

I have an application which has a database, and some versions of the application introduce schema changes. To make installation easier (?), I figured I want to create a meta table which contains the ...
9
votes
1answer
747 views

How can I make a query take a specific amount of time?

I am testing a monitoring application against a SQL Server 2000 instance. How can I write a T-SQL query that takes an amount of time that I specify? For example, most scripting languages have ...
5
votes
1answer
316 views

Optimize where clause using dates

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses: AND (DATEADD(DAY , ISNULL(a.[due_days], 30) + 30 , [dbo].[CalcDate]([type], ...
0
votes
1answer
44 views

Is a single batch request equivalent to a single tsql request?

I'm looking into some performance metrics and every recommendation includes monitoring Batch Requests/sec. But, maybe a silly question, does a singe batch request correspond to exactly one tsql ...
5
votes
2answers
149 views

'+' operator with one operand!

How does '+' operator behave in following statement? select + 'taco'; --Result is 'taco' Is it doing string concatenation with first string blank ('' + 'taco'), or does it mean something else?
3
votes
1answer
58 views

Table A triggers an insert onto Table B, Table B triggers an insert onto Table A, what happens?

Assume I have two tables, table A and table B. Table A has an insert trigger that inserts a record into Table B. Table B has an insert trigger that inserts a record into Table A. What would happen in ...
1
vote
2answers
116 views

Generic script for stored procedure rollback

We're trying to switch to feature-driven development in my organization. We're planning on using a new branch on our source control for each feature and merge when development is complete. I think it ...
1
vote
2answers
94 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 ...
2
votes
2answers
195 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
148 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
85 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
69 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
109 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
vote
1answer
59 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
65 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
104 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
226 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
62 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
96 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
268 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
51 views

Take query results and insert into new table [closed]

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
137 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
1k 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
56 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
94 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
54 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
75 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
84 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
97 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
63 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
59 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
84 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
170 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
28 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
102 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
131 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
58 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. ...
3
votes
1answer
141 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
64 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
116 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
164 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
286 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 ...