Text wrapping in SSRS Reports in ReportViewer Control 1

In this blog post “Report Viewer wrap text”, we are going to learn an important trick of text wrapping in SSRS reports for long texts (without space and any separator) exceeding the width of a report column’s defined width and being rendered in a Report Viewer control of .Net  Application. Below is the problem explanation in more detail;

I have a report which is rendering through a ReportViewer control in a .Net Web Application. I have defined the width of each and every column in my report. As there are multiple columns in my report and some of which contains long texts without any space or separator. What i want, if the text is too long to fit as per the defined width, the text should be wrapped in next row. I don’t want to expand the column width to accommodate this text in one row. In other words, I

More

Count of total spaces in a string in SQL Server – Various methods 6

In this post we are going to find the total number of spaces in a given string. I have a string which contains spaces at multiple places and i just want to count them all. We can achieve this in various ways. Here, i am sharing some of these methods. Why i am doing this in various ways and sharing with you is just to show you the performance of different approaches of problem solving and also to share some interesting tricks to solve this problem in different manners. I am not going to cover all the possible ways to solve this problem and in fact you can achieve this in some other ways too. In case you want to share your own method, I welcome it you all to share this in comment section of this post.

Create a demo table as below and then put some demo data … More


Convert rows into comma separated values in a column 9

In this post, we are going to learn a technique to combine all the values of a row (based on a particular condition) with a separator, in a column along with other columns. What i meant by this let me explain it in more detail. Have a look on below question as raised by a colleague;

I have a Students table and a Subjects table. One student has been assigned to multiple Subjects in a transaction table. I want to extract the name of all the Subjects for which the student is assigned in a comma separated way along with other details of the student. If student is not assigned yet, i want to show a “Not Assigned Yet” value for those records.

To achieve this task, i have created a demo script and sharing the same with all the details. Below you can find the table structures and demo … More


Count of total not null values from all columns of a table 1

In this post, we will count not null values from all the columns of a given table. One of my friend asked me to get the count of all not null values from all the columns of a given table. I used a CASE statement in my query and achieved this task but again i thought can i do this with the help of COUNT function too? And then i found an another way to achieve this task too. Here i am sharing both the ways. I am also sharing a trick to find only NULL values too.
To demonstrate this first create a table and put some demo data as below;

Create a Table:

CREATE TABLE dbo.TEST
(
    ColA VARCHAR(10),
    ColB VARCHAR(10),
    ColC VARCHAR(10),
    ColD VARCHAR(10),
    ColE VARCHAR(10),
    ColF VARCHAR(10)
)
GO

Add rows:

INSERT INTO dbo.TEST(ColA, ColB, ColC, ColD, ColE, ColF)
VALUES('Mango', 'Apple', 'Banana', NULL, NULL, NULL),
('Mango', 
More

sp_executesql vs execute in SQL Server 4

In this post “SP_EXECUTESQL vs Execute”, we are going to compare sp_executesql and execute in SQL Server. Apart from differences, we will also discuss the similarities between sp_executesql and execute commands. Lets start with few questions like, What is the difference between Execute and sp_ExecuteSQL? How can we execute a T-SQL String? Similarities between Execute and sp_ExecuteSQL. Execute and sp_ExecuteSQL common features. Benefits of sp_ExecuteSQL. Which is better to use sp_ExecuteSQL or EXEC? Execute vs sp_ExecuteSQL.

Let’s discuss these points one by one.

Difference between sp_ExecuteSQL vs Execute

sp_ExecuteSQL

sp_ExecuteSQL is also used to execute a T-SQL string in SQL Server and points are below:

  • It allows parametrization and hence more secure than EXEC command to execute a SQL string dynamically. It’s tough to inject.
  • We don’t need to cast the parameter values as like we need in EXEC command. We simply put the parameter name in T-SQL string as
More

Convert date range into date list in SQL Server 3

In this post “Convert date range in date list”, we will convert a given date of range into list of dates as per the business requirement. Just have a look on mentioned demo table which has three columns CustomerID, StartDate and EndDate. I want to generate the date list for the given date range for each customer. For example if we have a entry as CustomerID – “1”, StartDate – “10-Dec-2012” and EndDate – “19-Mar-2013”, then this should return a date list for CustomerID 1 with 4 rows. First row for 10-Dec-12 to 31-Dec-12, Second row for 01-Jan-13 to 31-Jan-13, Third row for 01-Feb-13 to 28-Feb-13 and fourth row for 01-Mar-13 to 19-Mar-13.

Mind that in first row i have started with the actual start date of the given date range and in the last row i have put the end date as actual end date. But if there is … More


Truncate vs Delete in SQL Server

Difference between DELETE and TRUNCATE in SQL Server

Let’s discuss this point “Truncate vs Delete” below in simple way.

  1. Delete is a DML statement whereas Truncate is a DDL statement.
  2. Delete commmand deletes each row from the table which are satisfying the WHERE clause and logs it in the log file where as Truncate command deallocates the data pages and logs it in the log file.
  3. Delete performs slower than truncate because of logging mechanism it has whereas Truncate is faster than delete.
  4. Delete can remove full or partial data as per it’s filter defined in WHERE clause whereas Truncate does not support WHERE clause and all data will be removed from table.
  5. Delete executes the Triggers on the table if it has any because it is a DML command whereas Truncate does not execute the Triggers.
  6. Delete requires more locks than truncate because each row is locked for deletion
More

Full Text Search on files in SQL Server

What is Full Text Search in SQL Server?

Full Text Search in SQL Server enables us to perform complex queries against character based data. Full Text Search supports char, varchar, nchar, nvarchar, text, ntext, image, varbinary and xml data types. We Can store document files in varbinary(max) format with their extensions and enable Full-Text search on it. SQL Server supports many types of document files for Full-Text indexing some of them are .asp, .aspx, .ascx, .c, .doc, .html, .ppt, .txt, .xls and many more. Some other extension like .pdf provides it’s own “Filter Pack” to enable Full-Text index on pdf files in SQL Server.

To enable Full Text Search on one or more columns of the given table or indexed view, we have to create a Full Text Index on it. After creating a full text index, we can perform word searching or phrase searching on a column based on … More


Fill Factor in SQL Server

Do you know what is FillFactor for an index? In this post, we will discuss about Fill Factor in SQL Server. Lets start discussion about FILLFACTOR in SQL Server.

What is FILLFACTOR?

Fill factor in SQL server is used to control the filled space of leaf pages with data. Remaining space is left to accommodate future growth of data inside the page. The default value for fill factor is 0 i.e. packed to 100%. Fill factor has a percentage value which could be anything between 1 and 100 including both. A page in SQL Server is the smallest unit of 8K, which holds the number of rows depending on the size of the row.

Setting Fill Factor

Fill factor can be set either at Server Level or at index level. Just see each in action below;

Setting Fill Factor at Server Level

By default it’s 0 at server level, but … More


Creating primary key without clustered index 1

One of my colleague asked me a question that “Can we create a primary key without a clustered index?”. I answered him that “Yes, of-course!” and also did not forget to share this information to all of you in this post named “Create nonclustered primary key”. I know many of us are well aware of this and have did this many times in our day to day SQL activity. Still for those who don’t know how they can accommodate a primary key with a nonclustered index, i am sharing this post, in which i will show you that how we can create a non-clustered primary key on a table.

Before creating this nonclustered primary key, here i am showing you the default behavior of SQL Server during Primary key creation. In case table does not have any Clustered index created on it, SQL Server creates a PRIMARY KEY on a … More