Encrypt Password in SQL

Hello folks,

Today we will see how we can encrypt password before saving into database table.

There are many ways to implement this feature like defining Asymmetric keys and using that into table/sp or use some hash algo to encrypt password etc but we will add some complexity into encryption stuff.

Objective:  Our goal is to generate very complex and encrypted password which can’t be hacked (not even by database guys using sql injection etc).

Solution:

Step 1: Create a table that will hold username, password and other details. See below sample. Continue reading

Need of KPI for business

Hello guys,

If you are working for any industry all across the globe, I am sure that the main target of any organization to generate revenue.  To measure the performance of an organization we need few benchmark or factors on the basis of which we can identify the health of business. A KPI is quantifiable and measureable unit to measure the health of business.

KPI:

  • Stands for Key Performance Indicator
  • It is a quantifiable measurement to measure and analyze the performance or health of your business
  • It is Critical Performance Indicator
  • It indicates that the strategy we chose are in right path to achieve your organization goal

Why do we need a KPI?

  • KPI will be aligned with your strategic business goal.
  • Result will show the progress and success of business strategies defined
  • Carry out business analysis and help us to gain knowledge by taking decision for the benefit of our organization
  • KPI is also used for Competitive Analysis
  • It helps us to focus on business actions on strategic goals
  • Through these Indicators you can measure success rather than result.
  • Will help you to take decision based on the KPI

How to choose effective KPI?

Below are few benchmarks/factors which will help you to decide your KPI for your business.

  • You should Adapt KPI as per your business need and strategic goals
  • You should be able to measure strategic success through KPI
  • KPI should be transparent across the organization
  • Should be measureable, relevant and easy to interpret
  • Should bring overall benefit to your business
  • Should answer your questions regarding your business (growth)
  • We should be able to make analysis on the KPI

How to define KPI?

Below steps we must consider in mind before defining the KPI for your business:

  • Strategic Goals/ Success Indicator: Identify critical success factors and actions and set a target for your business.
  • Measurement Period: There must a measurement period to review health of your business based on KPI
  • Measurement Unit: Unit of measurement must be along with benchmark value/figure
  • Result Interpretation: It should be Readable, Standardized and Relevant

Looking for your feedback.

SQL Server 2014 In-Memory OLTP Engine

Hello Techies,

While going through couple of posts for Sql server enhancements , I started deep diving some of important features which are mentioned below.

In-Memory OLTP Engine and AMR Tool:

In memory OLTP in sql-server 2014

The most awaited and important new feature in SQL Server 2014 is the In-Memory OLTP engine (code-name Hekaton).

Purpose: It moves required tables and stored procedures into memory which in turns drastically reduces system I/O and improve performance of your OLTP applications.

Microsoft claims that some applications can expect up to a 20x performance improvement. Edgenet, an early adopter, saw a 7x performance increase in its online and retail supply application.

  • The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays.
  • The In-Memory OLTP tables are copied into memory and made durable by transaction log written to disk.
  • An all-new lock-free engine processes the transactions for memory-resident tables.
  • Stored procedure performance is improved by compiling the stored procedures into native code DLLs hence no need to recompile each time if execution plan in cache get expired.
  • Standard T-SQL stored procedures are interpreted, which adds overhead to the execution process.
  • Compiling the stored procedures to native Win64 code makes them directly executable, thereby maximizing their performance and minimizing execution time.
  • To help you evaluate how the In-Memory OLTP engine will improve your database performance, Microsoft includes the new Analysis, Migrate, and Report (AMR) tool.
  • Like its name suggests, the AMR tool analyzes your database and helps you identify the tables and stored procedures that would benefit from moving them into memory.
  • It lists the expected performance improvements as well as any incompatibilities that need to be addressed.
  • In addition, the AMR tool can help you perform the actual migration of tables to the new memory-optimized format.
  • The In-Memory OLTP engine works with commodity server hardware, but it has a number of limitations.
  • For instance, not all of the data types are supported.
  • Some of the data types that aren’t supported for memory-optimized tables include geography, hierarchyid, image, text, ntext, varchar(max), and xml.
  • In addition, several database features can’t be used with the new In-Memory OLTP capability.
  • Database mirroring, snapshots, computed columns, triggers, clustered indexes, identity columns, FILESTREAM storage, and FOREIGN KEY, CHECK, and UNIQUE constraints aren’t supported.
  • The In-Memory OLTP engine is supported on Windows Server 2012 R2, Windows Server 2012, and Windows Server 2008 R2 SP2.
  • You need to be using the SQL Server 2014 Enterprise, Developer, or Evaluation edition. Notably, In-Memory OLTP won’t be supported on the SQL Server 2014 Standard edition.

