IT researcher wrote:
I am using SQL server 2008 R2 express edition.
Out of below 3 method which method is efficient to fetch count ?
1.select name,count * from table where name contains 'raj' and P = 1 select name,count * from table where name contains 'ram' and P = 1 merge result in code
2.select name,count * from table where name contains 'raj' and P = 1 union all select name,count * from table where name contains 'ram' and P = 1
3.select name,count * from table where P = 1 group by name then sum in datatable (contains name)
For SQL Server, all 3 of the answers in the original post above are terrible when it comes to SQL Server. As Erland points out, the CONTAINS keyword is only available if you're using the FULL TEXT SEARCH (FTS for short) option in SQL Server and that doesn't appear to be the case here so, right off the git, none of the answers will actually work in SQL Server.
I'm not sure where you're getting the questions and answers from but, if it's in a book or some form of online training, you need to get a book that's more SQL Server/T-SQL centric. If it's on a test or an interview question, you might want to consider a different company because, even if SQL Server did have a CONTAINS keyword outside of the FTS option, none of them have COUNT(*) correctly written, the first two will produce 2 scans of an Index/Heap/Clustered Table, and the 3rd one doesn't even have the same criteria as the others.
The word CONTAINS infers that the given quoted names could be anywhere in the string and so we'd also need to know what the "name" column actually contains because the equivalent of CONTAINS would be like what Scott posted in the WHERE clause of the code he posted and that has a leading wild card which will make it virtually impossible to do a high performance seek on the code even if there is an index on the "name" column.
So, to answer your question, the 3rd answer is the closest to being correct for whatever SQL dialect is being use by the people that wrote the question but it's also horribly wrong because neither an extra sum in a data table is required and it also has no filtering criteria.
Scott's code will work better than all 3 answers but there's probably a much better way even than his code depending on what's actually supposed to be present (first name only, full name, what?) in the name column.
And, seriously, the code you posted has some serious problems. If you're trying to learn something, get a better book or find a better website to study from. If these are questions on a school exam, find a better school. If these question are on a job interview, get up and leave. 😀
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)