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

learn more… | top users | synonyms

1
vote
1answer
45 views

Performance improvement on SQL query joining table on itself

I'm running this query on a table with approximately 20MM records in it. It's a very slow query, and I'm wondering if there's a better way to accomplish this that might run faster ? delete previous ...
0
votes
1answer
23 views

Create a T-sql script JOB SQL-2012 and scheduled

I would like to create a T-sql script and scheduled. My goal is create all my databases (4 databases) and when the QA or DEV team need to test something, they can use these databases. The problem is ...
0
votes
2answers
28 views

Help writing query for MS SQL Server [closed]

I need to write a MS SQL Server query statement that will generate the result set table name SUMMARY from the DETAIL table Server name: Development Database name: Test DETAIL ...
1
vote
1answer
34 views

How to group data and write it's group id for each row?

I have sample data: CREATE TABLE #T (Name varchar(5), GroupId int NULL) INSERT INTO #T (Name) VALUES ('A'), ('A'), ('A'), ('B'), ('B'), ('C'), ('D'), ('D') Name GroupId ----- ----------- A ...
0
votes
1answer
55 views

NC Index not being used [closed]

I have a table (9mm records) with a field called filename nvarchar(max)(I didn't decide on this datatype and can't change it). I'm wanting to count how many records meet the following criteria: ...
4
votes
4answers
550 views

SQL Server - Alter table vs drop and create

Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are ...
0
votes
0answers
35 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 ...
2
votes
1answer
49 views

Powershell - SQL Server - connectionstring in loop for multiple Instances

Thisis part of a bigger script which finds DatabaseFiles on a SQL Server machine (multiple instances). Following should just return all files for 3 instances. The server is called V3000801 and ...
1
vote
1answer
37 views

Dynamic SQL in a pivot table

I was watching a video on pivot tables and got to thinking, why can't we just push in a sub query into the IN clause, instead of declaring the expected columns statically? Well, turns out you cant ...
0
votes
0answers
23 views

SYNTAX ERROR: delete from multiple tables with inner join

