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

learn more… | top users | synonyms (1)

2
votes
1answer
46 views

RedGate SQL Prompt - Cursor Snippet mistake

Using RedGate's SQL Prompt & loving it. But I used the "curff" snippet for the first time today ("Fast-forward read-only cursor"), and it produced the following TSQL (which I've filled in to make ...
11
votes
2answers
150 views

Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if ...
1
vote
2answers
76 views

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | -----------...
5
votes
1answer
85 views

Benefit or use-case for empty variable / parameter / temporary table / temporary procedure names?

I just discovered, through sheer brilliance accidentally, that SQL Server allows you to create variables, parameters, table variables, temporary tables (local and global), and temporary stored ...
10
votes
5answers
1k views

How to hide/disable tables without dropping them to check redundancy?

I have to maintain and extend an old legacy system which contains webservice methods and database tables that are no longer used. Since I’m not entirely sure that the tables are really redundant, I’m ...
1
vote
1answer
38 views

SQL Server - Create Additional Database File for New Schema

I'm trying to create a change script for some upcoming changes. We're adding a new schema to the database and our client wants the new schema to make use of a new database file. How do I, in T-SQL, ...
1
vote
0answers
30 views

Resolve “stuck” system process of dropping indexes

I am re-vamping some indexes, and have decided to drop 3 on a certain table. The process has been running for almost 2 hours now. The table does have quite a few million rows, but I still feel that is ...
4
votes
1answer
72 views

How to check for Non-Ascii Characters

What is the best way to check if a VARCHAR field has Non-Ascii Characters? CHAR(1) through CHAR(31) and CHAR(127) through CHAR(255). I tried using PATINDEX and have run into the following issue. ...
0
votes
2answers
32 views

Compare possible null values in one query

