Search results matching tag 'SQL'

One more example - No order guaranteed if order by is not specified

chhapia_chintak — Fri, 13 Apr 2012 16:43:00 GMT

As we know that, No order guaranteed if order by is notf explcitly specified. Below is one more example of this.

Below is the code to pupolate dummy table.

If OBJECT_ID('dbo.testTable') is not null 
	drop table dbo.testTable
Go
Create table dbo.testTable
( c1 int not null,
c2 int not null,
c3 int not null,
c4 int not null)
go
Alter table dbo.testTable add constraint pk_testTable primary key (c1)
go
declare @i int
select @i = 1

set nocount on;
begin tran

while (@i < 1001)
begin
	insert into testTable (c1,c2,c3,c4)
	select @i, @i/2, 10000-(@i/4), @i%2

	select @i = @i + 1
end
commit
go

Now, when we run below query, have a look at rows returned

select * from testTable

Below is the truncated result of this query.

Now, if we create covering below index and again look at the results, rows are returned in different orders.

Create index idx_testTable_c3 on testTable(c3) include (c1,c2,c4) 
go 
select * from testTable

Below is the result returned after creating index

Actually, SQL choose the plan which is the more efficient, when we run below queries, we are able to find the reason.

set statistics io on 
go 
select * from testTable with(index=0) 
go 
select * from testTable go

Even we can look into why there are less pages when nonclustered index is used, with help of below query

select * from sys.dm_db_index_physical_stats(db_id(),object_id('testTable'),NULL,NULL,'DETAILED')

SQL Server - Changing Rows to Columns Using PIVOT

pprajapati — Sun, 19 Feb 2012 04:05:00 GMT

During working with one logic, i got chance to work with PIVOT operation. Sometime we need do require rowdata as column in our custom logic, then we can use some temp table and then populate agreegate data in temp table. But With PIVOT we can do it very easily. Let me prepare small example and explain as how how can we use PIVOT and get row data as column.

Before go ahead to run the script of Pivot, we will create database and table objects.

CREATE DATABASE DEMO
GO

USE DEMO
GO

-- Creating table for demo
IF (object_id('TblPivot','U') > 0)
DROP TABLE TblPivot

CREATE TABLE TblPivot
(
ItemCode int,
ItemName varchar(100),
ItemColour varchar(50)
)

GO

-- Inerting some sample records

INSERT INTO TblPivot
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 3,'Nokia Mobile','Green'
UNION ALL
SELECT 4,'Motorola Mobile','Red'
UNION ALL
SELECT 5,'Samsung Mobile','Green'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'

GO

Now we will check the original table data and aggregated data using Pivot.
So we will run both script for the same.

-- Getting table data
SELECT 
ItemCode, 
ItemName, 
ItemColour
from TblPivot
GO

-- Getting agreegated data using Pivot and converted rows to column
SELECT
*
 FROM
 (
    SELECT 
 ItemCode, 
 ItemName, 
 ItemColour
    FROM TblPivot
 ) AS P
PIVOT
(
  Count(ItemName) FOR ItemColour IN (Red, Blue, Green)
) AS pv

GO

You can review here and see how the PIVOT is working. Let me share your experience with PIVOT operation.

SSIS - Delete files older than retention period from folder with parameter using Script Task in SQL Server

pprajapati — Fri, 10 Feb 2012 14:03:00 GMT

After using some of the ways to delete the files from the particular folder like Delete file using Script Task and File System Task in SSIS. We have seen such methods and used logic to get the files from the loop container and process to delete them and same thing applied for the scripts as well. we also looked for the files deletion which are older than some Retention period with Script Task also.

In earlier posts we have not used any parameters in the scripts and directly applied folder path and retention period values there. But here i would like to use parameters for the folder path and retention period and pass through the scripts and delete them as per condition.

1. Let check the files from the target folder.

2. Create parameters and set the values. Here it is going to be delete the files which are older then 3 days from the E:\ImagesBackup folder in this case.

3. Drag and drop File System Task

4. Put the parameters as ReadOnlyVariables

5. Apply attached script in editor which have additional logic with condition to check the file lat modified date and check if older than specified retention period or not. Here you can see the parameters used in the script.

