Microsoft Business Intelligence (Data Tools): SQL- Difference between Temp Table and CTE

Microsoft Business Intelligence (Data Tools): SQL- Difference between Temp Table and CTE:

What's the difference between a temp table and Common Type Expression (CTE) in SQL Server? In the real practice, it depends on the...

Microsoft Business Intelligence (Data Tools): SQL - How can we convert rows to columns

Microsoft Business Intelligence (Data Tools): SQL - How can we convert rows to columns: How can we convert rows to columns in SQL? In our daily practice, we need to convert rows values into columns by using SQL. Sometimes ...

Microsoft Business Intelligence (Data Tools): SQL - How can we convert columns to Rows

Microsoft Business Intelligence (Data Tools): SQL - How can we convert columns to Rows: How can we convert columns to Rows in SQL? There are a lot of concepts to store multiple values against a single field to handle the ...

Microsoft Business Intelligence (Data Tools): SQL - Introduction of Joins

Microsoft Business Intelligence (Data Tools): SQL - Introduction of Joins: Joins are used to link one and more than two tables together to get the information from the database.  Join is the keyword which is us...

Microsoft Business Intelligence (Data Tools): SQL - Keywords, Identifiers, and Constants

Microsoft Business Intelligence (Data Tools): SQL - Keywords, Identifiers, and Constants: As we know about the sentences that they are made up of words that can be nouns, verbs, and so on. The same thing is applied for an SQL st...

Microsoft Business Intelligence (Data Tools): SQL- How to write or create the data into a file

Microsoft Business Intelligence (Data Tools): SQL- How to write or create the data into a file: There are a lots of ETL tools available to fetch the data from your database into the file. But these ETL tools are not possible for ea...

Microsoft Business Intelligence (Data Tools): SSRS - Drilldown SSRS Report

Microsoft Business Intelligence (Data Tools): SSRS - Drilldown SSRS Report: Drill-down SSRS Report – Before create a drill-down SSRS report, we need to know about the output of the SSRS report. We are assumin...

Microsoft Business Intelligence (Data Tools): SQL - Difference between Table Variable and Common...

Microsoft Business Intelligence (Data Tools): SQL - Difference between Table Variable and Common...: What's the difference between Table Variable and Common Type Expression (CTE) in SQL Server? In the real practice, it depends on t...

Microsoft Business Intelligence (Data Tools): SSRS - Drill-through Report

Microsoft Business Intelligence (Data Tools): SSRS - Drill-through Report: Drill-through SSRS Report  A drill-through reports is the combination of the two reports. One report is known as the base or main rep...

Role of SQL Views in Web Development

How do views relate to our day-to-day tasks as web developers?

When working on a large project in a team environment, you may be granted access to views only, not the underlying tables. For example, a Database Administrator (DBA) may have built the database, and you’re just using it. You might not even be aware that you’re using views. This is because, syntactically, both tables and views are used in the FROM clause in exactly the same way.

When you build your own database, you may wish to create views for the sake of convenience. For example, if you often need to display a list of entries and their category on different pages within the site, it’s a lot easier to write FROM entries_with_category than the underlying join.

What is the meaning of One-to-Many Relationships in SQL?

The more than one aspect of the relationship between a row in the categories table and matching rows in the entries table is the fundamental characteristic of what we call a one-to-many relationship. Each (one) category can have multiple (many) entries.

(Parent - Children Relationship)

Even though a given category (blog) might have no matching entries, and only one of the categories (humor) has more than one entry, the relationship between the categories and entries tables is still a one-to-many relationship in structure. Once the tables are fully populated with live data, it’s likely that all categories will have many entries.

(Vendor - Items Relationship)

Looking at this relationship from the other direction, as it were, we can see that each entry can belong to only one category. This is a direct result of the category column in the entries table having only one value, which can match only one category value in the categories table. Yet more than one entry can match the same category, as we saw with the humor entries. 

So a one-to-many relationship is also a many-to-one relationship. It just depends on the direction of the relationship being discussed.

SQL- Difference based interview Question

What is the difference between inner and outer join?

• The results of an outer join will always equal the results of the corresponding inner join between the two tables plus some unmatched rows from either the left table, the right table, or both—depending on whether it is a left, right, or full outer join, respectively.
• Thus the difference between a left outer join and a right outer join is simply the difference between whether the left table’s rows are all returned, with or without matching rows from the right table, or whether the right table’s rows are all returned, with or without matching rows from the left table.
• A full outer join, meanwhile, will always include the results from both left and right outer joins.

What is the difference between JOIN and UNION?

What is the difference between UNION and UNION ALL?

Sometimes it’s important to retain all rows produced by a union operation, and not have the duplicate rows removed. This can be accomplished by using the keywords UNION ALL instead of UNION.

• UNION removes duplicate rows. Only one row from each set of duplicate rows is included in the result set.
• UNION ALL retains all rows produced by the sub selects of the union, maintaining duplicate rows.

UNION ALL is significantly faster because the need to search for duplicate rows—in order to remove them—is redundant.

What is the difference between WHERE clause and HAVING clause?
What is the difference among UNION, MINUS and INTERSECT?
What is the difference among ROW_Number(), RANK and DENSE_RANK?

Facts of SQL?

GetDate()

The call to GETDATE() will return both the date and the current time, which very likely not match the data in the date field, whether the date only was stored or both the date and time were stored. The worst case is where both the date and time are stored in the field and only the date is really of interest. It will then be very difficult to query this table by date without using range searches or a function that converts the date and time on every row to just a date

AUTO_SHRINK

The AUTO_SHRINK option could have the most negative impact on a production database. This is because a database shrink could be issued during the middle of production queries, causing slowdowns or locking issues. Typically you would schedule database shrinks for off hours. The AUTO_CLOSE option is also a negative impact to production as whenever all users are disconnected, the database will close. Reopening it causes a delay in response from the server.

Read only doesn't impact the performance of the server, just prevents writes. This is an option you may want enabled in production.

Recovery full isn't an option and Auto_Update_Statistics is recommended as being left on to ensure the query optimizer has reliable statistics for queries. Torn page detection should always be enabled to prevent corruption, and this doesn't negatively impact production servers