May 6, 2014 at 10:15 am
I have 2 identical tables one contains current settings, the other contains all historical settings.
I could create a union view to display the current values from tableA and all historical values from tableB, but
that would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
May 9, 2014 at 2:50 am
Budd,
I am not sure what you are trying to acheive. When you say "one joined or conditonal statement" what would the condition be? If it is a particular ID from Table A, then you would have to pass that as a parameter or variable. If you want to want all values from both tables, that is the statement you have without the WHERE clauses.
If you can explain more about what it is you want I should be able to help.
Regards,
Ash
May 9, 2014 at 2:59 am
What does tblid represent?
How many previous settings rows are there for a tblid?
How do you want the output to look like (Why PIVOT?)
Please supply table DDL, sample data and expected output.
Far away is close at hand in the images of elsewhere.
Anon.
May 9, 2014 at 7:34 am
Budd (5/6/2014)
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from tableA and all historical values from tableB, but
that would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
As David said providing ddl and sample data would be a big help.
Why not just use a left join?
DECLARE @tblid int = 501
SELECT ta.[Columns], tb.[Columns]
FROM TableA ta
left join TableB tb on tb.tblid = ta.tblid
where ta.tblid = @tblid
Everything in ta is current and the values in tb are previous.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2014 at 7:41 am
OK, I should not have suggested the possibility of using PIVOT for this, I just did not know what to put in the subject line, and maybe this was Not as well explained as I had thought.
Here is a sample including table layouts data and a UNION select query that demonstrates one way that I am able to achieve the correct results.
My Question; Can this be done without using a UNION, How, and Which is the best? After all this is Microsoft there's always more than one way to do it.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE dbo.TableA(
tblid INT IDENTITY(1,1) NOT NULL,
fld1 varchar(200) NOT NULL,
fld2 varchar(200) NOT NULL,
fld3 varchar(200) NOT NULL,
fld4 DATETIME NOT NULL)
CREATE TABLE dbo.TableA_hist(
tblid INT NOT NULL,
fld1 varchar(200) NOT NULL,
fld2 varchar(200) NOT NULL,
fld3 varchar(200) NOT NULL,
fld4 DATETIME NOT NULL,
CreDT DATETIME NOT NULL) --DEFAULT getdate() This would be the normal setting
SET IDENTITY_INSERT [TableA] OFF
INSERT INTO dbo.TableA
VALUES('App Setting For Reviews','TRUE','Hulk, Jim','2014-05-06 12:55:32.307')
SET IDENTITY_INSERT [TableA] ON
INSERT INTO dbo.TableA_hist VALUES
(1,'App Setting For Reviews','TRUE','Hulk, Jim','2014-05-06 09:31:53.820','2014-05-06 12:55:03.460'),
(1,'App Setting For Reviews','FALSE','Hulk, Jim','2014-05-06 09:25:13.543','2014-05-06 09:31:36.060'),
(1,'App Setting For Reviews','TRUE','Hulk, Jim','2014-05-06 09:19:03.287','2014-05-06 09:24:54.933'),
(1,'App Setting For Reviews','FALSE','Norris, Chuck','2014-05-06 08:18:30.763','2014-05-06 09:18:49.020'),
(1,'App Setting For Reviews','TRUE','Kent, Clark','2013-08-09 08:30:41.273','2014-05-06 08:48:46.580')
DECLARE @tblid int = 1
SELECT fld1,fld2,fld3,fld4,CreDT,'PreviosSetting'
FROM TableA_hist tb
WHERE tblid = @tblid
UNION
SELECT fld1,fld2,fld3,fld4,0,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
ORDER BY fld4 desc
DROP TABLE dbo.TableA
DROP TABLE dbo.TableA_hist
May 9, 2014 at 7:51 am
Budd I think that is the perfect way to do what you want. Why don't you want to use the UNION clause?
Ash
May 9, 2014 at 8:14 am
I have a feeling the reason is because of performance? Have you tried changing your UNION to UNION ALL? It seems that since you have a date in there you that all rows are effectively unique. Make sure you understand the difference before you use this though.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2014 at 8:50 am
Simply looking for alternatives and better performance.
I have gotten myself into a bad habit of only using UNION to avoid duplicates and had quickly brushed that thought away. As I think it over now I can see how the additional over head of filtering out duplicates could be costly, but now that you bring that to my attention I've run a little simple testing. When I run both UNION and UNION ALL at the same time and review the execution plans, they are both at 50%. If this is because the size of the selection is not enough to matter, how to I determine the size (aka volume of data) that it will take to make a difference between the 2?
But that is really another question and I am still open to ideas of different queries to produce the same results.
May 9, 2014 at 8:54 am
Budd,
I think with such a simple query performance would be best improved with good use of indexing. If possible create a clustered index on the column in your WHERE clause, or if that is not possible then a non-clustered index with the other columns in your SELECT clause as included columns.
Hope that helps,
Ash
May 9, 2014 at 9:01 am
Budd (5/9/2014)
Simply looking for alternatives and better performance.I have gotten myself into a bad habit of only using UNION to avoid duplicates and had quickly brushed that thought away. As I think it over now I can see how the additional over head of filtering out duplicates could be costly, but now that you bring that to my attention I've run a little simple testing. When I run both UNION and UNION ALL at the same time and review the execution plans, they are both at 50%. If this is because the size of the selection is not enough to matter, how to I determine the size (aka volume of data) that it will take to make a difference between the 2?
But that is really another question and I am still open to ideas of different queries to produce the same results.
It is not about when or when not to use UNION ALL it is about knowing what is appropriate for the results. If you have a result set where duplicates are ok (or each row is unique) you should use UNION ALL. Not because it is acceptable here but because it will perform better. It performs better because it does not have to check for uniqueness. On a small dataset it isn't likely to make much (if any) measurable difference.
When tackling performance problems you need to look at the whole picture. There are a lot of factors that come into play when determining performance improvements.
If this was my query I would almost certainly use a UNION or UNION ALL for this type of thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply