Full-text search is one of the needs of an application to find data in a database. The full-Text Index feature in SQL Server allows you to run a full text search on a database table. In this article, we will learn about full-text index in SQL Server, including what full-text index is, how to create a full-text search index, and other use cases.

What is Full-Text Index in SQL Server?
Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables. Full-Text index helps to perform complex queries against character data. These queries can include word or phrase searching. Before we can run full-text queries on a table, we first need to create a full-text index on the table. Only one full-text index is allowed per table, and this index can contain up to 1024 columns. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, char, nvarchar, text, ntext, image, XML, or varbinary.

Full-text queries perform searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase. A full-text query returns any document that contains at least one match (also known as a hit). A match occurs when a target document contains all the terms specified in the full-text query and meets any other search conditions, such as the distance between the matching terms.

Why do we need a Full Text Index (FTI) if we can use a statement for searching?
Let us consider a scenario; I have a table with column name data. What query will we use if we want to search for the name ‘smith’ in the data column, basically, we use the command below.

The above query is efficient for the above scenario, but what if you're not looking for an exact match? FTS has some better algorithms for matching data, as does some better statistics on variations of names. Therefore, FTS can provide better performance for matching Smith, Smythe, Smithers, etc., when you look for Smith. In such a case, FTS provides better results compared to the traditional like method.
When to use FTI over the LIKE statement?

    A word or phrase close to the search word or phrase
    When the result size is several hundred thousand
    Millions of rows, each with a string like "wordAwordBwordC..."
    Any word derived from a particular root (for example, run, ran, or running)

How to Create a Full-Text Index?
Now, I will explain how to create a full-text index. But, first, we will read two methods to create the full-text index, using manually and using the SQL command.

Create Full-Text Index Manually
The following steps are performed to create the Full Text Index.
    Create a Full-Text Catalog
    Create Full-Text Index
    Populate the Index

1. Create a Full-Text Catalog
The full-text catalog is used for the full-text index. If we don’t specify the full-text catalog, then SQL Server will use the default catalog. So now we have learned how to create a full-text catalog.

To create a full-text catalog, select your database, go to the Storage folder, right-click on Full-Text Catalog, and select the New Full-Text Catalog option.

Now provide a name for the full-text catalog.

You can see that a new catalog has been created in the Storage folder.

2. Create Full-Text Index
To create a full-text index choose your table and right-click on that table and select the “ Define Full-Text Index” option.

Now select Unique Index. It is compulsory that for “Full-Text Index” table must have at least one unique index.

Select columns name and language types for columns. You can only select character-based and image-based columns.

Select change tracking.

Now select the full-text catalog for the index.

The last image confirms that the full-text index is created successfully. Now we populate this full-text index.

3. Populate the Index
To populate the index, right-click on the table and select the “Start Full Population” option.

Create Full-Text Index using SQL Command
Use the following command syntax to create the Full Text Index.

Syntax
CREATE FULLTEXT INDEX ON table_name
[ ( { column_name
[ TYPE COLUMN type_column_name ]
[ LANGUAGE language_term ]
[ STATISTICAL_SEMANTICS ]
} [ ,...n]
) ]
KEY INDEX index_name
[ ON<catalog_filegroup_option> ]
[ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]
[;]

<catalog_filegroup_option>::=
{
fulltext_catalog_name
| ( fulltext_catalog_name, FILEGROUP filegroup_name )
| ( FILEGROUP filegroup_name, fulltext_catalog_name )
| ( FILEGROUP filegroup_name )
}

<with_option>::=
{
CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }
| STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }
| SEARCH PROPERTY LIST [ = ] property_list_name
}

Parameters

Example

CREATE FULLTEXTCATALOG New_CatalogASDEFAULT;
CREATE FULLTEXTINDEX ON dbo.Employee(EmployeeName TYPECOLUMN varchar LANGUAGE 1033,EmpSalary TYPECOLUMN varchar LANGUAGE 1033)
KEY INDEX UN_Pankaj
ON
New_Catalog

After creating the FULL Text Catalog and Full Text Index, we now learn how to use these in search queries for better performance. There are four principal T-SQL functions that allow one to interact with your Full-Text indices:

CONTAINS and  FREETEXT Method
CONTAINS and FREETEXT functions return a boolean value, meaning we could use them directly in a WHERE clause. The remaining two return a two-column table—KEY and RANK, allowing one to manage ranked searches.

FREETEXT
FREETEXT T-SQL function performs predicate searches for values that match the meaning and not just the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches:

• Separates the string into individual words based on word boundaries (word-breaking).
• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

Example
SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski
WHERE
FREE TEXT(Keyword_Text,'Hotel Above')

Output

CONTAINS
CONTAINS searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server. It is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search, then we need to provide the “and” or “or” in search.

CONTAINS can search for
    A word or phrase.
    The prefix of a word or phrase.
    A word near another word.
    A word is inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
    A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