AMR Tool:

With SQL Server 2014’s new In-Memory OLTP engine, you can load tables and stored procedures in memory, which provides very fast response times.

The goal isn’t to load all the database tables and stored procedures in memory, but rather just those tables that are crucial to performance and those stored procedures that have complex logical calculations.

To help you identify which tables and stored procedures will give you the best performance gain after being migrated to In-Memory OLTP, SQL Server 2014 provides the new Analysis, Migrate, and Report (AMR) tool. Built into SQL Server Management Studio (SSMS), the AMR tool which consists of:

  • A) Transaction performance collector (which collects data about existing tables and stored procedures in order to analyze workloads) and transaction performance analysis reports (which gives recommendations about the tables and stored procedures to migrate to In-Memory OLTP based on the collected data)
  • B) Memory Optimization Advisor (which guides you through the process of migrating a table to a memory-optimized table)
  • C) Native Compilation Advisor (which helps you identify T-SQLelements that need to be changed before migrating a stored procedure to a natively compiled stored procedure)

The AMR tool leverages the new Transaction Performance Collection Sets for gathering information about workloads and the Management Data Warehouse (a relational database) to store the collected data.

The Transaction Performance Collection Sets includes:

  • A) Stored Procedure Usage Analysis collection set which captures information about stored procedures for a future migration to natively compiled stored procedures
  • B) Table Usage Analysiscollection set which captures information about disk-based tables for a future migration to memory optimized tables.

Awaiting for your feedback and suggestions.

Data & Type Of Data

Data

Hi Techie,

Today , I am going to share my thoughts on a series of posts for big data system. Lets get familiar with data terminologies so that upcoming posts would be helpful for us.

Below are few simple terminologies related to big data technology.

Data: Anything which resides in digital/binary format in computer system can be called as data. It really does not matter whether we get some useful information or not.

Information: Processed (meaningful data) data is called information. It means any data which can give us any conclusion and through which we can take decision to drive our business is called information.

Structured Data: Data which has been stored in structured or uniform way generally in relation DBMS or in spreadsheet is called structured data. So they are also known as Relational Data as well. Before storing structured data we first create a data model or a plan that how do we want to store our data. Consider an example of excel spreadsheet where first of all we create a template for columns then define the types of data that field will contain, any restriction or validation point if we want so that only correct and accurate information can be entered by user.

Advantage: We can enter, store, query and analyze the structured data without any overhead. It is easy to understand and take decision. These structured data can easily be stored and retrieved by SQL(structured query language.

Unstructured Data: It is type of data that can not be stored in a tradition structured way i.e raw column cased structure.

Example: Example for unstructured data can be text and multimedia content. Examples include e-mail messages, word processing documents, videos, photos, audio files, presentations, webpages and many other kinds of business documents. many experts says that around 80-90% of the data in any organization is unstructured. And the amount of unstructured data in enterprises is growing significantly, often many times faster than structured databases are growing. 

Unstructured Data Management: Organizations use of variety of different software tools to help them organize and manage unstructured data.

These can include the following:

Big data tools: Software like Hadoop can process stores of both unstructured and structured data that are extremely large, very complex and changing rapidly.

Business intelligence software: Also known as BI, business intelligence is a broad category of analytics, data mining, dashboards and reporting tools that help companies make sense of their structured and unstructured data for the purpose of making better business decisions.

Data integration tools: These tools combine data from disparate sources so that they can be viewed or analyzed from a single application. They sometimes include the capability to unify structured and unstructured data.

Document management systems: Also called enterprise content management systems, a DMS can track, store and share unstructured data that is saved in the form of document files.

Information management solutions: This type of software tracks structured and unstructured enterprise data throughout its life cycle.

Search and indexing tools: These tools retrieve information from unstructured data files such as documents, Web pages and photos.

Hope this post gives you very basic idea of data and their type. Your ideas and feedback are always welcome.

Creating autogenerated sequence in SQL

Hi Guys,

Sometimes we may have to create a auto generated sequenced during development. We can do this in application language by creating a function that will check current value fro table in database and then will generate the next sequence as well as directly in database. Continue reading