Sunday, July 28, 2013

Full Text Search

Full Text Search


SUMMARY
SQL Server provides a way using “Full Text Search” to search in a more sophisticated way ahead then normal exact character search. You can search by meaning (run, ran, running), by any order (Nisarg Kinariwala, Kinariwala Nisarg). Also you can search in multiple columns in a single shot.
INFO
- Only 1 Full Text Index is allowed per table/indexed view
- Contains column which have char/varhcar/varbinary text
- Table should have at least one Unique key or Primary Key
- You can select language for each column participating in Index
- Change Tracking Option has “Auto” and “Manual” options. When it is “Manual”, we have to populate the data periodically. “Auto” will take care as and when data changes in a column.
- Create Full Text Catalog à Create Full Text Index à Populate Index
HOW TO?








USAGE
1. SELECT * from TableName WHERE FREETEXT(ColumnName, 'RUN')
This will identify all the rows where ColumnName contains “run”/”ran”/”running” etc.
2. SELECT * from TableName WHERE FREETEXT(*, 'RUN')
This will identify all the rows where all columns (participating in index) contains “run”/”ran”/”running” etc.
3. SELECT * from TableName WHERE CONTAINS(ColumnName, 'nisarg AND kinariwala')
This will identify all the rows where columnName contains “nisarg” and “kinariwala” anywhere in the text in any order
4. SELECT * from TableName WHERE CONTAINS(ColumnName, 'nisarg OR kinariwala')
This will identify all the rows where columnName contains either “nisarg” or “kinariwala” or both anywhere in the text in any order

Rowcount for all tables in SQL Server Database

Introduction

In this article we will take a look at different options that are available to get rowcount for all the tables in a SQL Server Database. Options mentioned in this article will have least impact when the queries are executed as these queries leverage system tables to fetch the rowcount data. One should always avoid using the costliest options which is SELECT COUNT(*) to fetch the rowcount data.
Let us take a look at each of the below mentioned methods to get the total number of rows in each of the SQL Server Tables in a database.
1. Using sys.objects and sys.partitions Catalog views available in SQL Server 2005 and above.
2. Using sys.dm_db_partition_stats DMV which is available in SQL Server 2005 and above.
3. Using sysobjects and sysindexes system view available in SQL Server 2000.
4. Quickly Get Row Count of all Table in SQL Server Database Using In-built SQL Server Reports.
5. Using SP_SPACEUSED System Stored Procedure.
6. Quickly Get Record Count of a Single Table using SP_SPACEUSED System Stored Procedure.

Using sys.objects and sys.partitions Catalog Views

Execute the below mentioned TSQL code which leverages sys.objects and sys.partitions catalog views to List all Tables with Rowcount in SQL Server Database.
/* List all Tables with Rowcount in SQL Server Database Using sys.objects and sys.partitions catalog views */
Use AdventureWorks2008
GO
SELECT
        SCHEMA_NAME(SOS.SCHEMA_ID) + '.' + (SOS.NAME) AS [Table Name]
      , (SPS.Rows) AS [Row Count]
      , [Heap / Clustered Table] = CASE SPS.INDEX_ID WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END

FROM

      sys.objects AS SOS
      INNER JOIN sys.partitions AS SPS
            ON SOS.object_id = SPS.object_id

WHERE

      SOS.type = 'U'
      AND SPS.index_id < 2

ORDER
 BY [Table Name]
GO
 


Using sys.dm_db_partition_stats Dynamic Management View

Execute the below mentioned TSQL code which leverages sys.dm_db_partition_stats Dynamic Management View (DMV) to get record count for all tables in a database.
/* List all Tables with Rowcount in SQL Server Database Using sys.objects and sys.partitions catalog views */
Use AdventureWorks2008
GO


SELECT

        SCHEMA_NAME(SOS.SCHEMA_ID) + '.' + (SOS.NAME) AS [Table Name]
      , (SPS.Rows) AS [Row Count]
      , [Heap / Clustered Table] = CASE SPS.INDEX_ID WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END
FROM

      sys.objects AS SOS
      INNER JOIN sys.partitions AS SPS
            ON SOS.object_id = SPS.object_id
WHERE

      SOS.type = 'U'
      AND SPS.index_id < 2
ORDER
 BY [Table Name]
GO

Using sysobjects and sysindexes system views