Example
SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski
WHERE
CONTAINS(Keyword_Text,'Hotel OR Above')

Use FULL-Text Index search when you have a large volume of data, and you want to perform a search for textual data columns for specific words and phrases. Full-Text Index can be used to search words, phrases, and multiple forms of a word or phrase using FREETEXT (), CONTAINS () with “and” or “or” operators (FREETEXT, CONTAINS).

HostForLIFEASP.NET SQL Server 2019 Hosting

A SQL Server Index is used on a database table for faster data access. In this article, learn what SQL Server Indexes are, why we need them, and how to implement them in SQL Server.

Indexes in SQL Server
SQL Indexes are used in relational databases to retrieve data quickly. They are similar to indexes at the end of the books whose purpose is quickly finding a topic. SQL provides Create Index, Alter Index, and Drop Index commands used to create a new index, update an existing one, and delete an index in SQL Server.

• Data is internally stored in a SQL Server database in "pages" where the size of each page is 8KB.
• A continuous eight pages are called an "Ex. "nt."
• When we create the table, one extent will be allocated for two tables; when it is computed, it is filled with the data. Then another time will be given, and this extent may or may not be continuous to the first extent.

Table Scan
In SQL Server, a system table with the name sysindexes contains information about indexes available on tables in the database. If a table has no index, there will be one row in the sysindexes table related to that table indicating no index on the table when you write a select statement with a condition where clause, the first SQL Server will refer to the "indid" (index id).

Columns of the "Sysindex" table determine whether or not the column on which you write the conditions has an index. When that indid columns, to get an address of the table's first extent and then searches each row of the table for the given value. This process checks the given condition with every table row, called a table scan. A drawback of table scan is that if there is no increase in rows in the table, the time taken to retrieve the data will increase, affecting performance.

Type of Indexes
SQL Server supports two types of indexes:

• Clustered Index
• Non-Clusterd Index.

Clustered Index in SQL Server
A B-Tree (computed) clustered index is the Index that will arrange the rows physically in the memory in sorted order.

An advantage of a clustered index is that searching for a range of values will be fast. A clustered index is internally maintained using a B-Tree data structure leaf node of the btree of the clustered Index will contain the table data; you can create only one clustered Index for a table.
How do we Retrieve the data with clustered Index?

When you write a select statement with a condition in a where clause, then the first SQL Server will refer to the "indid" columns of the "Sysindexes" table and when this column contains the value "1".

Then it indexes the table with a clustered index. In this case, it refers to the columns ." The Root" node of the B-tree of clustered index searches in the b-tree to find the leaf node that contains the first row that satisfies the given conditions and retrieves all the rows that satisfy the given situation that will be in sequence.

Insert and Update with Clustered Index
Since a clustered index arranges the rows physically in the memory in sorted order, insert and will become slow because the row must be inserted or updated in sorted order.
Finally, the page into which the row must be inserted or updated, and if free space is not available on the page, create the free space and then perform the insert, update and delete.

To overcome this problem while creating a clustering index, specify a fill factor, and when you specify a fill factor as 70, then in every page of that table, 70% will fill with data, and the remaining 30% will be left free. Since free space is available on every page, the insert and update will be fast.

Nonclustered Index in SQL Server

A nonclustered index is an index that will not arrange the rows physically in the memory in sorted order.
An advantage of a nonclustered index is that searching for the values in a range will be fast.
You can create a maximum of 999 nonclustered indexes on a table, 254 up to SQL Server 2005.
A nonclustered index is also maintained in a B-Tree data structure. Still, leaf nodes of a B-Tree of the nonclustered Index contain the pointers to the pages that contain the table data and not the table data directly.

How do we Retrieve data with a nonclustered index?
When you write a select statement with a condition in a where clause, then SQL Server will refer to the "indid" columns of the sysindexes table, and when this column contains a value in the range of 2 to 1000, then it indicates that the table has a non –clustered Index. In this case, it will refer to the columns root of the sysindexes table to get two addresses.

The root node of a B-Tree of a nonclustered index, and then search in the B-Tree to find the leaf node that contains the pointers to the rows that contain the value you are searching for and retrieve those rows.
Insert and Update with a Nonclustered Index

There will be no effect of inserting and updating with a nonclustered index because it will not arrange the row physically in the memory in sorted order.
With a nonclustered index, rows are inserted and updated at the end of the table.

How to Create Indexes in SQL Server?
Use the create index command with the following system to create an index.
create [unique][clustered /non clusted] index :
<indexname> on <object name>(<column list>)
[include(<columnlst>)]
[with fillfactor=<n>]

By default, an index is nonclustered.

For example, the following examples create a nonclustered index on department_no of emp tables.
create index DNoINdex on Emp(DeptNo)

Simple & Composite Indexes