Please note here we need to import system.IO namespace.

6. Turn on final step and run package. Files older than specified retention period get deleted.

You can use the script below for the same as mentioned in above image,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = Convert.ToInt32(Dts.Variables["User::Period"].Value.ToString());
string directoryPath = Dts.Variables["User::BackupFolder"].Value.ToString();
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.*");

foreach (string currFile in oldFiles)
  {
         FileInfo currFileInfo = new FileInfo(currFile);
              
            if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

SSIS - Delete files older than retention period from specified folder using Script Task in SQL Server

pprajapati — Sat, 04 Feb 2012 13:18:00 GMT

Earlier we have seen for the Delete file using Script Task and files deletion using File System Task in SSIS. We have directly wrote the logic to get the files and delete from specified folder in the script for the Script Task. For File System Task we have Used Foreach Loop Container and variables to hold file names which are passed from earlier stage one by one and then finally used with File System Task to delete it.

Here we have same but not to delete all the files inside passed folder but get deleted files which are older than specified days. This is nothing but minor change in script where we pass the folder path and retention period. Lets start the steps as following,

1. Let check the files from the target folder.

2. Drag and drop Script Task.

3. Open script editor from the properties.

4. Apply attached script in editor which have additional logic with condition to check the file lat modified date and check if older than specified retention period or not.

Please note here we need to import system.IO namespace.

5. Turn on final step and run package. Files older than specified retention period get deleted.

You can also find script code here,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = 3;
string directoryPath = @"E:\ImagesBackup";
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.png");
     foreach (string currFile in oldFiles)
            {
                FileInfo currFileInfo = new FileInfo(currFile);
                if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

Error encountered in SQL Server - Alter failed for Login sa. Cannot set a credential for principal 'sa'.

pprajapati — Sat, 28 Jan 2012 02:19:00 GMT

Recently when i worked with SQL Server security, i encountered with one error while trying to modify 'SA' account properties. The exception details looks following,

Alter failed for Login sa. Cannot set a credential for principal 'sa'.

Also you can see the image below for the same.

The fix for the error is the option "Map to Credential" is checked in the "General" tab of the Login Properties Page as mentioned below,

Generate test data using DBGen

Geniiius — Tue, 24 Jan 2012 06:00:00 GMT

Over the last couple of months I have on several occasions found myself in need of test data for demos, blogposts etc. You all know AdventureWorks, which will work for a lot of demoing stuff. But the amount of data is limited, and if you are demoing stuff about performance, AdventureWorks just isn’t that good.

I started looking around for tools, and thought I would have a look at TPC. My goal was not to be able to compare performance and transaction loads, but simply to generate data sets a bit bigger than AdventureWorks. So I found the TPC-H DBGen. In this blog post I will show you step by step how to use that tool to generate a database with a set of tables with data volumes large enough to be able to demonstrate performance tuning stuff.

1. Download DBGen

Download DBGen from http://www.tpc.org/tpch/spec/tpch_2_14_3.zip and extract it. In this guide I extracted the zip file to C:\tpch_2_14_3\

This is the project that we need to build to get the executable.

2. Build the solution

Now open C:\tpch_2_14_3\dbgen\tpch.sln in Visual Studio. Depending on your Visual Studio version, you might be faced with a conversion wizard. Just click Finish to execute the conversion. All you need to do, is to build the entire solution. I had some errors because of some locked files, so I had to manually delete all files from the C:\tpch_2_14_3\dbgen\Debug folder before I could compile the solution. The result is the file C:\tpch_2_14_3\dbgen\Debug\dbgen.exe.

3. Generate data using dbgen.exe

Now we need to execution dbgen.exe. If we execute the command with –h we get some help:

If we simply run dbgen.exe, it default to generating 1 GB of data, divided into 8 different tables (customers, nation, lineitem, orders, parts, partsupp, region, supplier). The –s parameter specifies a scale factor, so –s 10 gives us 10GB, and –s 100 generates 100GB of data. Let’s just try the default:

Whoops, we got an error! Why this is, I’m not sure, but I found the solution to be simple: Copy the file dbgen.exe one level up, so it is located in the C:\tpch_2_14_3\dbgen folder, and try again:

The –v gives verbose output. Now it generates files for each table. Depending on the speed of your system, this may take a few minutes.

The resulting files will be located in the same directory as dbgen.exe. The list of generated files is:

4. Create database and tables

The next we need to do, is create an empty database, and create the tables.

CREATE DATABASE DemoData
GO

The schema we need, is available in the file C:\tpch_2_14_3\dbgen\dss.ddl and it looks like this:

-- Sccsid:     @(#)dss.ddl    2.1.8.1
CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  INTEGER NOT NULL,
                            N_COMMENT    VARCHAR(152));

CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL,
                            R_NAME       CHAR(25) NOT NULL,
                            R_COMMENT    VARCHAR(152));

CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR(25) NOT NULL,
                          P_SIZE        INTEGER NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                          P_COMMENT     VARCHAR(23) NOT NULL );

CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
                             S_NAME        CHAR(25) NOT NULL,
                             S_ADDRESS     VARCHAR(40) NOT NULL,
                             S_NATIONKEY   INTEGER NOT NULL,
                             S_PHONE       CHAR(15) NOT NULL,
                             S_ACCTBAL     DECIMAL(15,2) NOT NULL,
                             S_COMMENT     VARCHAR(101) NOT NULL);

CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
                             PS_SUPPKEY     INTEGER NOT NULL,
                             PS_AVAILQTY    INTEGER NOT NULL,
                             PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
                             PS_COMMENT     VARCHAR(199) NOT NULL );

CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
                             C_NAME        VARCHAR(25) NOT NULL,
                             C_ADDRESS     VARCHAR(40) NOT NULL,
                             C_NATIONKEY   INTEGER NOT NULL,
                             C_PHONE       CHAR(15) NOT NULL,
                             C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
                             C_MKTSEGMENT  CHAR(10) NOT NULL,
                             C_COMMENT     VARCHAR(117) NOT NULL);

CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
                           O_CUSTKEY        INTEGER NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,
                           O_CLERK          CHAR(15) NOT NULL,
                           O_SHIPPRIORITY   INTEGER NOT NULL,
                           O_COMMENT        VARCHAR(79) NOT NULL);

CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DECIMAL(15,2) NOT NULL,
                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                             L_TAX         DECIMAL(15,2) NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL);

If you want referential integrity on your tables, you can find help in this file: C:\tpch_2_14_3\dbgen\dss.ri

5. Load data into tables

Now we simply need to execute the following commands to import the data:

USE DemoData
GO
BULK INSERT part FROM 'C:\tpch_2_14_3\dbgen\part.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT customer FROM 'C:\tpch_2_14_3\dbgen\customer.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT orders FROM 'C:\tpch_2_14_3\dbgen\orders.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT partsupp FROM 'C:\tpch_2_14_3\dbgen\partsupp.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT supplier FROM 'c:\tpch_2_14_3\dbgen\supplier.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT lineitem FROM 'C:\tpch_2_14_3\dbgen\lineitem.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT nation FROM 'C:\tpch_2_14_3\dbgen\nation.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT region FROM 'C:\tpch_2_14_3\dbgen\region.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')

And that’s it

Using this guide we can easily create 1, 10 or hundreds of GB of data, giving us the base we need to truly demonstrate the effect of performance tuning. This demo database will be used in later blog posts about query analyzing and performance optimizing. At some point I will also look into using the TPC tools for testing the transactional capacity of different systems, to be able to see the effect of configuration changes, hardware upgrades and other things we might wanna change to improve a system.

Republished with author's permission. See the original post here.

TOP WITH TIES

Geniiius — Tue, 17 Jan 2012 06:00:00 GMT

I have been working with SQL Server for quite a few years now, and it still happens quite often that I discover new cool things to do in t-sql. My latest discovery is not a new feature at all, because it was introduced back in SQL Server 2005 – but for some reason I haven’t come across it before. So I thought I might share it with you

Consider the following example:

CREATE TABLE AggregatedClientRequests
(
    Id INT IDENTITY PRIMARY KEY,
    ClientIp VARCHAR(200) NOT NULL,
    NumOfRequests INT NOT NULL
)
GO

