European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2019 Hosting - HostForLIFE.eu :: SQL Constraints Explained

clock November 12, 2019 10:22 by author Peter

In this blog, I will explain SQL Constraints. They are used to specify rules for the data in a table. The following SQL Constraints are commonly used (Primary key, foreign key, unique key, Composite key, null, Not Null, check).

Primary Key

The Primary key must contain be a unique value. It is the primary column and can’t have the null value. It uniquely identifies each row in a database table.
 
Syntax
    CREATE TABLE TB_NAME(Column1 datatype,column2 datatype PRIMARY KEY(Column1)) 

Foreign Key
1. Foreign key always refers to the primary key column.
2. Foreign key accepted to duplicate value.
 
Syntax
    CREATE TABLE TB_NAME(column1 datatype FOREIGN KEY REFERENCES(primary_key column_name),cloumn2 datatype) 

Unique Key
 
The unique key is the same as the primary key, but one row is accepted for the null value.
 
Syntax
    CREATE TABLE TB_NAME(Column_name datatatype UNIQUE,column_name2 datatype) 

Composite key
A composite key is a set of multiple keys that, together, uniquely identifies each record
 
Syntax
    CREATE TABLE TB_NAME(Column1 datatype,column2 datatype PRIMARY KEY(Column1,column2)) 

Not Null
Forces a column not to accept NULL values
 
Syntax
    CREATE TABLE TB_NAME(Column1 datatype,column2 datatype NOT NULL) 

Check
The CHECK constraint is used to limit the value range that can be placed in a column.
 
Syntax
    CREATE TABLE TB_NAME(MARKS INT CHECK(MARKS<=100))



SQL Server 2019 Hosting - HostForLIFE.eu :: How To Track Database Changes in SQL server?

clock November 6, 2019 11:33 by author Peter

Version control helps you to track the changes of a code repository. But, it doesn't much help to track database changes. General practice is to create a single script file that includes all the schema and update that file every time you make any changes into the database and commit it to version control. However, this is a bit longer a way to track the changes. Another way is to use popular tools like Red Gate Change Automation. But there is a native way around to handle tracking! simply put, DDL trigger can be used to track the DB changes.

Track Stored Procedure changes using DDL trigger

Here we'll see how to track stored procedure changes using DDL trigger.

Create your audit database and create a table.  
USE AuditDB;  
GO  
 
CREATE TABLE dbo.ProcedureChanges  
(  
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    EventType    NVARCHAR(100),  
    EventDDL     NVARCHAR(MAX),  
    DatabaseName NVARCHAR(255),  
    SchemaName   NVARCHAR(255),  
    ObjectName   NVARCHAR(255),  
    HostName     NVARCHAR(255),  
    IPAddress    VARCHAR(32),  
    ProgramName  NVARCHAR(255),  
    LoginName    NVARCHAR(255)  
);   

Add data of all existing stored procedures from your actual database (Product DB in this example)
USE ProductDB;  
GO  
 
INSERT AuditDB.dbo.ProcedureChanges  
(  
    EventType,  
    EventDDL,  
    DatabaseName,  
    SchemaName,  
    ObjectName  
)  
SELECT  
    N'Initial control',  
    OBJECT_DEFINITION([object_id]),  
    DB_NAME(),  
    OBJECT_SCHEMA_NAME([object_id]),  
    OBJECT_NAME([object_id])  
FROM  
    sys.procedures;  
Create DDL trigger to capture changes
USE ProductDB;  
GO  
 
CREATE TRIGGER CaptureStoredProcedureChanges  
    ON DATABASE  
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,   
    ALTER_SCHEMA, RENAME  
AS  
BEGIN  
    SET NOCOUNT ON;  
 
    DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);  
 
    SELECT @ip = client_net_address  
        FROM sys.dm_exec_connections  
        WHERE session_id = @@SPID;  
 
    INSERT AuditDB.dbo.ProcedureChanges  
    (  
        EventType,  
        EventDDL,  
        SchemaName,  
        ObjectName,  
        DatabaseName,  
        HostName,  
        IPAddress,  
        ProgramName,  
        LoginName  
    )  
    SELECT  
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),   
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),  
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),   
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),  
        DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();  