• Based on the number of columns on which an index is created, indexes are classified into simple and composite indexes.
• When indexes are created on single columns, it is called a simple index; when combined with multiple columns, it's called a composite index.

For example, the following example creates a nonclustered index in combination with the emp table's department number and job columns.
create index dnotedxi on emp(deptno asc,job desc)

Unique Index
    When an index is created using the keyword unique, it is called a unique index; you create a unique index on columns, and a unique index constraint will be created.
    If the columns on which you create a unique index contain duplicate values, then a unique index will not be created, and you will get an error.

Altering an Index
To alter an index, use an alter index command that has the following syntax:
    Alter index <ind Name> on <object Name> 
    rebuild/Recognize/Disable.

Alter the Index using the rebuild option. The Rebuild option will recreate the computer index; the recognize option will reorganize leaf nodes of the b-tree to Index. The disable option will disable the Index when it is eligible and then enable it.

For example, the following example alters the index "d" oidx" "available on the department number of columns on the emp table.
alter index DNOiDX on EMp rebuild

Getting a list of indexes
This stored procedure is used to compute a list of indexes available on a table.
sp_helpindex 'Stud'

Deleting indexes
Use the drop index' command that has the following syntax:
drop index <indexname> on <object name>

For example, the following example deletes the Index dnoidex available on the department number columns of the emp table.
drop index doindex on student

HostForLIFEASP.NET SQL Server 2019 Hosting

What is Temporal Table?
A system-versioned temporal table is a type of table in SQL Server that automatically tracks the historical changes to the data in the table, by keeping a separate history table that stores all the previous versions of the data. This allows you to easily see how the data has changed over time and also enables you to perform temporal queries to retrieve the data as it existed at a specific point in time. The system-versioning is done by using two columns, one for the start date and one for the end date, that keep track of when the data was valid. The system manages this automatically and transparently to the user.

You can also create a temporal table by specifying the Transact-SQL statements directly, as shown in the example below. Note that the mandatory elements of every temporal table are the PERIOD definition and the SYSTEM_VERSIONING clause with a reference to another user table that will store historical row versions:

CREATE TABLE [dbo].[AppUsers]
    (
        [UserID] int NOT NULL PRIMARY KEY CLUSTERED
      , [UserName] nvarchar(100) NOT NULL
      , [PagesVisited] int NOT NULL
      , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN
      , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END HIDDEN
      , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
     )
     WITH
     (
        SYSTEM_VERSIONING = ON
        (
            HISTORY_TABLE = dbo.AppUsersArchive,
            HISTORY_RETENTION_PERIOD = 2 MONTHS
        )
     );

ALTER TABLE AppUsers
    ADD
        ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN
            constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())
        , ValidTo datetime2 (2) GENERATED ALWAYS AS ROW END HIDDEN
            constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
        , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

    ALTER TABLE AppUsers
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AppUsersArchive));

Advantage of Temporal Table

The main advantage of using a system-versioned temporal table is the ability to easily track and analyze changes to the data over time. Some specific benefits include:

• Auditing: Temporal tables can be used to track changes to the data, making it easier to identify who made a change and when.
• Point-in-time reporting: You can retrieve the state of the data at a specific point in time, which is useful for generating historical reports or analyzing trends over time.
• Data recovery: If data is accidentally deleted or modified, you can easily recover the previous version of the data.
• Improved data integrity: By keeping a separate history table, temporal tables can help ensure that historical data is not accidentally modified or deleted.
• Improved performance: By keeping a separate history table, temporal tables can help improve query performance for certain types of queries that retrieve historical data.

It's worth noting that temporal tables are only available in SQL Server 2016 and later.

Temporal Table Considerations and Limitations

There are some considerations and limitations to be aware of when working with temporal tables, due to the nature of system-versioning:

• A temporal table must have a primary key defined in order to correlate records between the current table and the history table, and the history table can't have a primary key defined.
• The SYSTEM_TIME period columns used to record the ValidFrom and ValidTo values must be defined with a datatype of datetime2.
• By default, the history table is PAGE compressed.
• While temporal tables support blob data types, such as (n)varchar(max), varbinary(max), (n)text, and image, they'll incur significant storage costs and have performance implications due to their size. As such, when designing your system, care should be taken when using these data types.
• History table must be created in the same database as the current table. Temporal querying over linked servers isn't supported.
• History table can't have constraints (primary key, foreign key, table or column constraints).
• TRUNCATE TABLE isn't supported while SYSTEM_VERSIONING is ON.
• Direct modification of the data in a history table isn't permitted.
• INSTEAD OF triggers aren't permitted on either the current or the history table to avoid invalidating the DML logic. AFTER triggers are permitted only on the current table. 
• They're blocked on the history table to avoid invalidating the DML logic.

Hope the article would have helped you in understanding Temporal tables.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will see how to dynamically get a database List using C#.

Step 1. Used Namespaces
using System.Web.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

