Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
3,046
questions
1
vote
2
answers
3k
views
Getting the metadata about a Query and a Stored procedure in SQL Server
I would like to get all the names of the columns in a given query [SQL Textual Query] and also the columns that are in the SELECT clause in a Stored Procedure.
Is there any ways other than using ...
- 443
4
votes
2
answers
965
views
Tool to create a TSQL script from a SQL Server 2008 trace file
Are there any tools available to create an executable tsql script from a SQL Server trace file?
Is there an easy manual way?
- 41
4
votes
2
answers
9k
views
Solving high page load time using SQL Profiler
I'm looking into a performance of a login page taking 10-13 seconds to load. I'm using a DEMO database to test the performance and tweak to see any improvement. I'm using SQL Profiler to capture what ...
- 377
4
votes
2
answers
447
views
switch TOP clause between percentage and rows by parameter
For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows.
I have two ideas
Calling two different sub stored procedure based on passed ...
- 2,403
14
votes
2
answers
51k
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 ...
- 323
0
votes
1
answer
287
views
How to find "outer join in the where" syntax in SQL 2005 using cmptlevel 80?
Well last Christmas I was very happy, because we ceased support for SQL Server 2000.
I could stop twisting my brain and use friendly analytical functions. (Believe me, when it comes to migrate stored ...
- 12.2k
3
votes
2
answers
2k
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?
...
- 131
16
votes
3
answers
14k
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 ...
- 14.6k
2
votes
2
answers
956
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 ...
- 183
1
vote
2
answers
459
views
How do I record SQL transactions with SQL Server Express
With a test migration from 2000 to 2008R2 Express, one function from the client application fails silently. I'd like to find out what SQL is running and why it fails.
I think I would have attacked ...
- 39.7k
9
votes
3
answers
11k
views
Using Row_Number to find consecutive row count
I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row
If my data looks like this
724
727
728
733
735
737
743
747
...
- 91
0
votes
1
answer
774
views
Small Squares or Blocks after string in table field
My problem. I'm running SQL Server 2005 in a Development (Dev) and Production (Prod) environment. I created a view that extracts data from a Navision database to SQL using a pass-through query ...
Chris D.
2
votes
1
answer
216
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 ...
- 151
0
votes
1
answer
161
views
What would cause the speed difference between servers?
I have this query on my dev server:
select
snStudyNumber as ProjectNumber
from CDB.cdb.Quotes q
inner join CDB.cdb.LineItems l
on q.PK_quID=l.FK_quID
inner join CDB.cdb....
- 3,028
2
votes
2
answers
263
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 approved ...
- 121
4
votes
1
answer
2k
views
What is SQL/PSM and how does it differ from other versions of SQL?
I have searched SQL/PSM and I have learned that it stands for 'Structured Query Language/Persistent Stored Modules', but what I want to understand is what that really is and how it differs from T-SQL ...
0
votes
1
answer
546
views
How to start and then stop one cyclic process
I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) ...
- 6,702
39
votes
5
answers
157k
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 ...
- 6,702
3
votes
1
answer
201
views
What are some efficient ways in using COUNT(*) with WHERE
Instead of calling COUNT(*) with a WHERE clause, is there anything one can do to make this work more efficiently on the server? I would like to get this count on complicated joins, just wondering the ...
- 31
3
votes
1
answer
2k
views
Conversion of varchar data to datetime failing
We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering
As part of this effort, we are taking data from the base Entity table and ...
- 15.8k
5
votes
1
answer
2k
views
T-sql: Select most specific match from table
Apologies for the horrible title. I couldn't come up with a better way to briefly describe the problem.
Here's the scenario:
Users can set up service instructions to be attached to a work order ...
- 169
4
votes
4
answers
750
views
Cannot reproduce client SQL 2008R2 error: subqueries are not allowed
EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?"
We have a client that reports error "1046 subqueries are not allowed in this context. Only ...
- 41
5
votes
3
answers
2k
views
How do I row count into groups?
I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them ...
- 51
5
votes
1
answer
1k
views
SELECT INTO creates a new table?
I have an original table MessageQueue with six columns. When I try to do a SELECT INTO that table it creates a new (local) table called <network domain\user name>.MessageQueue with only three ...
- 333
6
votes
1
answer
1k
views
How to get text for default language
I have so tables:
and so data at Language table:
and so data at Text table:
I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
- 6,702
13
votes
1
answer
4k
views
Generating large strings for test data
I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the ...
- 6,353
23
votes
3
answers
13k
views
Why is `SELECT @@IDENTITY` returning a decimal?
I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.
INSERT INTO Customers (
Type, Name, Address, ...
- 559
4
votes
1
answer
847
views
Does T-SQL MOVE actually move the database, or just copy?
I'm a bit confused over the T-SQL command MOVE. I have a statement saying:
MOVE
N'Prod_Database_Data'
TO
N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Database.mdf'
...
- 333
8
votes
3
answers
636
views
When are procedural queries absolutely necessary?
I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not ...
- 42.1k
2
votes
2
answers
6k
views
Getting current date as part of an Stored Procedure
I have the following SP:
SELECT
moncallAdd.FirstListing,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
DATEADD(...
- 39
10
votes
2
answers
16k
views
Difference between EXEC() and sp_executesql with no parameters?
I just learned about the new sp_executesql stored procedure. I like the fact that there's a way to execute parameterized code from within SQL Server.
But, I'm wondering what the difference is ...
- 6,353
7
votes
2
answers
11k
views
Find all ancestor nodes of a HierarchyId using a single SQL statement?
I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID. Every solution I've seen using HierarchyID seems to use either a CTE or a variable. Is there a way to do this ...
- 6,353
2
votes
2
answers
267
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.
1
vote
2
answers
203
views
Determine free space on "nonfixed" drive
I am writing a stored procedure that needs to send an email when a dvd rw drive is almost full. I found
master.sys.xp_fixeddrives
but it only shows free disk space for fixed hard drives. I have not ...
- 113
3
votes
2
answers
2k
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
I'm ...
- 195
3
votes
5
answers
288
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 ...
- 243
3
votes
2
answers
556
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 ...
- 133
6
votes
2
answers
2k
views
How to change SQL sever configuration manager settings using TSQL?
SQL server configuration manager is used to configure certain settings like connection protocols, service start up, etc...
Is it possible to make these changes that is done in SQL server ...
- 5,952
5
votes
1
answer
1k
views
Why can the target of the OUTPUT INTO clause not be a view?
Here is my statement:
DELETE FROM table1
OUTPUT deleted.col1, deleted.col2
INTO view1;
It gives me this error:
The target 'view1' of the OUTPUT INTO clause cannot be a view or common table ...
- 1,875
4
votes
2
answers
5k
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 ...
- 43
3
votes
2
answers
955
views
MS SQL : Use Computed value to Compute other values
I'm having problem with generating results which are computed from data that exists in database. This is the case
I have Coupons that can be amount or percent, if percent is 0 then amount is used
...
- 173
35
votes
7
answers
78k
views
How can I tell if a SQL Server database is still being used?
We're looking to decommission a SQL Server instance which has a couple databases still remaining on it.
How can I tell if they are still being used by users or a web application?
I found a forum ...
- 1,040
5
votes
4
answers
5k
views
Which problems arise, declaring the size of all varchar parameters as max in stored proc?
Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time.
Increasing just the size of a column in a table is a rather simple task.
...
- 12.2k
3
votes
1
answer
499
views
Implementing a PIVOT query
How can I pivot the following SELECT statement:
SELECT sc.CID,
sc.CodeName as OverviewText,
scRAG.CodeName as RAGStatusText
FROM StatusCode ...
- 2,032
4
votes
2
answers
181
views
Randomizing table contents and storing them back in the table
I have a table with at least a million records in it. These rows were created by a custom app that reads several SharePoint site collections and stores the item urls in the table. Now, since we read ...
- 143
15
votes
5
answers
16k
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 ...
- 12.2k