[Show all top banners]

stylish
Replies to this thread:

More by stylish
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Refresh page to view new replies
 SQL Guff
[VIEWED 9766 TIMES]
SAVE! for ease of future access.
Posted on 06-08-08 5:26 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Hi Guys,

Questions I have:

a) How to find the 3rd largest value from a column ( not using stor proc) ?

b) Difference between index hint and table hint.

c) Dynamic Queries and Static Queries.

d) Whats the difference between using set and select for setting the value inside the stored procedure.

e) How do you call the nested stored procedure.?


 

 

 

 


 
Posted on 06-09-08 4:54 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Khoi ta keta ho....
 
Posted on 06-09-08 5:01 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

a) How to find the 3rd largest value from a column ( not using stor proc) ?

Answer: have a look at the column and find the first, second and third largest value. Easy

b) Difference between index hint and table hint.

Answer: First one is hint of index and second one is of table.

c) Dynamic Queries and Static Queries.

Answer: Dynamic is changing queries and static is fixed queries

d) Whats the difference between using set and select for setting the value inside the stored procedure.

Answer: is in the question itself.

e) How do you call the nested stored procedure.?

Answer: By name, "nested stored procedure". Mukha don't know that much?


 
Posted on 06-09-08 5:02 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Now you prove e = mc2

 
Posted on 06-09-08 5:12 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Its nice to see SQL questions and answers. :-)

lets share relatively more difficult questions, so that we can make use of this thread to enhance our SQL knowledge.


 
Posted on 01-06-09 11:27 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

I just want to give answer to first question right now

select Top 1 T.Number from (select top 3 Number from NumberTable order by Number desc) T order by T.Number asc
 
Posted on 01-06-09 11:37 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Now here is a lil Tricky Question
Suppose You have only Database Name. You don't know about table and the column field and datatype.
Suppose You want to find a word called "sajha" in one of the table in that database. You don't know in which table and in which column. You know only Database Name. Now how do you find it. You can use any SQL commands(Cursors, Store eproc, Assemblies whatever). Lets do this Sajha SQL guys. have fun :)
 
Posted on 01-06-09 12:07 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Hello Here is solution:


a) How to find the 3rd largest value from a column ( not using stor proc) ?
- first you pick top 3 value from column  with desc order
- pick top 1 from above  with asc order


In AdventureDatabase to  get Third ListPrice


SELECT TOP 1 T.ListPrice FROM


(SELECT DISTINCT TOP 3 ListPrice FROM Production.Product


ORDER BY ListPrice DESC


)T


ORDER BY T.ListPrice ASC


b) Difference between index hint and table hint.


Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query processing operation such as a table scan or index seek, or other options


c) Dynamic Queries and Static Queries.


- SQ gives always same value while DQ gives different value on Execution. You can take a simple example. when you make a condition like name = "stylish" it gives fixed result but when you pass some @name parameter it gives differnt value( this is just simple ex)


d) Whats the difference between using set and select for setting the value inside the stored procedure.


SET can only assign one variable at a time, SELECT can make multiple assignments at once


e) How do you call the nested stored procedure?


i dont have exact example right now but here is a link ,i found for you.


http://www.sqlservercentral.com/articles/Basic+Querying/nestingstoredprocedures/998/


 


hopefully, i will help you..


all the best


 


 


 


 


 


 
Posted on 01-06-09 12:49 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

What is the difference between Clustered Index and Unique Clustered Index?
 
Posted on 01-06-09 12:50 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

1)  Select  t.ItemList from


     (Select Rownumber( ) over (order by ItemList desc) as Seq, Itemlist from table)t


     Where t.seq =3


 
Posted on 01-06-09 12:53 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Non-Unique Clustered Index - Clustered index with Non unique value


Unique Clustered Index - Clustered Index with Unique value like primary Key,


 
Posted on 01-06-09 1:56 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

What is difference between covering index and covering query?
 
Posted on 01-08-09 8:52 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Virus No 1
Here is your Solution

CREATE PROC [dbo].[spUtil_Occur]
(
    @SearchString SQL_VARIANT
)
As
/****************************************************************************
*Procedure Name: spUtil_Occurences
*Author:        Pat Reddy
*Purpose:    This procedure is useful for finding field and/or table references.
                It will search the entire database. Best of all, if you create a key stroke shortcut
                to execute the proc, you can highlight any text in your editor and search for it instantly.
                And because the proc's only parameter is of type SQL_VARIANT, there's no need to
                surround the input parameter with single quotes!
                
                Feel free to contact me with any questions at all:

                Pat Reddy - Reddy Software Solutions at:        reddys@charter.net
*By the way, I cannot take full credit for this script as I found it somewhere
years ago and merely modified it to my liking.
******************************************************************************/
SET NOCOUNT ON

DECLARE @SQL VARCHAR(1500)
SELECT @SQL = 'SELECT
SUBSTRING(SO.name, 1, 40 ) as Object,
COUNT(*) as Occurences, ' +
'CASE ' +
' WHEN SO.xtype = 'D' THEN 'Default' ' +
' WHEN SO.xtype = 'F' THEN 'Foreign Key' ' +
' WHEN SO.xtype = 'P' THEN 'Stored Procedure' ' +
' WHEN SO.xtype = 'PK' THEN 'Primary Key' ' +
' WHEN SO.xtype = 'S' THEN 'System Table' ' +
' WHEN SO.xtype = 'TR' THEN 'Trigger' ' +
' WHEN SO.xtype = 'V' THEN 'View' ' +
'END AS TYPE ' +

'FROM dbo.syscomments as SC
JOIN dbo.sysobjects as SO ON SC.id = SO.id ' +
'WHERE PATINDEX('%' + CAST(@SearchString AS VARCHAR(100)) + '%', SC.text ) > 0 ' +
'GROUP BY SO.name, SO.xtype ' +
'UNION ' +
'SELECT
SUBSTRING(SO.name, 1, 40 ) as Object,
1 as Occurances,
'User Table' as TYPE
FROM sysobjects as SO
INNER JOIN syscolumns as SC on SC.id = SO.id
WHERE SC.name LIKE '' + CAST(@SearchString AS VARCHAR(100)) + '' AND SO.xtype =' + ''U''

EXECUTE( @SQL )

RETURN


Source: http://www.sqlservercentral.com/scripts/Search/61508/
 
Posted on 01-08-09 9:02 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

I have one Scenario:
I have some events in my Notification Services in SQL 2000 and SQL 2005.
I have to migrate them to SQL 2008.

How can I migrate them?

Although Microsoft has given some Patches for Notification Service to migrate to SQL Server 2008. Since Notification Service is already removed from 2008 so in near future, there would be no support from Microsoft. So Is there any third Party tool for Notification Services or how can I convert them to SSRS or to write policies on Declarative Management Framework.
 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 30 days
Recommended Popular Threads Controvertial Threads
TPS Re-registration case still pending ..
From Trump “I will revoke TPS, and deport them back to their country.”
ढ्याउ गर्दा दसैँको खसी गनाउच
To Sajha admin
How to Retrieve a Copy of Domestic Violence Complaint???
wanna be ruled by stupid or an Idiot ?
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters