Case sensitive search on column in SQL Server

Suppose we have a table #TBL with column SEARCH_CONTENT which have values like 'DILIP SINGH', 'dilip singh' and 'Dilip Singh'.

CREATE TABLE #TBL
(
      ID INT IDENTITY(1,1),
      SEARCH_CONTENT VARCHAR(500)
)

INSERT INTO #TBL(SEARCH_CONTENT)
SELECT 'DILIP SINGH'
UNION ALL
SELECT 'dilip singh'
UNION ALL
SELECT 'Dilip Singh'

If you will run the following script, it will return three-row of table

SELECT * FROM #TBL WHERE SEARCH_CONTENT LIKE '%DILIP SINGH%'

Result
ID          SEARCH_CONTENT
----------- -----------------------
1           DILIP SINGH
2           dilip singh
3           Dilip Singh

The above query may be changed in a case sensitive search, see the following query.

SELECT * FROM #TBL WHERE SEARCH_CONTENT  COLLATE Latin1_General_CS_AS LIKE '%DILIP SINGH%'

Result
ID          SEARCH_CONTENT
----------- ------------------------
1           DILIP SINGH

Here  COLLATE Latin1_General_CS_AS makes to search case sensitive.

How to identify open transactions in SQL Server

There are many ways to find open transaction which is not COMMIT or ROLLBACK.

Below script may help you to find out open TRANSACTION in SQL Server
1- Using  SYS.SYSPROCESSES 
SELECT * FROM SYS.SYSPROCESSES WHERE OPEN_TRAN = 1

2- Using  SYS.DM_TRAN_SESSION_TRANSACTIONS
SELECT * FROM SYS.DM_TRAN_SESSION_TRANSACTIONS

What is the difference between DATEFIRST and @@DATEFIRST in SQL Server

DATEFIRST keyword uses to reset the first day of the week in SQL Server and @@DATEFIRST returns the current value for the session of DATEFIRST.

For Example:-
As we know SQL Server set default language US English and SQL Server sets by default DATEFIRST to 7 (Sunday).

SET DATEFIRST 6
If we execute the above query it will set Saturday as the first day of the week.
@@DATEFIRST, will return the current value of SET DATEFIRST for the session.

SET LANGUAGE french
GO
SELECT @@DATEFIRST
GO
-- the result will return 1 (Monday)

SET LANGUAGE us_english
GO
SELECT @@DATEFIRST
GO
-- the result will return 7 (Sunday)

Difference between MVC3, MVC4, MVC5, MVC6


Microsoft has added exciting features in every new version of ASP.NET MVC that make developers more comfortable building scalable web applications easily. In this ASP.NET MVC tutorial, we will have a quick look into new and important features introduced in major versions of Microsoft ASP.NET MVC starting fromMVC3 to MVC6 (the latest one so far).

ASP.NET MVC3
  1. New Project Templates having support for HTML 5 and CSS 3.
  2. Razor View Engine introduced with a bundle of new features.
  3. Having support for Multiple View Engines i.e. Web Forms view engine, Razor or open source.

ASP.NET MVC 4
  1. ASP.NET Web API
  2. Refreshed and modernized default project templates
  3. New mobile project template
  4. Many new features to support mobile apps
  5. Display Modes
  6. Bundling and Minification
  7. Enabling Logins from Facebook and Other Sites Using OAuth and OpenID

ASP.NET MVC 5
  1. Attribute based routing such as [Route("Empolyee/{EmpID}")].
  2. ASP.NET Identity for authentication and identity management.ASP.NET Identity is a new Membership provider to handle the authentication and authorization for social networking site just like Google, twitter, face-book etc.
  3. Bootstrap replaced the default MVC template.
  4. Authentication Filters for authenticating user by custom or third-party authentication provider.

ASP.NET MVC6 | ASP.NET vNext
  1. MVC 6 added new cloud computing optimization system of MVC , web API, SignalR and entity framework.
  2. The Microsoft  make a bundle of MVC, Web API, WebPages, SignalR , That bundle we called MVC 6.
  3. In MVC 6, Microsoft removed the dependency of system.web.dll from MVC 6  because it's so expensive. Typically  it consume 30K memory per request/response.
  4. Right now, in MVC 6 consume 2K  memory per request response. It's too small memory  consume.
  5. Most of the problem solved using the Roslyn Compiler.
  6. The ASP .Net  vNext used the Roslyn Compiler,  By using Roslyn compiler do not need to compile the application Its  compile automatically the application code.
  7. The .Net vNext is a cross platform and open source.
  8. The .Net vNext has the new project extension project.json. Basically project.json contain the all dependency dll of the application.
  9. In MVC 5.1 and 5.2 support to Enum and EnumHelper in  razor views

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...