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/).
3
votes
0answers
526 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
2answers
828 views
Transact SQL using WITH in CREATE VIEW
I want to create VIEW using WITH clauses, but really can't find any references on correct syntax.
I want smth like this
WITH TempTbl AS (SELECT ...)
CREATE VIEW SomeView
SELECT *
FROM TempTbl
And ...
2
votes
2answers
240 views
Make sure two columns are sorted in the same order
I have a table like this:
CREATE TABLE T (
ID INT NOT NULL IDENTITY PRIMARY KEY,
CreateDateTime datetime2 NOT NULL,
--other columns
)
How can I make sure that ID and CreateDateTime are both ...
2
votes
1answer
609 views
SQL Server 2012 is putting [brackets] around Table and Column Names
Is there anyway to turn that off? I'm unable to find anything in options, and they're really a distraction.
2
votes
2answers
77 views
Why do I need to use a sub query to filter down a grouped select?
If I do this --
SELECT dv.Name
,MAX(hb.[DateEntered]) as DE
FROM
[Devices] as dv
INNER JOIN
[Heartbeats] as hb ON hb.DeviceID = dv.ID
WHERE DE < '2013-03-04'
GROUP BY dv.Name
...
2
votes
2answers
113 views
SQL SELECT Kill FROM problem
$sql = "SELECT Kill FROM tbl_pvporderview";
Problem is that il lend up with : Incorrect syntax near the keyword 'Kill'.
Cuz kill is a TSQL command... any way to bypass it?
I cant change the column ...
2
votes
2answers
387 views
Is there any way to speed up a query with 3x UNION on the same large table?
Assuming that the following query is legitimate and executes successfully, is there any way to speed it up?
SELECT foo from T1 --T1 is a huge (>5e6 rows) table with no indexes
UNION
SELECT bar ...
2
votes
2answers
462 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 ...
2
votes
3answers
388 views
Inserting rows into other table whilst preserving IDENTITY
I'm performing ETL logic on a SQL Server table. I'm going to be synchronizing data from one table to another. For all of the records that need to be added to the target table based on the source ...
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
179 views
return percentage of records based on value in column
I'm working on a sql query that needs to get a percentage of the records based on the value of a particular column. The percentage is given by the user. A stripped down version of the query is below
...
2
votes
2answers
155 views
Defrag a HEAP by creating clustered index and immediately dropping it
I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
2
votes
2answers
251 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
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
78 views
How to get dependencies order
I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem ...
2
votes
3answers
82 views
Programmatically find indexes that cannot be rebuilt online
I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included.
Is ...
2
votes
1answer
50 views
Synchronize Queries Retrieving Sessions And Locks From DMVs
I have a SQL script that I run when I want to see what is happening in the database. The script has many queries that return information from DMVs, but the two I use most are "Requests" ...
2
votes
1answer
427 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
1answer
330 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 ...
2
votes
2answers
471 views
Filters Joins or Where clauses TSQL
Where should i put the filters on a tsql query
SELECT a.colum FROM A
JOIN B ON A.ID = B.ID AND B.STATUS = 1
OR
SELECT a.colum FROM A
JOIN B ON A.ID = B.ID
WHERE B.STATUS = 1
Im ...
2
votes
2answers
80 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 ...
2
votes
2answers
933 views
SQL Server : how to drop all constraints on a table in T-SQL
I´d like to drop all constraints on all tables in my database, because I need to change the relationships of many of the tables.
Is there any way to do that with T-SQL? I cannot find any solution ...
2
votes
1answer
424 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 ...
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 ...
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
438 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. ...
2
votes
2answers
673 views
Why is query using Clustered Index when it shouldn't?
Let us presume I have a table named Category in a SQL Server 2005 database. Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)). There is obviously a clustered ...
2
votes
5answers
156 views
How can I store function names to be executed on a subset of rows?
I have a table, accounts, which contains the vast majority of the data I am concerned with. accountTypes contains different types of accounts (Business, Personal, etc.). I want to perform calculations ...
2
votes
1answer
185 views
exist (select * from my table)
My colleague suggests that Query 1 should run faster than Query 2. If it's true, please anyone explain me the reason. Thanks.
Query (1):
IF ( SELECT COUNT(UnitTrustCounterId)
FROM ...
2
votes
3answers
2k views
Questions about handling NULLs and empty strings in nvarchar and numeric fields
I understand that questions similar to these pop up often around here. I have searched before posting these but I didn't find any QA threads that completely answer my questions. In a table, I ...
2
votes
2answers
335 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 ...
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 ...
2
votes
2answers
664 views
AUTO_UPDATE_STATISTICS and FULLSCAN in SQL Server 2008 R2
Is it possible to force FULLSCAN when statistics are updated automatically by SQL Server 2008 R2?
If not, is a planned UPDATE STATISTICS WITH FULLSCAN the best way to keep statistics up-to-date?
...
2
votes
2answers
77 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 ...
2
votes
2answers
125 views
Help with tricky update statement
I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier.
I have two table variables:
DECLARE
...
2
votes
2answers
175 views
difference between UPDLOCK and FOR UPDATE
given the following code:
Set conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
conn.BeginTrans
...
//This is the line it is about
RS.Open "SELECT a,b FROM c FOR ...
2
votes
1answer
123 views
How are foreign keys resolved when creating tables in batch?
I am creating a database version management tool with the goal of being able to recreate a database from scratch using text/sql files contained in source control. As part of establishing a baseline ...
2
votes
2answers
274 views
How to map another server through SQL Server Management Studio
I am trying to map another server by giving the command
EXEC xp_cmdshell 'NET USE H:\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes'
I got an error with this network path not ...
2
votes
1answer
699 views
sp_execute expects parameter '@handle' of type 'int'
I'm trying to check in a stored procedure if a table exists in my destination database. If it does not then I will create the table useing the information_schema tables from the source database. ...
2
votes
2answers
156 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 ...
2
votes
1answer
304 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 ...
2
votes
2answers
549 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 ...
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 ...
2
votes
1answer
155 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 ...
2
votes
2answers
163 views
What is an industry standard method for verifying openquery connection is open prior to running queries?
I'm a programmer, but this question is more specifically related to databases so I'm posting it among this community. It could very well cross over into stackoverflow as well.
I'm seeking an ...
2
votes
2answers
235 views
Search on part of a column in a SQL Server cross-database view
I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views ...
2
votes
1answer
106 views
Execute sp_WhoIsActive as a query
I am at a client where they don't allow Adam Machanic's sp_WhoIsActive stored procedure to be installed anywhere on the SQL server. Does anyone have any suggestions for running it as a query instead?
2
votes
2answers
426 views
Merge two records in the same table, keeping foreign key relationships intact for both
I have a table, called SITES, that has three columns, lets say it looks like this:
ID Name Path
1 Google http://www.google.com/
2 Microsoft http://www.microsoft.com/
I also have ...
2
votes
2answers
163 views
T-SQL Naming Standard
Is there a recommended (official or not) style from Microsoft regarding naming and casing in T-SQL?
If there isn't one from Microsoft then is there one with broad acceptance?
2
votes
1answer
673 views
How to remove xmlns from child elements with FOR XML
I have a TSQL query that I use to generate a SOAP document. I am using two namespaces. Namely:
- http://schemas.xmlsoap.org/soap/envelope/ for "packaging" the soap document
- A custom one that ...
