s
Contact Login Register
h M

SQL Server Indexes

Data Retrieval Methods within databases:

Method 1: Sequential access method, by searching through each individual record looking for a match. Unindexed table AKA Full-table scan

Method 2: Direct Access Method. Use of Index(es) on a table.

What are Indexes?

An Index is a way of presenting data differently than the way it appears on the disk(within a SQL Table). Special types of Indexes reorder the records physical location within a table. An index can be created and assigned to a single column or multiple columns depending on your database implementation and what’s supported by your RDBMS.

An Index is very similar to the Order by clause however the key differences are:

  • Order By Clause Re-sorts and orders the data each time you execute the corresponding SQL Statment.
  • When using an Index, the database system creates a physical index object and reuses the same index each time you query the table.

*** Please note that SQL indexes require physical storage and resides on the disk hard drive.

Is there an appropriate and inappropriate time to use indexes?

The answer is yes. Indexes exist to assist with query optimization. Indexes if used incorrectly can cause additional overhead to your query. It’s important to analyze your Table and understand the best and worst case to use/remove an index.

Appropriate times to use Indexes:

  • Indexes yield the greatest improvement when the columns you have indexed contain a wide variety of data.
  • Indexes can optimize your queries when those queries are returning a small amount of data
  • Always index fields that are used in joins between tables. This technique can greatly increase the speed of a join.

Inappropriate times to use Indexes:

  • Indexes can improve the speed of data retrieval, however they cause slow data updates. When performing large inserts, updates, deletes, you may want to drop the index before doing so and adding it back after success.
  • For small tables, the use of indexes does not result in any performance improvement.
  • Indexes should not be used on columns that contain high number of NULL values.
  • Do not index on fields that are manipulated frequently. This will result with additional overhead.
  • Indexes take up space within your database. If you’re trying to manage space, then be aware of the space being allocated and used by your indexes.

Why would you use an Index?

Indexes are typically used for three primary reasons:

1.) To enforce referential integrity.
2.) To facilitate the ordering of data based on the contents of the indexes field or fields.
3.) To optimize the execution speed of queries.

Syntax to Create Indexes on SQL Server:

CREATE INDEX NameOfIndex
ON TableName(Column1)

You also have the capability to store the column information in ASC or DESC order. Indexes help sort all of the information in the tablebased on the column you’re sorting on.

CREATE INDEX NameOfIndex
ON TableName(Column1 ASC)
-- No need to specify ASC for ascending order since this is the default behavior.

Sunday, November 11, 2012/Author: Chad Nash/Number of views (71181)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:

SQL Server Constraints

Getting Started:

Constraints are rules assigned to columns in your SQL tables. The main use of a Constraint is to help ensure that data integrity measures are met. Data integrity is very important. The whole idea of Data Integrity is to maintain a consistency of data. Constraints assist with Data Integrity, referential integrity(Primary Keys and Foreign Keys), and aids Normalization for your Database tables.

Data Integrity:

Lets say that we have a SQL table named “Contacts” and one of the columns in this table is “PhoneNumber”. Now when you think of storing a phone number, there’s many formats that come to mind:

  1. (xxx) xxx – xxxx
  2. xxx-xxx-xxxx
  3. xxxxxxxxxx

Data Integrity is ensuring that you pick a format for “Phone Numbers” and stick to that format.  You would be violating Data Integrity if you chose to store format #1, #2 and #3 into the PhoneNumber column of the Contacts Table.

 

Types of Constraints:

On SQL Server, there are many types of Constraints that you can utilize. Below are the most commonly used:

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • CHECK

Using “PRIMARY KEY” Constraint:

The Primary Key Constraint, enforces that a column value must be unique and specifies the column as the Primary Key.

Example of Creating a Primary key on a CREATE TABLE statement:
-----------------------------------------------

CREATE TABLE TableName
(
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] NVARCHAR(200),
[Column2] NVARCHAR(200)
)

-----------------------------------------------

You can add a Primary Key Constraint to an existing table:
-----------------------------------------------

ALTER TABLE TableName
ADD CONSTRAINT Give_Constraint_a_Name PRIMARY KEY(ColumnName)

-----------------------------------------------

Using “FOREIGN KEY” Constraint:

The Foreign Key Constraint allows you to specify that a column in your table is the Primary key to another table, thus granting you linking access to the other table via the value in this column. This promotes referential integrity, meaning that you're making your SQL Table a dependent to another table(Child of Parent).

I’ve created a simple example, there are two tables “Table1” and “Table2”":
-----------------------------------------------

/*Parent Table */

CREATE TABLE Table1
(
[Table1ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] NVARCHAR(200),
[Column2] NVARCHAR(200),
[Column3] NVARCHAR(200)
)