Question is pretty short and simple: I have following query: SELECT * FROM CTE Now I want to get all rows with only maximum value of some field. SELECT * FROM CTE WHERE NodeId = (SELECT MAX(...
0
votes
1answer
35 views

Capture the Query Text of a DML Change

It may not be possible, but without doing a JOIN from the inserted/updated from the records coming in and adding them to an audit table column-by-column, is it possible to just capture the full DML ...
3
votes
1answer
56 views

Handling sql column with mixed varchar and float data types

We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to: [Parcel # 1] [Building Address] 038-60-171-16-000 1750 W 4th St ...
0
votes
2answers
47 views

Create a copy of a SQL Server Job

Using T-SQL or Powershell, how can I create a stored procedure that performs the following: Copy a SQL Server Agent Job to another backup Job on the same server (with a new name) Disable the backup ...
1
vote
0answers
47 views

Does anyone know where the SQL Server Connection Properties are stored?

When you add a Server Instance to Central Management Servers or even your local instance of SSMS, if you click on the connection properties tab you can select the default database you'd like to ...
0
votes
1answer
46 views

Query distinct emails not logged in within a period of time

I need help in writing a query to return distinct emails that have not logged into ABC database in the past 5 months. The exact task I have been asked is as following: Create a table in ABC ...
0
votes
1answer
67 views

comparing left join and outer apply doing the same thing

I have an update trigger on a table that that insert rows in another database/table the query look like insert into otherdb.table select manyfield from inserted left join (select id, ...
0
votes
0answers
41 views

Mulit-part identifier could be found - stored procedure

Hopefully I can get some sight into to what's going, Apologies for my lack of terminology and poor TSQL skills, I'm very much a developer that's inside an IDE and using ORM's. So I took the plunge to ...
0
votes
0answers
28 views

Tsql - getting list of instances in the alwayson Availability group

I want to connect to the listener in the AlwaysOn availability group and by running a query I will get the list of instances that are part of the AlwaysOn . For example: I has listener "MylistenerHost"...
1
vote
3answers
70 views

How to find values from one table missing in another?

I am a Junior Developer. I have been asked to create a subquery to solve this problem. If the question is not clear please let me know so I can provide more details. I have two tables - Article and ...
1
vote
3answers
71 views

T-SQL for Query that can be done in SQL. What do they want?

We are given tables Vendors(VendorId, VendorName, ..) , Invoices(InvoiceId, VendorId...) I am going over a book exercise that asks for a T-SQL statement to find all vendors with at least one invoice. ...
19
votes
9answers
4k views

Test if a string is a palindrome using T-SQL

I am a beginner in T-SQL. I want to decide whether an input string is a palindrome, with output = 0 if it is not and output = 1 if it is. I am still figuring out the syntax. I am not even getting an ...
1
vote
1answer
41 views

TSQL Solution to backup ALL databases on ALL instances of a server?

I'm pretty sure what I'm wanting with my current implementation is impossible, the sys.databases catalog view which is scoped at the service level to be aware of other SQL Server instances (lol). But ...
0
votes
1answer
37 views

List of queries run

I'm looking for a DMV query for SQL Server 2008 R2 which shows me all queries (full history) run by a specific user. I have this code for now: SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY ...
0
votes
3answers
46 views

Minimum value within a set (or window) within a three column table

I only have SQL Server 2008R2, therefore I do not have all the window functions available since SQL Server 2012. I have a table that looks like this: I would like to retrieve a result set like ...
0
votes
2answers
39 views

How to separate values from one column into two columns in SQL Server [closed]

I have this dataset as input: Name Contact No ------- ---------- Deepika 75865558 Deepika 207586558 Rnveer 746655558 Rnveer 946655558 vaibhav 2356841 vaibhav 6655558 I want to get ...
2
votes
1answer
36 views

msdb.dbo.sysjobs and last run date and time

I know that the last_run_date and last_run_time are stored as Int, but when coverted over to datetime (of which I have examples of), is the time GMT, or what? When converted it doesn't match the same ...
0
votes
1answer
28 views

Script to Delete Tables With A Prefix

I am running SQL Serve 2008 and we have a few test tables that were obviously used for testing. The prefix of the tables are Test_, my question is, will this script delete all tables with the prefix ...
0
votes
0answers
15 views

Passing Table Variable Column Values to a TVF [duplicate]

I am trying to pass values in a table variable to a Table Valued Function. However, I get the error "The multi-part identifier 'Accounts.AccountNumber' could not be bound". Why is this? SELECT ...
11
votes
1answer
311 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 ...
7
votes
1answer
148 views

Should we still be using QUOTENAME to protect from injection attacks?

I was looking at an old stored procedure today and noticed it was using quotename on the input parameters. After doing some digging to figure out what that does exactly I came across this site. I now ...
1
vote
1answer
57 views

OFFSET FETCH in SQL Server 2008

I have this query to paginate the results and it was working fine on SQL Server 2012. However i had to move my database to SQL Server 2008 and now my store procedure is not working. I did some ...
2
votes
1answer
54 views

Predicate re-write in where clause

How can I rewrite where col % 5 = 4 in SQL Server. The reason to rewrite this is because I am selecting some columns from the table based on this condition. Because of % used in where it is causing ...
4
votes
1answer
50 views

How can a database parameter be used on `sp_addextendedproperty`?

I have found the MSDN definition and code examples of 'sp_addextendedproperty'. In Example A on that page, I see an example of how to add an extended property to a database. USE AdventureWorks2012; ...
1
vote
3answers
60 views

How to replace column if not null in select statement?

I have a Contact (ID, FirstName, LastName, MiddleName). For example: | ID | FirstName | LastName | MiddleName | |====|===========|==========|============| | 1 | Mark |Jordan | E | |...
0
votes
1answer
56 views

Scalar UDF causing performance issues in SQL Server

I have a scalar UDF doing some string manipulations. When I use it in select clause it causes performance issues. I read that if we do not query tables or views in scalar functions it won't affect ...
0
votes
0answers
37 views

NEXT VALUE FOR SEQUENCE inside a CASE Statement

I want to use a new SEQUENCE each year (See here: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year ) However you can not use NEXT VALUE FOR inside a CASE statement. ...
0
votes
1answer
40 views

Is there anyway to tell when the last query was made against a database? [duplicate]

Is there anyway to tell when the last query was made against a SQL 2008 R2 database without downloading a third-party tool ?
0
votes
3answers
94 views

Select distinct value from row if condition exists

I am new to SQL and I am having trouble constructing a SELECT statement. What I would like to do is select distinct parts that have a location of A1 if it exists. If a part has no locations of A1 I ...
4
votes
1answer
134 views

Cross Apply Fails When DateTime Added

In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a ...
1
vote
1answer
41 views

Grouping by more than 1 column using Partion By or any other method - Sql Server

I have the following tables in my SQL Server database: Product -Id -Name -Price -TypeId (Foreign Key) -SubTypeId (Foreign Key) Type -Id -Name SubType -Id -Name -TypeId (Foreign Key) I currently ...
1
vote
3answers
79 views

Remove duplicates in SQL Server Group By

I have 2 tables, Product and Type, the tables looks something like this: Product -Id -Name -TypeId Type -Id -Name I want to be able to write a SQL Server query which outputs the products grouped ...
12
votes
3answers
1k views

What does OPTION FAST in SELECT statement do?

I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and am still confused on it. According to MSDN: Specifies that the query is optimized for fast ...
1
vote
1answer
33 views

Monitoring decimal values on Perfmon using User Settables

I am currently using sp_user_counter1 to store database information on Perfmon, which can be recovered by AWS CloudWatch, but the values are Decimal, and the sp_user_counter1 parameter supplied must ...
1
vote
1answer
42 views

How to synonymize a database name?

Within one database instance, I need to restore a set databases into new names. The new names will be suffixed based on the development phases, like: _DEV and _QA. Where: DBApp_DEV shall access ...
0
votes
2answers
52 views

Need help with this backup audit SQL Server script

I am using below backup script taken from http://www.ludovicocaldara.net/dba/sql-server-last-database-backup/ link to audit my servers for backups. This works great except when I have spilt backups. ...
8
votes
4answers
1k views

'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year

I have a a business requirement that each record in the Invoice table has an id which looks like YYYYNNNNNN. The NNNNNN part needs to restart at the beginning of each year. So the first row entered ...
2
votes
3answers
78 views

TSQL for “Ignore databases were the state is not online”

I have a few custom jobs on a mirrored SQL Server 2005 and they fail from time to time because of automatic failover. Is there some T-SQL code that I can write into my automated jobs or do I just ...
4
votes
1answer
72 views

Get a listing of snapshot data files

I need to programmatically drop a database with all of its snapshots and then recreate the database and create a new snapshot. I'm having trouble locating where the snapshot files are located from ...
0
votes
0answers
32 views

Run Select Statement Based Off Data In Field

Below is sample DDL of what I am trying to accomplish. What I need to happen is if region = 1 only pull data for PP or if region = 2 then only pull data for CC How would I set that up in my query? ...
1
vote
0answers
28 views

Matching table (with sub-tables) to another table(with sub-tables) efficiently

I have a problem in an ETL process. I need to integrate data into a data warehouse. The data is associated with a "variant". A variant is defined as having a specific set of parameters (identified by ...
5
votes
2answers
428 views

Custom ID Numbering - fast way on insert?

As start: this is for an ETL stored procedure that is serialized, so paralellism is not of concern. I need to assign custom ID numbers for markers during loads. I can not use an identity field, ...