Step 2. Referenced DLL

Step 3. Connection String:
<add name="<KEY>" connectionString="Data Source=<SERVERNAME>;Initial Catalog=<DATABASE>;Integrated Security=True" />

Step 4. Used to get Connection String from Web.config file.
public static string GetConnectionStringFromWebConfigByName(string name)
{
    return WebConfigurationManager.ConnectionStrings[name].ConnectionString;
}

Step 5. Usage
DatabaseCollection databases =  SQLDatabaseClass .DbCollection(connectionString);

Here you can get the collection of a database as a List.

Database database =  SQLDatabaseClass.SinglDatabase(connectionString);

Here you can get a single database by its name.

Step 6. Creating a Connection String for server connection.
public static SqlConnection Connecection(string connectionString)
{
    SqlConnection con = new SqlConnection(connectionString);
    return con;
}

Step 7. Create a Server connection using a Connection string.
public static ServerConnection GetServerConnection(string connectionString)
{
    ServerConnection serverCon = new ServerConnection(Connecection(connectionString));
    serverCon.Connect();
    return serverCon;
}

Step 8. Creating Server Object.
public static Server GetServer(string connectionString)
{
    Server server = new Server(GetServerConnection(connectionString));
    return server;
}

Step 9. Getting Database collection from the SQL Server:
public static DatabaseCollection DbCollection(string connectionString)
{
    Server server = GetServer(connectionString);
    return server.Databases;
}

Step 10. Getting a specific database from the collection of databases in the SQL Server:
public static Database SinglDatabase(string connectionString, string databaseName)
{
    return GetServer(connectionString).Databases[databaseName];
}

Code Snippet
class SQLDatabaseClass
{
    #region Database

    public static DatabaseCollection DbCollection(string connectionString)
    {
        Server server = GetServer(connectionString);
        return server.Databases;
    }

public static Database SinglDatabase(string connectionString, string Name)
{
    return GetServer(connectionString).Databases[databaseName];
}

#endregion

public static Server GetServer(string connectionString)
{
    Server server = new Server(GetServerConnection(connectionString));
    return server;
}

public static ServerConnection GetServerConnection(string connectionString)
{
    ServerConnection serverCon = new ServerConnection(Connecection(connectionString));
    serverCon.Connect();
    return serverCon;
}

public static ServerConnection GetServerConnectionByLogin(bool isWindows, string serverName)
    {
        ServerConnection serverCon = new ServerConnection();
        serverCon.LoginSecure = isWindows;
        serverCon.ServerInstance = serverName;
        serverCon.Connect();
        return serverCon;
    }

    public static SqlConnection Connecection(string connectionString)
    {
        SqlConnection con = new SqlConnection(connectionString);

        return con;
    }
}

Thanks for reading this article. I hope you have a nice day.

HostForLIFEASP.NET SQL Server 2019 Hosting

If you work with a SQL Server database, I'm sure you have heard of .mdf and .ldf files. When you install a new SQL Server database server on a machine, and create a database, these files are created on a hard drive. In this blog, let's learn about mdf and ldf files.

MDF - Main Database File
    It contains all the main information of the database that is part of the server.
    It plays a crucial role in information storage.

Note - All the successful queries go in MDF.

LDF - Log Database File
    It stores information related to transaction logs for the main data file.
    It stores changes related to CRUD Insert, Delete, and Update.

Note - All the unsuccessful or stuck queries go in LDF.

IMP Image Note
The below image shows the .mdf and .ldf files of EmployeeDB Database.

Difference between MDF and LDF

HostForLIFEASP.NET SQL Server 2019 Hosting

Common Table Expression (CTE) in SQL offers a more readable form of a derived table. A Common Table Expression is an expression that returns a temporary result set. This result set is similar to a hybrid Derived Table. The resultset can be declared once and referenced multiple times in a query. It does not require any extra effort to declare it. CTE is more powerful than the derived table. It can self-reflect, and we can also use CTE multiple times in the same query. Mainly, CTE improves readability and makes it easy to maintain complex queries. CTE can be used for selects, and DML (Insert, Update, and Delete) statements.

Common Structure of CTE 

;WITH CTE_name [ ( column_name [,...n] ) ]
AS
(
query_definition
)
select * from CTE_name;

The Common Table Expression is created using the WITH statement followed by the CTE name and List of Columns (specifying a column is optional). After the "AS," the information used to populate the returning columns begins. The CTE is then followed by a select calling it. Always start CTE with a semi-colon.

Example

Step 1. Create a query 