Execute the below mentioned TSQL code which leverages sysobjects and sysindexes system views to retrieve the Row Count for all Tables in a Database. The below script works on SQL Server 2000.
/* Retrieve Row Count for all Tables in a Database - Backward Compatibility Script for SQL Server 2000 */
Use AdventureWorks2008
GO
SELECT
      SOS.Name
    , SIS.Rows
    , [Fill Factor] = CASE SIS.OrigFillFactor WHEN 0 THEN '100' ELSE OrigFillFactor END
    , [Heap / Clustered Table] = CASE SIS.indid WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END

FROM

    sysobjects SOS
                        INNER JOIN sysindexes
                                    SIS ON SOS.id = SIS.id

WHERE

    type = 'U' AND SIS.IndId < 2

ORDER
 BY SOS.Name
GO
    

Rowcount for all tables in SQL Server Database

By: Editor 
Feb 2, 2010

Page: 2/2 

Quickly Get Row Count of all Table in SQL Server Database Using In built SQL Server Reports

You can Quickly Get Row Count of all Table in SQL Server Database Using In built SQL Server Reports.
1. Click View -> Object Explorer Details or Press F7 to open up Object Explorer Details.
2. Expand Database Node -> Expand Database -> Click Tables on the left side panel.
3. Right click any column in the right side panel and then choose Row Count as shown in the below snippet to display row count for all the tables within the choosen SQL Server Database.
Row Count for Tables in SQL Server Database 

Using SP_SPACEUSED System Stored Procedure

Execute the below mentioned TSQL code which leverages SP_SPACEUSED System Stored Procedure to get rowcount of all tables in SQL Server Database.
/* Get Rowcount of all tables in a SQL Server Database */ 
Use AdventureWorks2008
GO 
IF OBJECT_ID (N'dbo.RowCounts', N'U') IS NOT NULL 
    DROP TABLE dbo.RowCounts;

GO 
CREATE TABLE RowCounts 
(
 [TableName]            VARCHAR(150), [RowCount]               INT
,
 [Reserved]                 NVARCHAR(50)
,
 [Data]                        NVARCHAR(50)
,
 [Index_Size]               NVARCHAR(50)
,
 [UnUsed]                   NVARCHAR(50))
GO 
INSERT INTO RowCounts([TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed])
EXEC
 sp_MSforeachtable 'sp_spaceused "?"' 
GO
SELECT [TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed]
FROM
 RowCounts
ORDER
 BY [TableName]
GO
 



Quickly Get Total number of records in a Table using sp_spaceused system stored procedure

In case if you would like to know the total number of rows available in a table then execute the below mentioned TSQL code as this has least impact on the SQL Server Performance.
/* Total Row count and Space Used by a tables in SQL Server Database */
Use AdventureWorks2008
GO
 
sp_spaceused [Person.Address]   
GO


Conclusion

In this article you have seen how one can utilize in built system catalogs, dynamic management views, system stored procedures to know the total number of rows available in a table without impacting the overall SQL Server Performance. It is advisable not to use SELECT COUNT(*) FROM TABLENAME query as it will impact the overall performance of SQL Server especially when you are queries aganist a very large table.


Read more: http://www.mytechmantra.com/LearnSQLServer/RowCount_of_all_Tables_P2.html#ixzz3h4tp3gy5
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook


Read more: http://www.mytechmantra.com/LearnSQLServer/RowCount_of_all_Tables_P1.html#ixzz3h4tf045x
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook




Row Count of All Tables in a Database and size of tables

1. select '['+SCHEMA_NAME(t.[SCHEMA_ID])+'].['+t.NAME+']' AS [fulltable_name],
SCHEMA_NAME(t.[SCHEMA_ID]) as [schema_name],t.NAME as [table_name] ,i.rows
from sys.tables t
INNER JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2)order by t.NAME

2.
DECLARE @TABLESSIZE TABLE (TableId INT identity(1,1),TableName varchar(500),RowCounts int,Reserved varchar(50),Data varchar(50),index_size varchar(50),unused varchar(50))
DECLARE @TABLELIST TABLE (TableId INT identity(1,1),TableName varchar(500))
DECLARE @TABLENAME VARCHAR(500)
DECLARE @FLGTAB INT
DECLARE @CNTTAB INT
 INSERT INTO @TABLELIST select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_NAME like 'TOP%'
