Skip to main content

Interview Questions and answers - SQL Server Day 4

Question: What is Primary and foreign key in SQL server?

Answer: Primary key ensures the row have unique identity. Primary key have unique values and but can’t be null. A table can have only one primary key.
Foreign key ensures data integrity of the relationship tables.

Question: What is an identity in SQL Server?

Answer: If we want to set the unique value of a column then we have to use Identity. Identity automatically generates numeric value; we can set start and increment value.

Question: Can you please tell me default port of TCP/IP of SQL Server? Can we changed it if yes then how?

Answer: Default port of SQL Server is 1433, yes it can be changed. Network Utility TCP/IP propertiesà Port Number.

Question: What is difference between clustered and non-clustered index in SQL Server?

Answer: Best answer by Microsoft MSDN..
Clustered
  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Non-clustered
  • Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value.
  • The pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
  • You can add non-key columns to the leaf level of the non-clustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries. For more info https://msdn.microsoft.com/en-IN/library/ms190457.aspx

Question: How many clustered index we can create in a table- SQL Server?

Answer: We can create only one clustered index in a table.

Question: How many non-clustered index we can create in a table- SQL Server?

Answer: We can more then on clustered index in a table. Max number of non-clustered index in SQL Server 5 is 249 and max number of non-clustered index in SQL Server 8 is 999.

Question: Any disadvantage of index in SQL Server?

Answer: Frequently changes of table may increase the fragmentation of Index it may cause of slow query performance, so time to time index should be rebuilt.

Question: What is fill factor in SQL Server?

Answer: Fill Factor works in performance tuning area, for the index the most important property is Fill Factor. Fill Factor responsible to determine the percentage of space on each leaf-level page to be filled with data. As we know page is smallest unit of SQL server which size is 8k. Every page cans one or more than one row which is depending on size of row.

Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

What is difference between UNION and UNION ALL in SQL Server

We use UNION and UNION ALL operator to combine multiple results set into one result set.
UNION operator is used to combining multiple results set into one result set but removes any duplicate rows. Basically, UNION is used to performing a DISTINCT operation across all columns in the result set. UNION operator has the extra overhead of removing duplicate rows and sorting result.
UNION ALL operator use to combine multiple results set into one result set but it does not remove any duplicate result. Actually, this does not remove duplicate rows so it is faster than the UNION operator. If you want to combine multiple results and without duplicate records then use UNION otherwise UNION ALL is better.
Following some rules for using UNION/UNION ALL operator
1.The number of the column should be the same in the query's when you want to combine them. 2.The column should be of the same data type. 3.ORDER BY clause can be applied to the overall result set not within each result set.
4.Column name of …

Remove special character from string in MongoDB

Problem: Suppose wehave a collection and one field is type string contains some special character (like !@#$%) and we don’t want these special character.
Solution: We can easily remove the special character from field using script “replace(/[^a-zA-Z 0-9 ]/g, '')” in our query.  How can we remove special character from string using this script please see following example.
Example: Suppose we have a collection “EduSurvey “where we are collecting information from institutions.

{Name:"JB institute”, About:"This is good one collage for MBA", Information:"This $%%institute ##has good faculty etc$$"}
{Name:"MK institute”, About:"This is good one collage for MCA", Information:"This$$%# is the dummy text12"}
{Name:"MG institute”, About:"This is good one collage for B,Tech", Information:"This# institute@ has&* good infrastructure"}

Did you notice Information fields contains some special character so we…