Problem: Mr. Coach has Team A and Team B whom he wishes to play for different countries. Provided the given conditions satisfied:
- Team A and Team B both shouldn’t play in the same country at same time
- Team A and Team B should play at least once for each country.
- Number of matches played will be same as number of locations.
Now Mr. Coach needs little help in scheduling matches for each of his team along with the above constraints. Let’s check out the solution.
Read more…
Exploring each table in SSMS, just to check whether a particular table has IDENTITY property set or not i.e. presence of any IDENTITY column in the given table; might be a cumbersome task, especially when the number of tables in the given database is more than hundred. We need some shortcut to perform this type of tasks. Let’s discuss what identity property is and how to use some shortcuts i.e. custom procedures to get what we want.
Read more…
Although SQL Server has a big range of functionalities and is powerful to handle almost all type of data processing, but still there are some corners where it fails to do its job predominantly like pattern searching for text analysis, file operation, process calls, etc. or even when you want to perform some complex operations outside the world of database like custom mail operation, FTP operations on the basis of some dataset, uploading or downloading from internet or remote systems.
Read more…
Day by day the complexity of data has increased so much, resulting in demand of more complex analysis and reports. Days are gone when the analysts are happy with simple scorecards and dashboards with basic aggregation and computation and with some trend charts. But now they need more complex reports, more accuracy in results with better insights to lead in the information technology.
Read more…
Sp_MSForEachDB is a great procedure coming with MS SQL Server even though it is un-documented. It eases some of the complex operations that we mostly do by using CURSORS.
Read more…
CTEs are one of the beautiful and the most powerful feature of SQL Server. It is introduced in SQL Server 2005 and I guess is one of the best things in SQL Server. This not only simplifies most of the complex and impossible queries in T-SQL, but also provides one medium to deal with recursive queries. Moreover, I can say it is mainly for recursive queries. It can be used in replacement of derived tables (sub queries), temp tables, table variables, inline user-defined functions etc.
Read more…
NTILE(): This is one of the most coolest function provided by T-SQL. This is very useful while grouping a dataset into multiple sets i.e. in case if you want to distribute the result set into more than one tables or destination, this function is useful in accomplishing this task.
Read more…
DENSE_RANK(): This is almost similar to RANK() function except, it will assign rank continuously i.e. it will always return consecutive integers. It will assign rank to the records as per the condition used in <order by> clause for a partition without any gaps in the ranking values. The ranking values will be same as of RANK() i.e. if the <order by> condition satisfies for more than one record then it will assign the same rank to each record that satisfies the same condition.
Read more…
RANK (): For each partition, it will assign the rank to the records as per the condition used in <order by> clause. If the <order by> condition satisfies for more than one record then it will assign the same rank to each record that satisfies the same condition.
Read more…
ROWNUM(): For each partition, it will return the sequence number starting from 1. It is very useful in case you want any functionality similar to identity column on the fly. As IDENTITY() will work only in case of select into statement i.e. when you are inserting records into some table and not in simple select statement.
Read more…
Recent Comments