10 Rules for a Better SQL Schema

Summary:

1. Only Use Lowercase Letters, Numbers, and Underscores

2. Use Simple, Descriptive Column Names

3. Use Simple, Descriptive Table Names

4. Have an Integer Primary Key

5. Be Consistent with Foreign Keys

6. Store Datetimes as Datetimes

7. UTC, Always UTC

8. Have One Source of Truth

9. Prefer Tall Tables without JSON Columns

10. Don't Over-Normalize

The 3 Configuration Options that you always have to change in SQL Server!

The 3 Configuration Options that you always have to change in SQL Server! - SQLpassion:



'via Blog this'

How to open the firewall ports for SQL Server on Windows

netsh advfirewall firewall add rule name="Open Port 80" dir=in action=allow protocol=TCP localport=80

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433
@echo Enabling Dedicated Admin Connection port 1434
netsh advfirewall firewall add rule name="SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434
@echo Enabling Conventional SQL Server Service Broker port 4022
netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022
@echo Enabling Transact SQL/RPC port 135
netsh advfirewall firewall add rule name="SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh advfirewall firewall add rule name="Analysis Services" dir=in action=allow protocol=TCP localport=2383
@echo Enabling SQL Server Browser Service port 2382
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=TCP localport=2382

@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80
netsh advfirewall firewall add rule name="HTTP" dir=in action=allow protocol=TCP localport=80
@echo Enabling SSL port 443
netsh advfirewall firewall add rule name="SSL" dir=in action=allow protocol=TCP localport=443
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=UDP localport=1434
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

Understanding CancellationTokenSource with Tasks | johnbadams

Understanding CancellationTokenSource with Tasks | johnbadams:



'via Blog this'

Using LINQ to manipulate data in DataSet/DataTable | binoot!

MARCH 18, 2009

Using LINQ to manipulate data in DataSet/DataTable

Have you tried a micro ORM for your data access? Click to read more…

---

LINQ (Language Integrated Query) provides a very easy and fast way to manipulate data that is cached in a DataSet. In .Net applications, DataSet represents a disconnected cache of data. Your application typically needs to search, filter thru this data in order to display this data according to the need. DataView provides a very limited options
when it comes to creating a filtered view of the data. LINQ extends these capabilities to a greater extend.

A LINQ query opertaion consists of 3 actions (Ref:MSDN): obtain the data source, create the query and execute the query.

Any datasource that implements the IEnumerable(T) generic interface can be queried thru LINQ. So DataTable objects are good candidates to do any LINQ query opertions, we will see using the following examples, how some common tasks can be done using LINQ queries.

For our example, we will consider that our DataSet has one(1) table with the following schema,

1	dtCustomer (CustomerID,CustomerName,Address,City,PostalCode,State,Country,PhoneNumer)

A simple select:

1 2 3

IEnumerable query =     from customer in dtCustomer.AsEnumerable()     select customer;

Till this point, we have the LINQ query ready, but not executed it yet, query is executed when we actually use it.

1 2 3 4

foreach (DataRow dr in query) {     Console.WriteLine(dr.Field("CustomerName")); }

At this point, our query is executed and it prints the names of the customer.

We can also, get the resulset as a DataView by simply doing,

1	DataView view = query.AsDataView();

Most times, when we are dealing with DataSet/DataTable, data we will be creating a DataView as result of our LINQ query. ToList(), ToArray() methods are also very useful when you want to get your resultset
as a generic list or Array (Think AJAX/JSON!).

Lambda Expressions can be used as parameters to LINQ queries.

1 2 3 4 5 6 7 8

IEnumerable customers =     query.Where(c => c.Field("CustomerName").Containes("John"));   //All names that contain "John" foreach (DataRow cust in customers) {     Console.WriteLine(cust.Field("CustomerName")); }

Simple Where Clause:

1 2 3 4 5

EnumerableRowCollection query               = from customer in dtCustomer.AsEnumerable()                 where customer.Field("State") == "NJ"                 select customer;             DataView njview = query.AsDataView();

Pretty simple, njview represents all customers that live in NJ.
You can extend the example to add more criteria to your where clause…

1 2 3 4 5

EnumerableRowCollection query               = from customer in dtCustomer.AsEnumerable()                 where customer.Field("State") == "NJ" && customer.Field("PostalCode") == "08807"                 select customer;             DataView njview = query.AsDataView();

It is useful to note that when you write your where clause, you leverage the power of your C# (or VB.Net) language features to search and filter your resultset using LINQ.

So, A SQL where clause is

1 2	where customer.Field("State") == "NJ"      where customer.Field("State") != "NJ"

A SQL Like clause is

1	where customer.Field("CustomerName").Containes("John")

Skip and Take allows to get the skip the first n rows or get the top n rows as a result of the query.\

1 2 3 4 5 6 7 8 9

EnumerableRowCollection query               = (from customer in dtCustomer.AsEnumerable()                 where customer.Field("State") == "NJ"                 select customer).Skip(3);   EnumerableRowCollection query               = (from customer in dtCusomter.AsEnumerable()                 where customer.Field("State") == "NJ"                 select customer).Take(3);

Simple ORDER BY clause:

1 2 3 4

EnumerableRowCollection query               = from customer in dtCustomer.AsEnumerable()                 orderby customer.Field("CustomerName")                 select customer;

Above query, gets the result order by customer name (ascending is default). And if you want it by descending order,

1 2 3 4

EnumerableRowCollection query               = from customer in dtCusomter.AsEnumerable()                 orderby customer.Field("CustomerName")  descending                 select customer;

Reverse ORDER:

1 2 3 4

EnumerableRowCollection query               = (from customer in dtCustomer.AsEnumerable()                 orderby customer.Field("CustomerName")                 select customer.Reverse();

Simple JOIN Operator:

1 2 3 4 5 6 7 8 9

var customers = ds.Tables["Customer"].AsEnumerable(); var orders = ds.Tables["Order"].AsEnumerable(); var query =             from customer in customers             join order in orders         on order.Field("CustomerID")             equals customer.Field("Customer")     into custOrders     select custOrders;

All the examples given above is just the tip of iceberg on what you can do with LINQ and Lambda expressions. There is tons of samples and articles available on Internet on this, if you are looking for a way to simplify and speed up your business processing logic in your middle tier, LINQ is something you need to adopt!

Cheers!