Ms Sql Server Technology Discussions

[mtkr]

[quote name='Kaarthikeya' timestamp='1358522141' post='1303130207']
I have a result set something similar to this:

ID Name Start_Date End_Date New_ID New_Name
101 XYZ 2008-04-09 2011-11-14 101 ABC
101 XYZ 2011-04-09 2020-12-31 201 DKC

ID comes from one Table and New_ID comes from another table. Also, two make relation between these two tables, I do have two other tables involved.

I would like to retrieve only the record that has the New_ID based on the Date.
I am using the following query:

[CODE]
SELECT a.ID, a.Name, Max(b.Start_Date), Max(b.End_Date), b.New_ID, b.New_Name
FROM Table1 a INNER JOIN Table2 c ON a.C_ID = c.ID
INNER JOIN Table3 d ON d.ID = c.D_ID
INNER JOIN Table4 b ON b.ID = d.B_ID
[/CODE]
[/quote]



mama kudirithe ne source tables eyyagalavaaa!!!?



use some thing like this



where dt In
(select max(dt) from table group by id)

[deals2buy]

[quote name='bad__boy' timestamp='1358522875' post='1303130307']
First chusanu ba...adhe kanapadaledhuuu...so andukee installation emina pro vundho emo ane Uninstall chesanuuu
[/quote]
hmm...aithe malli install chesi chudu...inthaki em edition nuvvu install chesedi?
Express or Developer or Enterprise?
[quote name='mtkr' timestamp='1358523024' post='1303130337']


install cheseappudu ea ea components intall cheyyalo aduguthadi anukuntaa..
i gueess akkada miss kotti untavvv....
[/quote]may be...but unless manodu evaina components untick cheste tappa SSMS miss avvadaniki chance ledu mari naaku telisi
[quote name='mtkr' timestamp='1358523077' post='1303130345']
mama kudirithe ne source tables eyyagalavaaa!!!?



use some thing like this



where dt In
(select max(dt) from table group by id)
[/quote]already paina max chestunna kada mama...malli ee subquery enduku?

[mtkr]

[CODE]
create table #table1
(
id int,
name varchar(10)
)
[/CODE]


[CODE]
create table #table2
(
id int,
name varchar (10),
sdate datetime
)
[/CODE]


[CODE]
insert into #table1 values ( 1, 'abc')
insert into #table1 values ( 2, 'def')
insert into #table1 values ( 3, 'erf')
[/CODE]


[CODE]
insert into #table2 values ( 1, 'abc', '2-10-2011')
insert into #table2 values ( 1, 'qwe', '3-10-2011')
insert into #table2 values ( 3, 'qzxc', '8-11-2012')
insert into #table2 values ( 3, 'ghjg', '12-12-2012')
insert into #table2 values ( 2, 'eas', '8-6-2011')
insert into #table2 values ( 2, 'pol', '6-4-2011')
insert into #table2 values ( 3, 'uio', '9-4-2012')
[/CODE]



[CODE]
select * from #table1
select * from #table2
[/CODE]



