Archives: sql

Change SQL Server Database Collation

ppolyzos December 7, 2016 10860 2 Comments

A time may come when you will need to alter your database to use a different collation. You can do this by using the following snippet:

USE master;
GO
ALTER DATABASE [DatabaseName]
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'[DatabaseName]';
GO

USE master;

ALTER DATABASE [DatabaseName]

COLLATE SQL_Latin1_General_CP1_CI_AS ;

--Verify the collation setting.

SELECT name, collation_name

FROM sys.databases

WHERE name = N'[DatabaseName]';

When you execute the above snippet you may come across the following error: This error occurs when your database is in Multi-User mode or when there are objects referring to your database. To overcome this error, you…

Get size of tables in SQL Server

ppolyzos November 22, 2016 11517 1 Comment

Snippets

To get a rough view of how many rows, total, used and unused space each table has, in a sql server database you can run the following query:

USE {Database_Name};
GO 

SELECT
  t.Name                                       AS TableName,
  s.Name                                       AS SchemaName,
  p.Rows                                       AS RowCounts,
  SUM(a.total_pages) * 8                       AS TotalSpaceKB,
  SUM(a.used_pages) * 8                        AS UsedSpaceKB,
  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
  sys.tables t
  INNER JOIN sys.indexes i ON t.object_id = i.object_id
  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
  INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
  LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
  t.Name NOT LIKE 'dt%'
  AND t.is_ms_shipped = 0
  AND i.object_id > 255
GROUP BY
  t.Name, s.Name, p.Rows
ORDER BY
  t.Name;
GO

USE {Database_Name};

SELECT

t.Name AS TableName,

s.Name AS SchemaName,

p.Rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables t

INNER JOIN sys.indexes i ON t.object_id = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.Name NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.object_id > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

t.Name;

Another way is using the stored procedure sp_spaceused which displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in…

EF code first: creating string Index error

ppolyzos November 15, 2016 3712 0 Comment

Notes

In Entity Framework code-first, if you have a domain model like the following:

public class Country
{
    public int Id { get; set; }

    [Index]
    public string IsoCode { get; set; }
}

public class Country

{

public int Id { get; set; }

[Index]

public string IsoCode { get; set; }

}

and want to create an index for a string property, when you execute update-database for your migration, you may end up with the following error: This usually happens when you use VARCHAR(max), which is the default column type when creating a string property in EF. To make…

Cross Database Queries in Azure SQL databases

ppolyzos July 30, 2016 9639 7 Comments

Articles

In Azure if you have two or more SQL Databases and you want to perform a query across them, you might come across the following error:

Reference to database and/or server name in '[database table]' is not supported in this version of SQL Server.

1	Reference to database and/or server name in '[database table]' is not supported in this version of SQL Server.

In order to perform cross database queries among SQL Azure databases you need to use the elastic database query feature. What is the Elastic Database Query Feature? The elastic database query feature enables you to run a Transact-SQL query…

TSQL: Clean database from all tables, indexes, foreign keys

ppolyzos January 23, 2016 2129 0 Comment

Snippets

Configure a Server to Listen on a Specific TCP Port

ppolyzos December 10, 2015 4017 0 Comment

Notes

These are some quick notes on how to configure an SQL Server to Listen on a Specific TCP port. Open Run Command line and type: SQLServerManager12.msc for SQL Server 2014 SQLServerManager11.msc for SQL Server 2012 SQLServerManager10.msc for SQL Server 2008 and press Enter. To assign a TCP/IP port number to the SQL Server Database Engine In SQL Server Configuration Manager, in the console…

Handle Database Diagram support objects cannot be installed issue

ppolyzos October 9, 2015 3781 0 Comment

Notes

SQL Server management studio allows you to have Database Diagrams to graphically show the structure of the database. In addition, you can create and modify tables, columns, relationships, and keys. Plus, you can modify indexes and constraints. While trying to Create a New Database Diagram you might come across to the following error:

sql

Change SQL Server Database Collation

Get size of tables in SQL Server

EF code first: creating string Index error

Cross Database Queries in Azure SQL databases

TSQL: Clean database from all tables, indexes, foreign keys

Configure a Server to Listen on a Specific TCP Port

Handle Database Diagram support objects cannot be installed issue

Compare performance of “Object.values()” vs “_.values()” vs “for…in” to extract values from an object

Create a custom password policy for ASP.NET Core Identity using extension methods

How to rename an Azure Function in Azure Portal