a developer's notebook
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 When you execute the above snippet you may come across the following error: This…
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…
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; } } 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…
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. In order to perform cross database queries among SQL Azure databases you need to use the elastic database query feature. What is…
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…
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:
