ppolyzos – Page 6 – Paris Polyzos' blog

Change SQL Server Database Collation

ppolyzos December 7, 2016 12824 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 13742 2 Comments

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…

Improve website speed using Azure Storage and Azure CDN

ppolyzos November 21, 2016 7160 0 Comment

Articles

It’s a common thing for websites to serve some kind of static content, like images, sounds, fonts or videos. When deploying a new website, the most common way to serve the aforementioned content is to include these files in the app structure and allow the web server to serve the static content. In Azure, there are additional ways to serve static content, either through Azure Blob…

How to update environment variables based on a response in Postman

ppolyzos November 20, 2016 14593 36 Comments

Notes

Sometimes, the response from a request is required first in order to continue with all the other API requests currently stored in your Postman folders. Think of a request that, in order to return results, it requires from you to be authenticated, so a typical workflow would be: authenticate yourself with a proper username and password; get an authentication token; update…

EF code first: creating string Index error

ppolyzos November 15, 2016 4486 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…

Yarn vs NPM speed test

ppolyzos October 29, 2016 3735 0 Comment

Notes

A couple of days ago Facebook released Yarn, a new package manager for Javascript, with a focus on speed and consistent package dependency management across machines. With Yarn, engineers still have access to the npm registry, so I thought it worth the try to test the claimed speed improvements between package installation using yarn and npm default installation process. For my tests I used ng-lightning, a project for…

Setting up local environment for Azure Functions and continuous deployment through GitHub

ppolyzos October 16, 2016 4927 0 Comment

Articles

In this post we are going to explore what we need in order to setup a local environment for our Azure Functions implementation, and how we can easily update our Azure Functions App through GitHub, using continuous integrations. Let’s start with some details about Azure Functions. What is Azure Functions? Azure Functions is a serverless event-driven experience that extends the existing Azure App Service platform; serverless…

Azure web app deployment slots and testing in production

ppolyzos October 9, 2016 6206 3 Comments

Articles

In this post we are going to explore a feature provided by Azure App Service called “Testing in production” that allows you to direct a portion of live user traffic to one or more deployment slots of your web app before swapping this deployment slot to production. You can, thus, analyze your app, check for exceptions or errors, and make certain everything works properly, before your app…

How to test scale out rules and notifications in Azure Web Apps

ppolyzos October 5, 2016 4367 0 Comment

Articles

In this post we are going to explore: how to simulate CPU-load hosted on an Azure web app; how you can set simple rules for automatic scaling; and how to setup alerts to get notified when your app is under pressure. Let’s start by creating a very simple ASP.NET core web api application. The default web api template in Visual Studio is more than OK….

Auto Shutdown Windows from command line

ppolyzos September 27, 2016 2693 0 Comment

Notes

Sometimes, it is helpful to be able to set a timer to auto shutdown your PC, after a certain amount of time. In Windows 10 you can do that through many ways, but essentially, using the same command. Command Prompt Type shutdown -s -t 300 where 300 is the amount of seconds to wait till the shutdown start (around 5 minutes) Powershell The…

Author: ppolyzos

Change SQL Server Database Collation

Get size of tables in SQL Server

Improve website speed using Azure Storage and Azure CDN

How to update environment variables based on a response in Postman

EF code first: creating string Index error

Yarn vs NPM speed test

Setting up local environment for Azure Functions and continuous deployment through GitHub

Azure web app deployment slots and testing in production

How to test scale out rules and notifications in Azure Web Apps

Auto Shutdown Windows from command line

Easily delete all files in an Azure Storage container

[Greek Audio] AzureHeads 24# Meetup: Automatic updates using Azure Functions CosmosDB and SignalR

Rename your Azure “Default Directory” to something comprehensible