SET @FLGTAB=1
SELECT @CNTTAB=COUNT(1) FROM @TABLELIST
 WHILE(@FLGTAB<=@CNTTAB)
BEGIN
SELECT @TABLENAME=TableName from @TABLELIST WHERE TableId=@FLGTAB
insert into @TABLESSIZE EXEC sp_spaceused @TABLENAME
SET @FLGTAB=@FLGTAB+1
END
SELECT * FROM @TABLESSIZE

SQL Server Naming Conventions and Standards


In programming, we have many naming conventions like camelCase, PascalCase, under_scores etc. But each and every organization has its own naming conventions. In this article, I would like to share some common and useful naming conventions and standards that you should use while programming with SQL Server.

Table


Tables are used to store data in the database. The naming conventions for a table may have a "tbl" prefix, followed by the table name. Moreover, TableName should be plural. The syntax should be "tbl<TableName>".

Examples are below:


1.  tblEmployees
2.  tblOrders
3.  tblProducts 

Primary Key Constraint


Primary key is a field or a set of fields in the database table that uniquely identify records in the database table. A table can have only one primary key. The naming conventions for a primary key constraints should have a "PK_" prefix, followed by the table name. The syntax should be "PK_<TableName>".

Examples are below:


1.  PK_Employees
2.  PK_Orders
3.  PK_Products 

Foreign Key Constraint


Foreign key is a field in the database table that is primary key in other table. The naming conventions for a foreign key constraint should have a "FK_" prefix, followed by the target table name, followed by the source table name. The syntax should be "FK_<TargetTable>_<SourceTable>".

Examples are below:


1.  FK_Orders_Employees
2.  FK_Items_Products 

Unique Key Constraint


Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value. The naming conventions for a unique key constraints should have a "UQ_" prefix, followed by the table name, followed by the column name. The syntax for a unique constraint should be "UQ_<TableName>_<ColumnName(s)>".

Examples are below:


1.  UQ_Employees_EmailID
2.  UQ_Items_Code 

Default Constraint


Default Constraints insert a default value into the column when no value is provided for that column. The syntax for a unique constraint should be "DF_<TableName>_<ColumnName>".

Examples are below:


1.   DF_Employees_IsActive
2.  DF_Employees_CreateDate

Check Constraint


Check Constraints defines a business rule on a column in the database table that each row of the table must follow this rule. The syntax for a unique constraint should be "CHK_<TableName>_<ColumnName>".

Examples are below:


1.   CHK_Employees_Salary
2.  CHK_Employees_DOB

User Defined Stored Procedures


Stored Procedures are executed on the server side and perform a set of actions, before returning the results to the client side. This allows a set of actions to be executed with minimum time and also reduce the network traffic. While creating user defined stored procedures we should avoid the prefix "sp_" with the name of procedure. Since "sp_" prefix is already fixed for system defined stored procedures. The naming conventions for user defined stored procedure may have a "usp_" prefix, followed by the action and objects of the procedure. The syntax for a user defined stored procedure should be "usp_<Action>_<Object>".

Examples are below:


1.  usp_Insert_Employees
2.  usp_View_EmployeeOrders 

User Defined Functions


Functions are a set of SQL statements that accepts only input parameters, perform actions and return the result.Function can return only single value or a table. The naming conventions for user defined functions may have a "fn_" prefix, followed by it's action. The syntax should be "fn_<Action>".

Examples are below:


1.  fn_CalulateTax
2.  fn_CalculateAge 

Views


Views are like a virtual table that can be made over one or more database tables. Generally we put those columns in view that we need to retrieve/query again and again. The naming conventions for a view should have a "vw_" prefix, followed by the namespace, results. The syntax should be "vw_<Result>".

Examples are below:


1.  vw_EmpOrderDetails
2.  vw_SalesProductDetails 

Triggers


Triggers are database object. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. The naming conventions for a trigger should have a "trg_" prefix, followed by the action, and the table name. The syntax should be "trg_<Action>_<TableName>".

Examples are below:


1.  trg_Ins_Employee
2.  trg_Upd_Employee
3.  trg_Del_Employee 

Indexes


Indexes are database objects that help the SQL Server query engine to find the desired data. Indexes may attached to tables or views. The naming conventions for an index should have an "IX_" prefix, followed by the table name and columns. The syntax should be "IX_<TableName>_<Column(s)>”.

Examples are below:


1.  IX_Employee_Name
2.  IX_Employee_NameMobileNo 

What do you think?

I hope you will enjoy these tips while programming with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

TRIGGERS, TRIGGERS PERFORMANCE AND RULES FOR CREATING TRIGGERS

TRIGGERS, TRIGGERS PERFORMANCE AND RULES FOR CREATING TRIGGERS IN T-SQL

Triggers:

A Trigger is a special type of stored procedure that’s automatically invoked when the user try to modify data that it’s designed to protect. Triggers help to secure data’s integrity  by preventing unauthorized or inconsistent changes from being made.

Triggers Performance:

From the performance point-of-view, triggers have a relatively low amount of overhead. Most of the time involved in running a trigger is used up by referencing other tables. The referencing can be fast if the other tables are in memory or a bit slower if they read from disk.

Rules for creating Triggers:

i)                    Triggers can’t be created on temporary tables. They can, however, reference views and temporary tables.
ii)                  Triggers can’t return result sets. Using the IF EXISTS clause as a part of a SELECT Statement in trigger code is a common practice.
iii)                Triggers should be used to maintain data integrity, maintain referential integrity, and encapsulate business rules.
iv)                Triggers can be encrypted in the syscomments table if you specify the WITH ENCRYPTION option.
v)                  WRITETEXT statement don’t activate triggers. They are used to modify text or image data, and it’s a nonlogged transaction.
vi)                The following SQL Statement can’t be used in a trigger:
Ø  All CREATE Statements
Ø  All DROP Statements
Ø  ALTER TABLE and ALTER DATABASE
Ø  TRUNCATE TABLE
Ø  RECONFIGURE
Ø  LOAD DATABASE or TRANSACTION
Ø  UPDATE  STATISTICS
Ø  SELECT INTO
Ø  DISK STATEMENT

New Features in Microsoft SQL Server

New features of SQL Server 2005/2008

2005

  • Exception Handling using TRY-CATCH 
  • CTE (Common Table Expression)
  • PIVOT
  • CLR
  • New functions (ROW_NUMBER, RANK, DENSE_RANK )
  • New operands (INTERSECT, EXCEPT)
  • New clause (OUTPUT, TABLESTAMP)
  • New DataType (XML)
  • Data Tuning Advisor

2008





Introduction
At TechED 2009, Microsoft has announced the launch of SQL Server 2008 R2 which was also known to the SQL Server Community by its code name Kilimanjaro. Microsoft will release SQL Server 2008 R2, first Community Technical Preview (CTP) during the second half of 2009. SQL Server 2008 R2 provides lot many new features and capabilities for Business Intelligence users which can be leveraged by many organizations around the world. To know more about how to install SQL Server 2008 R2, I would recommend you to read the article titled How to Install SQL Server 2008 R2 on a Windows Server 2008 R2.
New Features in Microsoft SQL Server 2012

Overview of New Features in Microsoft SQL Server 2008 R2

Microsoft SQL Server 2008 R2 introduces lot many new features for Business Intelligence users, Developers and Database Administrators. This article gives you an overview of some of the new features in SQL Server 2008 R2.

Capitalize on Hardware Innovation

There was always a demand from customers to increase the number of logical processors supported in SQL Server. In SQL Server 2008 R2 the number of logical processors supported has been increased from 64 to 256 and it will take advantage of the multi-core technology. This will help improve performance and scalability for very large data warehousing applications.

Optimize Hardware Resources

This is a great new feature for database administrators as it will provide a real time insight into Server Utilization, Policy Violations etc. This feature will help organizations to strictly apply organization wide policies across servers thereby helping them maintain a healthy system.

Manage Efficiently at Scale

This feature will help database administrator to gain insight into growing applications and databases thereby helping them to ensure better management of database servers.

Enhance Collaboration Across Development and IT

Database Application development will be more closely integrated with Visual Studio 2010 which will help to ensure higher quality during the application development along with easier deployments and better handling of changes over time.

A Master Data Service (MDS) is a kind of data service that is responsible for: managing, in a single place, the uniqueness, integrity, quality, and interrelationships between the data that matters the most. This will improve the quality of your data because of centralized approach of defining; deploying and managing master data thereby ensure reporting consistency across systems.

Build Robust Analytical Applications

