SUBSTRING function is used in sql to select part of text string in text. This function belongs to the group of text functions – string functions. I created a big summary of T-SQL text functions – you can find it here. Syntax SELECT SUBSTRING(text, <start position of the text character>, <the number of characters to… Read More »
T-SQL command IF is used to control flow of code mainly in operations where we somehow influence db (DDL, DML operations) – for example procedures. You can mix it up with CASE command quite simply. The common mistake is that we try to push sql IF into SELECT clause. Query in which IF is used… Read More »
This article follows post in which I offered overview of Data types in SQL Server (more in SQL Data types – Overview ). You can find list of all system data types in it. We can now focus on another category from mentioned overview of t-sql Date and time data types. Date data types specification… Read More »
I will follow the previous article on Data types. I will go through numeral data types and their properties. Thanks to correct data type selection you may be able to save disk storage. You only need to think about range of values of column which you want to represent. Then just select the correct type.… Read More »
All columns, variables or terms in SQL Server have their defined data types. These data types define of what type are the values which can be contained in given column or other logical object. Every time you try to create a table , variable or term, a definition defining sql data types is required. Wrong… Read More »
IN operator in t-sql is used in cases where we need to specify a condition e.g in WHERE clause to multiple discrete values at once. It is similar to BETWEEN operator but there is one big difference. Difference between IN and BETWEEN: BETWEEN can be used to define range which is continuous IN is used… Read More »
This operator is used in SQL whenever it is needed to prove if certain value belongs to some range. This operator is used together with “AND” command which defines given range. Operator can be used in most of the clauses but is most common in WHERE for defining limiting requirement. Syntax SELECT [Date] FROM [dbo].[Table]… Read More »
What we call a transaction in SQL is logical set or sequence of operations which belong to this transaction. SQL Transactions can be used if we perform some changes in a databases. Advantage of transactions managing is that performed changes are reversible and we always need to use command COMMIT for confirmation or use ROLLBACK… Read More »
SQL COUNT function belongs to Aggregate functions and it is one of the most easy-to-use and also most used functions of SQL. It enables user to count rows in certain column or rows in whole table. (!!!) NULL records are missed out if we count number of records above certain column using sql COUNT. SQL… Read More »
EXISTS is a T-SQL operator which can be used to test existence of a result in subquery. Based on that it can perform query or some task. Sql EXISTS performs so called “Existence test”. Syntax 1) SQL Query SELECT Columns FROM dbo.Table WHERE EXISTS (SELECT columns FROM Dbo.Table2 WHERE Condition);… Read More »
REPLACE function is used to replace text string or its parts. It belongs to the category of string functions. I created big overview of T-SQL functions some time ago – you can find it here. Syntax of the Function (3 arguments) SELECT REPLACE(<Column with text in which we replace part of the text>, <The string… Read More »
SQL CTE or so called Common table expression is simply put a temporary table (or better said it is a result set) in form of an expression. We can refer to it in SELECT, INSERT, DELETE or UPDATE script after its declaration using clause WITH. We can make recursive queries using CTE (it is even… Read More »
What you will find below is a map of all components and tasks which are part of SSIS Project framed in SQL Server Data Tools 2015. Particular components and tasks can be found in SSIS Toolbox. I plan on connecting particular definitions in this map with BI dictionary. If you will be interested, I can… Read More »
We can add a column to the table in two ways. First is using sql server management studio graphical interface. Second one is using SQL command ADD COLUMN. T-SQL ADD COLUMN Syntax: ALTER TABLE dbo.Table ADD <Column> <Data Type> NULL (or) NOT NULL Column will be created on the end of the table. If the… Read More »
This article is meant to be some kind of a tutorial. After going through it, it should be clearer to you how to create Power Bi report like this on your own. Following this, you can create basically any report you want – company operation report (timesheets, wages, investments), financial analytics (balance sheet, income statement,… Read More »
We distinguish two types of commands with data deleting purpose – SQL commands DELETE and TRUNCATE. Each is suitable for different situations. It is good to know them both and be able to choose the right one in a specific situation. DELETE Syntax (First Command is Optional): DELETE FROM dbo.Table WHERE <Condition>; or DELETE dbo.Table WHERE… Read More »
Were you ever creating a report and did not find a feature in your Power BI Desktop that would enable you to visualize data according to your needs? What are Power BI Custom Visuals? Custom Power BI visuals were created by members of power BI community – programmers, users, and fans of this wonderful tool.… Read More »
Creating a table in the SQL Server database is a basic skill. While creating it using SQL command CREATE TABLE we define: Name of the table together with database definition and scheme List of the columns with appropriate data types, constraints and definition if the column can be empty (NULL vs. NOT NULL) Table can… Read More »
ISNULL and COALESCE are functions used for work with null values (NULL). Both functions return first value out of previously defined entry parameters. There are also some differences between both functions. ISNULL Syntax: SELECT ISNULL(<Column>, <Column Replacement – other column or logic>) FROM dbo.Table COALESCE Syntax: SELECT COALESCE(<Column>, <Column Replacement – other column or logic… Read More »
Views have definitely their place in databases and data warehouses. We are talking about objects which are good to use since they do not contain data (do not take up place in storage). They consist only of table query. More complex views can be more expensive for logical operations mostly with complicated queries with multiple… Read More »