Transact-SQL (t-sql) is a dialect of SQL used by Micrsoft's [SQL Server](http://www.microsoft.com/sqlserver/en/us/default.aspx) and SAP's [Sybase](http://www.sybase.com/).
119
votes
4answers
19k views
What's the difference between a temp table and table variable in SQL Server?
This seems to be an area with quite a few myths and conflicting views.
So what is the difference between a table variable and a local temporary table in SQL Server?
3
votes
2answers
610 views
Linked Server Query Results: OPENQUERY vs Distributed Transactions
I have two databases. They are on different servers. One is named REPORTDB and the other is named TRANSDB. TRANSDB is referenced as a linked server on REPORTDB named TRANS.
I execute a query like ...
0
votes
1answer
20 views
Give access to view or table?
I want to give an external user (new login) read access to part of the data model from my Database. The user will copy the data model and content from my Database, and then integrate that into their ...
12
votes
6answers
346 views
Trying to find the last time that a value has changed
I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table.
Records are inserted into this table periodically. The ID will always stay the same but ...
0
votes
1answer
60 views
Disabled trigger still fired [closed]
I've just run the following t-sql to disable a trigger (with a deploy script wrapper around osql):
disable trigger dbo.trExampleTableInsert ON dbo.ExampleTable
In SSMS I could see the trigger was ...
0
votes
1answer
56 views
Simplify subqueries with join
I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
...
0
votes
1answer
47 views
EXPORT specific columns to INSERT statements
Is there a way to export specific columns and a fixed number of rows from a table to INSERT statements?
I am trying to generate these for unit testing based on a large production table so using ...
4
votes
2answers
80 views
Difference Between SP with BEGIN/END and without BEGIN/END
I have found some SP(stored procedure) written as,
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
BEGIN
--- Actual Work
END
and some as
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
--- ...
1
vote
1answer
228 views
how to add attachment(text file) to database mail?
I have scenario
Daily i run a sql job to apply a new updates to one table - this job will create one text file daily - text file contains all new updates
I can send a mail to client that job is ...
2
votes
3answers
101 views
Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase
Have a quick general question. I have a table I'm trying to purge a table. I'm deleting using a WHILE loop with WAITFOR DELAY time of 50ms between batches and 2000 records per batch. The real issue is ...
0
votes
1answer
20 views
Searching for keywords in fulltext indexes using the “contains” function
Lets assume "and" is not a stopword, in my language it should not be.
How do I search for it using the contains function?
It is a keyword, but how do I escape the keywordness?
No luck with:
SELECT ...
1
vote
0answers
14 views
Fulltext search limiting users possibilities
We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure.
CREATE PROCEDURE [dbo].[SearchPages]
...
1
vote
1answer
66 views
Autogrow of database1_log file in database database1 took 1010871 milliseconds
in past i had a problem of that query been executed for 7 days. i couldnt find any solution. so i created again new database with indexes.
Now i am running a simple query:
UPDATE query
query has ...
1
vote
1answer
49 views
Sql Server Server Group query
Scenario
I have 4 front-end sql server.
All of them have the same db instances. Data are replicated from back-end databases.
Issue
In SQL Management Studio I have registered the servers and I have ...
2
votes
1answer
64 views
Create a sub query for sum data as a new column in SQL Server
Suppose that I have a table named tblTemp which has this data:
| ID | AMOUNT |
----------------
| 1 | 10 |
| 1-1 | 20 |
| 1-2 | 30 |
| 1-3 | 40 |
| 2 | 50 |
| 3 | 60 |
...
2
votes
1answer
33 views
Strategies for summary data in parent record based on detail data?
I have parent-child relationship as shown below. I need to find the first order date for each customer. Obviously, writing a query to do this is fairly straight-forward but it does require reading all ...
-1
votes
1answer
49 views
Good example of long stored procedure? [SQL Server] [closed]
I have a 400-line stored procedure that I need to copy and refactor for my own purposes. I'm new to SQL but I do think that the stored procedure that I'm working with can be cleaned up. I have been ...
0
votes
0answers
52 views
Error 0xc02020f4: Data Flow Task 1: The column cannot be processed because more than one code page [closed]
SQL SERVER :
i executed an query and i got result of 8 million data. I have to create a table out of this result output.
SO i simply save the result out to table1.csv.
Now when i try to import ...
2
votes
1answer
73 views
Execution Plan not matching Stored Proc
Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists.
The execution plan shows an INNER JOIN of the M and A tables, where in the ...
0
votes
1answer
50 views
How to add custom sequence number in table which is going to be populated automatically
I have table something like this
CREATE TABLE [dbo].[ado_test_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[tip] [varchar](10) NOT NULL,
[datum] [datetime] NOT NULL,
[doc_number] [int] ...
-2
votes
1answer
37 views
Create a Leveling of Intermediaries [closed]
I have a table called Intermediary, which have some columns like
CREATE TABLE Intermediary
(
IntermediaryPK INT ,
IntermediaryID NVARCHAR(20),
IntermediaryName NVARCHAR(200),
IntermediaryTypeID ...
-1
votes
1answer
64 views
validate where each parent intermediary is also a parent to itself [duplicate]
I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT If table has any.
I need to do is first of all get all parents ...
1
vote
1answer
77 views
limit the number of rows returned when a condition is met?
Is it possible to limit the number of rows returned when a condition is met?
I am working on a query to check if a student is ready to graduate, and they need to meet a certain number of credits per ...
1
vote
1answer
72 views
sp_startpublication_snapshot Parameter(s)
I am creating a stored procedure that:
Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up
(If specified in the SP's parameter) Creates ...
1
vote
2answers
53 views
How can I run a T-SQL Script using SQL Credentials?
I need to develop a T-SQL insert script for use on a remote SQL 2005 Express instance but I would like the script to include the necessary SQL credentials so all the user has to do is execute the ...
4
votes
1answer
80 views
Is it “legal” to CREATE and DROP #SomeTable more than once?
I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:
CREATE TABLE #WidgetSetting
...
0
votes
0answers
55 views
How to improve the performance for SQL Server Count(distinct()) and Sum() functions? [closed]
The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index ...
1
vote
1answer
66 views
How can we get table name from inside trigger?
I need to pass the table name from trigger to procedure.
ALTER TRIGGER [dbo].[Trg_ProjectCreation]
ON [dbo].[Projects]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- procedure call
...
0
votes
0answers
115 views
DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'
I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
2
votes
2answers
90 views
Cannot rebuild index, but there's no reason why not?
I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
0
votes
0answers
189 views
How to create table set from several XSD in SQL Server 2012 or with VS 2012
Is there any solution in T-SQL or in C# how to create XML schema collection in T-SQL or C#?
Problem is I cannot use:
USE Database_test
GO
CREATE XML SCHEMA COLLECTION Collection_from_XSD AS
N'
...
1
vote
1answer
58 views
Can I use Try_Cast to give me a query result column with different datatypes?
I have a column that is varchar(max). It contains mostly valid XML. But sometimes it has invalid xml (that I still need).
I am trying to make a view for this table that allows the value to display ...
6
votes
1answer
313 views
Restore a Database to a New database (T-SQL)
I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the ...
0
votes
3answers
74 views
DB comparer field by field for all tables
I have a liveDB and I have a development DB. Now when I make a change to development DB, I want to find out the changes later when I am deploying my application on server. For eg. I may have added ...
1
vote
1answer
151 views
error in Execute SQL Task with parameter
I have an Execute SQL Task object in the Error handlers section of a SSIS package. What it does is inserting a record to a table using OLE DB connection. There is a value in the insert statement that ...
0
votes
3answers
166 views
Easier way to handle so many isnull() situation
Is there any good way to avoid writing so many times isnull() function inside sproc ?
I have stored procedure that use almost 30 times isnull() function, I think that I am miss a concept , but until I ...
2
votes
2answers
110 views
Executing a table type parameter stored procedure or function within a select clause
Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and ...
3
votes
3answers
113 views
Conditional WHERE Clause in a Table Valued Function
I have a table valued function
CREATE FUNCTION [dbo].[DateRange]
(
@StartDate date,
@EndDate date,
@Location varchar(25),
@Device varchar(25)
)
...
2
votes
0answers
54 views
update statement with self join
I needed to copy information from one row of a table to another.
I noticed I could do the following:
update address
set col1 = a2.col1,
col2 = a2.col2,
.....etc
from address a1, address a2
where ...
0
votes
1answer
38 views
Conditional Where Statement Involving a NULL value?
Trying to find a way to pass off a third value within a 'bit' field. I would like to be able to utilize NULL as a searchable value. As in 'True', 'False', or 'NULL', the only issue is I'm unsure how ...
7
votes
2answers
162 views
How to flatten results of a table with two related “many” tables?
I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them.
I have the following tables (somewhat abbreviated ...
3
votes
3answers
3k views
Add a new Column and define its position in a table
I have Table A with 5 columns:
TableA
--
Name
Tel
Email
Address
I want to add a new column (mobile) in between Tel & Email:
TableA
--
Name
Tel
Mobile
Email
Address
If I use
ALTER TABLE ...
-2
votes
1answer
85 views
how cursor implementations are different for each cursor type in sql server
In Oracle, there are only 2 types of cursor i.e. Implicit and Explicit cursor. Which is easy to understand. But, in SQL Server there are 4 Cursor AFAIK i.e. Static,Dynamic,Forward Only and Scroll. ...
1
vote
3answers
88 views
How to handle update or insert situations
From time to time my stored procedures looks like
create procedure handle_data
@fk int
,@value varchar(10)
as
begin
if exists (select * from my_table ...
1
vote
1answer
58 views
how to show only changed rows
if want querying the following table :
EventDateTime (DateTime), Host (guid), Flag ( bit)
2013-04-01 05:00, {id-x}, 0
2013-04-01 05:01, {id-x}, 0
2013-04-01 05:02, {id-x}, 0
2013-04-01 05:03, {id-x}, ...
0
votes
1answer
72 views
Why is there such a huge performance difference in these two join statements?
I have a query which i first wrote as (query has been simplified a bit) :
select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
inner join product p on p.indID = ...
0
votes
0answers
53 views
T-SQL Script for segmenting data into finer categories based on percentage measure in another table
I have a SQL Server 2008 R2 database with two tables. The first table - Shops - has a list of establishments and the number of shoppers it attracts segmented by criteria A (2 categories) and B (2 ...
0
votes
5answers
602 views
The DELETE statement conflicted with the REFERENCE constraint
I'm trying to delete all users but getting the error:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_M02ArticlePersons_M06Persons". The conflict ...
0
votes
1answer
40 views
Need help in converting this cursor to a set based operation
I have a stored procedure which runs as part of a report database refresh nightly which takes about 2 hours complete. The object B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2 has 8 million rows. Can you suggest ...
5
votes
1answer
118 views
Unexpected Table Scan with Parameterized LIKE
I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