The following is a sample of creating two tables, EmployeeMasters and DepartmentMasters, and inserting some default values into them.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DepartmentMasters]') AND type in (N'U'))
DROP TABLE [dbo].[DepartmentMasters]
CREATE TABLE [dbo].[DepartmentMasters](
[DepartmentId] [int] IDENTITY(1,1) NOT NULL,
[DepartmentCode] [varchar](50) NULL,
[DepartmentName] [varchar](50) NULL,
CONSTRAINT [PK_DepartmentMasters] PRIMARY KEY CLUSTERED
(
[DepartmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeMasters]') AND type in (N'U'))
DROP TABLE [dbo].[EmployeeMasters]
CREATE TABLE [dbo].[EmployeeMasters](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [varchar](50) NULL,
[EmployeeCode] [varchar](50) NULL,
[DepartmentId] [int] NULL,
CONSTRAINT [PK_EmployeeMasters] PRIMARY KEY CLUSTERED
[EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[EmployeeMasters] WITH CHECK ADD CONSTRAINT [FK_EmployeeMaster_DepartmentMaster] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[DepartmentMasters] ([DepartmentId])
GO
ALTER TABLE [dbo].[EmployeeMasters] CHECK CONSTRAINT [FK_EmployeeMaster_DepartmentMaster]
GO
INSERT INTO DepartmentMasters VALUES
('Eaxm', 'Examination'),
('Staff', 'Exam Staff')
INSERT INTO EmployeeMasters VALUES
('Peter','D0093',1),
('Scott','D0094',1),
('Mike','D0095',1),
('Friedrich','D0096',2),
('Kenneth','D0097',2)

Step 2. Writing CTE Query

The following is a sample use of a CTE Query.

;WITH emp_detail(EmployeeName,EmployeeCode,DepartmentCode)
AS
(
SELECT e.EmployeeCode,e.EmployeeName,d.DepartmentCode FROM EmployeeMasters e JOIN DepartmentMasters d ON e.DepartmentId=d.DepartmentId
)
SELECT * FROM emp_detail;

When to Use CTE in SQL?

CTE offers the same functionality as a View (ideal for one-off usages). CTE provides the following four advantages.

• Create a recursive query. 
• Alternative from a view when the general use of an idea is not required, a case in which you do not have to store the definition in metadata.
• Enable grouping by a column derived from a scalar subselect or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement. 

CIt can not use with CTE

• The clauses like ORDER BY, INTO, OPTION clause with query hints, FOR XML, and BROWSE cannot be used in the CTE query definition.
•  "SELECT DISTINCT," GROUP BY, PIVOT, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN, and Subqueries "are not allowed in the CTE query definition of a recursive member.
•  A CTE can be self-referencing and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
• Specifying more than one "WITH" clause in a CTE is prohibited. For example, if a CTE query definition contains a subquery, then that subquery cannot have a nested WITH clause to define other CTE. 

Summary
CTE provides a more readable and usable approach to derived tables. CTE is not materialized into a work table (temporary table). CTEs are not a replacement for temporary Tables. The scope of the CTE is limited to the first SELECT statement only.

HostForLIFEASP.NET SQL Server 2019 Hosting

SQL Server TempDB is a special database in SQL Server that stores temporary objects. It allows you to partition your workloads such that frequently accessed data is stored in memory and less frequently accessed data can be stored on disk, reducing contention for resources. It not only provides you temp storage but also helps improve the performance of the database queries and reduces workloads of the front-end applications as well.

You rarely use TempDB directly, but it has many functions behind the scenes and it is always in use by SQL Server to ensure the performance and responsiveness of some system and non-system databases. For example, if a database operation is too large that needs more memory and storage than the database server currently has, the server uses TempDB as the temp storage to help execute the operation. You can think of TempDB as virtual memory and storage on the OS level that is used when Windows OS needs extra memory and storage.

What is SQL Server TempDB
    TempDB is a system database used by SQL Server (and other RDBMS).
    Apps requiring heavy sorting, grouping, etc. can use TempDB to offload temporary data.
    TempDB files are recreated every time SQL Server starts, so the data in them will be lost after a restart.
    You can use TempDB to store your custom tables, query results, views, and variables.

What is stored in TempDB?
TempDB is a system database. Its name is derived from the fact that it stores temporary user objects. These are tables, stored procedures, table variables, cursors, or derived tables that contain intermediate results when processing queries.

SQL Server uses the TempDB database for various purposes such as the storage of temporary user objects like tables, temporary stored procedures, table variables, cursors, or derived tables that contain intermediate results when processing queries and for internal SQL Server system objects such as row versioning information.

This database is used for sorting and grouping large amounts of data during the execution of a query. It can also be used for storing rows returned by an INSERT statement in batches (one batch per thread). The size of each row varies but is usually 8 KB or 16 KB for small tables and large rows respectively. For example: If you are inserting a million rows into your table with 100 columns each then you would have 100 million pages in tempdb.*

How to access TempDB?
The TempDB is a system database and in automatically created when a SQL Server is installed on a machine. You can access TempDB object by executing queries on TempDB or using SSMS.

Let’s look at the SSMS system databases installed by default when a new SQL Server is installed. As you can see from the following image, four system databases are installed by default when a new SQL Server is installed, master, mode, msdb, and tempdb.

If you expand tempdb database, you will see Tables, Views etc. However, until you start working with databases, they all will be empty.

Where is TempDB location?
The default location of tempdb database is the data folder same where other system databases are. If you right click on tempdb in SSMS, select Properties and select Files, you can find the exact location of tempdb.mdf and other supporting files. The templog file is also in the same folder.

How to move TempDB to a Different Drive or Folder?
Often times, we do not want to store tempdb data and log files on our main drive. You can change the default location of tempdb data and log files by moving them to another drive or folder using the ALTER DATABASE statement.

Run the following command on tempdb.
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = N'D:\tempdbstorage\tmp.mdf');
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = N'D:\tempdbstorage\tmplog.ldf');
GO

Once you execute the above queries, Stop and Restart SQL Server service to make this change in effect.

Also, don’t forget to delete old tempdb.mdf and templog.ldf files.

How to create a Temporary Table in TempDB?
We can also use tempdb explicitly by createng tables, views etc on a tempdb. We can also create regular table as well as temp tables. The following queries create two tables in tempdb, CSharpCorner and Csharp2.
CREATE TABLE CSharpCorner (ID int, data text);
CREATE TABLE #CSharp2 (ID int, name text);

Select tempdb in SSMS and execute above queries in query explorer on tempdb. Right click on tempdb, select New Query, type above SQL statements and execute.

Now refresh tempdb, Right click on tempdb, select Refresh.

Expand Tables and Temporary Tables nodes.

You will CSharpCorner tabe and CSharp2 temporaty table in the database.

To learn how to work with temproraty tables, read Working with Temporaty Tables in SQL Server.

The tempdb is also used to store Temporary Variables. Temporary Variables are used to store data temporarily in tempdb.

Apps requiring heavy sorting, grouping etc. can use TempDB to offload temporary data
You can use TempDB to offload temporary data from the transactional tables. For example, if you are writing an OLTP application that requires heavy sorting and grouping operations, you can use TempDB to store these temporary results.

Sorting or grouping is a common operation when you are processing large amounts of data in a relational database. However, sorting is one of the most expensive operations in traditional databases because it involves disk I/O as well as CPU utilization by the query processor (SQL Server).
TempDB files are recreated every time SQL Server starts.

The TempDB is a system database. It's not specific to any SQL Server instance, but it's always created when you start SQL Server.

For this reason, TempDB is recreated every time SQL Server starts. This happens regardless of whether it was dropped or not, and also if the files are manually deleted (but not using RECREATE).

If TempDB is on an SSD it performs better compared with HDD
If the TempDB database is on an SSD, it performs better compared with a HDD. SSDs are faster, more reliable and durable than HDDs, but they are also more expensive.
TempDB should be one single file per core - up to 8 cores.

SQL Server recommends that TempDB be one single file per core - up to 8 cores. The number of cores in a server can be determined by using the following query:
SELECT * FROM sys.dm_os_sys_info WHERE name='max degree of parallelism'

To determine the number of associated data files for an instance, you can use this script:
select * from sys.master_files

You can monitor TempDB usage using sys.dm_db_task_space_usage and sys.dm_exec_query_resource_semaphores DMVs.

In general, you can monitor TempDB usage using sys.dm_db_task_space_usage and sys.dm_exec_query_resource_semaphores DMVs.
    sys.dm_db_task_space_usage: This DMV returns information about the memory used by all user processes that are connected to the instance of SQL Server. The information includes a breakdown of how much memory each process is using in its buffers, as well as whether any allocations have been deferred or not yet committed by a specific session.
    sys.dm_exec
    exec (sqlserver) This function returns a recordset object that represents an execution plan for an SQL statement compiled with the specified options and parameter values—or no such plan if there is none available because of insufficient system resources or incompatible settings on this instance of SQL Server2005 2005 Management Studio (SSMS).

How to distribute TempDB files for better performance?
When multiple tempdb files are used, make sure they are evenly distributed across different physical disks to improve performance and reliability (e.g., by putting the tempdb files on separate physical disks).

When monitoring the performance of SQL Server, look at the disk I/O subsystem performance. If a disk is reached its maximum throughput, it becomes the bottleneck for SQL Server's workload. If you experience lower than expected CPU utilization, it may indicate that your I/O subsystem is limiting your system’s overall performance instead of CPU resources being consumed by other tasks such as data compression or encryption operations running concurrently with other workloads.

HostForLIFEASP.NET SQL Server 2019 Hosting

If you have a Web API or a Web Service and want to call from SQL Server to get data in your database, you can do that. In this blog, let's learn how to call a Web API or a Web Service form SQL Server.

Enable configuration in SQL
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Sample API link
-- Sample API Link
http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile

Sample JSON Request
@Mobile varchar(max),
@Flag varchar(max)

{
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Content Type
application/json

Create Store Procedure for Calling Web API.
--//========================
    -- if you learn more please visit my blog

    -- https://saipathrikar.blogspot.com/
--//========================
--================ execute this 1st for 1st time use only

Create Proc Proc_CallApiFromSQL
(
@Mobile varchar(max),
@Flag varchar(max)
)
as
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
 declare @json table (Json_Table nvarchar(max))
 declare @body varchar(max)
 declare @Apilink varchar(max)
 set @Apilink='http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile';
 set @body='

 {
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'post',@Apilink,'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
Exec sp_OAMethod @Object, 'send', null, @body
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

  INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
--select * from @json
-- Parse the JSON string
SELECT * FROM OPENJSON((select * from @json))
WITH (
CID bigint,CName varchar(max),AC_Balance varchar(max),AC_No varchar(max),AC_ID bigint,CDOB datetime,
CEmail varchar(max),ReciverEmail varchar(max),CMObile varchar(max),CGender varchar(max),CPan varchar(max),
CAdhaar varchar(max),CNationality varchar(max),CAddress varchar(max),City varchar(max),State varchar(max),
Country  varchar(max),PinCode varchar(max),Cisdelete bit,CreatedBy varchar(max),CreatedDate datetime,ModifiedBy varchar(max),
ModifiedDate datetime,UID bigint,CustImgPath varchar(max),CustAdaarPath varchar(max),CustPanPath varchar(max),
Flag varchar(max),OpMsg varchar(max),Pass varchar(max)
)
return

Execute Stored Procedure
EXEC  Proc_CallApiFromSQL '8541254874','SE'

Output

HostForLIFEASP.NET SQL Server 2019 Hosting

In our earlier article, we had learned how to create a login in SQL Server with help of GUI as well as T-SQL script. Granting read, write, execute, create, etc. in SQL Server comes under a security context, and being a Database Administrator, it’s very important to make sure that a user must have sufficient permission to access the database and its objects once he/she gets a new login for the server.

The Database Administrator must ensure that no other anonymous user can access the database to perform any unauthorized activity.

By default, the SQL Server denies access to database objects after you create a login for a user. Therefore, if you want to access the database objects with your login, you must have sufficient permissions provided by your database administrator.

Before proceeding, below are the details we’ve provided for the demo:
    Database: CSharpCornerDB
    User: Peter
    Connection Required: 2 (Admin Session, SQL Login Session)

Currently, we’ve connected with Admin login and have three tables with some data in it.

You can find attached script to create database and objects used in this demo. If you want to grant permissions to any user, you must be a member of sysadmin, db_owner or the SA account. Let’s begin with the demo.

Connect to your server session with the SQL login as in the following:

After successful authentication, you’ll get connected to the server with the username as in the following:

As we mapped our user to CSharpCornerDB database, we’ll connect to it. But if you try to connect to other database, you’ll end up with the following error message.

Msg 916, Level 14, State 1, Line 1
The server principal "MChand" is not able to access the database "SQL432DB" under the current security context.

Also if you try to expand other database node, SQL Server throws the following error message window. And the '+' sign disappears as shown below.

So, this simply means that the user can only connect to the database on which it is mapped. We’ll select our mapped database i.e. CSharpCornerDB. As we already know, we have three tables in our database, as mentioned earlier. But if you expand the Tables folder you won’t find any table in it. You’ll only get the following output:

Also, if you try to execute the following query to get data in tables, you’ll get the following error message.
    SELECT * FROM tblArticles 
    SELECT * FROM tblCategories 
    SELECT * FROM tblAuthors 

Error Message:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'tblArticles', database 'CSharpCornerDB', schema 'dbo'.

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblCategories', database 'CSharpCornerDB', schema 'dbo'.

Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'tblAuthors', database 'CSharpCornerDB', schema 'dbo'.

This is nothing new but the default behavior of SQL Server to deny access on database objects after creating new login for user. If we want to access those objects, we require read permission for the user with which we’re connected.

To provide READ permission to the user on the database, connectthe server with your admin account or with Windows account and follow the below steps.

Expand Security - Logins, then Right Click on login and then click Properties.

You’ll get Login Properties window as in the following screenshot:

Go to User Mapping tab and select the database on which you want to give permission and at bottom select db_datareader as shown below.

Click Ok and you’re done.

The following is the T-SQL for the same.

    USE [CSharpCornerDB] 
    GO 
    EXEC sp_addrolemember N'db_datareader', N'Peter' 
    GO 

Now go to the session on which you’re connected with Peter login and refresh the Tables folder. If you see all the tables are now visible and are listed below.

Now if you execute the following select query, you’ll be able to see all the data in the table.

    USE CSharpCornerDB 
    GO 
    SELECT * FROM tblArticles 
    SELECT * FROM tblCategories 
    SELECT * FROM tblAuthors 

Also, if you run SELECT command to get table details, you’ll get output for both.

Now let’s insert another record into table tblArticles.
    USE CSharpCornerDB 
    GO 
    INSERT INTO tblArticles values('Strings in C#','Scott') 
    GO 

If you execute the above query, you’ll encounter the following error message:

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'tblArticles', database 'CSharpCornerDB', schema 'dbo'.

The error message simply states that INSERT permission is denied on the object. This means we don’t have permission to write any data in the database objects.

To give WRITE permission on database to the user, follow below steps.

Security - Logins, then double click on Login and you’ll get Login Properties window.

Go to User Mapping Tab and select the database on which you want to give permission.

Under ‘Database role membership for’ section, check ‘db_datawriter’ checkbox as shown below.

Click Ok and you’re done.

The following is the T-SQL script for the same.
    USE [CSharpCornerDB] 
    GO 
    EXEC sp_addrolemember N'db_datawriter', N'Peter' 
    GO 

Now if you execute INSERT command from Peter session, it’ll add the record without any error.

After getting WRITE permission, you can run INSERT, UPDATE and DELETE commands.

If you want to revert back the action you can execute the following query from admin session.
    USE [CSharpCornerDB] 
    GO 
    EXEC sp_droprolemember N'db_datareader', N'Peter' 
    GO 
    USE [CSharpCornerDB] 
    GO 
    EXEC sp_droprolemember N'db_datawriter', N'Peter' 
    GO 

Conclusion
So, this is an article based on SQL Security where we learned how to provide read/write permission for user to read and write data from/to SQL Server database. If you are new to SQL, please read What is SQL.

Hope this article helps you o understand how to provide read write permissions. There are also other permissions which you can try with database on your server. If you need any help in this, please let me know. I’ll try my best to solve those.

Please provide your valuable feedback and comments that will help me in providing a better article the next time. Till then keep learning and keep sharing.

HostForLIFEASP.NET SQL Server 2019 Hosting

Simplify complex joins and subqueries using SQL Server Common Table Expressions or CTEs. It also provides a way to query hierarchical data. This article provides a complete overview of CTEs, types of CTEs, benefits, drawbacks, and how to use them with SQL Server.

A Common Table Expression or CTE is a short-lived named result set created from an easy SELECT statement employed in a subsequent SELECT statement. Every SQL CTE is sort of a named query, the result of which is kept in a very virtual table (a CTE) which is referenced later within the main query.

Syntax
WITH cte_expression_name[ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

To view the CTE results, use a select query with the name of the CTE expression.
Select [Column1,Column2,Column3,Column4...] from cte_expression_name

OR

SELECT * FROM cte_expression_name

Types of CTE(Common Table Expression)
There are 2 types of CTEs,
    Recursive CTE.
    Non-Recursive CTE.

Recursive CTE
A Recursive common table expression (CTE) could be a CTE that references itself. By doing so, the CTE repeatedly executes, and returns subsets of information, till it returns the whole result set.

A recursive CTE is beneficial in querying ranked data adore organization charts wherever one worker reports to a manager or multi-level bill of materials once a product consists of many components, and every component itself additionally consists of many different components.

We'll see how to use a CTE to create a simple recursive query that displays row numbers from 1 to 10.

First, he declared an integer variable as 'RowNo', set the default value to 1, and created our first CTE query as the expression name 'ROW_CTE'. This CTE first displays the default row number, then uses union ALL to increment the row number by 1 until the row number reaches the incremented value of 10. To view the results, use the query of your choice to view the CTE results.
Declare @RowNo int =1;
;with ROW_CTE as
   (
      SELECT @RowNo as ROWNO
        UNION ALL
      SELECT  ROWNO+1
  FROM  ROW_CTE
  WHERE RowNo < 10
    )

SELECT * FROM ROW_CTE

Output
ROWNO
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

Non-Recursive CTE
Non-Recursive CTEs are easy in which the CTE doesn’t use any recursion, or repeated processing in of a sub-routine. We will create an easy Non-Recursive CTE to show the row variety from 1 to 10.

As in keeping with the CTE Syntax, every CTE question will begin with a "With" observed with the aid of using the CTE Expression call with a column list.

Here we had been the usage of only one column as ROWNO. Next is the Query part, right here we write our pick-out question to be executed for our CTE. After developing our CTE question to run the CTE uses the pick out an announcement with the CTE Expression call.
;with ROW_CTE(ROWNO) as
   (
     SELECT
  ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM table_name
WHERE id <= 10
    )

SELECT * FROM ROW_CTE

Output:
ROWNO
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

Nested CTE (Common Table Expression)
WITH First_CTE
   AS (SELECT 1 EmpId, 'Peter James' Name)

  ,Second_CTE
   AS (SELECT EmpID, Name, 'London' State FROM First_CTE)

SELECT *   FROM Second_CTE

HostForLIFEASP.NET SQL Server 2019 Hosting