INSERT INTO AggregatedClientRequests (ClientIp, NumOfRequests)
VALUES
    ('1.1.1.1', 5100),
    ('2.2.2.2', 10000),
    ('3.3.3.3', 200),
    ('3.3.3.3', 44000),
    ('4.4.4.4', 2200),
    ('5.5.5.5', 10000),
    ('6.6.6.6', 31000),
    ('7.7.7.7', 100),
    ('8.8.8.8', 300),
    ('9.9.9.9', 10000)
GO

This table holds an aggegated value of the number of requests a given ClientIp has made on a system. Now you want to identify the top three ClientIps that have generated the most requests. That easy, right? Without thinking too much about it, I would have written something like this:

SELECT TOP (3) *
FROM AggregatedClientRequests
ORDER BY NumOfRequests DESC

That looks right… or does it? What about the ClientIps ‘5.5.5.5’ and ‘9.9.9.9’ ? They also generated 10.000 requests, so they are actually equally to the ‘2.2.2.2’ ip. So which one of the three with 10.000 request should I return?. If I want those returned as well (think of a use case where the top three are sales persons that should have a bonus), how could I do that? That’s actually pretty simple by using the TOP WITH TIES clause like this:

SELECT TOP (3) WITH TIES *
FROM AggregatedClientRequests
ORDER BY NumOfRequests DESC

Now it get this back:

Notice that I actually get 5 rows returned, even though my TOP clause stated that I only wanted three! The WITH TIES clause looks at the last row of the TOP (3) rows, and add all other rows with identical values.

How simple is that?! I don’t even want to think about how the query would look to achieve the same thing without using the WITH TIES clause. Perhaps you will give it a go?

Republished with author's permission. See the original post here.

SQL Server - Custom Database Role

pprajapati — Fri, 13 Jan 2012 13:53:00 GMT

Recently while working with database security, I learned database roles as how the each role used. Apart from the server level roles if we need to require to assign access/rights to particular databases level then we need to go through database level roles.

Following are the fixed database level roles as per MSDN,
db_owner : Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin : Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin : Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator : Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin : Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter : Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader : Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter : Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader : Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

You can see the image for all above fixed database roles. Now we will see how can we use the roles and bind with users. Let's create a small demo with example.

Here i am creating required objects used for demo, So let's do that.

USE demo
GO

CREATE TABLE SampleTable1
(
Id int,
Name varchar(10)
)
GO

CREATE TABLE SampleTable2
(
Id int,
Name varchar(10)
)
GO

CREATE PROCEDURE SampleSP1
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable1
End
GO

CREATE PROCEDURE SampleSP2
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable2
End
GO

Now here i want assigned to the access to user as following,
1. User can see the data from SampleTable2 table.
2. User can perform select/insert/update operation on SampleTable1 table, not delete operation.
3. Can execute stored procedure SampleSP1.
4. Can not execute stored procedure SampleSP2.

Workaround 1:
If we assigned fixed database roles to User like db_datareader and db_datawriter for particular database, then user can perform all DML operation on all tables, views for the database. Even if we assigned EXECUTE permission to user then user can execute all the stored procedures.

After all these permission we need to deny permission from the user for some of the tables and stored procedures which are not required to be accessed.

Workaround 2:
Instead of doing above such stuffs, We will create a new custom database level roles and assigned requited access to user for the objects.

USE demo
GO

CREATE LOGIN [SupportUser] 
WITH PASSWORD=N'SupportUser', 
 DEFAULT_DATABASE=[master], 
 DEFAULT_LANGUAGE=[us_english], 
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

CREATE USER [SupportUser] FOR LOGIN [SupportUser]
GO

CREATE ROLE [SupportRole] 
Go

GRANT SELECT ON SampleTable2 TO [SupportRole];
GRANT SELECT,INSERT,UPDATE ON SampleTable1 to [SupportRole];
GRANT EXEC ON SampleSP1 to [SupportRole]
GO

EXEC sp_addrolemember N'SupportRole', N'SupportUser'
GO

Let's connect the SQL Server instance with this newly created user and see the access rights,

