ms sql

sql select * from table where column_name = value + spaces

select * from table where column_name = value + spaces  , where column_name type is NVARCHAR, renders the same output as sql select * from table where column_name = value.

Example:  select * from table where column_name = "123  ", where column_name type is NVARCHAR(3), is equivalent to  sql select * from table where column_name = "123".

validate datetime value

In order to find out the row containing an invalid datetime value you can use the following sql statement:

select * from table where ISDATE(column_name) = 0

Further reference to ISDATE at http://technet.microsoft.com/en-us/library/ms187347.aspx

finding duplicate rows

SELECT     Col1, COUNT(*) AS dup 

FROM         TABLE

GROUP BY [Col1]  

HAVING      (COUNT(*) > 1)

Error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" when inserting a date in format "dd/mm/yyyy"

According to microsoft, the default server language implicitly establishes the dateformat. See https://msdn.microsoft.com/es-es/library/ms174398.aspx

I wanted to insert a date in format "dd/mm/yyyy". However, the language of my SQL Server installation is English. So, I kept getting the annoying error "The conversion of a varchar data type to a datetime ...".

As far as I know, I have to options two solve this problem:

Permanently Change The Language from SQL Management Studio

grant user admin access to a local SQL Server instance

I installed SQL Server 2016 express to my local machine but I can't administrate user roles o generate new logins. 

Maybe I forgot to add my user to the sysadmin role when installing SQL Server. The next solution granted me admin accesss without having to reinstall.

Solution:

More info on Startup options can be found at Database Engine Service Startup Options.

Linq To Entities Compare without Diacritics

SQL Server was installed in my system with default collation Modern_Spanish_CI_AS, i.e. Accent Sensitive.

LINQ to Entities cannot compare without accents in a SQL Database with Accent Sensitive (AS) collation. This is a problem when you run instructions such as:

   var materials = db.Material

                .Where(m => m.Description.Contains(searchString) );

Since LINQ fails to find materials whose Description contains accents if the searchString does not contain the right accents, and viceversa. For instance, searchString= "telefono", does not find materials including "teléfono" as a description.

The solution is to change the collation of the affected column. In this example, from SQL Server Management Studio (SSMS), we need to modify the collation for the column Description from its properties. For instance, changing it from Modern_Spanish_CI_AS to Modern_Spanish_CI_AI. 

This setup can be done from SSMS without the need for T-SQL:

Moreover, in order to prevent the same problem in newly created tables, I changed the collation of the database to Modern_Spanish_CI_AI. This setup can be done from SSMS, by going to the Database Properties window and then modifying the collation from the Options section. I needed to temporarily change the state from MULTI_USER to SINGLE_USER, to be able to modify the collation.

Create Unique Index with Column that Accepts Null Values

CREATE UNIQUE NONCLUSTERED INDEX [IX_ColumnWithNulls]

    ON [dbo].[Table]([ColumnWithNulls] ASC) WHERE ([ColumnWithNulls] IS NOT NULL);

Too Large SQL Server Log Folder

To change  the number of error log files kept by SQL Server: 

A much more detailed explanation can be found here and here.

IDENTITY_CACHE 

Disable identity cache:

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

GO

PSEUDOTRUNCATE TABLE WITH FOREIGN KEY CONSTRAINTS 



DELETE FROM [MyTable];

 DBCC CHECKIDENT ('[MyTable]', RESEED, 0);