Microsoft SQL Server Interview Questions and Answers | Arindam Mondal | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Microsoft SQL Server Interview Questions and Answers

teacher avatar Arindam Mondal

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

32 Lessons (40m)
    • 1. Introduction

      2:14
    • 2. What is primary key?

      0:39
    • 3. What is default constraint?

      2:17
    • 4. What is unique index?

      1:11
    • 5. What is temporary tables?

      0:20
    • 6. What Is Local Temporary Tables?

      1:18
    • 7. What is table variable?

      1:15
    • 8. What is a stored procedure?

      1:25
    • 9. How do you improve stored procedure performance?

      1:38
    • 10. What is having clause?

      1:49
    • 11. What is an er diagram?

      0:21
    • 12. What is normalization?

      0:15
    • 13. Which is faster a table variable or a temp table?

      0:25
    • 14. What is Clustered Index?

      1:36
    • 15. What is COALESCE in SQL Server?

      0:38
    • 16. What is CTE?

      1:18
    • 17. What is Nonclustered Index?

      1:04
    • 18. What is self join?

      0:17
    • 19. How many types of relationships available in SQL Server

      0:17
    • 20. What is the difference between UNION and UNION ALL

      0:12
    • 21. What is recursive stored procedure?

      1:43
    • 22. How Many Parameters Can Be Provided To A Udf 1

      0:15
    • 23. What is distributed tables

      1:36
    • 24. What are different types of Sql command available?

      2:45
    • 25. What is ACID property?

      2:40
    • 26. What is the difference between ISNULL and COALESCE function

      3:08
    • 27. What is heap

      1:46
    • 28. What is SQL full join or full outer join

      1:16
    • 29. What are the differences between Primary Key and Unique Key

      0:40
    • 30. What are the differences between Primary Key and Surrogate Key

      0:48
    • 31. What are the differences between ISNULL and COALESCE

      1:52
    • 32. What are the differences between Primary Key and Foreign Key

      1:15
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

20

Students

--

Projects

About This Class

In this Class I have added all frequently asked questions in SQL Server. This class  designed for all levels (Beginner,Intermediate and Advanced).