/*Dependent Table */

CREATE TABLE Table2
(
[Table2ID] INT IDENTITY(1,1) PRIMARY KEY,
[Table1ID] INT FOREIGN KEY REFERENCES Table1 (Table1ID),
[Column1] NVARCHAR(200),
[Column2] NVARCHAR(200),
[Column3] NVARCHAR(200)
)

-----------------------------------------------

NOTE: By adding a FOREIGN KEY constraint t

Tuesday, November 6, 2012/Author: Chad Nash/Number of views (73059)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:

Dynamic Views Alternate Row Colors

Hello Everyone,

I have come up with a simple method in which you can apply Alternate Row colors to your Dynamic Views. Here’s an image of my Dynamic View:

DynamicView

 

This can only be accomplished when using Custom SQL as the Data Source of your Dynamic View. Here’s the SQL that’s being used in this Dynamic View:

select *, (CASE WHEN ABS(TemplateID) % 2 = 1 THEN '#F6F6F6' ELSE '#E8E8E8' END) As TheRowColor from Module_Templates

** The Highlighted section is the column that’s responsible for Alternating Colors.

Now that you’re returning this column in your Dynamic View you can use this token in your Dynamic Views Item Template.

You’ll want to assign “[therowcolor]” to the TR of your table.

 

For instance here’s the HTML that I used for my Dynamic View:

View my Template HTML

 

Leave a comment if you have any questions or like the blog.

 

-Ryan Bakerink

Friday, September 14, 2012/Author: Chad Nash/Number of views (65434)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:

Create a Dynamic Views Action Confirmation

Have you created an Action in Dynamic Views to delete a record if clicked? Many people would like to have a confirmation of delete instead of just deleting the record off of first click.

Here are the steps in which you can create a Confirm Message before executing an Action.

 

Step 1.)

Create a Dynamic Views Action in your Dynamic View. After configuring, save the Action.

 

Step 2.)

Place the Action [token] into your Dynamic Views Item Template so you can view your Action. Inspect the Action / Hyperlink and you’ll see that a URL redirection is in place if clicked. Copy this link URL, this is very important to hold onto.

The link will look similar, but not exact, to the link below:

 

www.yousite.com/DynamicViews/tabid/95/CurModuleID/486/Action/True/ActionID/3/PrimaryID/[userid]/Default.aspx'

Which ever column you’ve specified as the Primary Key for your Dynamic View you will replace with the“[userid]” in the URL link above.

 

Step 3.)

Create your own HTML button, hyperlink or image button for the Action so that you have full control.

 

Step 3a.)

Here’s an example hyperlink example to customize your Action.

Step 4.)

Save your Templates in Dynamic Views and test this method.

 

Please let me know if you have any questions.

Thanks,

Ryan

Tuesday, September 4, 2012/Author: Chad Nash/Number of views (70541)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:

Ideal Payment Gateway w/ Dynamic Forms

Recently, Datasprings added, upon the request of one of our clients, the possibility to use iDeal as payment provider in its module Dynamic Forms. Dynamic Forms already contained support for Authorize.Net, Paypal and a number of other payment providers.
iDeal is the de facto standard for online payments in the Netherlands (about 10 million consumers use iDeal), every webshop uses it and it is accepted as a secure solution by Dutch consumers. iDeal is supported by all major banks in the Netherlands and many smaller banks.  iDeal is not free, you will usually pay a subscription fee and transaction costs per transaction, this depends on your bank.
With this addition the module can now be used to directly accept payments using iDeal. This gives the site owner full control over his payment process, furthermore, it is also a cost effective solution.

Full details can be found here:

http://www.40fingers.net/WeblogsNews/Weblogs/tabid/58/ID/104/language/en-US/Using-Datasprings-Dynamic-Forms-to-make-iDeal-payments.aspx

 

Thanks!

 

-Chad

Tuesday, September 4, 2012/Author: Chad Nash/Number of views (65304)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:
RSS
First 567891011121314

Enter your email below AND grab your spot in our big giveaway!

The winner will receive the entire Data Springs Collection 7.0 - Designed to get your website up and running like a DNN superhero (spandex not included).

  
Subscribe
united luxury shop
konulu porno
deneme bonusu maltcasino bahis siteleri
sex video free download in mobile hot sexy naked indian girls download free xxx video
atasehir eskort antalya escort bahcelievler escort alanya escort atasehir escort
porno izle
gaziantep escort gaziantep escort
escort pendik
buca escort
izmir escort bayan
vdcasino casino metropol casinomaxi
buca escort
izmir escort
baybahis
holiganbet