Every developer knows the Logical Operators such as Inner Join, Left Outer, Right Outer Join etc in SQL Server.
A user complained saying that they are getting continuous timeouts from their application during some processing. They insisted that it is a problem with the server resources. Since the server in question is housing more than 30 databases, that cannot be the reason. Further, even this application is working nicely with other queries but not with one particular query. When the requested for the particular query, it was not provided as the basic conclusion is that this is due to the server resources. Then the development team insisted to have a look on the database server which we did. At that time, server memory was 97% with CPU is at 4%. So, naturally, all were pointing the guns at server memory. The server memory is something that I have been elaborating to the users over the years, but with little success. It was very difficult to make them believe that database is a different animal is altogether.
Finally, I got the table name even though I could not get the exact query. Well, the table has more than 300,000 records with one clustered index on the identity column. I was told that there process this table for each user. They were having around 1,500 users. That means the query in question, is doing table scans of 300,000 * 1,500. This table had more than 100+ columns but still, I couldn't make them satisfy that this an index problem. Then I offered them to delete a few records and check. Well, with fewer data their query worked nicely. Finally, they accepted it was an index issue, but for that more than 4-5 hrs were spent like the previous incident on the index.
Learnings from the incident
#1 - Increasing the hardware resource will not the first solution, there can be more other options than that.
#2 - Don't be panic seeing that the server memory of a database server is hitting 90+%. That is how it is. You should worry if it is not.
#3 - Index can do wonders for you only if you know the index concepts.
If you need more details on Index read this article.
24 hours of SQL Pass is happening on Sept 17 & 18th. These are short little 1-hour web casts, but may be of interest to someone. Registration is FREEEEEEEE!!!
Link to sessions:
http://www.sqlpass.org/24hours/2015/summitpreview/Schedule.aspx
Row-level security is great for all or nothing decisions, but there are times when users need access to a subset of the data. For example, the last four digits of a credit card or social security number. This can be done at the application level, but that leaves room for error. You only need to forget the mask one time to leak sensitive data.
SQL Server 2016 attempts to address this with a feature called Dynamic Data Masking. When a column is created with a mask, it defaults to returning only the data exposed through the mask. There are three types of masks currently available:
Read more at http://www.infoq.com/news/2015/06/SQL-Server-Masking
This is the question some one asked today. I haven’t done this but I can remember I read this some where. So thought of digging this today.
Some forums are suggesting a product.
http://www.datawatch.com/monarch-is-back/
Out of the box, Datawatch Monarch can work with a wide range of report formats including PDF, XML, HTML, text, spool and ASCII files. Access data from invoices, sales reports, balance sheets, customer lists, inventory, logs and more. The system is easy to use, allowing you to quickly select a file and automatically convert it into structured data for analysis.
Following blog includes how to import several types to SQL Server.
http://sqlage.blogspot.com/2013/12/ssis-how-to-import-files-text-pdf-excel.html
Following blogspot also have lot of links
http://www.forumarray.com/ssis-data-flow-source-component-to-read-a-pdf-file-226359
Following blog is the most relevant one for me. http://sql31.blogspot.com/2013/03/how-to-load-data-from-pdf-file-in-ssis.html
This users script task and you have the option of customizing it.
SqlBak.com presents you a free SQL Server String Functions Cheat Sheet.
http://sqlbak.com/blog/wp-content/uploads/2015/01/TSQL-STRING-FUNCTIONS-CHEAT-SHEET.pdf
DATALENGTH - Returns the number of bytes used to represent any expression.
LEN - Returns the number of characters of the specified string expression, excluding trailing blanks
Output is 30, 3.
Important point to remember is is LEN function does not depend on the data type and it is storage mechanism. LEN function total depends on the number of string characters.
Output is 8 and 3.
So DATALENGTH is depends on the storage but not the LEN function.
Parameter sniffing is when SQL Server compiles a stored procedure’s execution plan with the first parameter that has been used and then uses this plan for subsequent executions regardless of the parameters. Read more about parameter sniffing and how to avoid it from here.
There are lots of questions asking whether there are before triggers in SQL Server. There are nothing called Before Triggers in SQL Server.
What is the requirement for the Before Trigger?
Let us say, you want to verify some values other table before inserting it. In SQL Server, you can use INSTEAD OF TRIGGER.
CREATE TRIGGER tr_data_before ON Table_Data
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM Tablle_Data2)
BEGIN
INSERT INTO dbo.Table_Data
SELECT *
FROM INSERTED
END
END
This was a question brought to my attention and I didn’t have an answer. After searching, I realized that there is no direct function for it. Hence I came up with following solution.
I used inserted and deleted virtual tables.
| Statement | inserted | deleted |
| INSERT | rows just inserted | |
| DELETE | rows just deleted | |
| UPDATE | modified row contents | original row contents |
With the above cases, I came up with following trigger.
CREATE TRIGGER trg_data_ins_del_upd
ON Data
FOR INSERT,DELETE,UPDATE
AS
BEGIN
DECLARE @ins int ,@del int
SELECT @ins = Count (*) From inserted
SELECT @del = Count (*) From deleted
IF @ins > 0 AND @del > 0
INSERT INTO Operation (Operation) VALUES ('Update')
ELSE IF @ins > 0
INSERT INTO Operation (Operation) VALUES ('Insert')
ELSE IF @del > 0
INSERT INTO Operation (Operation) VALUES ('Delete')
END
However, I feel in the above case, you better of having three separate triggers than loading everything to one trigger.
Learning best practices for MongoDB development and administration can help reduce development time for your application, and increase reliability and performance. Get yourself and your team up to speed quickly with 10gen's comprehensive courses in person and online:
There are two myths around BIT column indexes.
Myth #1. Cannot create indexes on BIT columns.
Not sure how this was evolved. But this not absolutely false. Let us try it.
USE tempdb
GO
CREATE TABLE Employee
(ID INT IDENTITY PRIMARY KEY CLUSTERED,
Name Varchar(50),
Status BIT
)
CREATE INDEX idx_employee_status ON Employee(Status)
Above statement is successful and you can create the index is created.
I verified this in SQL Server 2000 SP4 and it was successful.
Myth #2. BIT Column indexes are not useful.
Above argument has a base. BIT column can have possible three values, 0, 1 and NULL. Therefore index selectivity is low. For example, consider the above table which has 10 million rows and status will say whether these are active or not. In large table, active records will be very less so active = 0X1 will be highly selective.
