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:

• • Use the instruction "SET LANGUAGE Spanish;" before the Insert instruction,

  • Or permanently change the language.

Permanently Change The Language from SQL Management Studio

• • Go To Security,

  • Logins,

  • Right Click on the Login User you want to set the language for,

  • select Properties,

  • Change the default language to Spanish.

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:

• 1. add -m to startup parameters of your SQLSERVER instance on Sql Server Configuration Manager and save/apply changes

  2. restart SQLSERVER service

  3. connect to your SQLSERVER instance from SQL Server Management Studio

  4. add checkbox sysadmin on your login user. The Logins section with the login accounts can be found within the Security section of SQL Server Management Studio.

  5. remove -m from startup parameters on Sql Server Configuration Manager

  6. restart SQLSERVER service.

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:

1. right click database (e.g. "Material"),

2. select Design to open Table Designer,

3. select  column (e.g. "Description"),

   1. select Collation of Table Designer section within Column Properties,

4. deselect Accent Sentive,

5. Save change Table Designer.

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: 

1. Open SSMS,

2. Expand the “Management” folder,

3. Right click on “SQL Server Logs”,

4. Select “Configure”,

5. Check the box “Limit the number of error log files before they are recycled”,

6. Write a value in the “Maximum number of error log files” box,

7. Click “OK”

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);