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
3answers
938 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 ...
4
votes
1answer
534 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 ...
11
votes
2answers
1k views
Why do wildcards in GROUP BY statements not work?
I am trying to make the following SQL statement work, but I get a syntax error:
SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.*
Here, A is a wide table ...
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 ...
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 ...
1
vote
2answers
584 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 ...
119
votes
3answers
19k 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?
13
votes
3answers
2k 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, ...
10
votes
5answers
843 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 ...
8
votes
1answer
259 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 ...
7
votes
3answers
888 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
...
5
votes
2answers
670 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
2answers
275 views
Can I send a string over TCP using T-SQL?
I've set up some alerts, based on this article
Now I would like the Job that is triggered by the alert to send some information to a c# application (this application, can be running in the same ...
5
votes
3answers
1k 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.
...
3
votes
3answers
352 views
t-sql - combinatorics
I'm trying to find all possible character combinations in a variable length string.
For example: '--' would have 2^n = 2^2 = 4 possibilities, 'x-', '-x', 'xx', '--'
I think that essentially I need ...
2
votes
3answers
98 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
2answers
94 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
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
2answers
176 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
454 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
2answers
97 views
How to link each column in sql?
I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase ...
0
votes
0answers
173 views
How to create table set from several XSD in SQL Server 2012 or with VS 2012
Is there any solution in T-SQL or in C# how to create XML schema collection in T-SQL or C#?
Problem is I cannot use:
USE Database_test
GO
CREATE XML SCHEMA COLLECTION Collection_from_XSD AS
N'
...
0
votes
1answer
98 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
1answer
84 views
how cursor implementations are different for each cursor type in sql server
In Oracle, there are only 2 types of cursor i.e. Implicit and Explicit cursor. Which is easy to understand. But, in SQL Server there are 4 Cursor AFAIK i.e. Static,Dynamic,Forward Only and Scroll. ...
