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

learn more… | top users | synonyms (1)

1
vote
2answers
21 views

SSMS clean output window with sql instruction

How can i clean output window in SSMS using t-sql syntax? Is it possible ? Do you know any workarounds ?
1
vote
2answers
26 views

How can I perform a full-text CONTAINS call across a list of phrases?

I have a list of words/phrases in a table phrases. I have another table with a full-text catalog indexing the description column. I want to populate a table results with a record for each phrase, and ...
0
votes
1answer
27 views

OVER and PARTITION BY for db size change

I have the following column below. I am not too familiar with the OVER function. Question is, will the column usage_change_mb be the daily change (from daily snapshots) or an average change of all the ...
0
votes
0answers
14 views

t-sql errors during OPENROWSET(BULK… possibly related to terminators

I have a CSV file and I am trying to use OPENROWSET(BULK... on it with a non-xml format file. I am receiving errors which I have been unable to overcome. They lead me to believe that my field or row ...
0
votes
1answer
26 views

Querying SQL Server extended properties for tables/views and its columns

I need to get metadata information about tables/views and its columns in SQL Server. When I run the query separatelly for tables/views (sys.tables) or columns (sys.columns) I can get the extended ...
2
votes
1answer
27 views

How to migrate a huge application to CONCAT_NULL_YIELDS_NULL ON

Finally, none of our customers are using SQL Server 2005 anymore, so we can drop support for it and start using all those awesome "new" SQL Server 2008 features (indexed views, filtered indexes, query ...
1
vote
1answer
111 views

Shrink database or DB files not reducing size even after dropping biggest table

Our MDF file was originally 550GB in size (log is just 10MB), and the biggest table we had [HourlyCounters] was 140 million rows with 580 columns (varchar[50]). It's a table with a very inefficient ...
0
votes
0answers
47 views

Why the same execution plan causes LOB reads in some cases?

I have a spatial index and the following query: SELECT DS2.[ID] ,DS2.[InstanceID] ,DS2.[Location].STDistance(@Location) FROM [dbo].[DataSource01] DS1 INNER JOIN [dbo].[DataSource02] DS2 ...
1
vote
2answers
47 views

Сopy rows from one table to another and replace duplicates in the process

I have an SSIS job that loads daily data from CSV files into a raw staging table in a SQL Server database (technically is an Azure SQL database). Then after that, a store procedure is executed to copy ...
0
votes
0answers
48 views

Cannot execute code connecting to SQL Server 2008 R2 in SQL Server 2014 SSMS

I am not able to connect and execute a code in SQL Server 2008 R2 while connected to SQL Server 2014 SSMS. SQL Server 2008 R2 is on Windows 2008 R2 64 Bit SP2 (Stand Alone) SQL Server 2014 is on ...
2
votes
1answer
46 views

Make index work on join to table variable

I a table with several columns, but the most importants are: DocumentId(bigint), StatusId(int) and ReceiveDateTime(datetimeoffset(3)). CREATE TABLE [DocumentInfo]( [DocumentId] [bigint] NOT NULL, ...
0
votes
2answers
34 views

Need to create a “summary grid” query?

I have multiple transaction records in table A like this: TXN# DATE CUSTOMER 1001 10/05/2015 Cust 1 1002 02/03/2016 Cust 2 1003 02/06/2016 Cust 1 1004 03/25/2016 Cust 3 1005 03/26/...
0
votes
1answer
29 views

Select 1 Row Per Day For Each Database

I have a table with over 60 million rows. Snapshots taken at multiple times a day for all databases across all instances. I am trying to reduce the rows in my select by only returning 1 snapshot per ...
0
votes
0answers
29 views

concatenate/using string doesn't work while query for clearing table [closed]

My boss required me to write a query that clear tables... and i searched and found some code, put them together, at the end, for simplify and remove any mistake, put the code in variables... but it ...
3
votes
1answer
117 views

Trigger/Event that fires when a Transaction finishes

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.) The problem I have is that these tables ...
0
votes
1answer
45 views

Query to Find Out How Many Days Until Database Is Full

Using my central MDW database I am trying to make a query that will be able to calculate the number of days until all my databases free space will be full. Currently the query I have is overly ...
1
vote
2answers
143 views

PERCENT_RANK does not distribute over 100

I'm trying to find out the position of each given record of a query in 0-100 scale. I use PERCENT_RANK ranking function this way: select Term, Frequency, percent_rank() over (order by Frequency desc) ...
1
vote
6answers
434 views

Combining (joining) tables with no key

I have table B and I have Table A. Table B contains records with dates I need to pull. However, I only want to pull them if: They do not exist at all in table A They exist in table A, but only when ...
5
votes
6answers
126 views

Alternative to xp_cmdshell for emailing a report as a CSV file

I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible. I know that xp_cmdshell itself poses risks, even with a proxy ...
0
votes
2answers
47 views

Need to fetch latest record always and ignore if there is no latest data [closed]

Basically i want to send mail whenever there is a new record in this table throughout the day with list of all records got created. I have a table with one date column. i have to always fetch the ...
2
votes
1answer
188 views

Determe the command type in a DDL trigger / get names and datatypes of new columns

Given strings corresponding to DDL commands, how can I determine which DDL command (column addition, constraint addition etc.) it precisely is? I have a DDL trigger on a database that will be fired ...
0
votes
1answer
25 views

sp_recompile does not recompile inline TVF. What alternatives are there other than using an ALTER statement?

In the following, I would expect the first SELECT of the new column [Bit01] to be successful however it is not. Is there any way of avoiding the use of the ALTER statement to get it to work? I have ...
3
votes
2answers
8k views

Nonclustered Index still doing index scan

I have this simple query. SELECT a.RecordID SubmissionID, a.[DateSubmitted], a.[FinalAction], a.[FinalActionReason], a.[FinalActionDate], b.GradeSubmissionListID, b.[Action], b....
3
votes
1answer
101 views

Object_name(object_id) returning incorrect results

We are trying to exclude stored procedures which have name like '%backup%' or %WI%'.. Here is the total query i used.. ;WITH CTE AS (select object_name(object_id) as procname from sys.sql_modules ...
1
vote
1answer
57 views

How to set rank per group with a SQL UPDATE statement?

Please first refer to the table structure: Now you could see there is one unique constraint including manager_code, archive_year and archive_day_of_year. I need to rank the managers for every ...
3
votes
1answer
310 views

SQL query failing because of commented part of T-SQL query

I am executing below query but I am facing issue. Note :SQL 2008 R2 and SQL 2014 running with two diff serv accounts on Win 2008 R2 and Win 2012 R2. TCP Ports, Firewall ports are all enabled along ...
3
votes
2answers
66 views

How to escape XML characters in TSQL before converting to XML?

I have XML structure which is input param for my stored procedure. It contains element with & (which is escaped in XML). When I extract that element to VARCHAR I receive & which is not valid ...
0
votes
0answers
25 views

Using a Window Function to Replace a Subquery

I am having an issue with a query that I am trying to change to take advantage of the Windows Functions - mainly as a learning tool. The working query is below: SELECT A.county_name AS [CountyName] ...
0
votes
1answer
77 views

Dynamic length obfuscation method in T-SQL

I would like to obfuscate (scramble) sensitive data from a SQL Server database, but in the way which will provide: irreversibility (the plaintext can't be derived from the obfuscated data), ...
0
votes
2answers
47 views

SQL Script to Exclude Weekends and Holidays

I have a query where I need to add 2 days to the date variable based on a condition. However, this date variable needs to skip weekends and holidays. I have a table that stores holidays. How do I ...
1
vote
1answer
46 views

sp_help with different object types with the same name

I have a table with the same name as a user-defined data type (UDDT), and when I use sp_help, only the table definition is shown. Is there a way to use sp_help or an equivalent to show the ...
0
votes
1answer
115 views

How to Insert all PDF files from a folder into a varbinary(max) column in SQL using a Query

I am trying to create a query script for SQL Server that will convert pdf file into varbinary data and store it into the SQL Table column which is varbinary(max). I also have to make sure it loops so ...
1
vote
1answer
284 views

UPLOCK to avoid multiple reads

Given the following table structure... Orders ====== OrderId int PK OrderEvent =========== OrderEventId int PK OrderId int FK EventTypeId int FK I've got multiple concurrent processes SELECT-ing ...
3
votes
1answer
118 views

Find the number of rows in a table from metadata

I want to write a single select statement to determine the number of rows in a set of tables in Sybase IQ. In SAS, I write this as CREATE TABLE dsnobs as SELECT libname,memname,nlobs, datetime() ...
2
votes
1answer
796 views

CLR SQL vs T-SQL for queries

I am aware that CLR stored-procedures is more efficient in procedural code and T-SQL is better for queries - however I am having a hard time deciding what to use for my specific needs, and I would ...
7
votes
2answers
6k views

Difference between EXEC() and sp_executesql with no parameters?

I just learned about the new sp_executesql stored procedure. I like the fact that there's a way to execute parameterized code from within SQL Server. But, I'm wondering what the difference is ...
0
votes
0answers
44 views

Simplifying this NULL check [duplicate]

Is there a way to simplify this check? I would like to compare if two columns are equal and that includes if one is NULL and the other has a value (which would mean they're considered different). (A.[...
8
votes
2answers
396 views

Is there a way to make this select retrieve the same results with a single seek?

Is it possible to retrieve the same data as the following with a single seek or scan, either by modifying the query or influencing the optimizer's strategy? Code and schema similar to this are ...
4
votes
2answers
88 views

SQL Server- Limit to Concurrent table queries?

In SQL Server, or RDBMS systems in general, is there a limit to the number of concurrent queries that can be made against a table? How is it that N number of users can access the same physical data ...
3
votes
2answers
43 views

Insert if something changed other than specific column

I have a table with many columns but my snippet is simplified. I have the trigger shown below but how can I have it so that nothing is inserted if only the created_at column has changed? ALTER ...
0
votes
1answer
274 views

Breaking up long running query into batches runs fine for a while and then produces fatal error

I'm using a variation of this post which details using "careful batching" to delete records in batches from a table that contains 38MM records. The script below runs for a while and shows results ...
0
votes
2answers
484 views

Create new instance MS SQL Server 2014

I have installed MS SQL Server 2014 , but after installation there is no instance created. There is only the default instance. How make a new instance in existing MS Sql Server ? I found a lot of ...
1
vote
1answer
56 views

Update all tables containing column name

In SQL Server, I can find all tables containing a COLUMN_NAME: SELECT * FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'COLUMN_NAME' How can I update all of these columns like: UPDATE <...
18
votes
2answers
2k views

If positive, sum all items. If negative, return each one

I am needing to find a way to SUM() all of the positive values for num and return the SUM() of all positive numbers and an individual row for each negative number. Below is a sample DDL: Create ...
-1
votes
1answer
28 views

SELECT rows according to another table

Table A (Id, Value), Table B(AId, Value). I need to select only rows from A, for which the pair (Id, Value) is not in table B. Solution with "JOIN" would probably better, because of performance.
41
votes
1answer
34k views

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It ...
2
votes
1answer
1k views

Is someone trying to hack into my SQL Server Database?

I was asked by a friend, who isn't technical at all, to help her with her application. I thought I would just being doing a little C# development and some SQL queries. Turns out it has been a lot ...
0
votes
0answers
30 views

ERROR: Arithmetic overflow error converting float to data type numeric

I unfortunately have some bad data and need to work around it. I have a Parcel column in a spreadsheet with some parcels that have inadvertently been converted to floating point numbers. I posted a ...
1
vote
1answer
55 views

Contradiction in book related to variable scope in Stored Procedures

I was reading the 70-461 TSQL training book and the part about stored procedures said this: Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by ...
1
vote
1answer
55 views

How to search by text in SSIS packages? [duplicate]

Is there a way to search by text in SQL Server Integration Services packages using T-SQL statement (or other language)? I want to check if certain table is used in any package as I am going to drop ...