END  
GO  

Modify any stored procedure and check the ProcedureChanges table from AuditDB.

The method might have some limitations, but this is the simplest way to tracking changes of small size databases.



SQL Server Hosting - HostForLIFE.eu :: Effective Paging, Sorting And Filtering Using SQL Server Stored Procedure

clock October 30, 2019 12:01 by author Peter

There was a situation where I had to implement fast/effective Paging, Sorting and Filtering with Stored Procedure in MS SQL Server. There are a number of articles and blogs where you can find about to do Paging, Sorting and Filtering with Stored Procedure in MS SQL Server. Hence, I started and done some research on this to find out the best solution. I found it in “Pagination with OFFSET / FETCH : A better way”

Using OFFSET / FETCH into the CTE I have created a stored procedure that was at least faster twice in return time as the alternatives found on the internet.

Here, I have kept all the implementation details along with a complete example.

Prerequisites
You should have a basic knowledge of MS SQL Stored Procedure, queries and CTE.

How to do effective Paging, Sorting and Filtering with Stored Procedure

To implement and execute this let us follow three steps:

  • Create table.
  • Insert data into the table.
  • Create stored procedure.


Create table
CREATE TABLE Employee 

    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    Name varchar(25) NOT NULL, 
    City varchar(25) NOT NULL 


Insert data into the table
declare @i int = 1 
declare @total int = 500000 
 
while @i <= @total 
begin 
    insert into Employee2 (Name, City) values (RIGHT('000000'+convert(varchar, @i),6), convert(varchar, @i%4)) 
    set @i += 1; 
end

For testing purpose, I have added 5 lakh records into the Employee table.

Create stored procedure
--GetAllEmployeesWay4 '', '', 1, 25, 'Name', 'Asc' 
--GetAllEmployeesWay4 'Name', '1', 1, 25, 'Name', 'Asc' 
--GetAllEmployeesWay4 'City', '1', 1, 25, 'Name', 'Asc' 
CREATE PROCEDURE [dbo].[GetAllEmployeesWay4] 

    @SearchColumn NVARCHAR(50) = NULL, 
    @SearchValue NVARCHAR(50) = NULL, 
    @PageNo INT = 1, 
    @PageSize INT = 10, 
    @SortColumn NVARCHAR(20) = 'Name', 
    @SortOrder NVARCHAR(20) = 'ASC' 

AS BEGIN 
    SET NOCOUNT ON; 
 
    SET @SearchColumn = LTRIM(RTRIM(@SearchColumn)) 
    SET @SearchValue = LTRIM(RTRIM(@SearchValue)) 
 
    ; WITH CTE_Results AS  
    ( 
        SELECT Id, Name, City from Employee 
 
        WHERE @SearchColumn= '' OR  (  
                CASE @SearchColumn  
                    WHEN 'Name' THEN Name  
                    WHEN 'City' THEN City 
                END 
            ) LIKE '%' + @SearchValue + '%' 
 
            ORDER BY 
            CASE WHEN (@SortColumn = 'Name' AND @SortOrder='ASC') 
                        THEN Name 
            END ASC, 
            CASE WHEN (@SortColumn = 'Name' AND @SortOrder='DESC') 
                        THEN Name 
            END DESC, 
            CASE WHEN (@SortColumn = 'City' AND @SortOrder='ASC') 
                        THEN City 
            END ASC, 
            CASE WHEN (@SortColumn = 'City' AND @SortOrder='DESC') 
                        THEN City 
            END DESC  
            OFFSET @PageSize * (@PageNo - 1) ROWS 
            FETCH NEXT @PageSize ROWS ONLY 
    ), 
    CTE_TotalRows AS  
    ( 
        select count(ID) as TotalRows from Employee 
        WHERE @SearchColumn= '' OR  (  
                CASE @SearchColumn  
                    WHEN 'Name' THEN Name  
                    WHEN 'City' THEN City 
                END 
            ) LIKE '%' + @SearchValue + '%' 
    ) 
    Select TotalRows, t.Id, t.Name, t.City from dbo.Employee as t, CTE_TotalRows  
    WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.ID = t.ID) 
 
    OPTION (RECOMPILE) 
