Tagged Questions

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

learn more… | top users | synonyms

8
votes
2answers
614 views

How does SQL Server store the information in Job Schedule Properties?

I know there is SELECT * FROM msdb..sysjobschedules, but that just shows the next time a job is scheduled to run. I want to know how this is all determined and stored. So where does it store the ...
9
votes
2answers
3k views

SQL Server Decimal(9, 0) vs INT

One of our customers uses for some columns the datatype DECIMAL(18,0) in his SQL Server 2008R2 database. Because the columns grow quite slowly, he recently proposed to change the datatype to ...
10
votes
2answers
3k views

Good way to call multiple SQL Server Agent jobs sequentially from one main job?

I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC ...
2
votes
0answers
404 views

SQL Server : where with cast(value as varbinary) vs cast(value as varbinary(300))

Are there any theoretical performance differences between these queries in the scenarios below. Scenarios are: MyTable has Value nvarchar(300) MyTable has Value nvarchar(max) Q1: declare @p ...
2
votes
2answers
523 views

Reorganize Indexes T-SQL

I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on ...
3
votes
2answers
128 views

When did a specific database come online?

How can I determine when the state of a specific database switched to online? Background: I want to determine how long the database is in use to find out how reliable sys.dm_db_index_usage_stats ...
10
votes
5answers
1k views

UPDATE with JOIN on 100mm records, how to do this better? (in T-SQL)

I need to update 100 million records in a single table, in effect, normalizing the table by replacing the varchar value of a column with simply an ID. (I say "replacing" but really I'm writing the ID ...
3
votes
1answer
630 views

DENY EXECUTE on one schema object hides other objects in same schema

I have five stored procedures in a schema [XTR], the users are all in one AD group [AD\Users] and have db_datareader and public assigned to them. On one of the stored procedures I don't want to give ...
4
votes
1answer
1k views

Using FLOATs with RAISERROR

I am using RAISERROR() to provide some basic Unit Testing functionality (as here), but I am frustrated by the inability to use FLOATs in the error message. I know I can cast the float to a string, but ...
3
votes
1answer
786 views

Can I Rebuild indexes in a DB except for one table?

I have a table in my database that is constantly monitored by an application server for changes. Unfortunately rebuilding the indexes connected with this table breaks the app server's connection. This ...
4
votes
2answers
6k views

A Script to insert dummy Data in all tables of Database

I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...
2
votes
1answer
1k views

Looking for a Trigger to change inserting strings?

I have an MSSQL Server 2008 Database. There are some tables in my Database like below : table1, table2, table3, .... each table has a NVARCHAR(50) field (Name) Now, I wanna change the strings that ...
1
vote
1answer
444 views

Return a percentage of the full record set

I'm pulling back a list of orders but I need to only display a percentage of the records. for example we want the top 30 or top 50. The percentagbe is variable so I'd like to not use TOP. I tried ...
2
votes
1answer
865 views

Bitwise operators affecting performance

I have recently seen some stored procs coming my way that contain BITWISE operators that are causing the optimizer problems. I know from playing with execution plans that these are the cause of the ...
5
votes
4answers
15k 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
2answers
568 views

Design best practices for last tracking with indexed view

My tables structure is below : TbDoc (ID int , ...) TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...) I want to have indexed view to get last TbDocActions columns for ...
3
votes
2answers
5k views

Create new function by code if it doesn't exist

I want to create new function by script in my database. The script code is below: IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return; CREATE FUNCTION ...
5
votes
1answer
275 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
686 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
345 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
311 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
740 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
124 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
220 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
823 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
653 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
308 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
2k 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
4k 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 ...
9
votes
2answers
5k 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
54k 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
2k 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
313 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
499 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
950 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
418 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
216 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
368 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
426 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 ...