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
17 views

T-SQL Trying to create a trigger that uses joins across 2 server instances [on hold]

I am trying to complete a trigger on 2 instances of SQL Server. I am running MS Dynamics ERP set CRM & GP. I need to remove data from one server once an update happens to another table. I have ...
2
votes
3answers
59 views

Show all stored procedure definitions (content) for editing

Apologies if this has been asked before, the suggested 'related' questions weren't relevant and my own search turned up very little. I need to list the definitions (code) of all my stored procedures, ...
0
votes
0answers
12 views

Is it possible to move more than one database object into a schema using the `ALTER SCHEMA` statement in SQL Server 2008 R2? [duplicate]

I have a TSQL Statement like this: ALTER SCHEMA FindUsedAndUnused TRANSFER dbo.UsedUnusedNotSure_the_counts; And when I try to run it with multiple TABLEs or VIEWs I receive an error; Is there ...
2
votes
2answers
34 views

Ensure only one of the several records for a given item has a column with true value

I have a table called Computer with columns: id NIC_name is_primary My primary key is (id, NIC_name) and is_primary is a bit field for a boolean value. I would like to ensure that for all ...
0
votes
1answer
23 views

How to merge consecutive record pairs over a table?

I have a table where I need to merge consecutive record pairs (when ordered by id, data and from). Note that for pairs being merged, the first row's prevfrom is NULL and the second row's nextto is ...
1
vote
2answers
183 views

Why does FETCH NEXT return second row after opening cursor?

I have a cursor declaration: DECLARE idCursor CURSOR FOR SELECT DISTINCT [id], [data] FROM #tempRemaining OPEN idCursor And the statement: SELECT DISTINCT [id], [data] ...
2
votes
1answer
20 views

How to copy non clustered indexes in transactional replication

I create transactional replication ,but forget to include non clustered indexes and now my subscriber have only clustered indexes (primary key) and I want to transfer all nonclustered indexes to ...
-1
votes
1answer
39 views

How to do superscript in SQL Server 2012 query? [closed]

I am trying to change superscript in SQL query string. Example: Select 'Your submitting date is 2nd January 2016'; As above query, I want to change nd to superscript.
1
vote
1answer
36 views

Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

I experienced very strange problem today with TSQL function. The function has several parameters (int and bit). One of the (int) is set with value NULL. When calling a function with NULL inside the ...
1
vote
0answers
12 views

How can I return the full text of a document indexed in SQL Server Full-Text?

I'm using SQL Server 2014 with FileTables to store a large number of documents in different formats. The iFilters are working great, and everything is getting indexed with FTS + Semantic Search. Now ...
0
votes
1answer
15 views

How to FETCH from a cursor into a table variable?

I have a cursor defined as: DECLARE idCursor CURSOR FOR SELECT DISTINCT [id], [data] FROM #tempTable And I have a variable defined as: DECLARE @currentId TABLE ( ...
0
votes
1answer
50 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 ...
1
vote
1answer
39 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
17 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
26 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
30 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 ...
1
vote
0answers
11 views

Extracts all characters before the 3rd occurrence of a delimiter in SQL Server 2012 [migrated]

For example the string is AL*12345*B001*1 Please help me get the result to be AL*12345*B001 (all characters before the third '*' delimiter. Below I'm using a reverse to look for the last delimiter ...
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
435 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
27 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
36 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
27 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
19 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
179 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
45 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
39 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
84 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
202 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
135 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
38 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
35 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
22 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
32 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
406 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
23 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
47 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
158 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
42 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
46 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
97 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 ...
7
votes
3answers
932 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
70 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
58 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
85 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
34 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
536 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
67 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 ...