Archive

Posts Tagged ‘sql’

English & Arabic Countries List for SQL Server

February 14, 2013 7 comments

Below you will find a script for all the countries in both Arabic and English names:

1 – Create your table:

USE [DatabaseName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Country](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EnglishName] [nvarchar](100) NULL,
[ArabicName] [nvarchar](100) NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2 – Add the records:

insert into Country(englishname, arabicname) values
(‘Afghanistan’,N’أفغانستان’),
(‘Albania’,N’ألبانيا’),
(‘Algeria’,N’الجزائر’),
(‘American Samoa’,N’ساموا-الأمريكي’),
(‘Andorra’,N’أندورا’),
(‘Angola’,N’أنغولا’),
(‘Anguilla’,N’أنغويلا’),
(‘Antarctica’,N’أنتاركتيكا’),
(‘Antigua and Barbuda’,N’أنتيغوا وبربودا’),
(‘Argentina’,N’الأرجنتين’),
(‘Armenia’,N’أرمينيا’),
(‘Aruba’,N’أروبه’),
(‘Australia’,N’أستراليا’),
(‘Austria’,N’النمسا’),
(‘Azerbaijan’,N’أذربيجان’),
(‘Bahamas’,N’الباهاماس’),
(‘Bahrain’,N’البحرين’),
(‘Bangladesh’,N’بنغلاديش’),
(‘Barbados’,N’بربادوس’),
(‘Belarus’,N’روسيا البيضاء’),
(‘Belgium’,N’بلجيكا’),
(‘Belize’,N’بيليز’),
(‘Benin’,N’بنين’),
(‘Bermuda’,N’جزر برمودا’),
(‘Bhutan’,N’بوتان’),
(‘Bolivia’,N’بوليفيا’),
(‘Bosnia and Herzegovina’,N’البوسنة و الهرسك’),
(‘Botswana’,N’بوتسوانا’),
(‘Brazil’,N’البرازيل’),
(‘Brunei Darussalam’,N’بروني’),
(‘Bulgaria’,N’بلغاريا’),
(‘Burkina Faso’,N’بوركينا فاسو’),
(‘Burundi’,N’بوروندي’),
(‘Cambodia’,N’كمبوديا’),
(‘Cameroon’,N’كاميرون’),
(‘Canada’,N’كندا’),
(‘Cape Verde’,N’الرأس الأخضر’),
(‘Central African Republic’,N’جمهورية أفريقيا الوسطى’),
(‘Chad’,N’تشاد’),
(‘Chile’,N’شيلي’),
(‘China’,N’جمهورية الصين الشعبية’),
(‘Colombia’,N’كولومبيا’),
(‘Comoros’,N’جزر القمر’),
(‘Democratic Republic’,N’جمهورية الكونغو الديمقراطية’),
(‘Congo, Republic of (Brazzaville)’,N’جمهورية الكونغو’),
(‘Cook Islands’,N’جزر كوك’),
(‘Costa Rica’,N’كوستاريكا’),
(‘Cote d”Ivoire’,N’ساحل العاج’),
(‘Croatia’,N’كرواتيا’),
(‘Cuba’,N’كوبا’),
(‘Cyprus’,N’قبرص’),
(‘Czech Republic’,N’الجمهورية التشيكية’),
(‘Denmark’,N’الدانمارك’),
(‘Djibouti’,N’جيبوتي’),
(‘Dominica’,N’دومينيكا’),
(‘Dominican Republic’,N’الجمهورية الدومينيكية’),
(‘East Timor Timor-Leste’,N’تيمور الشرقية’),
(‘Ecuador’,N’إكوادور’),
(‘Egypt’,N’مصر’),
(‘El Salvador’,N’إلسلفادور’),
(‘Equatorial Guinea’,N’غينيا الاستوائي’),
(‘Eritrea’,N’إريتريا’),
(‘Estonia’,N’استونيا’),
(‘Ethiopia’,N’أثيوبيا’),
(‘Faroe Islands’,N’جزر فارو’),
(‘Fiji’,N’فيجي’),
(‘Finland’,N’فنلندا’),
(‘France’,N’فرنسا’),
(‘French Guiana’,N’غويانا الفرنسية’),
(‘French Polynesia’,N’بولينيزيا الفرنسية’),
(‘Gabon’,N’الغابون’),
(‘Gambia’,N’غامبيا’),
(‘Georgia’,N’جيورجيا’),
(‘Germany’,N’ألمانيا’),
(‘Ghana’,N’غانا’),
(‘Gibraltar’,N’جبل طارق’),
(‘Greece’,N’اليونان’),
(‘Greenland’,N’جرينلاند’),
(‘Grenada’,N’غرينادا’),
(‘Guadeloupe’,N’جزر جوادلوب’),
(‘Guam’,N’جوام’),
(‘Guatemala’,N’غواتيمال’),
(‘Guinea’,N’غينيا’),
(‘Guinea-Bissau’,N’غينيا-بيساو’),
(‘Guyana’,N’غيانا’),
(‘Haiti’,N’هايتي’),
(‘Honduras’,N’هندوراس’),
(‘Hong Kong’,N’هونغ كونغ’),
(‘Hungary’,N’المجر’),
(‘Iceland’,N’آيسلندا’),
(‘India’,N’الهند’),
(‘Indonesia’,N’أندونيسيا’),
(‘Iran’,N’إيران’),
(‘Iraq’,N’العراق’),
(‘Ireland’,N’إيرلندا’),
(‘Italy’,N’إيطاليا’),
(‘Jamaica’,N’جمايكا’),
(‘Japan’,N’اليابان’),
(‘Jordan’,N’الأردن’),
(‘Kazakhstan’,N’كازاخستان’),
(‘Kenya’,N’كينيا’),
(‘Kiribati’,N’كيريباتي’),
(‘Korea, (North Korea)’,N’كوريا الشمالية’),
(‘Korea, (South Korea)’,N’كوريا الجنوبية’),
(‘Kuwait’,N’الكويت’),
(‘Kyrgyzstan’,N’قيرغيزستان’),
(‘Lao, PDR’,N’لاوس’),
(‘Latvia’,N’لاتفيا’),
(‘Lebanon’,N’لبنان’),
(‘Lesotho’,N’ليسوتو’),
(‘Liberia’,N’ليبيريا’),
(‘Libya’,N’ليبيا’),
(‘Liechtenstein’,N’ليختنشتين’),
(‘Lithuania’,N’لتوانيا’),
(‘Luxembourg’,N’لوكسمبورغ’),
(‘Macao’,N’ماكاو’),
(‘Macedonia, Rep. of’,N’مقدونيا’),
(‘Madagascar’,N’مدغشقر’),
(‘Malawi’,N’مالاوي’),
(‘Malaysia’,N’ماليزيا’),
(‘Maldives’,N’المالديف’),
(‘Mali’,N’مالي’),
(‘Malta’,N’مالطا’),
(‘Marshall Islands’,N’جزر مارشال’),
(‘Martinique’,N’مارتينيك’),
(‘Mauritania’,N’موريتانيا’),
(‘Mauritius’,N’موريشيوس’),
(‘Mexico’,N’المكسيك’),
(‘Micronesia’,N’مايكرونيزيا’),
(‘Moldova’,N’مولدافيا’),
(‘Monaco’,N’موناكو’),
(‘Mongolia’,N’منغوليا’),
(‘Montenegro’,N’الجبل الأسو’),
(‘Montserrat’,N’مونتسيرات’),
(‘Morocco’,N’المغرب’),
(‘Mozambique’,N’موزمبيق’),
(‘Myanmar, Burma’,N’ميانمار’),
(‘Namibia’,N’ناميبيا’),
(‘Nauru’,N’نورو’),
(‘Nepal’,N’نيبال’),
(‘Netherlands’,N’هولندا’),
(‘Netherlands Antilles’,N’جزر الأنتيل الهولندي’),
(‘New Caledonia’,N’كاليدونيا الجديدة’),
(‘New Zealand’,N’نيوزيلندا’),
(‘Nicaragua’,N’نيكاراجوا’),
(‘Niger’,N’النيجر’),
(‘Nigeria’,N’نيجيريا’),
(‘Niue’,N’ني’),
(‘Northern Mariana Islands’,N’جزر ماريانا الشمالية’),
(‘Norway’,N’النرويج’),
(‘Oman’,N’عُمان’),
(‘Pakistan’,N’باكستان’),
(‘Palau’,N’بالاو’),
(‘Palestine’,N’فلسطين’),
(‘Panama’,N’بنما’),
(‘Papua New Guinea’,N’بابوا غينيا الجديدة’),
(‘Paraguay’,N’باراغواي’),
(‘Peru’,N’بيرو’),
(‘Philippines’,N’الفليبين’),
(‘Poland’,N’بولونيا’),
(‘Portugal’,N’البرتغال’),
(‘Puerto Rico’,N’بورتو ريكو’),
(‘Qatar’,N’قطر’),
(‘Reunion Island’,N’ريونيون’),
(‘Romania’,N’رومانيا’),
(‘Russia’,N’روسيا’),
(‘Rwanda’,N’رواندا’),
(‘Saint Kitts and Nevis’,N’سانت كيتس ونيفس’),
(‘Saint Lucia’,N’سانت لوسيا’),
(‘Saint Vincent and the’,N’سانت فنسنت وجزر غرينادين’),
(‘Samoa’,N’المناطق’),
(‘San Marino’,N’سان مارينو’),
(‘Sao Tome and Príncipe’,N’ساو تومي وبرينسيبي’),
(‘Saudi Arabia’,N’المملكة العربية السعودية’),
(‘Senegal’,N’السنغال’),
(‘Serbia’,N’جمهورية صربيا’),
(‘Seychelles’,N’سيشيل’),
(‘Sierra Leone’,N’سيراليون’),
(‘Singapore’,N’سنغافورة’),
(‘Slovakia’,N’سلوفاكيا’),
(‘Slovenia’,N’سلوفينيا’),
(‘Solomon Islands’,N’جزر سليمان’),
(‘Somalia’,N’الصومال’),
(‘South Africa’,N’جنوب أفريقيا’),
(‘Spain’,N’إسبانيا’),
(‘Sri Lanka’,N’سريلانكا’),
(‘Sudan’,N’السودان’),
(‘Suriname’,N’سورينام’),
(‘Swaziland’,N’سوازيلند’),
(‘Sweden’,N’السويد’),
(‘Switzerland’,N’سويسرا’),
(‘Syria’,N’سوريا’),
(‘Taiwan’,N’تايوان’),
(‘Tajikistan’,N’طاجيكستان’),
(‘Tanzania’,N’تنزانيا’),
(‘Thailand’,N’تايلندا’),
(‘Tibet’,N’تبت’),
(‘Timor-Leste (East Timor)’,N’تيمور الشرقية’),
(‘Togo’,N’توغو’),
(‘Tonga’,N’تونغا’),
(‘Trinidad and Tobago’,N’ترينيداد وتوباغو’),
(‘Tunisia’,N’تونس’),
(‘Turkey’,N’تركيا’),
(‘Turkmenistan’,N’تركمانستان’),
(‘Tuvalu’,N’توفالو’),
(‘Uganda’,N’أوغندا’),
(‘Ukraine’,N’أوكرانيا’),
(‘United Arab Emirates’,N’الإمارات العرب’),
(‘United Kingdom’,N’المملكة المتحدة’),
(‘United States’,N’الولايات المتحدة’),
(‘Uruguay’,N’أورغواي’),
(‘Uzbekistan’,N’أوزباكستان’),
(‘Vanuatu’,N’فانواتو’),
(‘Vatican City State’,N’الفاتيكان’),
(‘Venezuela’,N’فنزويلا’),
(‘Vietnam’,N’فيتنام’),
(‘Virgin Islands (British)’,N’الجزر العذراء البريطانية’),
(‘Virgin Islands (U.S.)’,N’الجزر العذراء الأمريكي’),
(‘Wallis and ‘,N’والس وفوتونا’),
(‘Western Sahara’,N’الصحراء الغربية’),
(‘Yemen’,N’اليمن’),
(‘Zambia’,N’زامبيا’),
(‘Zimbabwe’,N’زمبابوي’)

