Ms Sql Server Technology Discussions

[SonyVaio]

[quote name='SonyVaio' timestamp='1359499302' post='1303198010']
Please evaraina ee ques ki answers cheyandi asap

1. ***Indexes valla RDBMS access speed slow down avuda? Nenu kadu anukuntunna, am i right?***
2. ***A relation in fifth normal form may not contain any anomalies, is this true/false?***
3. ***Parallel query processing speed is not significantly different from running queries in a non-parallel mode, True / false?***
4. ***When two or more attributes describe the same characteristic of an entity, they are synonyms, True / False?***
5. ***In an E-R diagram, strong entities are represented by double-walled rectangles, True / False?***
[/quote]

some one plz help me

[loveindia]

[quote name='SonyVaio' timestamp='1359499302' post='1303198010']
Please evaraina ee ques ki answers cheyandi asap

1. ***Indexes valla RDBMS access speed slow down avuda? Nenu kadu anukuntunna, am i right?***
2. ***A relation in fifth normal form may not contain any anomalies, is this true/false?***
3. ***Parallel query processing speed is not significantly different from running queries in a non-parallel mode, True / false?***
4. ***When two or more attributes describe the same characteristic of an entity, they are synonyms, True / False?***
5. ***In an E-R diagram, strong entities are represented by double-walled rectangles, True / False?***
[/quote]

1. Indexes are a good way to speed up RDBMS data querying. When used in excess number unnecessarily, they can cause troubles too.
2. Personally, I have or most ppl here wouldn't have tried normalization upto that level. that is like the highest level of normalization available after Boyce-Codd and Domain Key (DKNF).
3. don't know
4. don't know
5. don't know

[SonyVaio]

[quote name='loveindia' timestamp='1359518802' post='1303199253']

1. Indexes are a good way to speed up RDBMS data querying. When used in excess number unnecessarily, they can cause troubles too.
2. Personally, I have or most ppl here wouldn't have tried normalization upto that level. that is like the highest level of normalization available after Boyce-Codd and Domain Key (DKNF).
3. don't know
4. don't know
5. don't know
[/quote]
ok bro, will wait for other replies.

[deals2buy]

[quote name='SonyVaio' timestamp='1359499302' post='1303198010']
Please evaraina ee ques ki answers cheyandi asap

1. ***Indexes valla RDBMS access speed slow down avuda? Nenu kadu anukuntunna, am i right?***
2. ***A relation in fifth normal form may not contain any anomalies, is this true/false?***
3. ***Parallel query processing speed is not significantly different from running queries in a non-parallel mode, True / false?***
4. ***When two or more attributes describe the same characteristic of an entity, they are synonyms, True / False?***
5. ***In an E-R diagram, strong entities are represented by double-walled rectangles, True / False?***
[/quote]


1. Data retreival fast ga untundi but using too many of them may slow down.
2. DK/NF lo anomalies undavu which is the next phase of 5NF.
3. No idea
4. True
5. False - a rectangle depicts an entity, a double-walled rectangle depicts a weak entity, and a diamond within a rectangle depicts a composite entity.

PS: most of them Google cheste dorikinave...lets see if others respond to it

[Darling999]

SSIS COntainers


[b] SSIS Containers[/b]
[font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=2]