USE demo
GO

PRINT 'Inserting in SampleTable1'
GO
INSERT INTO SampleTable1
 (
 Id, 
 Name
 )
SELECT 
 1,
 'Sample1'
GO

PRINT 'Inserting in SampleTable2'
GO
INSERT INTO SampleTable2
 (
 Id, 
 Name
 )
SELECT 
 1,
 'Sample2'
GO

PRINT 'Deleting from SampleTable1'
GO
DELETE FROM SampleTable1
GO

PRINT 'Viewing from SampleTable1'
GO
SELECT * FROM SampleTable2
GO
SELECT * FROM SampleTable1
GO


PRINT 'Executing SampleSP11'
GO
EXEC SampleSP1
GO
PRINT 'Executing SampleSP2'
GO
EXEC SampleSP2
GO

You can see the below image to see the access of running user,

Main benefit of the custom database role is role can be assigned to multiple users. You can see below script where i have assigned same role to different user. So once role created it can be assigned to multiple users.

USE demo
GO

CREATE LOGIN [DBAUser] 
WITH PASSWORD=N'DBAUser', 
 DEFAULT_DATABASE=[master], 
 DEFAULT_LANGUAGE=[us_english], 
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

CREATE USER [DBAUser] FOR LOGIN [DBAUser]
GO

EXEC sp_addrolemember N'SupportRole', N'DBAUser'
GO

Hope you liked this post.

SQL Server 2012 FileTable – Part 2

Geniiius — Thu, 12 Jan 2012 06:00:00 GMT

In this weeks blog post we will have a look at how easy it is to combine FileTables and FullText Search. Last week we had a look at some of the basics of the new FileTable feature – if you missed out on last week blog post, you can read it here – FileTable Part 1.

To get the full effect of this powerful combination, it is highly recommended that you install “Microsoft Office 2010 Filter packs”. to check if you have that installed take a look at your installed programs and look after something like this:

if not, you can download and install it from here – Microsoft Filter Pack 2.0. By installing this feature Full Text Search will now have the ability to index the content of Microsoft Word and many other file types. To se a complete list of the file types supported in your setup, run the following query:

select * from sys.fulltext_document_types

After installing the Microsoft filter pack, you should have support for more than 150 document_types.

Ok, let’s get started with the proper demo stuff, I’ll be using the same table structure as I did in Part 1 of this series. I am not going to post the code once again, so you’ll have to go back to Part 1 and get it from there. I start out with an empty table and an empty FileTable folder. Before I do anything else I’ll create a text document with the following text in it: “AGF please buy back Morten Duncan Rasmussen” – I save the document and name it Duncan.txt.

Fulltext Search

First of all, we need a FullText catalog, I ‘ll create that like this:

CREATE FULLTEXT CATALOG MyFullTextCatalog AS DEFAULT
GO

Next step is to create a FullText Index on the FileTable table that we have, the interesting column is the file_stream column, this is where the content of the document is stored. As with any other FullText Index we need a Unique key index to build the index with, this is where a quick lookup in the sys.indexes is handy.

Another thing that you might notice is that I use language 1030, this is the code for Danish word breaker. Change tracking is set to auto and I’m using the system stop list. This is not a detailed FullText blog post, so I’ll not go into further details with that right now.

Here is the code to create the FullText Index:

CREATE FULLTEXT INDEX ON dbo.MyDocumentStore
(
    name LANGUAGE 1030,
    file_stream TYPE COLUMN file_type LANGUAGE 1030
)
KEY INDEX PK__MyDocume__5A5B77D54AAAEEEA
ON MyFullTextCatalog
WITH
    CHANGE_TRACKING AUTO,
    STOPLIST = SYSTEM
GO

Now everything is configured, and we are ready to write some FullText queries against the documents in our FileTable folder. Here is some examples:

-- My Surname should not be found in the text
SELECT
    stream_id, name, file_stream.GetFileNamespacePath(1,1) as FileLocation
FROM dbo.MyDocumentStore
WHERE CONTAINS(file_stream, 'Schmidt')

-- Geniiius APS or http://www.Geniiius.com/blog/?
SELECT
    stream_id, name, file_stream.GetFileNamespacePath(1,1) as FileLocation
FROM dbo.MyDocumentStore
WHERE CONTAINS(file_stream, '"Geniiius Aps" OR "http://www.Geniiius.com/blog/"')

-- The best team on the planet 
SELECT
    stream_id, name, file_stream.GetFileNamespacePath(1,1) as FileLocation
FROM dbo.MyDocumentStore
WHERE CONTAINS(file_stream, 'AGF')

No surprise that only the last query return some rows. Now let’s do some more testing, and because that Change Tracking is set to auto this should be very easy. I’ll create a Microsoft PowerPoint document, copy it to my FileTable folder and seconds after I should be able to query the content from SQL Server. Let’s try.

This is how my PowerPoint document looks:

Now let’s us try to rerun the three queries again, and wupaa all three of them return results. Why is the fist one returning rows you might ask? The first query return rows because of the meta data on the file, where my name in the auther.

Is this a cool feature or what Happy Searching.

Republished with author's permission. See the original post here.

SQL Server - Archive old database backup files using TSQL Script

pprajapati — Sat, 07 Jan 2012 13:37:00 GMT

I experienced into one issues for database backups were failed. And this was due to space issues on disk drive. This disk drive is spacific to allocated for the database backups only.

The space was eaten by this database backups and this drive contains so many old backups. I have manually deleted all files and continue this activity so many days.If you create a maintenance plans then it have option to delete old backups files. But i have the stored procedure for the database backups. So i do not have option to delete old and unused database backup files.

Finally, I have created a script to clean those old backups. I have created one stored procedure in which you need to pass there parameters, One is Backup type as want to delete full, differential or transaction log backups. Second is From days and third one is End day.

Please make sure XP_CMDSHELL is enabled in database instance as thisis require to enable it to delete database backup files to be deleted physically.
Here id query to enable it.

USE MASTER
GO

EXEC SP_CONFIGURE 'show advanced options',1
GO
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
GO
RECONFIGURE
GO

I have already told you as i have created script to delete the old DATABASEPROPERTY backups, please find below SP for the same.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteDBBackupOldFiles]
@BackupType char(1) = 'D', -- 'D'-Full, 'I'-Differential, 'L'-Log backup type
@StartDayFromToday int,
@EndDayFromToday int
AS
BEGIN
SET NOCOUNT ON

DECLARE @IsFileExists int
DECLARE @DeletedFile VARCHAR(500)
DECLARE @OldFiles VARCHAR(500)

DECLARE OldFiles CURSOR FAST_FORWARD  FOR
SELECT 
bmf.physical_device_name
from msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
WHERE DEVICE_TYPE = 2 
AND TYPE = @BackupType 
AND BACKUP_START_DATE < = GETDATE() - @StartDayFromToday
AND BACKUP_START_DATE > = GETDATE() - @EndDayFromToday
-- AND DATEDIFF(DAY,BACKUP_START_DATE,GETDATE()) > BETWEEN @StartDayFromToday and @EndDayFromToday 

OPEN OldFiles

FETCH NEXT FROM OldFiles INTO @OldFiles

WHILE @@FETCH_STATUS =0
BEGIN

EXEC XP_FILEEXIST @OldFiles,@IsFileExists OUTPUT
IF @IsFileExists=1
BEGIN

PRINT 'Deleted File : ' + @DeletedFile

SET @DeletedFile = 'DEL ' + @OldFiles

EXEC XP_CMDSHELL @DeletedFile

END
FETCH NEXT FROM OldFiles INTO @OldFiles
END
CLOSE OldFiles
DEALLOCATE OldFiles
END

Finally after creating a stored procedures , it is time to run and clean the old database backups. After running it will remove all the old database baclups as per specified parameters. Like we have passed 'D',3 and 10 with SP, So i will delete Full database backups whicl are older then 3 days ago and 10 days before created.

EXEC [DeleteDBBackupOldFiles] 
  @BackupType  = 'D',
  @StartDayFromToday = 3,
  @EndDayFromToday = 10

Which method you are using for old databsee backup maintenance? You can read my earlier posts for delete files using File System Task and Script Task in SSIS.