END


Execute stored procedure
Execute the above stored procedure with different parameters and you can get result accordingly:
GetAllEmployeesWay4 '', '', 1, 25, 'Name', 'Asc'
GetAllEmployeesWay4 'Name', '1', 1, 25, 'Name', 'Asc'
GetAllEmployeesWay4 'City', '1', 1, 25, 'Name', 'Asc'


Complete example

For your reference, I have kept complete example in a single folder and uploaded that with this article and it contains below script files:
Step1_Create_Table
Step2_Insert_Data_into_Table
Step3_Create_Stored_Procedure

Summary
Now, I believe you will be able to do Effective Paging, Sorting and Filtering with Stored Procedure in MS SQL Server using.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2019 Hosting - HostForLIFE.eu :: How To Track Database Changes in SQL server?

clock October 23, 2019 12:18 by author Peter

Version control helps you to track the changes of a code repository. But, it doesn't much help to track database changes. General practice is to create a single script file that includes all the schema and update that file every time you make any changes into the database and commit it to version control.

However, this is a bit longer a way to track the changes. Another way is to use popular tools like Red Gate Change Automation. But there is a native way around to handle tracking! simply put, DDL trigger can be used to track the DB changes.

Track Stored Procedure changes using DDL trigger

Here we'll see how to track stored procedure changes using DDL trigger.

Create your audit database and create a table. 
USE AuditDB; 
GO 
 
CREATE TABLE dbo.ProcedureChanges 

    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    EventType    NVARCHAR(100), 
    EventDDL     NVARCHAR(MAX), 
    DatabaseName NVARCHAR(255), 
    SchemaName   NVARCHAR(255), 
    ObjectName   NVARCHAR(255), 
    HostName     NVARCHAR(255), 
    IPAddress    VARCHAR(32), 
    ProgramName  NVARCHAR(255), 
    LoginName    NVARCHAR(255) 
);  
Add data of all existing stored procedures from your actual database (Product DB in this example)
USE ProductDB; 
GO 

 
INSERT AuditDB.dbo.ProcedureChanges 

    EventType, 
    EventDDL, 
    DatabaseName, 
    SchemaName, 
    ObjectName 

SELECT 
    N'Initial control', 
    OBJECT_DEFINITION([object_id]), 
    DB_NAME(), 
    OBJECT_SCHEMA_NAME([object_id]), 
    OBJECT_NAME([object_id]) 
FROM 
    sys.procedures; 
Create DDL trigger to capture changes
USE ProductDB; 
GO 
 
CREATE TRIGGER CaptureStoredProcedureChanges 
    ON DATABASE 
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,  
    ALTER_SCHEMA, RENAME 
AS 
BEGIN 
    SET NOCOUNT ON; 
 
    DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32); 
 
    SELECT @ip = client_net_address 
        FROM sys.dm_exec_connections 
        WHERE session_id = @@SPID; 
 
    INSERT AuditDB.dbo.ProcedureChanges 
    ( 
        EventType, 
        EventDDL, 
        SchemaName, 
        ObjectName, 
        DatabaseName, 
        HostName, 
        IPAddress, 
        ProgramName, 
        LoginName 
    ) 
    SELECT 
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),  
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'), 
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),  
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'), 
        DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME(); 
END 
GO  

Modify any stored procedure and check the ProcedureChanges table from AuditDB.

The method might have some limitations, but this is the simplest way to tracking changes of small size databases.