Advertisements

Pass a comma separated list to a stored procedure

October 24, 2012 Leave a comment

Sometimes we need to pass a list or an array to a stored procedure to retrieve some results like in the query below:

select * from mytable where id in (1,2)

But doing this using a Stored Procedure will cause the query to be rendered as follows (select * from mytable where id in (‘1,2’)) which will cause this error: Conversion failed when converting the varchar value ‘1,2’ to data type int.

So To pass an array of ids (numbers) to a stored procedure, you can do it using the below:

Create PROCEDURE [dbo].[spGetData]
— Add the parameters for the stored procedure here
@ids nvarchar(MAX)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #TempTable(ID int)
while len(@ids) > 0
begin
insert into #TempTable values (left(@ids, charindex(‘,’, @ids+’,’)-1))
set @ids = stuff(@ids, 1, charindex(‘,’, @ids+’,’), ”)
end
— Insert statements for procedure here
select * from mytable where id in (select ID from #TempTable)
END

Database Normalization

March 9, 2012 Leave a comment

What is Normalization?

Normalization is the process of efficiently organizing data in your database. There are two goals of the normalization process: reducing redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (all fields in a tale can be uniquely determined from the primary key). Both of these are the main goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
The Normal Forms

The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you’ll often see 1NF, 2NF, and 3NF. Fourth and Fifth normal forms won’t be discussed in this article since they are not much seen.

Before we begin our discussion of the normal forms, it’s important to point out that they are guidelines and guidelines only. Occasionally, it becomes necessary to stray from them to meet practical business requirements. However, when variations take place, it’s extremely important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies. That said, let’s explore the normal forms.
First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:
• Eliminate duplicative columns from the same table (For example, to track an inventory item that may come from two possible sources, an inventory record may contain fields for Vendor Code 1 and Vendor Code 2.
What happens when you add a third vendor? Adding a field is not the answer; it requires program and table modifications and does not smoothly accommodate a dynamic number of vendors. Instead, place all vendor information in a separate table called Vendors, then link inventory to vendors with an item number key, or vendors to inventory with a vendor code key.)
• Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:
• Meet all the requirements of the first normal form.
• Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• Create relationships between these new tables and their predecessors through the use of foreign keys.
Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:
• Meet all the requirements of the second normal form.
• Remove columns that are not dependent upon the primary key.

Database Architecture Ground Rules

March 9, 2012 2 comments

I will highlight some of the most important Ground Rules while working with database architecture:

  1. Tables, Columns, Queries, Stored Procedures and any other objects in the database should only contain alphanumeric characters and/or underscores, any other characters are not allowed under any circumstances.
  2. Unless we have tables from two different projects under the same database, tables shouldn’t be prefixed like (Student and not tblStudents) since this will save you time when dealing with the tables as objects in your application.
  3. Use well defined and consistent names for tables and columns (e.g. School, StudentCourse, CourseID …).
  4. Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names.
  5. Don’t use spaces for table names. Otherwise you will have to use ‘{‘, ‘[‘, ‘“’ etc. characters to define tables (i.e. for accesing table Student Course you’ll write “Student Course”. StudentCourse is much better).
  6. Keep passwords encrypted for security. Decrypt them in application when required.
  7. Use integer id fields for all tables. If id is not required for the time being, it may be required in the future (for association tables, indexing …).
  8. Choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performance problems.
  9. Use bit fields for Boolean values. Using integer or varchar is unnecessarily storage consuming. Also start those column names with “Is”.
  10. Provide authentication for database access. Don’t give admin role to each user.
  11. Avoid “select *” queries until it is really needed. Use “select [required_columns_list]” for better performance.
  12. Use an ORM (object relational mapping) framework (i.e. hibernate, iBatis …) if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.
  13. Partition big and unused/rarely used tables/table parts to different physical storages for better query performance.
  14. For big, sensitive and mission critical database systems, use disaster recovery and security services like failover clustering, auto backups, replication etc.
  15. Use constraints (foreign key, check, not null …) for data integrity. Don’t give whole control to application code.
  16. Lack of database documentation is evil. Document your database design with ER schemas and instructions. Also write comment lines for your triggers, stored procedures and other scripts.
  17. Use indexes for frequently used queries on big tables. Analyzer tools can be used to determine where indexes will be defined. For queries retrieving a range of rows, clustered indexes are usually better. For point queries, non-clustered indexes are usually better.
  18. Database server and the web server must be placed in different machines. This will provide more security (attackers can’t access data directly) and server CPU and memory performance will be better because of reduced request number and process usage.
  19. Image and blob data columns must not be defined in frequently queried tables because of performance issues. These data must be placed in separate tables and their pointer can be used in queried tables.
  20. Normalization must be used as required, to optimize the performance. Under-normalization will cause excessive repetition of data, over-normalization will cause excessive joins across too many tables. Both of them will get worse performance.
  21. Spend time for database modeling and design as much as required. Otherwise maintenance and re-design time will take much more.

SQL CTE (common table expression)

February 17, 2012 Leave a comment

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

  • Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

SQL CTE Example

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
SELECT
p.ProductName,
c.CategoryName,
p.UnitPrice
FROM Products p
INNER JOIN Categories c ON
c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

The CTE should be used directly by the query following it since it will be deleted after that, so you can’t define a cte and use it after 2 or 3 queries since an error will be generated telling you that the table ctetable1 (for example) doesn’t exist.

For Recursive Queries Using Common Table Expressions check this link:

http://msdn.microsoft.com/en-us/library/ms186243.aspx