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/).
2
votes
2answers
463 views
Bulk grant execute permissions for scalar functions & stored procedures
We have a situation where we have restored a large number (50+) of MSSQL2008 databases from backup and most if not all of them require that a user is granted EXECUTE permissions on scalar functions ...
5
votes
1answer
521 views
Should I add SET NOCOUNT ON to all my triggers?
It is fairly common knowledge that you should have
SET NOCOUNT ON
by default when creating new stored procedures.
Microsoft has changed the default template to include this in 2012. I thought ...
4
votes
1answer
3k views
How can I strip non-numeric characters out of a string?
Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type.
One ...
8
votes
2answers
239 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 ...
3
votes
0answers
528 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 ...
2
votes
0answers
276 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
337 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
86 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 ...
9
votes
5answers
575 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
312 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
420 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 ...
2
votes
1answer
429 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 ...
2
votes
2answers
1k 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
334 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 ...
9
votes
4answers
5k views
Logical operators OR AND in condition and order of conditions in WHERE
Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will ...
1
vote
1answer
165 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
2answers
158 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 ...
1
vote
2answers
851 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 ...
3
votes
2answers
2k views
How can I get a list of column names and types from a resultset?
Say I get a resultset back from the following query:
select *
from sys.database_files;
For any given resultset, I want to be able to query the column names and types so I can then create tables to ...
5
votes
1answer
196 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.
2
votes
1answer
305 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 ...
7
votes
2answers
1k views
Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?
I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns.
As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
2
votes
1answer
56 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 ...
10
votes
5answers
808 views
Does it make sense to use SQL Server's bracket notation in hand written code?
Code generators tend to be simpler when they generate output using the new Microsoft bracket notation ([]) for nearly everything.
When I first saw it, I though wow a reincarnation of the somewhat ...
3
votes
2answers
129 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
556 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 ...
7
votes
2answers
1k 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
...
2
votes
2answers
230 views
Creating a new role
How do I create a new role in my database with my first name which is Denise, and add read only access on the HR user's EMPLOYEES table to this role.
6
votes
2answers
348 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 ...
2
votes
1answer
428 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 ...
7
votes
3answers
1k 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 ...
1
vote
2answers
88 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:
...
0
votes
1answer
96 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
2answers
119 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
609 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
354 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
...
2
votes
1answer
77 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
206 views
What can cause an UPDATE statement to execute endless?
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
457 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
653 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 ...
1
vote
1answer
438 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
254 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 ...
2
votes
1answer
129 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 ...
8
votes
2answers
874 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, ...
3
votes
2answers
327 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
191 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 ...
0
votes
1answer
149 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 ...
5
votes
1answer
643 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
676 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
193 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 ...
