New answers tagged t-sql
7
Is it “legal” TSQL to CREATE TABLE #SomeTable and then DROP TABLE #SomeTable repeatedly in a script?
No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:
IF 1 = 1
BEGIN
...
4
I skipped the code example, and jumped to what seems to be the real question here
On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX). Could there be something I'm missing here?
For the record, this is a clustered index.
You certainly are missing something, ...
0
Aaron had missed the spatial data types. Modify it like this.
AND ((c.system_type_id IN (34,35,99,241, 240)) -- image, text, ntext, xml, CLR types
OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary
AND max_length = -1))
That's the easy way. But it does include the datatype hierarchyid. I'm not sure if indexes on hierarchyid ...
4
All output of the case expression must be of the same datatype.
If maintaining the XML datatype for the valid XML data is worth a little manipulation of the invalid data, then one workaround could be to wrap the invalid data up like a declaration before casting it to XML:
cast('<?invalid ' + RunLog + ' ?>' as xml)
Also, you shouldn't need to use ...
1
There are many free tools - native and opensource available for comparing schema between 2 databases:
Open DBDiff ==> This is on Codeplex and works great.
SQL Admin Studio ==> This is now a free tool.
Hidden Gem from SQL 2005 and up : tablediff.exe (you can find this in the COM directory of your SQL Server install folder) Compare schemas: Regular or ...
0
The versions of Visual Studio that support "Database Projects" should provide a schema comparison tool that can be used against two different "live" copies of the database (as opposed to a "live" copy and the copy that you are working on in Visual Studio.)
You don't need to create a full-blown project (although Microsoft pushes you to do so since that is ...
0
I personally don't like free tools messing with my databases...
like others said before me, the best tool for comparing is Redgate Compare
(another option from Redgate is their new Source Control - if you have a repository).
You have another options as a developer:
Develop your own .NET tool using SMO Scripter Class Methods
Scripting Basics.
Then you will ...
0
Rather than looking at the UI, check out the underlying system table:
SELECT name, is_disabled FROM sys.triggers
4
This might not be the best approach to dealing for your problem, but it will certainly achieve the stated goal.
ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON [dbo].[Projects]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @TableName sysname
select @tablename = object_schema_name(parent_id) + '.' + object_name(parent_id)
from ...
1
Dump isnulls into an inner query and use them outside maybe to avoid multiple isnulls?
SELECT A
FROM (SELECT ISNULL (A, 0) FROM TABLEA) A
...
3
I have a similar query showing a TON of encapsulating ISNULL statements. I need the null values to represent items that have not been touched (as many columns required distinct identifiers for alterations). I tried a million different things to get around it and in the end just ended up with an annoyingly long code laced with a bunch of ISNULLs. The ...
0
This is definitely not the best of ideas, but you could always use a table, sorry if this is incorrect syntax (more trying to get the idea across) of a terrible idea in general as I'm not the most adept at SQL, just trying to help spitball ideas for you!
CREATE TABLE @ClassTable
DECLARE @Counter int = 0;
While (@Counter < 3)
{
SELECT INTO ...
6
No, there is no way to tell SQL Server to treat all NULL float values as zero. You will have to surround these expressions with ISNULL() or, better yet IMHO, COALESCE(). You can do this in a view so you don't have to repeat it in every query.
1
If the output is a single result set that always has the same columns (e.g. TableName, RowCount) you could write a table valued CLR function and take the query string as parameter. There is no real clean way to do this in plain T-SQL.
1
The way you're doing it is about the fastest, easiest and most comfortable way you're going to find to pass a TVP to a stored procedure, sorry. You can't pass expressions to stored procedure parameters, even simple things like:
EXEC sp_who2 N'act'+'ive';
Fails with:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.
In order to use an ...
1
Alright, found a way to do the same with Table Valued Types
CREATE FUNCTION [dbo].[DateRange]
(
@StartDate date,
@EndDate date,
@Location TVT.Location ReadOnly,
@Device TVT.Device ReadOnly
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM MyTable
WHERE Date < @EndDate
...
2
Use an OR to check if either the parameter matches the value, or both the parameter and the value are NULL, like this:
WHERE
(
[TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status
OR
([TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND @Status IS NULL)
)
You could also use a CASE statement as your IF if you really wanted to, but I think it's easier to ...
3
The most efficient way according to my own testing is:
*Remark: only valid for NON-NULLABLE columns, as commented by Aaron.
CREATE FUNCTION [dbo].[DateRange]
(
@StartDate date,
@EndDate date,
@Location varchar(25),
@Device varchar(25)
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM MyTable
WHERE Date < @EndDate
AND Date ...
3
Like so :
CREATE FUNCTION [dbo].[DateRange]
(
@StartDate date,
@EndDate date,
@Location varchar(25) = NULL,
@Device varchar(25) = NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM MyTable
WHERE
Date < @EndDate
AND Date > @StartDate
AND ( ...
6
This can be done using the PIVOT function, but since it sounds like you want to change the query based on the schemaId, then you will want to use dynamic SQL.
If you had a known number of values or knew the columns for a specific schemaID, then you could hard-coded the query. A static query would be:
select loannumber,
[First Name],
[Middle Name],
...
3
You can use this pattern. For more schema fields, just expand on the 2nd to last line with all the schema field names.
select *
from (
select l.LoanNumber, s.FieldName, f.FieldValue
from Loans l
join Schemafields s on s.SchemaId = l.SchemaId
join LoanFields f on f.LoanId = l.Id and f.SchemaFieldId = s.Id) p
pivot (
...
Top 50 recent answers are included
