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

learn more… | top users | synonyms

5
votes
1answer
272 views

difference of left join and left outer join

In SQL Server join syntax we can use LEFT JOIN or LEFT OUTER JOIN and query result with each of above join not difference. this is a question for me that which are more useful.
3
votes
1answer
665 views

New foreign key and merge replication

We are experiencing an issue when creating a new foreign key to an existing table, which is being replicated to another SQL Server 2008. More specifically, we are adding a new foreign key to this ...
3
votes
2answers
301 views

can I convert an integer to base-2 without a function

Here's a bit of a challenge. I am working with the table ReportServer.Schedule and there is a column called DaysOfWeek. I may not be using the proper terminology here, but this is an integer from ...
2
votes
2answers
2k views

Replace cursor with set-based approach

I am looking to replace my cursor-based solution if possible in a particular stored procedure. If it makes any difference, this is running on SQL Server 2008 R2. I am looking more for an algorithm ...
8
votes
1answer
306 views

Variable for element name

I have this tSQL code which works OK: SELECT c.logguid, a.b.value('./PropertyValue', 'varchar(max)') asd FROM [dnn].[dbo].[EventLog2] c cross apply ...
2
votes
1answer
1k views

SQL Server DATEDIFF: How's dayofyear different from day?

When I look at the DATEDIFF() function on SQL Server, I see that it takes a datepart as its first parameter. Possible datepart values include day and dayofyear. I understand that dayofyear is the ...
6
votes
2answers
707 views

Is there a better way to create dynamic time averaged series over a dynamic frequency?

I've got a series of tables with lots of high precision data collected from various devices. The intervals that they were collected on varies and even wanders over the time series. My users want the ...
1
vote
2answers
123 views

Naming question for functions: implicit or explicit naming of grouping in function name [closed]

Requesting help with naming TSQL functions. Existing function names (see below for table and relationship descriptions) I currently have functions named as follows: ...
9
votes
3answers
3k views

TSQL performance - JOIN on value BETWEEN min and max

I have two tables in which I store: an IP range - country lookup table a list of requests coming from different IPs The IPs were stored as bigints to improve lookup performance. This is the table ...
2
votes
2answers
215 views

Is it possible to retrieve the specific databases selected in a maintenance plan step?

I would like to use a tsql query to retrieve the databases involved in one of the steps in a database backup plan I've created. From a previous question, I understand that somehow, the data is ...
5
votes
2answers
3k views

Grouping records based on intervals of time

I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the ...
1
vote
1answer
2k views

Find last (max) value according to TimeStamp using update method

I have task to find values of integer according to last (max) of timestamp (datetime) value. Because it is so complicated query I am consindering this way to find last value something like this ...
1
vote
2answers
769 views

Which is better for long term database scalability: adding columns or having key/value store

For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach: to add new columns to settings table every time new ...
2
votes
1answer
631 views

How to design database for tree view with an infinite depth

I am trying to combine C# and SQL2005 for an application which need to have tree view control. One of user requests is to that tree view can user friendly grow into infinite dept of child nodes. That ...
2
votes
1answer
69 views

How to consolidate script duplication?

I just want to know how you deal with long scripts and redundant information. Here is an example: Suppose you have three tables: table1, table2, and sometable. And you have SELECT id = 1 org ...
3
votes
1answer
104 views

Relationship with a default value

Firstly, I know my title is somewhat misleading, but I couldn't think of a better title. Here is my current relationship CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT NULL, Name ...
1
vote
1answer
307 views

What can cause an UPDATE statement to execute endless? [closed]

I have a stored-procedure which is executed at 7 o'clock in the morning that updates the database because new records were imported at night. Today it started but never endet. So i've tried to run ...
3
votes
1answer
1k views

Script to query multiple instances

SQL Server - What is the best method to run the same query over multiple databases on multiple instances? for example we have many application databases, with the same schema that need to have ...
1
vote
1answer
3k views

TSQL Get minute part from time data type

