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.

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.

There are times when we need to pass a SQL query as a string that has been created dynamically and execute it on database or from the code. For this purpose, we can use a built-in stored procedure, sp_executesql.

Stored procedure, sp_executesql executes a SQL statement or batch that can be reused many times, or one that has been built dynamically.

Here is the syntax:
sp_executesql [ @stmt = ] statement 
[ 
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } 
{ , [ @param1 = ] 'value1' [ ,...n ] } 
] 

Here is an example where SQL is compiled as a string, str1. The SELECT SQL statement is executed via the string parameter passed to the sp_executesql.
declare @str1 nvarchar(200) -----declare a variable 
set @str1='SELECT * FROM tablename' --- set your query to variable 
exec sp_executesql @str1 

Here we will explain how to find the Indian financial year and financial quarter in a particular date with an example in SQL Server.  I have used the CASE statement and DATEPART() function to achieve this requirement.

DATEPART() in SQL Server
The DATEPART() function returns a specified part of a date, like – year, month, day, hour, minute, etc.

CASE Statement in SQL Server
CASE is the extension of IF ... ELSE statement. So, once a condition is true, it will stop reading & return the result. If no conditions are true, it returns the value in the ELSE block.
 
A) Find the FINANCIAL YEAR from date
 Write the below-given SQL code to find the financial year from given particular date,
    DECLARE@FilterDateASDATETIME 
    SET@FilterDate = GETDATE() 
    SELECTCASEWHENDATEPART(QUARTER,@FilterDate)= 1 THENYEAR(@FilterDate)ELSEYEAR(@FilterDate)+ 1 ENDAS[FINANCIAL_YEAR] 
    ** Note - @FilterDate - The date to be find the financial year

Output
Following is the result of the SQL query for financial year,

 
B) Find the FINANCIAL QUARTER from date
 
Write the below SQL code to find the financial Quarter from given particular date,
    DECLARE@FilterDateASDATETIME 
    SET@FilterDate = GETDATE() 
    SELECTCASEDATEPART(QUARTER, @FilterDate)WHEN 1 THEN'Q4'WHEN 2 THEN'Q1'WHEN 3 THEN'Q2'WHEN 4 THEN'Q3'ENDAS[FINANCIAL_QUARTER] 
    ** Note - @FilterDate - The date to be find the financial Quarter

Output
Following is the result of the SQL query for the financial quarter,

In this post, I'll share few useful SQL queries for database analysis on SQL Server 2012. I shared few SQL queries useful in analyzing database, which I use quite often. This query will return all table names and no.of rows in it for built-in tables.

    -- List all table names and number of rows in it for user-defined tables 
    SELECT distinct t.name,prt.rows 
    FROM sys.tables t INNER JOIN sys.partitions AS prt 
    ON t.object_id = prt.object_id where t.is_ms_shipped=1 -- 0 for user-defined tables 
    order by prt.rows desc 

This query will return column names and its data type of a table.
    -- Get column names and its types of a table 
    SELECT cols.name,t.name 
    FROM sys.objects o join sys.columns cols on o.object_id= cols.object_id 
    join sys.types t on t.system_type_id=cols.system_type_id 
    and o.name='Employee'-- Table Name

This query will return file name, its size and file group name of a database.
    SELECT sdf.name AS [FileName], 
    size/128 AS [Size], 
    fg.name AS [File_Group_Name] 
    FROM sys.database_files sdf 
    INNER JOIN 
    sys.filegroups fg 
    ON sdf.data_space_id=fg.data_space_id 

Batch file to execute all sql files in a directory, Save it as .bat in a folder that have sql script files to be executed.
    @Echo Off 
    FOR /f %%i IN ('DIR *.Sql /B') do call :RunSql %%i 
    GOTO :END 
    :RunSql 
    Echo Executing SQL: %1 
    SQLCMD -S server1 -U user1 -P pwd1 -d DB1 -i %1 
    Echo Completed SQL: %1 
    :END 

This query will return all table names that have a Foreign key:
    SELECT SCHEMA_NAME(schema_id) AS SchemaName, 
    name AS TableName 
    FROM sys.tables where OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 1 -- Return all

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.