SQL Server 2012 Hosting Belgium - HostForLIFE.eu :: How to Fix Distribution setup SQL Server Agent error: "RegCreateKeyEx() returned error 5, 'Access is denied.'" ?

clock October 15, 2019 12:15 by author Peter

With this short article, I will tell you about How to Fix Distribution setup SQL Server Agent error: "RegCreateKeyEx() returned error 5, 'Access is denied.'" on my SQL Server 2012 Hosting.

In the Configure Distribution Wizard, the step "Configuring SQL Server Agent to start automatically" errors with the following text:

TITLE: Configure Distribution Wizard
------------------------------
An error occurred configuring SQL Server Agent.
------------------------------
ADDITIONAL INFORMATION:
RegCreateKeyEx() returned error 5, 'Access is denied.' (Microsoft SQL Server, Error: 22002)

This is a very minor error, and not difficult to work around at all. The wizard is making an attempt to alter the SQL Server Agent service "Start Mode" to Automatic. you'll be able to try this via the SQL Server Configuration Manager instead.

In the Sysinternals method Monitor, you'll see: Operation: RegCreateKey Result: ACCESS DENIED Path: "HKLM\System\CurrentControlSet\Services\SQLAgent$SQL2012"

 

If you encounter this error, choose "No" in the "SQL Server Agent Start" page in the configure Distribution Wizard (as shown below), so set your agent service to Automatic start Mode via the SQL Server Configuration Manager.

The third step of the wizard that failed before won't happen. Why the failure truly happens I didn't figure this out, and i am open to feedback, however this seems like a vestigial step to a wizard that otherwise has no negative impact. Running SSMS with "run as Administrator" doesn't seem to fix this error either. i would like to recognize why this error happens within the 1st place.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2019 Hosting - HostForLIFE.eu :: Enforcing Foreign Key Constraint In A Multi-Valued Column In SQL Server

clock October 4, 2019 09:04 by author Peter

I have seen that sometimes, a few developers create a multi-valued column to store more than one value in a comma-separated manner (like 1,3,4) and then, they read the individual values by splitting using comma.

However, due to such design, they can't add a foreign key constraint like below.

  • ALTER TABLE <Table-name> 
  • ADD CONSTRAINT <FK-Name> FOREIGN KEY (<col-name>) REFERENCES <Lookup Table-name>(<Lookup col-name>); 

PS - Personally, I am not a fan of such design and I would recommend having a mapping table in such cases; however, at times, mostly on the existing system, you don't have the choice to rewrite or change the design and hence finding a quick fix is the only option.
 
To illustrate the problem and solution, let's take an example of two tables - Employee and Country - as below.
    CREATE TABLE Country ( 
       Id INT NOT NULL PRIMARY KEY, 
       Name varchar(100) NOT NULL, 
       Code varchar(50) NULL 
    ); 
      
    CREATE TABLE Employee ( 
       Id INT NOT NULL PRIMARY KEY, 
       HomeCountryId INT NOT NULL, 
       VisitedCountryIds varchar(200) NULL, 
       Constraint FK_Employee_Country FOREIGN KEY (HomeCountryId) REFERENCES Country(Id) 
    ); 


Let's assume the country id as 1, 2 till 249 (As per the latest data available during the time of writing the post).
 
As you can see there is FK constraint on the HomeCountryId, hence only valid Country Id (from 1-249) can be entered; however, in the field VisitedCountryIds, there is no check and any id (like 250, 251, etc.) can also be added even if it doesn't exist in the country table. Well, this can lead to the data integrity issue.
 
So how we can make sure that users can only enter valid country ids (from 1-249) in the VisitedCountryIds column?
 
The fix is two-fold as following.
 