[CODE]
SELECT a.ID, a.Name, b.id, b.sdate, b.Name
FROM #table1 a INNER JOIN #table2 b
ON a.ID = b.ID
WHERE b.sdate IN
(SELECT MAX(sdate)FROM #table2 GROUP BY id)
[/CODE]


output

[CODE]
a.id a.name b.id b.sdate b.name
1 abc 1 2011-03-10 qwe
2 def 2 2011-08-06 eas
3 erf 3 2012-12-12 ghjg
[/CODE]

[mtkr]

[quote name='Kaarthikeya' timestamp='1358523562' post='1303130444']
hmm...aithe malli install chesi chudu...inthaki em edition nuvvu install chesedi?
Express or Developer or Enterprise?
may be...but unless manodu evaina components untick cheste tappa SSMS miss avvadaniki chance ledu mari naaku telisi
already paina max chestunna kada mama...malli ee subquery enduku?
[/quote]

select list lo max chesthe entire colum data lo max isthadiii...

where condition lo pedithe frst filter chesi then select chestahdiiii....

[deals2buy]

[quote name='mtkr' timestamp='1358524159' post='1303130535']

select list lo max chesthe entire colum data lo max isthadiii...

where condition lo pedithe frst filter chesi then select chestahdiiii....
[/quote]
naaku already WHERE clause lo vere conditions unnayi

nuvvu cheppinattu WHERE lo MAX isthe teesukovatledu...so HAVING lo raasanu nuvvu cheppina query...but no use...it is showing two records

[mtkr]

[quote name='Kaarthikeya' timestamp='1358526072' post='1303130891']
naaku already WHERE clause lo vere conditions unnayi

nuvvu cheppinattu WHERE lo MAX isthe teesukovatledu...so HAVING lo raasanu nuvvu cheppina query...but no use...it is showing two records
[/quote]


teesukovatledhu ante error ostundaa??? r result crrt ga raavadam ledhaa??

[deals2buy]

[quote name='mtkr' timestamp='1358526230' post='1303130915']


teesukovatledhu ante error ostundaa??? r result crrt ga raavadam ledhaa??
[/quote]
WHERE clause lo teeskovatam ledu

HAVING lo teesukuntondi...but two records chupistondi instead of showing one

[mtkr]

[quote name='Kaarthikeya' timestamp='1358537825' post='1303132813']
WHERE clause lo teeskovatam ledu

HAVING lo teesukuntondi...but two records chupistondi instead of showing one
[/quote]

having ante unnavi annni isthadi gaa mama...
nuv em raasthunnavo ikkada veyyagala koncham...

[deals2buy]

[quote name='mtkr' timestamp='1358538460' post='1303132966']
having ante unnavi annni isthadi gaa mama...
nuv em raasthunnavo ikkada veyyagala koncham...
[/quote]
ya mari MAX ni WHERE CLAUSE lo use cheyyanivvadu kadaa?

SUBQUERY lo aithe same result vastondi

[deals2buy]

[b] Download Whitepaper – Introducing the BI Semantic Model in Microsoft SQL Server 2012[/b]


[url="http://technet.microsoft.com/en-us/library/jj735264.aspx"]Download Whitepaper – Introducing the BI Semantic Model in Microsoft SQL Server 2012[/url]

[deals2buy]

[b] DBCC RESEED Table Identity Value – Reset Table Identity[/b]

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.

[CODE]
DBCC CHECKIDENT (yourtable, reseed, 34)
[/CODE]

If table has to start with an identity of 1 with the next insert then table should be reseeded with the identity to 0. If identity seed is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.

[deals2buy]

[b] TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed[/b]

[b]“DELETE can be rolled back and TRUNCATE can not be rolled back”.[/b]
OR
[b]“DELETE can be rolled back as well as TRUNCATE can be rolled back”.[/b]

As soon as above sentence is completed, someone will object it saying either TRUNCATE can be or can not be rolled back. Let us make sure that we understand this today, in simple words without talking about theory in depth.

[i][b]While database is in full recovery mode, it can rollback any changes done by DELETE using Log files. TRUNCATE can not be rolled back using log files in full recovery mode. [/b][/i]

[i][b]DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.[/b][/i]

Let us understand this concept in detail.

In case of DELETE, SQL Server removes all the rows from table and records them in Log file in case it is needed to rollback in future. Due to that reason it is slow.

In case of TRUNCATE, SQL Server deallocates the data files in the table and records deallocation of the data files in the log files. If deallocated data files are overwritten by other data it can be recovered using rollback. There is no guarantee of the rollback in case of TRUNCATE. However, while using T-SQL following code demonstrates that TRUNCATE can be rolled back for that particular session.
First create test table which some data. Afterwards run following T-SQL code in Query Editor and test the effect of TRUNCATE on created test table.

[CODE]
BEGIN TRAN
TRUNCATE TABLE TestTable
-- Following SELECT will return TestTable empty
SELECT *
FROM TestTable
-- Following SELECT will return TestTable with original data
ROLLBACK
SELECT *
FROM TestTable
[/CODE]

[b]Summary :[/b] DELETE can be recovered from log file always if full recovery mode is set for database. TRUNCATE may or may not be recovered always from log files.

[deals2buy]

[b] What is – DML, DDL, DCL and TCL – Introduction and Examples[/b]


[b]DML[/b]
DML is abbreviation of [b]Data Manipulation Language[/b]. It is used to retrieve, store, modify, delete, insert and update data in database.
Examples: SELECT, UPDATE, INSERT statements

[b]DDL[/b]
DDL is abbreviation of [b]Data Definition Language[/b]. It is used to create and modify the structure of database objects in database.
Examples: CREATE, ALTER, DROP statements

[b]DCL[/b]
DCL is abbreviation of [b]Data Control Language[/b]. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.
Examples: GRANT, REVOKE statements

[b]TCL[/b]
TCL is abbreviation of [b]Transactional Control Language[/b]. It is used to manage different transactions occurring within a database.
Examples: COMMIT, ROLLBACK statements

[deals2buy]

[b] TRIM Function to Remove Leading and Trailing Spaces of String[/b]


Trim is one of the most frequently used operation over String data types. A developer often come across a scenario where they have the string with leading and trailing spaces around string. If your business logic suggests that the logs around the spaces are not useful they should be trimmed. However, in SQL Server there is no TRIM function. When a TRIM function is used it will throw an error.

For example, here is the script when executed it will throw an error.
[CODE]
-- The following will throw an error
DECLARE @String1 NVARCHAR(MAX)
SET @String1 = ' String '
SELECT TRIM(@String1) TrimmedValue
GO
[/CODE]
The above script will return following error:

[color=#ff0000]Msg 195, Level 15, State 10, Line 4[/color]
[color=#ff0000]‘TRIM’ is not a recognized built-in function name.[/color]

Let us not everything why this simple function is not implemented but try to resolve how we can achieve the result of the same function. SQL Server has two functions which when nested can give us the same result as a TRIM function.
1) RTRIM – Removes the Spaces on the right side (or leading spaces) of the string
2) LTRIM – Removes the Spaces on the left side (or trailing spaces) of the string

We can combine them as following and it will not throw an error.

[CODE]
-- The following will work
DECLARE @String1 NVARCHAR(MAX)
SET @String1 = ' String '
SELECT @String1 OriginalString, RTRIM(LTRIM(@String1)) TrimmedValue
GO
[/CODE]

Let us see the same concept in following SQL in Sixty Seconds Video:

[media=]http://www.youtube.com/watch?feature=player_embedded&v=1-hhApy6MHM[/media]

Additionally, if you want you can create a user defined function which is using RTRIM and LTRIM and can use the function when trim functionality is required.

[CODE]
-- Create Function
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO
SELECT dbo.TRIM(' String ')
GO
[/CODE]

Now when we are on the topic of the TRIM function, let me remind you one very important impact of this function if used in the WHERE clause. If any function is used in the WHERE clause, it will negatively impact on the performance of the query. SQL Server has to process the function on whole column leading Table Scan or Index Scan instead of Index Seek. This will increase the resource utilization and lead to poor performance. However, using this function in SELECT statement does not degrade performance much. In simple words – please be mindful of using any functions. Use the functions when you absolutely need it or enforcing business needs.

[chelsea]

[url="http://archive.msdn.microsoft.com/SQLExamples"]http://archive.msdn.microsoft.com/SQLExamples[/url]

[b] Common Solutions for T-SQL Problems[/b]