Category: SQL Server

NOTES | Querying Full-Text Data

Creating Full-Text Catalogs and Indexes Full-Text Search Components In order to start using full-text search, you have to understand full-text components. For a start, you can check whether Full-Text Search is installed by using the following query. If Full-Text Search is not installed, you must re-run the setup. You can create full-text indexes on columns of type CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, IMAGE, XML, and VARBINARY(MAX). Besides using full-text indexes on SQL Server character data, you can store whole documents in binary or XML columns, and use full-text queries on those documents. Columns of data type VARBINARY(MAX), IMAGE, or… Read More

NOTES | Grouping and Windowing

SQL Server can perform some data analysis operations. A data analysis function is a function applied to a set of rows, and it returns a single value. An example of such a function is the SUM aggregate function. A data analysis function can be either a group function or a window function. The two types differ in how you define the set of rows for the function to operate on. You can use grouped queries to define grouped tables, and then a group function is applied to each group. Or, you can use windowed queries that define windowed tables, and… Read More

NOTES | Combining Sets

Using Joins Often, data that you need to query is spread across multiple tables. The more normalized the environment is, the more tables you usually have. The tables are usually related through keys, such as a foreign key in one side and a primary key in the other. Then you can use joins to query the data from the different tables and match the rows that need to be related. Cross Joins A cross join is the simplest type of join, though not the most commonly used one. This join performs what’s known as a Cartesian product of the two… Read More

NOTES | Filtering and Sorting Data

While the traditional way to filter data in T-SQL is based on predicates, it also supports filtering data based on a specified number of rows and ordering. To do so, use TOP and OFFSET. Filtering Data with Predicates ON, WHERE, and HAVING are the three query clauses which enable your filtering of data based on predicates as supported by T-SQL. Predicates, Three-Valued Logic, and Search Arguments Not all filtering aspects are obvious, but you need to understand how to form a predicate to maximize query efficiency and how predicates interact with NULLs; for this, you will need to be familiar… Read More

NOTES | Foundations of Querying

Foundations of Querying T-SQL is the main language used to manage and manipulate data in Microsoft’s main relational database management system (RDBMS), SQL Server—whether on premises or in the cloud (Microsoft Windows Azure SQL Database). It all stems from the mathematical principals of set theory and predicate logic. T-SQL is a dialect of standard SQL and therefore the core elements look the same. However, all leading database vendors decides which features to implement and which to not. Also, each vendor usually implements extensions to the standard in cases where the vendor feels an important feature is not covered by the… Read More

NOTES | A Few Things

In preparing for this exam, my plan is to work my way through each chapter in the Training Kit book, to take notes, gather more information from across the internet and the book SQL Queries For Mere Mortals. In doing so, I’ll be documenting my notes. At times, it can/may be word for word from the training kit or SQL Queries book. This is a summary/collection for me to quickly refer back on, reread or review again.

Preparation | Exam 70-461 (Querying Microsoft SQL Server 2012)

My current goal is to become a Microsoft Certified Solutions Expert: Business Intelligence. Preparing for the exam My plan is to work my way through each chapter in the Training Kit book. I will read each chapter, answer the quick check questions, the lesson review questions, work through the case scenarios and complete all the practice exercises. I will also be watching some YouTube videos and the Udemy course Microsoft SQL Server 2012 Certification Training Exam 70-461 by Infinite Skills as I have read some positive things about it. I will NOT be completing the practice tests that came with the book because I… Read More

MCSE: Business Intelligence | Starting my journey

My goal for 2020/2021 is to become a Microsoft Certified Solutions Expert (MCSE): Business Intelligence. The three Training Kit books arrived today:  Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 Training Kit (Exam 70-462): Administering Microsoft SQL Server 2012 Databases Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012. While the reviews on Amazon range from “useless” to “excellent” they will be a good starting point and guide to the areas I need to learn. I can also search and read online other blogs as well as the Microsoft docs to fill gaps. My own… Read More