Create the function in the SQL Server as below.
    CREATE FUNCTION [dbo].[svf_CheckCountryIds](@CountryIds nvarchar(200)) 
    RETURNS bit AS 
    BEGIN 
    declare @valid bit 
    declare @rowsInserted INT 
    declare @addedCountryIds table([CountryId] nvarchar(200)) 
     
    insert into @addedCountryIds 
    select value from STRING_SPLIT(@CountryIds, ',') 
    set @rowsInserted = @@rowcount 
     
    if (@rowsInserted = (select count(a.CountryId) from @addedCountryIds a join [Country] b on a.CountryId = b.Id)) 
    begin 
    set @valid = 1 
    end 
    else 
    begin 
    set @valid = 0 
    end 
     
    RETURN @valid 
    END 


As you can see in the above function, we are passing the column data that is in the comma concatenated form and then they are split using STRING_SPLIT function and stored in the addedCountryIds table variable. Also, the inserted row count is stored in the rowsInserted variable.
 
Later, the values on addedCountryIds arejoined with Country table and if the count is matching, i.e., if all the passed country id is present in the Country table, true/1 is returned else false/0 is returned.
 
Create the FK with check constraint on the VisitedCountryIds as follows,
    ALTER TABLE Employee 
    ADD CONSTRAINT [FK_Employee_VisitedCountryIds] CHECK ([dbo].[svf_CheckCountryIds]([VisitedCountryIds]) = 1) 


As you can see constraint FK_Employee_VisitedCountryIds is created on VisitedCountryIds with condition that function svf_CheckCountryIds should return value as 1/true.
Now when you enter any country id other than 1 to 249, for example, if you enter VisitedCountryIds as '103,236,250', an error will be thrown as follows as id 250 is not the part of the country id list.
 
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint "FK_Employee_VisitedCountryIds". The conflict occurred in database "TestDb", table "dbo.Employee", column 'VisitedCountryIds'.
The statement has been terminated.
 
However, if you enter VisitedCountryIds as '103,236,249', it will be successfully inserted because all the ids are part of the country list.
 
I hope you found this post useful in handling the foreign keys in multivalued columns. Looking forward to your comments.



SQL Server Hosting - HostForLIFE.eu :: How To Find Running Total And Final Total In SQL Server?

clock September 11, 2019 10:04 by author Peter

Here, we will explain how to find Running Total and Final Total or Grand Total in SQL Server. For example, you have the below given #tblEmployee temporary table and has the data, as shown below.

    CREATE TABLE #tblEmployee 
    ( 
       EmpID int identity, 
       Salary Decimal(8,2), 
       Grade varchar(10) 
    ) 


Insert some values in a temporary table like this.
    INSERT INTO #tblEmployee(Salary,Grade) Values(10000.51,'B') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(20000.12,'B') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(25000.02,'B') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(9000.28,'C') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(7000,'C') 
    INSERT INTO #tblEmployee(Salary,Grade) Values(6000,'C') 


Find the Running Total in the SQL Server

Write the following SQL Query to find the running total for the Salary column from #tblEmployee temporary table.
SELECT EmpID, Grade, Emp.Salary ,(SELECT SUM(Salary) FROM #tblEmployee WHERE EmpID <= Emp.EmpID) 'Running Total' FROM #tblEmployee Emp 
I have used the sub-query to find the running total.
 
Find the Final Total or Grand Total in SQL Server
 Write the following SQL Query to find the final total or grand total for the Salary column from #tblEmployee temporary table.
    SELECT EmpID, SUM(Salary) AS Salary FROM #tblEmployee GROUP BY EmpID WITH ROLLUP 

Here I used ROLLUP to achieve the desired result.
 
What is ROLLUP
The ROLLUP is an extension of the GROUP BY clause. The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. By using the ROLLUP option, you can use a single query to generate multiple grouping sets.
 
Following Result produce by this SQL Query,

So, in this blog, we tried to explain how to find the running total from the SQL table. I hope this blog helps all of us. It is also most asked question from the interview.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

 



SQL Server Hosting - HostForLIFE.eu :: Instead Of Triggers

clock September 4, 2019 12:41 by author Peter

Instead of triggers are used to skip DML commands. They fire when you try to execute insert, update or delete statement but instead of executing these commands trigger actually works and trigger functionality executes.
 

Example
    create table approved_emp ( eid int identity(1,1), ename varchar(30)) 
    create table emp ( id int identity(1,1) , ename varchar(30), AddedBy varchar(30)) 
      
    Create trigger instead_of on approved_emp 
    instead of insert 
    as 
    begin 
    declare @name varchar(30) 
    select @name=ename from inserted 
    insert into temp_audit values(@name, USER ) 
    end 


So, basically, trigger will work as, when we will try to add new record in approved_emp table, instead of inserting new records it will add ename into emp table. No data will reflect in approved_emp table as trigger is fired on the table every time while adding data into that table.
 
You can also create instead of triggers for update and delete as well.
 



SQL Server 2019 Hosting - HostForLIFE.eu :: Store Multiple NULL Values With Unique Data In SQL Server

clock August 30, 2019 12:01 by author Peter

Sometimes, we get data that needs to be unique but we can also get NULL records. Existing ways to achieve uniqueness don’t allow NULLs (Primary Key) or allow a maximum of one NULL (Unique Constraint).
 
Let’s take an example to understand this better. Suppose, you have an Employee table with fields like Id, FirstName, MiddleName, LastName, Email, etc. As per the requirement, the email needs to be unique (if supplied), however, users are also allowed to register without entering their email and hence in the database Email is a nullable field.
 
So, how would you achieve the Email uniqueness having more than one NULL?

    CREATE UNIQUE NONCLUSTERED INDEX [UX_Employee_Email] ON [dbo].Employee WHERE Email IS NOT NULL 

The above code will ensure that the Email doesn’t have duplicate data, however, it may store more than one NULL.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: SQL Server Full Text Search with rank values

clock August 28, 2019 12:51 by author Peter

OnceSQL Server Full-Text Search with rank values  I wrote a post titled enabling Fulltext search in Azure SQL database discussing Full-Text search in Azure. while using it with one of my databases, needed to show the result of the search ordered by however well they match to the search criteria. in order to sort the result as i need, the best is, get a rank generated for every row and use it for ordering the result. I had used Freetext operate for obtaining the result but if i realized that this can not be achieved using the Freetext function.

The CONTAINSTABLE and FREETEXTTABLE functions return a column named Rank for showing the rank related to the record based on matching. this can be used get the result sorted based on it, showing most relevant records at the top. Remember, the higher value of the Rank generated indicates the best matching.

Now, write the following code:
view plainprint?

    -- Creating a table  
    CREATE TABLE dbo.EmployeeDetails  
    (  
     EmployeeDetailsId int identity(1,1) not null  
     , constraint pk_EmployeeDetails primary key (EmployeeDetailsId)  
     , WorkingExperience nvarchar(4000) not null  
     , ProjectsWorked nvarchar(4000) not null  
     , Resume nvarchar(max)   
    )  
    GO  
      
    CREATE FULLTEXT CATALOG EmployeeCatelog;  
    GO  
      
    CREATE FULLTEXT INDEX ON dbo.EmployeeDetails   
     (WorkingExperience, ProjectsWorked, Resume) KEY INDEX pk_EmployeeDetails  
     ON EmployeeCatelog;  
     -- By default CHANGE_TRACKING = AUTO  
      
      
    -- Once enabled, search can be performed;  
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((WorkingExperience, ProjectsWorked, Resume), 'SQL');  
      
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((Resume), 'SQL');  
      
    -- Get the rank and sort the result using it  
    SELECT t.Rank, e.*  
    FROM dbo.EmployeeDetails e  
     INNER JOIN CONTAINSTABLE (dbo.EmployeeDetails, (WorkingExperience, ProjectsWorked, Resume), 'SQL') AS t  
      ON e.EmployeeDetailsId = t.[Key]  
    ORDER BY t.Rank DESC  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in