With the time data type (I'm using Microsoft SQL Server 2008), is there any way to take only the minute part out of it? I tried to pass the time to datepart and datediff function but both refused to ...
8
votes
2answers
4k views

Subqueries' aliases same as main queries' aliases

I have an SQL query whose aliases are the same as some of its subquery's aliases. For example: select * from ROOM r where ... ( select * from ROAD r ...
204
votes
4answers
50k 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?
1
vote
1answer
1k views

How do I delete rows from a table on a linked server

I need to delete rows from a table in several databases on several servers with the same schema. OPENROWSET() does not seem to be the answer. If I do this: SET @sqlStr = ' SELECT TOP 0 * FROM ...
2
votes
2answers
309 views

What do these statements mean in the MS β exam 70-461 “skills measured” list?

I have registered for the upcoming beta exam 70-461 entitled Querying Microsoft SQL Server 2012. I've been reading through the exam overview and found a bunch of descriptions I'm not exactly sure ...
1
vote
2answers
2k views

Cast NTEXT to INT for calculation and comparison

I need to convert an NTEXT column to an INT so I can perform some checks on it. The first check would be is value = 0, and the second would be if it was an even or an odd number. I've got this ...
9
votes
2answers
4k views

In Sql Server's RANK function, how to limit the results to just the first 3 ranks

In Sql Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from Rank 1, 2, ...
2
votes
1answer
461 views

Speeding Up Querying Views

We've got a complicated stored procedure. The biggest pain point of the procedure is a join on a complicated view--it has unions and subqueries. I can't add indexes to it. So, I want to cheat. By ...
3
votes
2answers
899 views

How can I create different sets of sequential values based on another column in a table?

I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table. CREATE TABLE Notes ( Id int IDENTITY(1,1) NOT ...
1
vote
1answer
402 views

Why is the output of this query being returned like this?

I have a T-SQL query : select CAST(0 as float(53)),CONVERT(nvarchar(max),CAST(0 as float(53)),128) the output of which is 0,0.0E0 Can some one explain why it is not 0,0 ? The database is SQL ...
0
votes
1answer
215 views

Tips for using DML Statements [closed]

I've recently began learning T-SQL and have found that there are some rules to using single table queries. For instance: GROUP BY If aggregate and non-aggregate expressions are used together in a ...
1
vote
1answer
350 views

Execute Stored Procedure against CMS from within a SQL Server Agent job

Is it possible to execute a stored procedure (or any T-SQL) against a Central Management Server (CMS) via a SQL Agent job? The idea here is to exercise the CMS to hit all the registered instances ...
5
votes
1answer
2k views

How to detach corrupt database in SQL Server

I have Sql server data files saved on external storage. I want to detach specific database. If external storage is not connected I want to drop database. Is this safe way to do that: DECLARE ...
1
vote
2answers
2k views

Should I put the error handler inside the loop, or the loop inside the error handler?

When a procedure is looping over a statement that could raise an error condition, should I put the loop inside the error handler, or put the error handler inside the loop? I want the procedure to run ...
4
votes
3answers
398 views

Copy 8 Million Records

I have a table in a local MS SQL Server database that has 72 columns and over 8 million records. Most of the columns are floats except for the primary key which is an INT Identity column. The table ...
5
votes
1answer
10k views

Table-Valued Parameter as Output parameter for stored procedure

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...
2
votes
1answer
248 views

Execute Queries Against CMS within SSRS Reports?

Is it possible to execute queries against a Central Management Server (CMS) using SSRS? For example, I know of no way to tell a stored procedure that it is inherently supposed to call the CMS, so it ...
7
votes
1answer
807 views

Is there a way to force Deferred Name Resolution even if the table exists when creating a sproc?

When creating a Stored Procedure in SQL Server you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that ...
3
votes
2answers
2k views

How to check when statistics was last executed?

We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have ...
8
votes
3answers
13k views

What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

Many times I need to write something like the following when dealing with SQL Server. create table #table_name ( column1 int, column2 varchar(200) ... ) insert into #table_name execute ...
1
vote
1answer
111 views

SQL Server function invocation syntax question

The following excerpt is extract from this page. CURRENT_USER (Transact-SQL) Returns the name of the current user. This function is equivalent to USER_NAME(). When I execute the two ...
4
votes
1answer
11k views

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...
3
votes
2answers
966 views

Problem with Czech collation Czech_CI_AI in MS SQL Server 2008 R2

I have the following issue with accent insensitive search in Czech language. My database is set to Czech_CI_AI collation. For some diacritics system works correctly (ie. I), but for some does not (ie. ...
1
vote
1answer
666 views

T-SQL, show a set of rows (specific column) as a field, for a better desc, please see the table relationship

I have been having a problem for quite a while now, that I can show with a sample of 5 tables (I'm actually dealing with more than 5!): -------------------------------- shipments ...
5
votes
2answers
2k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
0
votes
1answer
117 views

TSQL - Bringing Data Together from Different Sources …refactoring PK and FKs

I have various offices and one central head office. Each office has its own SQL Server instance so each office has its own data set with its own set of IDs. Each office has already imported data ...
2
votes
1answer
2k views

CREATE DATABASE Permission denied in database 'master' error

I'm working with a tool that as one of the automated steps will attach databases from my file system to my local SQL Express 2008 instance. The tool requires a login in the sysadmin server role, for ...
0
votes
1answer
135 views

Help me to better design my query so I can gain more performance

I need to find so called first entry in database for items regarding their partners. I wrote query and it is giving me correct results but it sucks with performances. I suspect that I have missed ...
3
votes
3answers
570 views

t-sql - combinatorics

I'm trying to find all possible character combinations in a variable length string. For example: '--' would have 2^n = 2^2 = 4 possibilities, 'x-', '-x', 'xx', '--' I think that essentially I need ...
1
vote
2answers
459 views

Can I send a string over TCP using T-SQL?

I've set up some alerts, based on this article Now I would like the Job that is triggered by the alert to send some information to a c# application (this application, can be running in the same ...
3
votes
2answers
7k views

CASE ORDER BY with multiple columns, and different sort options

I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT. @Orderby = 1 Then Date column should be ASC @Orderby = 2 Then Date column ...
-3
votes
1answer
6k views

T-SQL to list all the user mappings with database roles/permissions for a User

I am looking for a t-sql script which can list the databases and and the respective roles/privileges mapped for a particular user. Using SQL Server 2008 R2. Regards Mohammed