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
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
338 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
167 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 ...
1
vote
1answer
289 views
bitwise operators effecting 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 ...
3
votes
3answers
2k 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
159 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
859 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
197 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
307 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
130 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
562 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
1answer
244 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
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 ...
6
votes
2answers
350 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
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:
...
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 ...
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
615 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
358 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
302 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
362 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
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 ...
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
463 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
670 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 ...
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
...
117
votes
3answers
18k 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
441 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 ...
1
vote
2answers
948 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 ...
8
votes
2answers
885 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
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 ...
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
225 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
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 ...
1
vote
1answer
195 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
646 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
684 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
194 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 ...
2
votes
1answer
3k 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
156 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 ...
5
votes
1answer
381 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 ...
2
votes
2answers
473 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 ...
3
votes
3answers
778 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
83 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 ...
2
votes
1answer
5k 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]
...
2
votes
1answer
443 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
428 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 ...
