How to delete partitioned table data from a table without dropping the table?

Problem: How to delete partitioned table data from a table without dropping the table?

Solution:  TRUNCATE TABLE, SQL server 2016 and later

In some cases, where you want to clear out the existing data from the table to load the table fresh. You may want to delete it fast without logging the rows that have been deleted.

TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

Syntax:

TRUNCATE TABLE  

    [ { database_name .[ schema_name ] . | schema_name . } ] 

    table_name 

    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }  

    [ , ...n ] ) ) ] 

[ ; ] 

Limitations:

     1.       A table that has foreign keys
2.       A table that participates in an indexed view
3.       Replicated tables
 

Example:

In this example, I would like to show we can truncate specific partitions inside a partitioned table.

To know more about how to create a partitioned table please go through the following article. Partitioning a table

TRUNCATE TABLE dbo.Employees  WITH (PARTITIONS (2,3, 4)); 

GO

Results:

 As we can see in the below image the partitions 2,3,4 doesn't have rows.

Read More

How to create a linked server, what’s it used for?

Problem: How to create a linked server, what’s it used for?

Solution:  Create Linked Servers

Linked Server creates a connection between two SQL Server Instances which can be helpful to use cross server database queries.

A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

Usage scenarios:

      1.       It will be helpful to extract data or execute a procedure from one server to another server using four part naming convention
2.       In cases where you want to load data from one server to another server.

Syntax:

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]  

     [ , [ @provider= ] 'provider_name' ] 

     [ , [ @datasrc= ] 'data_source' ]  

     [ , [ @location= ] 'location' ]  

     [ , [ @provstr= ] 'provider_string' ]  

     [ , [ @catalog= ] 'catalog' ]   

Example:

In this example, I would like to show we can add, test and drop a linked server.

-- Add the linked servers

EXEC sp_addlinkedserver  

   @server='TROPPLTMSAMBU_LinkedServer',

   @srvproduct='',

   @provider='SQLNCLI',

   @datasrc='TRODVDTMSAMBU';-- target servers

-- Test the linked servers

EXEC sp_linkedservers 

-- query servers tables to get the linked servers

select * from sys.servers where is_linked=1

-- Drops the linked server

EXEC sp_dropserver 'TROPPLTMSAMBU_LinkedServer', 'droplogins'; 

-- query from the other server using four part naming convention

SELECT *FROM [TROPPLTMSAMBU_LinkedServer].[Test].[dbo].[TableA]

Read More

How to create, alter or remove the permission of a user inside a database or on a SQL server instance?

Problem: How to create, alter or remove the permission of a user inside a database or on a SQL server instance?

Solution:  ROLE

Adds or removes members to or from a database role, or changes the name of a user-defined database role. 

To know more about a database roles please go through this article. what are different type of Roles

Syntax:

ALTER ROLE  role_name 

{ 

       ADD MEMBER database_principal 

    |  DROP MEMBER database_principal 

    |  WITH NAME = new_name 

}  [;] 

Example:

In this example, I would like to show we can create a role and assign a role to user.

-- create a role

CREATE ROLE Test; 

As you can see from the below image the role test has been created in the database roles.

-- select the roles to see wether it has been created or not.

select * from sys.database_principals where type='R' and is_fixed_role=0

-- add member to the role

ALTER ROLE Test ADD MEMBER TestUser; 

--query to see the roles and members are added.

select u.name as Rolename,u1.name as membername from sys.database_role_members m

INNER JOIN sys.database_principals u

ON m.role_principal_id=u.principal_id

INNER JOIN sys.database_principals u1

ON m.member_principal_id=u1.principal_id

where u.name='Test'

Results:

Rolename	membername
Test	TestUser

Example 2: How to drop the member and alter the role.

-- drop member to the role

ALTER ROLE Test DROP MEMBER TestUser; 

--alter role name

ALTER ROLE Test with name =test1

Read More

How to import data file like (csv or txt or xlsx) into a SQL Server Table?

Problem: How to import data file like (csv or txt or xlsx) into a SQL Server Table?

Solution:  BULK INSERT

There are sometimes where you want to import a data file into a table, like product prices which comes from the business as excel or some other data access files. If you don’t have SSIS, Import export wizard.

Syntax:

BULK INSERT  

   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]  

      FROM 'data_file'  

     [ WITH  

    (  

   [ [ , ] BATCHSIZE = batch_size ]  

   [ [ , ] CHECK_CONSTRAINTS ]  

   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]  

   [ [ , ] DATAFILETYPE =  

      { 'char' | 'native'| 'widechar' | 'widenative' } ]  

   [ [ , ] DATASOURCE = 'data_source_name' ]

   [ [ , ] ERRORFILE = 'file_name' ]

   [ [ , ] ERRORFILE_DATASOURCE = 'data_source_name' ]  

   [ [ , ] FIRSTROW = first_row ]  

   [ [ , ] FIRE_TRIGGERS ]  

   [ [ , ] FORMATFILE_DATASOURCE = 'data_source_name' ]

   [ [ , ] KEEPIDENTITY ]  

   [ [ , ] KEEPNULLS ]  

   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]  

   [ [ , ] LASTROW = last_row ]  

   [ [ , ] MAXERRORS = max_errors ]  

   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]  

   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]  

   [ [ , ] ROWTERMINATOR = 'row_terminator' ]  

   [ [ , ] TABLOCK ]  

   -- input file format options

   [ [ , ] FORMAT = 'CSV' ]

   [ [ , ] FIELDQUOTE = 'quote_characters']

   [ [ , ] FORMATFILE = 'format_file_path' ]  

   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]  

   [ [ , ] ROWTERMINATOR = 'row_terminator' ]  

    )] 

Example:

In this example, I would like to show we can Insert data into the SQL Server Table from csv file where the data looks like below.

BULK

INSERT dbo.TestBULKInsert

FROM 'C:\Temp\Export Files\ArticlesPost2017-05-22.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

GO

--Check the content of the table.

SELECT *

FROM dbo.TestBULKInsert

GO

--Drop the table to clean up database.

DROP TABLE dbo.TestBULKInsert

GO

Results: 

The below result set shows the data after loading from csv file into table.

Read More

How to stop SQL Server Query processing after number of rows to be returned?

Problem: How to stop SQL Server Query processing after number of rows to be returned?

Solution:  ROWCOUNT

In some cases, where infinite searches occur we need to stop the query processing for several reasons. If we must break the query after several rows are processed to set a limit use ROW COUNT on Top of the query.

To set this option off so that all rows are returned, specify SET ROWCOUNT 0.

Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes triggers. The ROWCOUNT option does not affect dynamic cursors, but it does limit the rowset of keyset and insensitive cursors. This option should be used with caution.

SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

The setting of SET ROWCOUNT is set at execute or run time and not at parse time.

Example:

In this example, I would like to show we can limit the number of rows returned in the select statement.

SET ROWCOUNT 10

SELECT * from dbo.spt_values

SET ROWCOUNT 0

SELECT * from dbo.spt_values

Results:

Read More

What are package configurations and how are they used.?

Problem: What are package configurations and how are they used.?

Solution:  Package Configurations:

SSIS provides package configurations that you can use to update the values of properties at run time.

A configuration is a property/value pair that you add to a completed package. Typically, you create a package set properties on the package objects during package development, and then add the configuration to the package. When the package runs, it gets the new values of the property from the configuration. For example, by using a configuration, you can change the connection string of a connection manager, or update the value of a variable.

Package configurations provide the following benefits:

Configurations make it easier to move packages from a development environment to a production environment. For example, a configuration can update the path of a source file, or change the name of a database or server.

Configurations are useful when you deploy packages to many different servers. For example, a variable in the configuration for each deployed package can contain a different disk space value, and if the available disk space does not meet this value, the package does not run.

Configurations make packages more flexible. For example, a configuration can update the value of a variable that is used in a property expression.

Integration Services supports several different methods of storing package configurations, such as XML files, tables in a SQL Server database, and environment and package variables.

Each configuration is a property/value pair. The XML configuration file and SQL Server configuration types can include multiple configurations.

The configurations are included when you create a package deployment utility for installing packages. When you install the packages, the configurations can be updated as a step in the package installation.

Example:

In this example, I would like to show you how we can create a package level configuration on a package.

Step 1: go to the solution of the package and convert the solution to package deployment model.

Step 2: select a package and go to the control flow of the package and from the SSIS tab select package configurations option from the drop down, this will open a package configuration wizard.

Step 3: From the package configuration wizard enter the file name to save and type of configuration you want to choose.

Step 4: select the properties to export.

Results:

 

Read More

What are the available predefined roles in SSRS Report Server.?

Problem: What are the available predefined roles in SSRS Report Server.?

Solution:  Role Definitions - Predefined Roles

After reports are deployed on to report server, reports are given specific role permissions that are available predefined. These options are shown inside report server database to list available report permission roles.

SELECT [RoleName]

               ,[Description]

  FROM [dbo].[Roles]

RoleName	Description
Browser	May view folders, reports and subscribe to reports.
Content Manager	May manage content in the Report Server.  This includes folders, reports and resources.
Model Item Browser	Allows users to view model items in a particular model.
My Reports	May publish reports and linked reports; manage folders, reports and resources in a users My Reports folder.
Publisher	May publish reports and linked reports to the Report Server.
Report Builder	May view report definitions.
System Administrator	View and modify system role assignments, system role definitions, system properties, and shared schedules.
System User	View system properties, shared schedules, and allow use of Report Builder or other clients that execute report definitions.

Example:

In a report server to change the report security, from one role to another role.

Step 1: go to the report server.

Step 2: select a report and click on the security, and then select the role and edit the permissions.

Step 3: Select the role you want assign from the list of options.

Read More

How to load data concurrently into a destination within the data flow task to fast load?

Problem: How to load data concurrently into a destination within the data flow task to fast load?

Solution: Balanced Data Distributor Transformation

The Balanced Data Distributor (BDD) transformation takes advantage of concurrent processing capability of modern CPUs. It distributes buffers of incoming rows uniformly across outputs on separate threads. By using separate threads for each output path, the BDD component improves the 

performance of an SSIS package on multi-core or multi-processor machines.

Balanced data distributor will be in useful in the following cases:

      1.       Large incoming record set.
2.       Reads source data faster.
3.       Not so good for smaller data loads where single buffer would be enough.

Example:

In this article, I am going to show we can use the balanced data distributor transformation.

To prove better results, I am going to select a table that has about 1million rows and load it into a brand-new table.

Step 1: Create a data flow task to insert the data into a new table by pulling ole DB Source with a table of larger rows.

Step 2: execute to capture the numbers and see performance measures. Clock the time to execute total task.

Step 3: Introduce Balanced data distributor into the data flow transformation, add three destinations and map to the same table in the destination table area.

Step 4: execute to see time taken to finish.

Results: As we can see there is a slight difference but the margin is 30% between both the time intervals and on a broader side this would give much better performance, like a hour time would cut down to 30mins or so depending on the buffers you keep and parallel executions.

Read More

How to compile a stored procedure without executing the actual procedure to see potential problems.

Problem: How to compile a stored procedure without executing the actual procedure to see potential problems.

Solution: NOEXEC

When deploying the procedures to a production database where you want to know the compilation errors without executing the procedure. For example there are validation errors which did not come up until the first time of the execution of a procedure, which would be hard to find.

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.

The execution of statements in SQL Server has two phases: compilation and execution. This setting is useful for having SQL Server validate the syntax and object names in Transact-SQL code when executing. It is also useful for debugging statements that would generally be part of a larger batch of statements.

The setting of SET NOEXEC is set at execute or run time and not at parse time.

Example:

In this article, I am going to show we can get a potential error of a procedure without executing it.

CREATE PROCEDURE [dbo].[getdatabyDate]

@InpudateDate DATETIME

AS

BEGIN

insert into dbo.TableA

select Col1,col2,col3 from dbo.TableB

END

drop table tableA

set showplan_text on

go

set noexec on

EXEC [dbo].[getdatabyDate]

set noexec off

go

set showplan_text off

go

Results:

Msg 208, Level 16, State 1, Procedure getdatabyDate, Line 8

Invalid object name 'dbo.TableA'.

Featured Script:

This below script checks for compile error without executing all the procedures that are currently present inside a database.

Set NOCOUNT   ON;

SELECT 'set showplan_text on' stmCompiledProcName

UNION ALL

SELECT 'go' stmCompiledProcName

UNION ALL

SELECT 'set noexec on'

UNION ALL

SELECT 'EXEC [' + SPECIFIC_SCHEMA + '].[' + SPECIFIC_NAME + ']' AS stmCompiledProcName

FROM information_schema.routines

WHERE routine_type = 'PROCEDURE'

Read More