[/size][/font][font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3]
[b][color=#274E13]Integration Services Containers[/color][/b]
SSIS Containers are controls (objects) that provide structure to SSIS packages. Containers support repeating control flows in packages and they group tasks and containers into meaningful units of [url="http://sql-bi-dev.blogspot.com/2010/06/ssis-containers.html#"]work[/url]. Containers can include other containers in addition to tasks.

[b]Types of SSIS Container[/b]
SSIS provides four types of containers. [color=#660000]I'll explain these containers with example in my following Posts.[/color]
The following table lists the container types:

Container Type Container Description Purpose of SSIS Container Foreach Loop Container This container runs a Control Flow repeatedly using an enumerator. To repeat tasks for each element in a collection, for example retrieve files from a folder, running T-SQL statements that reside in multiple files, or running a command for multiple objects. For Loop Container This container runs a Control Flow repeatedly by [url="http://sql-bi-dev.blogspot.com/2010/06/ssis-containers.html#"]checking[/url] conditional expression (same as For Loop in programming language). To repeat tasks until a specified expression evaluates to false. For example, a package can send a different e-mail message seven times, one time for every day of the week. Sequence Container Groups tasks as well as containers into Control Flows that are subsets of the package Control Flow. This container group tasks and containers that must succeed or fail as a unit. For example, a package can group tasks that delete and add rows in a database table, and then commit or roll back all the tasks when one fails. Task Host Container Provides services to a single task. The task Host container encapsulates a single task. But this task is not configured separately in SSIS Designer. It is configured when you set the properties of the task it encapsulates.
Containers are fundamental to the operation of transactions, checkpoints and event handlers. Each container has some common properties that affect the usage of these features. Understanding these properties and what they do helps a lot in the developing SSIS packages.

Property Description DelayValidation A Boolean value that indicates whether [url="http://sql-bi-dev.blogspot.com/2010/06/ssis-containers.html#"]validation[/url] of the container is delayed until run time Disable A Boolean value that indicates whether the container runs DisableEventHandlers A Boolean value that indicates whether the event handlers associated with the container run FailPackageOnFailure A Boolean value that specifies whether the package fails if an error occurs in the container. FailParentOnError A Boolean value that specifies whether the parent container fails if an error occurs in the container. IsolationLevel The isolation level of the container transaction. The values are Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot. MaximumErrorCount The maximum number of errors that can occur before a container stops running. TransactionOption The transactional participation of the container. The values are NotSupported, Supported, Required.
[/size][/font]

[Darling999]

[font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3]SSIS Containers[/size][/font]

[font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3]Content post chestey sarigga raavatledu please see link[/size][/font]

[font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3][url="http://sql-bi-dev.blogspot.com/2010/06/ssis-containers.html"]http://sql-bi-dev.blogspot.com/2010/06/ssis-containers.html[/url]
[/size][/font]

[Darling999]

Indexes with Include


[b] What are indexes for?[/b]
[color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
We need to talk indexes first, before we get to INCLUDE. When you index, you’re creating a reference that your query can look at to find the WHERE and [url="http://www.midnightdba.com/Jen/2010/09/brief-intro-to-indexes-and-include/#"]JOIN[/url] terms quickly. So for example, for this query: [/background][/size][/font][/color][indent][size=3][background=transparent]
[color=#0000FF][size=1][background=transparent][background=transparent]SELECT[background=transparent] col1 [/background][color=#808080][background=transparent][background=transparent], [/background][/background][/color][background=transparent]col2
[background=transparent][background=transparent]FROM[background=transparent] dbo[/background][color=#808080][background=transparent][background=transparent].[/background][/background][/color][background=transparent]BORK[/background]
[background=transparent][background=transparent]WHERE[background=transparent] col3 [/background][color=#808080][background=transparent][background=transparent]=[/background][/background][/color][background=transparent] 6[/background] [/background][/background][/background][/background][/background][/background][/background][/size][/color][/background][/size]
[/indent][color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
You’d ideally want an index like this: [b]CREATE INDEX IX_BORK_111 ON dbo.BORK (col3)[/b] This index will help the query find the rows that it needs, but a nonclustered index like this is a separate structure from the table itself. Each time the SQL engine finds a “6″ in the index, it then has to hop over to the row in the table itself to get the values for col1 and co2…that’s called a “bookmark lookup”. Bookmark lookups are bad because they involve extra overhead – you have to read from the index AND the table, instead of just the index. [/background][/size][/font][/color][color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
To fix this, you can create a covering index – an index that covers all the columns you’ll need for your query:[b] CREATE INDEX IX_BORK_222 ON dbo.BORK (col3, col1, col2)[/b] Notice that I kept col3 as the lefthand index column, because it’s our search column. Covering indexes are wonderful, but they can[url="http://www.midnightdba.com/Jen/2010/09/brief-intro-to-indexes-and-include/#"]start[/url] to take up a lot of room. Imagine if the select statement had been [b]SELECT col1, col2, col3, col4 …. col10[/b]. Covering a query like that will make a rather wide index. INCLUDE can mitigate some of those space concerns. [/background][/size][/font][/color]
[b] Index structure made easy (I hope)[/b]
[color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
To understand INCLUDE, you first must understand (a little bit) the structure of an index. An index is organized in a b-tree hierarchy – each node of data (in the case of our index [b]IX_BORK_111[/b], col3) has two nodes beneath it – the left node higher in the sort range, and the right node lower, like this: [/background][/size][/font][/color][color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
……….5……..
…3……….7….
1…4……6…9.. [/background][/size][/font][/color][color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
3 and 7 are the child nodes of 5. 1 and 4 are the child nodes of 3. The lowest level nodes are called leaf nodes, so 1,4,6,and 9 are the leaf nodes. The SQL engine walks through the tree to find the values it needs… [/background][/size][/font][/color][color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
If we create our covering index like this: [b]CREATE INDEX IX_BORK_222 ON dbo.BORK (col3, col1, col2)[/b] then each node in the tree will contain values for col3, col1, and col2. That can take up a lot of space. But in our query, we don’t need to search based on col1 and col2….we only search based on col3. The other two columns are just there to be returned in the SELECT statement. [/background][/size][/font][/color]
[b] About INCLUDE [/b]
[color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
INCLUDE lets us make the index smaller, while still supplying our SELECTed columns. First, here’s the index declaration: [b]CREATE INDEX IX_BORK_333 ON dbo.BORK (col3) INCLUDE (col1, col2) [/b][/background][/size][/font][/color][color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
Each node in the tree will contain the value for col3. [b]But only the leaf level nodes will hold the values for col1 and col2[/b]. That’s how INCLUDE makes the index smaller – the rest of the tree doesn’t contain extra, unused [url="http://www.midnightdba.com/Jen/2010/09/brief-intro-to-indexes-and-include/#"]information[/url]. The SQL engine performs its search based on col3, finds the leaf level node(s) that match, and there at the leaf level is the rest of the information we need. [/background][/size][/font][/color][color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
[size=1][background=transparent] [/background][/size][/background][/size][/font][/color]
[b] Key Ideas Review[/b]
[color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
Key ideas mentioned: [/background][/size][/font][/color][list]
[*][size=3][background=transparent]
Indexes let queries find data faster.[/background][/size]
[*][size=3][background=transparent]
Nonclustered indexes are separate objects from tables.[/background][/size]
[*][size=3][background=transparent]
A covering index is ideal – it gives the query everything it needs, without having to touch the table itself (that’s called a bookmark lookup).[/background][/size]
[*][size=3][background=transparent]
Nonclustered indexes take up space, so you don’t necessarily want a lot of really wide covering indexes. Use with discretion.[/background][/size]
[/list][color=#333333][font=Helvetica, Arial,][size=3][background=rgb(248, 248, 248)]
One final point: Remember that indexes must be updated every time data is updated or inserted into the table. The more indexes, the longer your inserts and updates will take. Indexing is a balance between supplying enough to support your read operations, and keeping insert/update overhead[/background][/size][/font][/color]

[SonyVaio]

[quote name='Kaarthikeya' timestamp='1359560171' post='1303201027']


1. Data retreival fast ga untundi but using too many of them may slow down.
2. DK/NF lo anomalies undavu which is the next phase of 5NF.
3. No idea
4. True
5. False - a rectangle depicts an entity, a double-walled rectangle depicts a weak entity, and a diamond within a rectangle depicts a composite entity.

PS: most of them Google cheste dorikinave...lets see if others respond to it
[/quote]

Thanks

[deals2buy]

[quote name='DARLING...' timestamp='1359572246' post='1303202339']
[font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3]SSIS Containers[/size][/font]

[font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3]Content post chestey sarigga raavatledu please see link[/size][/font]

[font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3][url="http://sql-bi-dev.blogspot.com/2010/06/ssis-containers.html"]http://sql-bi-dev.bl...containers.html[/url]
[/size][/font]
[/quote]
ivi use chese chance naak eppudu vastundo?

[deals2buy]

[quote name='Guest' timestamp='1359487613' post='1303196666']



select T2.ID,T2.Name,T2.State from Table1 T1
JOIN(
select ID,Name,State, Max(InactiveDate)
From Table2
Group by ID,Name,State
) T2

On T1.ID = T2.ID AND T1.Name = T2.Name AND T1.State = T2.State
[/quote]
[quote name='loveindia' timestamp='1359484887' post='1303196414']
idi vaadu


select t1.*, t3.*
from Table1 t1
join (select ID, max(inactive_date) as inactive_date from Table2 group by ID) t2 on t2.ID =t1.ID
join Table2 t3 on t3.ID = t2.ID and t3.Inactive_Date = t2.inactive_date
[/quote]

ee queries work avvatam ledu vayya evaraina help cheyyandi plz

[mtkr]

[quote name='mtkr' timestamp='1358524025' post='1303130518']
[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]
[/quote]


deals2 bayyaa..... paina unna sample data n queries try chei...
vaatitho ne xpected results e vastunnaiii... but nuvvemoo where lo max teesukovatle antunnavvv!!!

[Darling999]

Question edi?

[mtkr]

[quote name='DARLING...' timestamp='1359759684' post='1303207891']
Question edi?
[/quote]

page# 54
Post# 805
posted by kaarthikeya aka deals2buy

[SonyVaio]

nenu vidyardhi ni, job cheyatledu, plz help me
Naa dagara oka nalugu existing tables unnayi, aitay ipudu maa vodu em cheyamantundu antay oka table create cheyamantundu
ahaa table lo konni column names ichi avi petamanadu, idi chesina

1) ipudu nenu create chesina table lo 5 columns unnayi, vatilo 2 colums ki primary key petamantundu, general ga oka column ki PK petochu kada, 2nd column ki pk ela petali?
2) ipudu nenu create chesina table ki "the table has relationship with already existing oka table peru ichindu, ee relation ela set cheyali?
3) ee process SQL Statement ela tiyali nenu?

[deals2buy]

[quote name='SonyVaio' timestamp='1359802690' post='1303210194']
nenu vidyardhi ni, job cheyatledu, plz help me
Naa dagara oka nalugu existing tables unnayi, aitay ipudu maa vodu em cheyamantundu antay oka table create cheyamantundu
ahaa table lo konni column names ichi avi petamanadu, idi chesina

1) ipudu nenu create chesina table lo 5 columns unnayi, vatilo 2 colums ki primary key petamantundu, general ga oka column ki PK petochu kada, 2nd column ki pk ela petali?
2) ipudu nenu create chesina table ki "the table has relationship with already existing oka table peru ichindu, ee relation ela set cheyali?
3) ee process SQL Statement ela tiyali nenu?
[/quote]


idi check chey..neeke ardhamavutundi...

http://stackoverflow.com/questions/2626158/multiple-columns-as-primary-keys