Using Microsoft Office Excel 2010 you can build robust analytical applications which will allow in-memory, column oriented processing engine to allow users to interactively explore and perform complex calculations on millions of data at lightening speeds. Using Microsoft Excel 2010 you can easily integrate data from multiple sources such as corporate databases, spreadsheets and external data sources.

Share and Collaborate with Confidence

Using Microsoft Office SharePoint 2010 users can easily publish reports to SharePoint sites thereby making it available across the organizations. The other advantage is that the reports can be refreshed automatically and you can also maintain version controlling and tracking using SharePoint. Once you start using Microsoft Office SharePoint the SharePoint Administrators will have the ability to set server policies and monitor them more effectively thereby securing the business critical information.

Support for Geospatial Visualization

Microsoft SQL Server 2008 R2 will provide support for geospatial visualization including mapping, routing, and custom shapes. It will also support SQL Spatial and will also provide integration with Microsoft Virtual Earth tiles.
Now that you are familiar with the new features in SQL Server 2008 R2, I would recommend you to read the article titled How to Install SQL Server 2008 R2 on a Windows Server 2008 R2.


Read more: http://www.mytechmantra.com/LearnSQLServer/Microsoft_SQL_Server_2008_R2.html#ixzz3h4sYJ5C0
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook



PARSE() Conversion Function

Introduction 

We all know that to convert data type (form one data type to another), we must use the CONVERT or CAST data conversion function. Microsoft SQL Server 2012 gives us some new data type conversion function that we have to understand.  
Here in this article we are trying to discuss about PARSE()  data conversion function came from Microsoft SQL Sever 2012 onwards. Hope all of you find it informative.

PARSE()
PARSE() function convert any string values to Numeric or Date/Time format. If PARSE() cannot convert, it will returns a zero. PARSE() will result to an error. PARSE() function relies on Common Language Runtime (CLR) to convert the string value. If there is no CLR installed on the server, PARSE() function will return an error. 

Syntax: 

PARSE ( String_Value AS data_type   [ USING culture ] )

Parameter Details:

String_Value 
String expression which needs to be parsed.
data_type
Output data type, e.g. INT, NUMERIC, DATETIME etc.
Culture
Optional string that identifies the culture in which String_Value is              formatted. If it is not specified, then it takes the language of the current session

Example

-- PARSE String to INT
SELECT PARSE('1000' AS INT) AS 'String to INT'
GO

String to INT
-------------
1000

-- PARSE String to Numeric
SELECT PARSE('1000.06' AS NUMERIC(8,2)) AS 'String to Numeric'
GO

String to Numeric
---------------------------------------
1000.06

-- PARSE String to DateTime
SELECT PARSE('05-18-2013' as DATETIME) AS 'String to DATETIME'
GO

String to DATETIME
-----------------------
2013-05-18 00:00:00.000


-- PARSE String to DateTime
SELECT PARSE('2013/05/18' as DATETIME) AS 'String to DATETIME'
GO

String to DATETIME
-----------------------
2013-05-18 00:00:00.000


-- PARSE string value in the India date format to DateTime
SELECT PARSE('18-05-2013' as DATETIME using 'en-in') AS 'Indian DateTime Format'
GO

Indian DateTime Format
-----------------------
2013-05-18 00:00:00.000

-- PARSE string value is in the US currency format to Money
SELECT PARSE('$2500' as MONEY using 'en-US') AS 'US Currency Format to MONEY'
GO

US Currency Format to MONEY
---------------------------
2500.00


-- Best Conversion by PARSE
SELECT PARSE('08-JUNE-2013' AS DATETIME)
GO

SELECT PARSE('08-JUN-2013' AS DATETIME)
GO

SELECT PARSE('2013JUNE08' AS DATETIME)
GO

SELECT PARSE('08/JUN/2013' AS DATETIME)
GO

Output is
-----------------------
2013-06-08 00:00:00.000

Difference Between PARSE() and CONVERT()
Here we are providing an example where PARSE() is converting the Value to Date/Time but CONVERT() fails to do.

SELECT PARSE('Saturday, 08 June 2013' AS DATETIME) AS 'PARSE Result'
GO

PARSE Result
-----------------------
2013-06-08 00:00:00.000

SELECT CONVERT(DATETIME, 'Saturday, 08 June 2013') AS 'CONVERT Result'
GO

CONVERT Result
-----------------------
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

In next several article we are trying to discuss about others datra Conversion Function of Microsoft SQL Server 2012.

Hope you like it.