questions about sql

Question

questions about sql

coool_sweet

SSCommitted

Points: 1892
More actions
August 28, 2016 at 3:24 pm

#316679

hi
i have following questions :
(1)how to find nolock?
(2) can a table have only non cluster index?
(3) Design a package to transfer 10 files from FTP and store them to hard drive?
(4)What is self join and what is new functionality in sql 2008 came which replace self join
requirement.
please if you know answer me

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

coool_sweet (8/28/2016)
(1)how to find nolock?
(2) can a table have only non cluster index?
(3) Design a package to transfer 10 files from FTP and store them to hard drive?
(4)What is self join and what is new functionality in sql 2008 came which replace self join
requirement.

1) Find? You'll find all sorts of information about it in Books Online.

3) That's not a question, that's a week's project

Interview questions?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coool_sweet SSCommitted Points: 1892 More actions · Answer 2

coool_sweet

SSCommitted

Points: 1892

August 28, 2016 at 4:09 pm

#1898008

i am not finding it anywhere

yes interview question

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

I would while away the hours conversing with the flowers, discoursing with the rain....

Toto, I don't think we're in Kansas anymore.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

durga.palepu Hall of Fame Points: 3530 More actions · Answer 4

nolock is same as Isolation level ReadUncommitted.

You can find the saem using below query,

SELECT CASE transaction_isolation_level

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'ReadUncommitted'

WHEN 2 THEN 'ReadCommitted'

WHEN 3 THEN 'Repeatable'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL

FROM sys.dm_exec_sessions

where session_id = @@SPID.

For self join alternative, try to use Group By.

Thanks,

Durga Prasad.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

coool_sweet (8/28/2016)
i am not finding it anywhere

I assure you, there's information on the isolation levels in the official documentation (Books Online)

yes interview question

The point of interview questions is to see what you know, not what the collective internet knows. So, what are your answers to those questions?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

(1) not sure what that means.

(2) no. a table can have many nonclustered indexes.

(4) presumably they mean hierarchy ids, but can't tell for sure, the q is vague enough they could just mean ctes.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

(3) Design a package to transfer 10 files from FTP and store them to hard drive?

Done. Ohh was this a question? 😛

Did you have to draw this out on a white board? And why only ten and how do you differentiate which you want if there are 11 files?

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

coool_sweet (8/28/2016)
hi
i have following questions :
(1)how to find nolock?
(2) can a table have only non cluster index?
(3) Design a package to transfer 10 files from FTP and store them to hard drive?
(4)What is self join and what is new functionality in sql 2008 came which replace self join
requirement.
please if you know answer me

On 1)

I'm assuming that they mean how to check all of the code in the database that may have the text "NOLOCK" somewhere within it. There's a system DMV that has all of that code. I'll leave it up to you to figure out which one. You need to be familiar with a fair number of them to be able to do "off the top of your head" programming and familiar enough with the others to be able to find them in Books Online.

If they mean in code being executed, pretty much the same thing except different DMVs or Extended Events.

On 2)

A lot of people will answer yes or no on this without clarifying and it could be a trick question. In my mind and in the documentation, if a "table" doesn't have a clustered index, it's not actually called a "table". It's called something else and I'll let you do the research to figure that out so they can't pin you on a similar question in the future.

On 3)

Apologies for sounding a bit harsh but I seriously agree with Gail. An interview is to find out what YOU know, not how well you can ask a question on the forum. There are a couple of things you need to know as a DBA or an SQL Developer. First, if you applied for a position where the job description mentioned knowledge of SSIS as a requirement, then you'd better know things like this question or some damned good alternatives (I don't use SSIS for anything but can still do this using only what's installed with Windows Server and SQL Server). The other thing that you need to know is how to take advantage of an opportunity. In order to do that, you need to spend some time with your "assistant DBA" (Yabingooglewho ;-)) and look it up in Books Online, MSDN, and TechNet and give yourself the opportunity to teach yourself something new. Remember that one of the other questions on any decent interview would be to explain how you find out how to do something that you've never done before. The answer to that question may have sufficed as the answer to this question, as well. If you don't know how to find something out about SQL Server using one of the many internet search engines, you should take up finger painting as a new career. 😉

On 4)

Same thing. In fact, same thing on all of these questions. Stop asking other people for the answers to interview questions (and most questions in general). It's your career. Invest some time in it and start teaching yourself. If you don't have a decent home computer or laptop and can't afford one, mow some lawns, do some dishes, or whatever it takes to get one and an internet connection. Then, download the now free copy of SQL Server and SSMS and start teaching yourself.

Again, apologies for being harsh but think of it as tough love. No one is going to want a DBA or a Developer that relies on the kindness of folks on a forum for you to do the job you're applying for. And, to be honest, all of these questions are fairly low level. Lots of people will help you with a problem if you get stuck, especially on this fine forum, but most aren't going to do your work for you unless you can actually demonstrate that you've tried, especially interview questions that have a ton of answers on the search engines and in Books Online. The same holds true with a job.

Get crackin' and teach yourself some stuff. When you think you know something, try to teach someone else because, as a wise man once said, if you can't explain it simply to someone else so they understand it enough to figure it out, then you still don't know it well enough. 😀

p.s. If you don't know what "Books Online" or "SSMS" is, you know what to do. Find out or finger paint. :w00t:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

x SSC-Insane Points: 23660 More actions · Answer 9

Jeff Moden (9/2/2016)
On 2)
A lot of people will answer yes or no on this without clarifying and it could be a trick question. In my mind and in the documentation, if a "table" doesn't have a clustered index, it's not actually called a "table". It's called something else and I'll let you do the research to figure that out so they can't pin you on a similar question in the future.

"A heap is a table without a clustered index."

https://msdn.microsoft.com/en-us/library/hh213609.aspx

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

patrickmcginnis59 10839 (9/7/2016)
Jeff Moden (9/2/2016)
On 2)
A lot of people will answer yes or no on this without clarifying and it could be a trick question. In my mind and in the documentation, if a "table" doesn't have a clustered index, it's not actually called a "table". It's called something else and I'll let you do the research to figure that out so they can't pin you on a similar question in the future.
"A heap is a table without a clustered index."
https://msdn.microsoft.com/en-us/library/hh213609.aspx

I was looking forward to the OP discovering such a thing for themselves because it looks like they need the practice.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

x SSC-Insane Points: 23660 More actions · Answer 11

Jeff Moden (9/7/2016)
patrickmcginnis59 10839 (9/7/2016)
Jeff Moden (9/2/2016)
On 2)
A lot of people will answer yes or no on this without clarifying and it could be a trick question. In my mind and in the documentation, if a "table" doesn't have a clustered index, it's not actually called a "table". It's called something else and I'll let you do the research to figure that out so they can't pin you on a similar question in the future.
"A heap is a table without a clustered index."
https://msdn.microsoft.com/en-us/library/hh213609.aspx
I was looking forward to the OP discovering such a thing for themselves because it looks like they need the practice.

You said the documentation says heaps are not called tables, when a routine search calls up an msdn page mentioning that a heap is very much called a table and in fact, I'm having difficulty finding any documentation that declares that heaps should not be called a table. So if you could post that doc that would be great!

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 12

patrickmcginnis59 10839 (9/7/2016)
Jeff Moden (9/7/2016)
patrickmcginnis59 10839 (9/7/2016)
Jeff Moden (9/2/2016)
On 2)
A lot of people will answer yes or no on this without clarifying and it could be a trick question. In my mind and in the documentation, if a "table" doesn't have a clustered index, it's not actually called a "table". It's called something else and I'll let you do the research to figure that out so they can't pin you on a similar question in the future.
"A heap is a table without a clustered index."
https://msdn.microsoft.com/en-us/library/hh213609.aspx
I was looking forward to the OP discovering such a thing for themselves because it looks like they need the practice.
You said the documentation says heaps are not called tables, when a routine search calls up an msdn page mentioning that a heap is very much called a table and in fact, I'm having difficulty finding any documentation that declares that heaps should not be called a table. So if you could post that doc that would be great!

A lot of documentation (and internet knowledge) uses the term heaps interchangeably with table which can be accurate enough, all heaps are tables but not vice versa.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 13

I keep things simple, if it is in sys.tables then it is a table even if it is not ordered. I can always use an ORDER BY clause to pull my results. I am not saying this is ideal, just technically speaking I mean.

----------------------------------------------------

Eric M Russell SSC Guru Points: 125561 More actions · Answer 14

coool_sweet (8/28/2016)
i am not finding it anywhere
yes interview question

The following website can be used to locate Nolocks in the US.

http://www.peoplefinders.com/peoplesearch/searchresults?Criteria.search=People&Criteria.fn=&Criteria.mn=&Criteria.ln=nolock

I'm seeing several in Colorado, Wisconsin, New Jersey, and Michigan.

Regards

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho