SQL Nationalities Table (SQL format)

In the same way that getting a list of countries of the world can be tedious when developing a web-based system or website, nationality is one of those drop-down list boxes that is very time-consuming to populate behind the scenes. In many cases most developers resort to the copy/paste from Wikipedia method. So after doing that, I decided to export the SQL so that you don’t have to!

CREATE TABLE `settingsNationalities`
`NationalityID` int(11) NOT NULL AUTO_INCREMENT,
`Nationality` varchar(200) DEFAULT NULL,
PRIMARY KEY (`NationalityID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and then run the insert query below: (take care that the single quotes don’t get changed into back-ticks meaning that the ones with spaces won’t run)

INSERT INTO ‘settingsNationalities’ VALUES (1,’Afghan’),(2,’Albanian’),(3,’Algerian’),(4,’American’),(5,’Andorran’),(6,’Angolan’),(7,’Antiguans’),(8,’Argentinean’),(9,’Armenian’),(10,’Australian’),(11,’Austrian’),(12,’Azerbaijani’),(13,’Bahamian’),(14,’Bahraini’),(15,’Bangladeshi’),(16,’Barbadian’),(17,’Barbudans’),(18,’Batswana’),(19,’Belarusian’),(20,’Belgian’),(21,’Belizean’),(22,’Beninese’),(23,’Bhutanese’),(24,’Bolivian’),(25,’Bosnian’),(26,’Brazilian’),(27,’British’),(28,’Bruneian’),(29,’Bulgarian’),(30,’Burkinabe’),(31,’Burmese’),(32,’Burundian’),(33,’Cambodian’),(34,’Cameroonian’),(35,’Canadian’),(36,’Cape Verdean’),(37,’Central African’),(38,’Chadian’),(39,’Chilean’),(40,’Chinese’),(41,’Colombian’),(42,’Comoran’),(43,’Congolese’),(44,’Congolese’),(45,’Costa Rican’),(46,’Croatian’),(47,’Cuban’),(48,’Cypriot’),(49,’Czech’),(50,’Danish’),(51,’Djibouti’),(52,’Dominican’),(53,’Dominican’),(54,’Dutch’),(55,’Dutchman’),(56,’Dutchwoman’),(57,’East Timorese’),(58,’Ecuadorean’),(59,’Egyptian’),(60,’Emirian’),(61,’Equatorial Guinean’),(62,’Eritrean’),(63,’Estonian’),(64,’Ethiopian’),(65,’Fijian’),(66,’Filipino’),(67,’Finnish’),(68,’French’),(69,’Gabonese’),(70,’Gambian’),(71,’Georgian’),(72,’German’),(73,’Ghanaian’),(74,’Greek’),(75,’Grenadian’),(76,’Guatemalan’),(77,’Guinea-Bissauan’),(78,’Guinean’),(79,’Guyanese’),(80,’Haitian’),(81,’Herzegovinian’),(82,’Honduran’),(83,’Hungarian’),(84,’I-Kiribati’),(85,’Icelander’),(86,’Indian’),(87,’Indonesian’),(88,’Iranian’),(89,’Iraqi’),(90,’Irish’),(91,’Irish’),(92,’Israeli’),(93,’Italian’),(94,’Ivorian’),(95,’Jamaican’),(96,’Japanese’),(97,’Jordanian’),(98,’Kazakhstani’),(99,’Kenyan’),(100,’Kittian and Nevisian’),(101,’Kuwaiti’),(102,’Kyrgyz’),(103,’Laotian’),(104,’Latvian’),(105,’Lebanese’),(106,’Liberian’),(107,’Libyan’),(108,’Liechtensteiner’),(109,’Lithuanian’),(110,’Luxembourger’),(111,’Macedonian’),(112,’Malagasy’),(113,’Malawian’),(114,’Malaysian’),(115,’Maldivan’),(116,’Malian’),(117,’Maltese’),(118,’Marshallese’),(119,’Mauritanian’),(120,’Mauritian’),(121,’Mexican’),(122,’Micronesian’),(123,’Moldovan’),(124,’Monacan’),(125,’Mongolian’),(126,’Moroccan’),(127,’Mosotho’),(128,’Motswana’),(129,’Mozambican’),(130,’Namibian’),(131,’Nauruan’),(132,’Nepalese’),(133,’Netherlander’),(134,’New Zealander’),(135,’Ni-Vanuatu’),(136,’Nicaraguan’),(137,’Nigerian’),(138,’Nigerien’),(139,’North Korean’),(140,’Northern Irish’),(141,’Norwegian’),(142,’Omani’),(143,’Pakistani’),(144,’Palauan’),(145,’Panamanian’),(146,’Papua New Guinean’),(147,’Paraguayan’),(148,’Peruvian’),(149,’Polish’),(150,’Portuguese’),(151,’Qatari’),(152,’Romanian’),(153,’Russian’),(154,’Rwandan’),(155,’Saint Lucian’),(156,’Salvadoran’),(157,’Samoan’),(158,’San Marinese’),(159,’Sao Tomean’),(160,’Saudi’),(161,’Scottish’),(162,’Senegalese’),(163,’Serbian’),(164,’Seychellois’),(165,’Sierra Leonean’),(166,’Singaporean’),(167,’Slovakian’),(168,’Slovenian’),(169,’Solomon Islander’),(170,’Somali’),(171,’South African’),(172,’South Korean’),(173,’Spanish’),(174,’Sri Lankan’),(175,’Sudanese’),(176,’Surinamer’),(177,’Swazi’),(178,’Swedish’),(179,’Swiss’),(180,’Syrian’),(181,’Taiwanese’),(182,’Tajik’),(183,’Tanzanian’),(184,’Thai’),(185,’Togolese’),(186,’Tongan’),(187,’Trinidadian or Tobagonian’),(188,’Tunisian’),(189,’Turkish’),(190,’Tuvaluan’),(191,’Ugandan’),(192,’Ukrainian’),(193,’Uruguayan’),(194,’Uzbekistani’),(195,’Venezuelan’),(196,’Vietnamese’),(197,’Welsh’),(198,’Welsh’),(199,’Yemenite’),(200,’Zambian’),(201,’Zimbabwean’);

MySQL Countries Of The World Table in SQL

Sometimes we need a list of all countries, and whilst searching Wikipedia and copy and pasting is fine, it can take a long time and is tedious, so here is an SQL Country List to copy and paste.

 

CREATE TABLE `settingsCountries` (
`CountryID` int(11) NOT NULL AUTO_INCREMENT,
`CountryCode` varchar(2) NOT NULL DEFAULT ”,
`CountryName` varchar(100) NOT NULL DEFAULT ”,
PRIMARY KEY (`CountryID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

….and then insert all the items

ALTER TABLE `settingsCountries` DISABLE KEYS;

INSERT INTO `settingsCountries` VALUES (1,’AF’,’Afghanistan’),(2,’AL’,’Albania’),(3,’DZ’,’Algeria’),(4,’DS’,’American Samoa’),(5,’AD’,’Andorra’),(6,’AO’,’Angola’),(7,’AI’,’Anguilla’),(8,’AQ’,’Antarctica’),(9,’AG’,’Antigua and Barbuda’),(10,’AR’,’Argentina’),(11,’AM’,’Armenia’),(12,’AW’,’Aruba’),(13,’AU’,’Australia’),(14,’AT’,’Austria’),(15,’AZ’,’Azerbaijan’),(16,’BS’,’Bahamas’),(17,’BH’,’Bahrain’),(18,’BD’,’Bangladesh’),(19,’BB’,’Barbados’),(20,’BY’,’Belarus’),(21,’BE’,’Belgium’),(22,’BZ’,’Belize’),(23,’BJ’,’Benin’),(24,’BM’,’Bermuda’),(25,’BT’,’Bhutan’),(26,’BO’,’Bolivia’),(27,’BA’,’Bosnia and Herzegovina’),(28,’BW’,’Botswana’),(29,’BV’,’Bouvet Island’),(30,’BR’,’Brazil’),(31,’IO’,’British Indian Ocean Territory’),(32,’BN’,’Brunei Darussalam’),(33,’BG’,’Bulgaria’),(34,’BF’,’Burkina Faso’),(35,’BI’,’Burundi’),(36,’KH’,’Cambodia’),(37,’CM’,’Cameroon’),(38,’CA’,’Canada’),(39,’CV’,’Cape Verde’),(40,’KY’,’Cayman Islands’),(41,’CF’,’Central African Republic’),(42,’TD’,’Chad’),(43,’CL’,’Chile’),(44,’CN’,’China’),(45,’CX’,’Christmas Island’),(46,’CC’,’Cocos (Keeling) Islands’),(47,’CO’,’Colombia’),(48,’KM’,’Comoros’),(49,’CG’,’Congo’),(50,’CK’,’Cook Islands’),(51,’CR’,’Costa Rica’),(52,’HR’,’Croatia (Hrvatska)’),(53,’CU’,’Cuba’),(54,’CY’,’Cyprus’),(55,’CZ’,’Czech Republic’),(56,’DK’,’Denmark’),(57,’DJ’,’Djibouti’),(58,’DM’,’Dominica’),(59,’DO’,’Dominican Republic’),(60,’TP’,’East Timor’),(61,’EC’,’Ecuador’),(62,’EG’,’Egypt’),(63,’SV’,’El Salvador’),(64,’GQ’,’Equatorial Guinea’),(65,’ER’,’Eritrea’),(66,’EE’,’Estonia’),(67,’ET’,’Ethiopia’),(68,’FK’,’Falkland Islands (Malvinas)’),(69,’FO’,’Faroe Islands’),(70,’FJ’,’Fiji’),(71,’FI’,’Finland’),(72,’FR’,’France’),(73,’FX’,’France, Metropolitan’),(74,’GF’,’French Guiana’),(75,’PF’,’French Polynesia’),(76,’TF’,’French Southern Territories’),(77,’GA’,’Gabon’),(78,’GM’,’Gambia’),(79,’GE’,’Georgia’),(80,’DE’,’Germany’),(81,’GH’,’Ghana’),(82,’GI’,’Gibraltar’),(83,’GK’,’Guernsey’),(84,’GR’,’Greece’),(85,’GL’,’Greenland’),(86,’GD’,’Grenada’),(87,’GP’,’Guadeloupe’),(88,’GU’,’Guam’),(89,’GT’,’Guatemala’),(90,’GN’,’Guinea’),(91,’GW’,’Guinea-Bissau’),(92,’GY’,’Guyana’),(93,’HT’,’Haiti’),(94,’HM’,’Heard and Mc Donald Islands’),(95,’HN’,’Honduras’),(96,’HK’,’Hong Kong’),(97,’HU’,’Hungary’),(98,’IS’,’Iceland’),(99,’IN’,’India’),(100,’IM’,’Isle of Man’),(101,’ID’,’Indonesia’),(102,’IR’,’Iran (Islamic Republic of)’),(103,’IQ’,’Iraq’),(104,’IE’,’Ireland’),(105,’IL’,’Israel’),(106,’IT’,’Italy’),(107,’CI’,’Ivory Coast’),(108,’JE’,’Jersey’),(109,’JM’,’Jamaica’),(110,’JP’,’Japan’),(111,’JO’,’Jordan’),(112,’KZ’,’Kazakhstan’),(113,’KE’,’Kenya’),(114,’KI’,’Kiribati’),(115,’KP’,’Korea, Democratic People\’s Republic of’),(116,’KR’,’Korea, Republic of’),(117,’XK’,’Kosovo’),(118,’KW’,’Kuwait’),(119,’KG’,’Kyrgyzstan’),(120,’LA’,’Lao People\’s Democratic Republic’),(121,’LV’,’Latvia’),(122,’LB’,’Lebanon’),(123,’LS’,’Lesotho’),(124,’LR’,’Liberia’),(125,’LY’,’Libyan Arab Jamahiriya’),(126,’LI’,’Liechtenstein’),(127,’LT’,’Lithuania’),(128,’LU’,’Luxembourg’),(129,’MO’,’Macau’),(130,’MK’,’Macedonia’),(131,’MG’,’Madagascar’),(132,’MW’,’Malawi’),(133,’MY’,’Malaysia’),(134,’MV’,’Maldives’),(135,’ML’,’Mali’),(136,’MT’,’Malta’),(137,’MH’,’Marshall Islands’),(138,’MQ’,’Martinique’),(139,’MR’,’Mauritania’),(140,’MU’,’Mauritius’),(141,’TY’,’Mayotte’),(142,’MX’,’Mexico’),(143,’FM’,’Micronesia, Federated States of’),(144,’MD’,’Moldova, Republic of’),(145,’MC’,’Monaco’),(146,’MN’,’Mongolia’),(147,’ME’,’Montenegro’),(148,’MS’,’Montserrat’),(149,’MA’,’Morocco’),(150,’MZ’,’Mozambique’),(151,’MM’,’Myanmar’),(152,’NA’,’Namibia’),(153,’NR’,’Nauru’),(154,’NP’,’Nepal’),(155,’NL’,’Netherlands’),(156,’AN’,’Netherlands Antilles’),(157,’NC’,’New Caledonia’),(158,’NZ’,’New Zealand’),(159,’NI’,’Nicaragua’),(160,’NE’,’Niger’),(161,’NG’,’Nigeria’),(162,’NU’,’Niue’),(163,’NF’,’Norfolk Island’),(164,’MP’,’Northern Mariana Islands’),(165,’NO’,’Norway’),(166,’OM’,’Oman’),(167,’PK’,’Pakistan’),(168,’PW’,’Palau’),(169,’PS’,’Palestine’),(170,’PA’,’Panama’),(171,’PG’,’Papua New Guinea’),(172,’PY’,’Paraguay’),(173,’PE’,’Peru’),(174,’PH’,’Philippines’),(175,’PN’,’Pitcairn’),(176,’PL’,’Poland’),(177,’PT’,’Portugal’),(178,’PR’,’Puerto Rico’),(179,’QA’,’Qatar’),(180,’RE’,’Reunion’),(181,’RO’,’Romania’),(182,’RU’,’Russian Federation’),(183,’RW’,’Rwanda’),(184,’KN’,’Saint Kitts and Nevis’),(185,’LC’,’Saint Lucia’),(186,’VC’,’Saint Vincent and the Grenadines’),(187,’WS’,’Samoa’),(188,’SM’,’San Marino’),(189,’ST’,’Sao Tome and Principe’),(190,’SA’,’Saudi Arabia’),(191,’SN’,’Senegal’),(192,’RS’,’Serbia’),(193,’SC’,’Seychelles’),(194,’SL’,’Sierra Leone’),(195,’SG’,’Singapore’),(196,’SK’,’Slovakia’),(197,’SI’,’Slovenia’),(198,’SB’,’Solomon Islands’),(199,’SO’,’Somalia’),(200,’ZA’,’South Africa’),(201,’GS’,’South Georgia South Sandwich Islands’),(202,’ES’,’Spain’),(203,’LK’,’Sri Lanka’),(204,’SH’,’St. Helena’),(205,’PM’,’St. Pierre and Miquelon’),(206,’SD’,’Sudan’),(207,’SR’,’Suriname’),(208,’SJ’,’Svalbard and Jan Mayen Islands’),(209,’SZ’,’Swaziland’),(210,’SE’,’Sweden’),(211,’CH’,’Switzerland’),(212,’SY’,’Syrian Arab Republic’),(213,’TW’,’Taiwan’),(214,’TJ’,’Tajikistan’),(215,’TZ’,’Tanzania, United Republic of’),(216,’TH’,’Thailand’),(217,’TG’,’Togo’),(218,’TK’,’Tokelau’),(219,’TO’,’Tonga’),(220,’TT’,’Trinidad and Tobago’),(221,’TN’,’Tunisia’),(222,’TR’,’Turkey’),(223,’TM’,’Turkmenistan’),(224,’TC’,’Turks and Caicos Islands’),(225,’TV’,’Tuvalu’),(226,’UG’,’Uganda’),(227,’UA’,’Ukraine’),(228,’AE’,’United Arab Emirates’),(229,’GB’,’United Kingdom’),(230,’US’,’United States’),(231,’UM’,’United States minor outlying islands’),(232,’UY’,’Uruguay’),(233,’UZ’,’Uzbekistan’),(234,’VU’,’Vanuatu’),(235,’VA’,’Vatican City State’),(236,’VE’,’Venezuela’),(237,’VN’,’Vietnam’),(238,’VG’,’Virgin Islands (British)’),(239,’VI’,’Virgin Islands (U.S.)’),(240,’WF’,’Wallis and Futuna Islands’),(241,’EH’,’Western Sahara’),(242,’YE’,’Yemen’),(243,’ZR’,’Zaire’),(244,’ZM’,’Zambia’),(245,’ZW’,’Zimbabwe’);

ALTER TABLE `settingsCountries` ENABLE KEYS;

SQL Server check table fragmentation level

If you are using SQL Server, and discover that what should be a simple select query, or particularly multiple select queries that are unioned together with a sort are running slowly, then the issue is your table indexes. This is particularly the case when using Date and DateTime fields for sorting.

If you are trying to sort a table or multiple tables by Date, then it is a good idea to add an index on the Date column, it makes things significantly faster.

Here is the SQL Query to check all tables in your database and the levels of fragmentation of each:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

SQL Server – List all tables and the fragmentation percentage

This is another check you should perform once databases start to get large, as having fragmented indexes have a huge performance hit.

For more on what fragmented indexes are, read this fantastic article.

After significant trial and error we have come up with an SQL script that lists all indexes for every table and shows the percentage fragmentation for the selected database on your server in one go!

 

SELECT
a.database_id,
db_name(a.database_id) As DatabaseName,
a.page_count,
a.OBJECT_ID,
a.index_id,
b.name,
a.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b ON a.OBJECT_ID = b.OBJECT_ID AND a.index_id = b.index_id
WHERE
a.database_id = DB_ID()
ORDER BY
a.OBJECT_ID

SQL Server – checking if any tables don’t have a primary key

Its easily done, in the rush to create tables someone forgets to tell the auto-number field that it is also a primary key.

Its not an issue until the database gets big, at which point (as listed on Microsoft TechNet) the performance can suffer by anything between 13% and 460% – which is potentially crippling for your database.

Thankfully there is a simple SQL script you can run to double-check that there are no tables without a primary key.

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasPrimaryKey’) = 0
ORDER BY SchemaName, TableName;