Meet Your Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: hi All will come to the course. Sigel's ever interview questions and answers myself are in the Mundell. I am working on Microsoft Technologies for more than eight years. What you learn in the schools after completion of the stores you love. Knowledge on Key concept in sequel The school's covers most important topics in cycles over leg relationship, prime Maliki's foreign keys and on that he's concept most important functions in cycles ever. And also, I'm discuss various temporary object sensical servers and many other topics, as this course is about. Interview Costin's for sickle cell over. So I covered most important in questions asked doing sickle. Several interview complexity level this course cover star fix for all levels. So if you have zero experience in sequel, you can take this course. Or, if you work a couple of years sensical several. You can check out the intermediate or advanced level questions right in here. Target audience. Anyone near Darcy Girl? If you are just starting to work on sickle cell, very can take this course. If you are a stick a level of her, then this court's definitely will help you to get more insights. Sensical. If you are a database developer and you consider looting this course because it what's most of them? Plavix. Um, under nativist. A loveman. If you are being a developer, then sickle is a part off your daily Occidental, and this course will definitely help you to get insects of many sickle components. 2. What is primary key?: What is prime? Aleke? Primerica, uniquely identify said. Accord in a table. Here I am creating a table. Authors We die d column your friend. As time Alec, it's create adaptable. We can also create free Maliki by combining multiple columns to defend well, I'm an icky using multiple columns when into use the constant cured in the constant name by many Key and the columnist Execute No come in and let's created. 3. What is default constraint?: What is the port constant? We can ascended the full fellow in a column so that while inserting Bella's toe the table, there is no inputs. Provide to the default column that the fund will will be was to insert in that column. Let's see an example here. We're creating a new table. We adored him for you with the columns. I'd be first name, middle name unless them know that the first name column declared as normal. All other columns, New clearness. No, it's executed. A comment? No, I mean starting in tow. The incredible except the middle Name column, which is accepting Malval. Lose, That's execute Now select the employee table. You can see her own kilometers values in the middle name as non values, as we have not provided any inputs for middle name. And this column declared as now. So the default value is now. Now let's try to insert into employed. But this time, everyone, the first time column, which is declared as normal no see later, cannot insert non values into the column past him. So in these scenarios we can provide their default values so that if the input values is unavailable, Cotecna the phone values while inserting into the Rose Drop the employee table. Now I'm creating the table with their deport Constant provided a default fell on None for the first time column. Now inserting into the incredible wit only idea and last name column. Let's select the employee table now. You can see her in the first time column in the Miller's Unknown. You can also use the older comment who added people constant to an existing Calama for table. Let's said that the full constant for the i D column off Incredible. I mean Sardinero to the employed devil to see if we really received the department is in the I. D column executed selected employees table. You can see the Department of stain inserted from the those. 4. What is unique index?: unique index insurance. The uniqueness off its valinda in this column if they indexed is a composite, then the witnesses and forced across the columns as a whole, not on the individual column. For example, if you are creating unique index, um, first name and last name column in their names together must be unique. But the individual first name or last thing can be duplicated now will create a unique index. On that day. Ivanka's two months on. CED column The same Texas Creative Nick Index and they index name on table name and then Collomb name. Let's execute the comment. Create index successful. Let's take if they next created on customer stable. So expand our later Miss school training extender. Tables spend customers Will that six men I am nexus. You can see the index name, and it is a unique unflustered enix 5. What is temporary tables?: what is temporary intervals. Tim Primitive IHL's Houston stored it down in sequel server for a starting time so that it can be exist multiple times by the application. Tim Pawlenty Wills are two types. Look on temporary devils and move on temporary difference. 6. What Is Local Temporary Tables?: What is local temper? Tentative ills. The local temporary devils declared within single hash or pound sign. It is accessible by only the connection, which creates that incredible. Here I am creating a local temporary irritable can see the devil enemies, the MP employees, the creative ALS in Texas, him as creating, you know among Kevin, except for temporarily different. We're using Yah Hasher pouncing before dr with them. Let's create that unprintable. The devil is created. No, let's insert some hellos with that employer tables. You're going to see them in such seem texts emails in our multiple sex Akyel That depends in some Tim. Not on a select query on TV. An employee. You see that you can see one Royce here in the real MPRI in the real M play. 7. What is table variable?: What is it? A table variable, if they will variable is a variable which can hold. But they were like guitar within it. Here I am creating customer table variable with two columns. Idea Nim. You can see the They are very evil. It's in Texas. Same as declaring any other variables. They will variable name and devil. Then the columnist with no today here I'm inserting poor rose so that they will variable costs too much and slipping the depth. Um, customer. So I need to run all this statement at one go because their table variable scope is current vaginally. Yet, son, all the statements together couldn't see then of four rows inserted into the they will very every customers. 8. What is a stored procedure?: quality. The stored president. It's stored Procedure is a pretty compelling sequel statement with Reconsider and in the U Z Over and watering. And here I am creating a steward person here. Nor thus indexes create press in your in the process of your name. Get customer data. Then, as after that, we didn't begin. And in between beginning in, we can write the fasten your sequel statement here I have read Denis Allex statement from Personal Person Table. Let's create the president. The President Execution is successful. It's good to the object explorer toe find oppressive here Victim is physical training. Then program ability. Stone President That is the started president. If you live longer in the store person here food. Now let's execute the store person Here You can see that select statement within the stored procedure is executed and we're receiving the first name middle name and the last name color 9. How do you improve stored procedure performance?: How do you improve is stored president peppermints? There are many options of level for consideration While checking installed prostitute performance specially column names in the select list you should their word using slipped start from Devon limb instead of we should stress free the column names Reacher. We can't buy the application use. No, look. He was in no lock will improve the performance of the select Very you sit No count on. She considerably terms information and mis it's been running in the selector. The M in a person if you're president has meaning, it's that statements you got set already. Why? Look will display Lord off such messages, which will increase the network traffic. This message can be averted by using sit no count on option and it can increase the apartments. Your scheme on him before objects. It will help Second server to find the actual object use exist instead of count. The sequel. Several transition technique works. Best street he was up exists. If we want to check in a Cardiff exist, you can use they exist instead of count. Because common scans the inter table 10. What is having clause?: What is having closed the having closed, used to fit under down while losing that will buy clothes because we're close could not be used? Well, goodbye. Let's see an example of having close here I am using the personal person table from adventure works Hand me up. But sometimes when First name column So we lose grew by. But some day van first name had I am using it to get function and grew by close count off first name. And but some day let's execute this statement. You can see the first name cone on the person type information every level here. Now let's sadly have been closed to filter out the first time. Cone, that's the first name gone. It's greater than 300 here, everything that grew by close and and it the heavy clothes in it. So, having count fast name greater than 300 I'll set in our diary. You had son. Wouldn't that sweep do you that so that we can identify? A That isn't I am learning would de select together. So here is the faster isn't without that having closed and there is the second isn't too in time. In close having close displaying his end off fast name Cohn, who each year more than 300 So you can say in closing stuff intel out the data from the ingredient function. 11. What is an er diagram?: what is any are drag room and he had rectum or entity. Relationship diagram is the pictorial representation off it's system requirements. From a top down perspective, this diagram shows the relationship between entities, also known as stables, United Office. 12. What is normalization?: What is normal? Addition? Database normalization is the process of organizing the pills and tables off a relational data, Miss, You mean image, redundancy and dependency. 13. Which is faster a table variable or a temp table?: which is faster if they will maybe even order. Temple irritable Normally a table variable is faster because it is stored in memory. Why let em primitively stored under this? But if the table very Evans side exit memory sage, then they will very even and temporary table performs almost same. 14. What is Clustered Index?: what is clustered in its it must have the Knicks sort and store on a table or view by using their key values as if they were. A lot of you can be sorted in one direction only, so they will has only one close turn index. No, this key points on Cluster the Knicks, when it they will, has close to the next Sonic. It is called Master Table when it they will S no clustered index and the D Day stored without any order. Discord. Heave. When any creative prime Maliki My default cluster index is created in the script we're creating Plastered Index past, we're creating it. They will call C P with three columns, idee name and description. We'll create cluster index on I d column. That's great. Adaptable. Now it's clear. Douglas studying Next on i. D. Column off suitable. Now let's go to the Object Explorer to check if indexes created successfully and Scotto No datum is their bills. City in Nexus can see it. The Knicks I exit e i d created and it is cluster in its 15. What is COALESCE in SQL Server?: What is school is insecure service, coalesce, issue student and fast? No, none. Hellos within arguments this function is to is to return it. You know, no values from more than one column, in the argument's least coalesce strong family except all the hellos from the calamities are they expressions. But it only returns first. Norman values presenting the expression the same Texas police, then multiple expression. Our column lease. 16. What is CTE?: What is C B. This should be of common table expression. Defends the temporary results. It that don't get a difference within a number Select insect. Our deal it statement. It becomes a convenient way to manage complicated queries and simplify complex joints. Our sub goodies and to provide the means to equity. Had a candida such as an organizational hierarchy City were first introduced in sickle servers. Person 2005. Let's see an example of city Here we have an example of implicit E. This is the city definition where it was in a select stepping from employee table using the width clothes were defending their city, and we can also name the columns accordingly, So this will help you to hide. The complex square is within the city and this is the select step in using that up city. Let's stunned Look, really, we have that is then from the city. So have cities acting like you 17. What is Nonclustered Index?: what is non cluster index in on clustered index contents. Non clustered Kiva lose its key values. Contents pointed to those that contents. The cumulus in this script will create in on Cluster the Knicks on a table called location This Devil s three columns I D. Name and description. Let's create their table. Now let's create an UN cluster. The Knicks on I. D column of the locus interval Midst on the table. If then on cluster indexes created two minutes, locations and eunuchs, you can see the non blustery nexus created on the locations they will. 18. What is self join?: what is self joined by using self trend. It they will join to itself. It is used while creating havoc. Can deter are comparing rose within the same people itself? Journeys this energon or left turn? 19. How many types of relationships available in SQL Server: how many types of relations every level in sickle service. There are three types of the relationships, every level 1 to 1 relationship, one to many relationship, and many to many listens. 20. What is the difference between UNION and UNION ALL: What is the difference between the union and union? All union is used to select distinct it up from the tables, but union all allows duplicate prose is selected from notables. 21. What is recursive stored procedure?: What is the start? Must do recursive store capacity or is the start present here, which calls by itself until it idiots some exit condition. He cuts it. Mr. President helps the programs. Who is the same set of court in multiple times. A common application of record sister president is too far from numeric competition that lend themselves to a definitive evolution by the same process. In steps, for example, we can calculate factorial off a number by using the caption. Harry, we're creating the president to calculate the factory element we have taken to believe ALS number, you know, as input of the values whose factory a little be concluded on the very of leaded, then for the output of the factory. Now here we're calculating the bacterial part and within the procedure were calling. The same procedure is to effect real with a input and our parameters. That's why this is cornered because it's told president. Now let's create the Presidio. The President can't executed successfully. Let's execute order. Presidio Here We're executing the prostitute who can clear the factory and flew off Frank. No. The factorial of 5 120 is being calculated 22. How Many Parameters Can Be Provided To A Udf 1: how many perimeters can be passed with you, dear, or use a different function. You can pass next mama 2000 and 100 kilometers away gives their different functions. 23. What is distributed tables: What is this true? 20 wills in a distributed table in orderto within Dartevelle is distributed across the north within their plans to allow for massive skin. It's a door in south in one distribution in one computer, known as you can see in the image it's developed belongs in one distribution on one compared notes. This is calculated by hashing love. Hello, Content with India defined this tuition column for that room. As you can see in the video statement for the Clear Table statement, you test them minor edition for the distributor tables they will start distributed by a deterministic. Has function have fled to the distribution column chosen for particular people here in this example Product key as the Lish tuition column in the fact Internet Sensible because of the high card in ality and absence off school, Therefore distributing the devil evenly across the notes. 24. What are different types of Sql command available?: What are the different types of sickle comments? Several levels. There are five types of sickle comments. Every level. This sickle command, sir. Big deal Data definition language. Vicky y data query language. The M L determine Inflation limits The seal later Contra language and PCL transact some controlling gets bebe alert. Better definition language. Actually consistent sickle comments that can be used to refrain very as the service objects . It is used to create and modify very estate over subjects. Examples off mediacom answers create our drop off. Drank it comment and hitting him. Create is used to create an idiot Travis Objects like Devil Views Function Index store prasidha use its search All that is used to modify a bit of a subject the office use to delete any database objects from the service drank. It is your story. Move all that it affirmatively. Comment is history had comments to the service. The name is his pseudonym. Any objects in Libya? Davis Dick, You will or data query language issues too far from queries In the data vis example of ridiculous select statement. It is used to retrieve data from that it of us. Do you, milord, determine If listen, Leg needs this single common used to many fuller data presenting the data vis examples of the email sub insect of let and be, in fact, issues to insert hellos or daytime into a table. I'm reticent. Stool Abdin of hellos in any existing table abilities is to be lied to the cards. Former table be seen or later. Contra language is to provide access sites or permissions in the uterus. Examples of these silk, um answer Grant and river grant use to provide formations to the Davis, and the work statement is used to determine it. Use that sexist D. C. Lord. Conduct some control languages. Diesel comments. You stranding connections in the rate of this example. Softly. Celcom Answer. Comment at or back safe fund, comet usedto, comets and interconnection But all vacuums to roll back. Any connection, sir, Point used to save within a connection 25. What is ACID property?: what is acid properties, as it stands for at a Mississippi consistency I solution and durability. These four properties should be maintained and followed by 11. Lee does. Let's see what this properties stands for in the past. Properties, Abdomen, City at the recipes and all are non proposition. That means that I don't The entire transaction takes place at once or does not happen. It'll 10 Jackson Donut Oka. But Sherry, it's transaction is considered as one single unit and I def France to completion. Art is not executed. Antle at seven citic on this stuff What type off operations fast earnings embark in case off it and election failure tinges are not visible in negative. It's on the second honest comic effect on Dickson comets that changes are permanent and every level in that it ofhis this settlement City rule also known as all or nothing. The second rule is consistence consistence again, is that it? Election never leaves your data vis in 1/2 finished state that Adivasis consistent before and after each connection, it defends the correctness offered it of us the time property means isolation. This property ensures that multiple transactions and local concurrently and each connection subverted from each other until they're finished. Convictions Akar Independently, without any indifference. Ginger circling any practical of connection will not be with Israel to any other connection . Until that time, Jackson is committed the fourth properties durability, this property and sheer step that the service will keep track often this once that conviction is completed, the information are stored in and the return to the beast. And they persist even in case off system failure rockers the effects of the connection test I never lost so we can easily recover in interconnection if needed. These are the four properties should be followed and maintain my reliable data vis this poor properties together called a sit properties. 26. What is the difference between ISNULL and COALESCE function: What is the difference between is now Alan. Call this function. Do I smell function? Takes two parameters immediately replaces the novel. If the fast parameter value is now, if the first element about who is storm Tunnel, they need to returns the first value only the call this function can take more than two input parameters and the it returns the fast. Not all values among many. Stumpel's Let's see the example off is not function here. We have two is not a function. The first function is sticking Nalle values as first parameter, and then a character, Williams. The second is malfunction. Contents known. Alva Lose. It takes tow character values. Let six go document as expected. The first is non function return ever lose by the placing them al values, and the second is non function. Is returning the northern values me. Now let's see an example of Polish Johnson. That call is from Some can picks more than two kilometers meet returns, the first normal values. So, yeah, we have to call its function. Taking three primitives lets them the comment and then see the fast coolest from some returned A, which appears before the velvety I'm a second coolest function. It turned B which appear before the A Now there is another difference between is now on Collis We will see these behavior, right? This example against here we're using is now and college function What? The function is taking no perimeters and Sammy imports the fast, really easy and secondaries one. What do you think it should give? So we live in me, Sam For what? The function it's executed and see what happens. I'm learning. The first is malfunction. Please give studies and output here we just find Now let's on the Kulish Johnson But now we have a seeming any that made element cities convention field and converting the barker flu you to do the typing. This happens because is now the tense that it that I've of the fast input parameter and it implicitly converted hello into the second perimeter. But in case of coolest function, it gives us the date of a president's to determine that did that life and the retirement will be whichever data times and the highest presidents. Let's take a look and the did I presidents least here you can see enter into Jeff has hard presidents, then market did today 27. What is heap: what is a he if they will, without a glass study next. It's called him nonplussed. Early Next can be created on a leaf, so he does not store data in any particular or the the order of the detainees cannot be predicted. So if you want your return guitar Toby, or that you must use the order by clause when you use a he flocks missed for small set of later. For example, you want to store to indie records are happy recalls. In that case, he will works fine because while searching letter gifts scans the entire table toe, find that is a red rose. So if you have large state of records, then he will not far from the efficiently when you should avoid using him. If you want sorted that out, then you should have heard, because he does not store the town in a sort of way. So in that case, you must use the cluster leaning when he was searching for a range of data's. Then you should have heard him because it will, such then Dad records do not even see if, if you are grouping that tough originally, because before grouping later the data must be shark. And how do you create a thief? If you want to clear to he you must create a given. We don't a cluster beatniks. Or if you have existing devil it Close start Index, you can drop that close start index terms from the table we 28. What is SQL full join or full outer join: What is secret? Full Jen are full out surgeon in Sickle, Full Jane. It returns result from both left devil and devil. The result. Set contents All the records from what the devil and for non matching mellows it saw. There's now. Now let's see the sickle. Full assurance in Texas. The same Texas Select column. Elise from Left, Devil Full join our full out Arjun that devil on column one equal toe column toe and where condition? Let's see an example here I have total sales Customer Devil and order table In customer level, I have clumps C a D first them middle name and last time, you know, reductive. I am C A D and order developed. Let's turn what Dr will use in Full Jen on C A D column. You can see the result here. All the records a return crumbled the devils for non menting CED records. The values are stringing us now 29. What are the differences between Primary Key and Unique Key: What other defenses sweeping prime Maliki. And you, Nikki, Family key and unique e book you willingly. I didn't deface a recording it. Let's see the differences between them. He's fact herbal. Only one per Mickey is allowed proactively, but more than one. You, Nicky can represent ignitable Melbourne's Crime, Really Good restaurant and no moral values within it. Al values are allowed in you, Nicky columns before 10 days. When people Primerica creates a colostomy knicks, my default unique it creates. And on class Terminix. 30. What are the differences between Primary Key and Surrogate Key: what other difference between primary can celebrate. Primerica and Surrogate Key, both used to uniquely identify records in the service later this stain family key wilstein will tip in it with while surrogate key was in all of this mellows in prime Maliki family Can us both? New American not new Mid Wales Onley numeric millions. Sana, Lodin, Saraki Miss Less money, Primary care Hold some business meaning in the nick Sergeant K does not know any business money. We didn't it example Example of family kids that Castellan i d Social Security number example of certain Kiser unique I. D ju 80 Exeter. 31. What are the differences between ISNULL and COALESCE: What are the differences between Islam and clueless? Standard is lonely 11 as a this girl Pinson, and it is not in any sensical standard coalesces in unison. Sickle standard number of parameters They stand for ransom candy. Two Parameters. Coolest can take more than two. Family returned. It's not Return the first film winter. If it isn artman and returned the second firm, it'll in the first film Interest now colisee tender and not fail any time starting from hostile escapes. Flexibility. Islamist less flexible than Collis Publicis. More powerful and flexible. Then it's time in certain to stick man. If it's that is used in select into statement, it always creates normal, constant We got less off parameter value now are normally. If Collis is used in selecting two statement, then it creates a normal constant if all values are not normal and creates now constant. If anyone who is smell screamed Islamist faster than Collis because in this building sickle server function, Corliss's slower than Ismael because it turns slips. Okay, statements example is not a leaf on one and telephone toe this relate. I'm telephone one if tell from one is not or return telephone toe if telephone oneness now coolest Stefano one pitiful to tell 13 It will litem faster. Alvin Liew in the list. 32. What are the differences between Primary Key and Foreign Key: what are the different suited family can for a definition from Nikki, Uniquely, I didn't face a record in activity if Frankie was tooling gritting Toe DEVIL MEL values novelists are not allowed in play. MICKEY COLUMN for N Q. Columns can have multiple novelists. The front index. My default communicate creates cluster limits. Frankie does not create any. Next by default, we can only create any necks. If record number of keys party pretty, only one prime Maliki is allowed factory. We can have more than one Frankie in Italy inception, we can insert into a primary key column if you need. The Valley is not present in the foreign KEY column. We cannot insert him. Hello in the foreign key column. The Valley is not available in that primary color volition before deleting every look from a prime. MALIKI column, you need to delete the fellow from reference in foreign. KEY COLUMN. You can delete it HELLO from a foreign key column. Even if the village is not present in the planet. Nicki Color