Sorry, but I need syntax help :( This needs to run on SQL Server 2005 + DELETE SO, ASO, ASOV, AOV FROM SYS_OPTION SO INNER JOIN ADM_SYS_OPTION ASO on SO.OPTION_ID = ASO.OPTION_ID INNER JOIN ...
2
votes
3answers
235 views

No advantage of using Cross Apply or CTE over inline sub-query

I came across a query like this: SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C] FROM Items i I changed it to following ;WITH cte_count AS ( SELECT COUNT(1) c, OrderId ...
0
votes
3answers
59 views

how to set “result to text” with a tsql statement in sp?

is there any way to set "result to text" within of SP while this execute a process? a tsql stament? Thanks.
3
votes
1answer
40 views

Execution plan for COMPUTE BY

I understand that the COMPUTE BY feature is deprecated. Out of curiosity I would like to know how the query processor executes a COMPUTE BY query. The execution plan does not contain any hint: How ...
2
votes
1answer
94 views

Update one column based on count of records of grouped value in another column

Given a table CREATE TABLE TABLE_ITEMS ( serial_num int null, item_group int null ); INSERT INTO TABLE_ITEMS VALUES (0,1), (1,1), (2,1), (3,1), (4,1), (0,2), (1,2), (2,2), (3,2), (4,2), ...
3
votes
2answers
212 views

What is the difference between Used and Allocated space?

I am learning Database operations and I have found a couple of resources online that have given me a loose understanding, but I still couldn't explain the difference myself. Explain like I am five ...
8
votes
1answer
139 views

Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another ...
0
votes
0answers
22 views

How to Backup a Database with Specific Date Range in SQL Server 2008? [duplicate]

I have a database around 160 GB size. I want to backup the database with specific date range. Example the database contains from 2013 to until today. I want to backup database from '2015-01-01 ...
1
vote
1answer
73 views

How to separate name string by the spaces in the name in T-SQL

I have a list of names, such as: Doe John James, Doe Jane and Doe Henry Albert Sr I need to split those out into the four component parts of the name (if the name has four parts) to put each of them ...
1
vote
1answer
51 views

How to do a SELECT inside of a PIVOT

I am trying to SELECT values from a column in my database in order to perform a PIVOT on those values without hard-coding them. SELECT * FROM (SELECT ItemType, [Status] FROM ...
1
vote
0answers
24 views

Using :Connect I keep getting msg 911 - database [dbname] does not exist

I have a two part T-SQL script, the first part runs against a database on a different server, the second part is supposed to run on the local PC. I'm using SQLCMD mode and the :Connect keyword but ...
1
vote
1answer
43 views

SET ANSI_NULLS at the database level versus setting it at the table level

I have generated schema-only script for my sql server 2008 R2, here is part of it:- USE [master] GO /****** Object: Database [****] Script Date: 03/25/2015 12:17:09 ******/ CREATE DATABASE [****] ...
5
votes
2answers
411 views

Optimize a SQL query

I have to select 2 values that were less that the given id and greater than the given id. I have tried this query but is there any better way to do it Fiddle Sql Begin declare @rootValue int ...
0
votes
1answer
24 views

Do i need “CHECK CONSTRAINT” when i am using “WITH CHECK ADD CONSTRAINT”

I am working on SQL Server 2008 R2, where I generated a schema-only database script using SQL Server Management Studio. The generated script is as follows: ALTER TABLE [dbo].[ConsoleServer] WITH ...
0
votes
2answers
67 views

how to create trigger in SQL server using two tables to find stock quantity

I have two table sales and purchase. I want to create a trigger which stores final quantity in stock table by subtracting sales table sold quantity from purchase table. There is kikkat as a name n ...
2
votes
1answer
22 views

How to do housekeep the database in sql server 2008 by specific date range?

I have database with around 160 GB size. The database contains the data is from 2013 to until today, there are 180 tables, I want to remove the records in specific date range like 2013 to 2014 in all ...
2
votes
2answers
214 views

Creating a stored procedure and adding date manually to filename

I am new to SQL administration and have been tasked with creating some nightly jobs that send an email with certain details contained within a spreadsheet and so far I have the following: EXEC ...
2
votes
0answers
49 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 ...
6
votes
1answer
48 views

I want to add a user/login to role in another database without switching databases

I have a role / login in a 'new' database mynewdb with a role "emailsender". I have scripted a profile for sp_send_dbmail and am now trying to script adding the the login to the DatabaseMailUserRole ...
3
votes
3answers
124 views

Insert data between 30 minute time intervals

I want to divided my to my time interval depending to the start time and end time. To explain it further, let me have this example. I have this table: User Elapsed Time Start Time ...
16
votes
6answers
3k views

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
2
votes
2answers
78 views

concatenating columns from multiple unrelated 1-row resultsets

Assume I have these two queries in SQL Server 2014, both of which return one row from unrelated tables: SELECT SUM(A) A, SUM(B) B FROM X SELECT SUM(C) C, SUM(D) D FROM Y I'd like to combine these ...
4
votes
2answers
59 views

Strange looking where clause in sql server

I've found fa strange looking code in T-SQL. Here is the strange looking part: WHERE @term1 IS NULL OR (@term1 IS NOT NULL AND (businessLogicCondition)) The query works well, but what is the point ...
2
votes
1answer
163 views

Can I use a script to restore multiple SQL databases from .bak files?

I'm migrating 100 plus SQL databases over to a new server in a new location. I have the maintenance plan in place to take full back ups and then a script to copy over the .bak files to a temp ...
0
votes
1answer
62 views

how to set default value of column 0 when NULL is inseerted

I have a table with a column which has default value to 0. However when null is inserted to this table, I want it to set it to default. As the insert query is generic and used by other databases too, ...
7
votes
3answers
546 views

Use 'default value' in table when NULL

I am building a config table and want one row to be the default value. For the example, I have a list table of all my databases and a msg table of my return values. In the msg table, I have one row ...
1
vote
1answer
37 views

Statistics needs to be captured for query analysis

What statistics are required to theoretically prove that X query work better than Y query. As per my knowledge I need to look for: Query elapsed time Logical read Logical write Physical read ...
2
votes
1answer
77 views

How to Retrieve value from a User-Defined Table Type (UDTT)

Goal: I want to reach and display the value of @data inside of [wwtest]. Problem: The UDTT is from the database [test] and I receive an error message: The type name 'test.dbo.tvf_id' contains ...
3
votes
1answer
32 views

A Customized Table with Chronological order

Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id. Problem: When I ...
1
vote
1answer
42 views

How do I change name format in TSQL Query Result?

I have a simple tsql select query that returns a user's name in this format: e.g. Doe, John How do I update the output so that it is in the format John Doe? I have tried using a split string ...
6
votes
4answers
206 views

Compare 4 or more columns values

I have a table like: ID A0 A1 A2 A3 A4 14 A B A C A 15 A A A A A I need to compare all 5 columns and check if they have same value and retrive the only one that have all same ...
3
votes
2answers
48 views

Is there a less manual way of changing database properties for all databases in an instance?

I've recently audited a SharePoint server and found configurations that are against best practice. I'm hoping there is better and quicker way than right-clicking and changing these settings manually ...
0
votes
1answer
186 views

SQLcmd error 'Failed to initialize sqlcmd library with error number -2147467259'

I'm trying to update some maintenance plans to make use of the Ola Hallengrens IndexOptimize, but also mail the result of the operation using sp_send_dbmail. Old T-SQL: declare @indexQuery ...
0
votes
2answers
33 views

'Use <database> ' statement not executing T-SQL

Okay, this seems funny for a simple script: but I really need this small piece to execute well. DECLARE @dbName NVARCHAR(50) SET @dbName = 'rx_lab' DECLARE @SQL02 NVARCHAR(MAX) SET @SQL02 = 'USE ['+ ...
1
vote
0answers
34 views

Publishing Database Changes With SSDT that include Adding a New Column [closed]

Similar to Publishing database changes with SSDT that include altering data types of columns But I have the issue when I'm adding a new column to my database. I tried publishing incrementally by ...
2
votes
1answer
63 views

Wrong results from joining two CTEs

I've had some help to create a query to solve a problem I had and the solution seemed to be to create two CTE's and then join and query them. If I run the two CTE's as single queries each table ...
2
votes
1answer
17 views

Retreive a Customized List with input data

I have a database that has about 223 tables and I need to locate the name of the constraint. My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the ...
2
votes
3answers
238 views

How to use “BACKUP DATABASE”, but the database is in a different server.? [closed]

We have 3 servers ( SQL SERVER 2008 ). Is there a way to backup a database, connected in a different server? Example: i'm on SQL1. I want to do something like this: BACKUP DATABASE ...
3
votes
3answers
1k views

T-SQL - How to split (char separated) string into rows and columns

What is the best way to split a char separated string into rows and columns? Here is my input string: !1;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5 And here is my desired output table: Id ...
0
votes
1answer
65 views

how to extract current year and combine with specific month and date as a date

I have to get the current year with June the 16th as a date. For example, if I run the query now I want to get 2015-06-16. I tried the following: CONVERT(DATETIME,cast( (cast(YEAR(GetDate()) AS ...
1
vote
1answer
269 views

Joining multiple tables results in duplicate rows

I am running SQL 2005. I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. Here is my query: There are multiple tables that have ...