SQL Adding Row Number To The Returned Rows

Its something that took a bit of finding, but is very simple to do. I wanted to automatically add a row number to returned rows from a table in a database

e.g.

SELECT FirstNames, LastName, DOB FROM Staff ORDER BY FirstNames ASC

returns

FirstNames LastName DOB
Richard Taylor 01/01/1980
James Watts 01/01/1980
Richard Stringer 01/01/1980

we can modify this so that it returns a row number by changing the SQL as follows:

SELECT ROW_NUMBER() OVER (ORDER BY FirstNames) As RowNumber, FirstNames, LastName, DOB FROM Staff ORDER BY FirstNames ASC

now returns

RowNumber FirstNames LastName DOB
1 Richard Taylor 01/01/1980
2 James Watts 01/01/1980
3 Richard Stringer 01/01/1980

NB. The ROW_NUMBER() Requires that the over keyword be included after it, and that the order you choose should be the same as the order by on the query.

If you know how to do this in a different way, or a way that will work for a query that does not have an order by (a very rare occurence) then please do post a comment to let others know.

SQL Server – Increasing query performance for large reports

Its always a balance when creating reports, you want to produce an amazing report, but you dont want to cripple your server and make it unresponsive for other users whilst it is produced.

After a lot of research, we found that adding:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

as the first line in a stored procedure greatly improves performance for other users as the stored procedure then doesn’t lock tables for reading when it is producing the report.

increase-chart-11As a real-world example, we wrote a system that could produce an annual statistical report that had to go through 1.1million records 18 times per row, and for around 40,000 rows, so a staggering 792 billion reads on the database. We optimised the query, but it still took around 40 seconds to produce, and during that time all the CPUs in the database server were at 100% and disk IO went through the roof, and the system performance suffered dramatically. Other users were left with the system appearing to hang for up to a minute whilst one user created the report.

By adding the transaction level statement we not only decreased the query time to under 30 seconds, but it also allowed all other users to continue to use the system and not notice any performance hang in the system as their queries were still be executed as needed since the tables were not locked.