As in my previous blog posts, I've been posting some new features in SQL Server 2012. In today's post, I would like to show you a new T-SQL function wich will lead you to solve a daily problem. So far, when you needed to get the last day from current month you would do something like example below: select dateadd(MONTH,1,convert(datetime,left(convert(varchar,GETDATE(),112),6)+'01'))-1 Result: 2012-04-30 00:00:00 which in other words, these are the steps we are doing: - convert(varchar,GETDATE(),...
Read More
As in my previous blog posts, I've been posting some new features in SQL Server 2012. In today's post, I would like to show you new features in SSIS. One of them is the left function wich till now you had to use substring to achieve the same result. These are the new functions in SSIS: LEFT - You now can easily return the leftmost portion of a string rather than use the SUBSTRING function. Left syntax is the same as we know in T-SQL: LEFT(character_expression,number) REPLACENULL - You can use th...
Today, one of my team members faced an issue that I was not able to give the answer right away. The problem was, in SSMS while he was connected to DEV JOBs, he couldnt edit steps, as image below: First we started for privileges/permissions issue. But that was not the problem. After checking his SS version we got the problem: select @@version Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6...
« 1 | 2 |3 » This is the 3rd and final post about BULK INSERT. In this one I want to show you how to bulk insert a file into a table with an identity column. Here is the script to create table: CREATE TABLE [dbo].[Users] ( [UserID] int IDENTITY(1,1), [FirstName] [varchar](10) NULL, [LastName] [varchar](10) NULL, [Country] [varchar](20) NULL ) ON [PRIMARY] I want to insert a csv file without mapping UserID, since it is an identity column. File format is shown below: When trying to run...
« 1 |2| 3 » In my previous post I've shown how to bulk insert a csv file through T-SQL. In that example I've used the same structure to both csv file and destination table. In this post I'll show you how to bulk insert a file wich struture differs from destination table. For this example, I'll take the same destination table: CREATE TABLE [dbo].[Users] ( [UserID] int NULL, [FirstName] [varchar](10) NULL, [LastName] [varchar](10) NULL, [Country] [varchar](20) NULL ) ON [PRIMARY] From ...
« 1| 2 | 3 » Recently, there was a post where the user found himself in a problem when loading data utilizing BULK INSERT This post is the first from a series, wich I want to explain how to use BULK INSERT in multiple possible ways. BULK INSERT - as mentioned in BOL , BULK INSERT "Imports a data file into a database table or view in a user-specified format in SQL Server 2008 R2. Use this statement to efficiently transfer data between SQL Server and heterogeneous data sources." Notice...
In the last September 28th I've passed my 1st microsoft certification. In fact, I've passed 2 microsoft certifications!! Yes, I was able to do both certification exams in the same day. It was my very first experience in certification exams. I've got 1000/1000 pts in 70-452 and more than 900 pts in 70-448 (I think I missed 1 question). I believe 70-452 is easier, but this is my opinion. My study/training was based in Testking and I had 3 weeks to be prepared. Not that I wanted, but because my com...
Yes! For the second time, we host SQL Saturday. This event will be held Mar 17 2012 at Universidade Autónoma de Lisboa , Rua de Santa Marta, nº 56, Lisbon , 1169-023, Portugal . Here is the link . I wonder if I will get the change to meet any of you?
As I've shown in my previous posts, there is a bunch of new functions and features with SQL Server 2012. In this thread, I would like to show you how to use the new logical function which select and return data from a specific index of a list of supplied values. The index list always start by 1. This new function is the Choose function. This function has some similarities with program languages such as switch function in VB.net or C#, and the function Choose itself in VS2008 and SSRS. Below the ...
With this topic, I want to share Visio 2010 and sharepoint 2010 new features. But mainly, I would like to hear your feedback, ideas, experience and if possible, some examples where you find this would be interesting, but must of all if you use this in your professional activity, why, and benefits it brought to you and end users. Data Graphic was first introduced in Visio 2007. In Visio 2010, among new features in ribbon, it is now possible to create sub-process and workflows. Sharepoint 2010 int...
In earlier versions of SQL Server, when you were writing T-SQL, how many time you asked yourself "is there a if function?". Even when you used CASE function, how many time did you said "if there was a if function, this was a lot easier". Well, finally with SQL Server 2012, IIF function is added. IIF stands for Inline IF . IIF functions is very similar to CASE function, however you can only evaluate the expression for two values and no more. Syntax is shown below: IIF (boolean_expression, true_va...
I've previously talked about the sequence object: http://www.bidn.com/blogs/marcoadf/bidn-blog/2396/sql-server-2012-sequence-object But I'ld like to call your attention to what happens when generating numbers inside a transaction. As mencioned, in the blog, we can generate next number by just running statement select. And, what happens if I insert that select inside a transaction, as shown below: BEGIN TRAN SELECT NEXT VALUE FOR dbo.Seq ROLLBACK TRAN The answer is: next number generated will be ...
With the new SQL Server 2012, microsoft added a lot of new features, such as T-SQL function and objects. In the post, I will show you the brand new Sequence object. For those familiar with Oracle/PLSQL, sequence has been a long time used function. But only recently it was added to SQL Server. Sequence object provides, as the name suggests, a sequence number based in the specification in wich the sequence object was created. It has similar functionality to the identity column. The big diference i...
*Edited: Videos already available: http://www.sqlshare.com/IntersectOperator_786.aspx http://www.sqlshare.com/Exceptoperator_787.aspx Intersect and Except operators, operates on table expressions producing the intersection and difference, respectively. The synstax is shown below: { <query_specification> | ( <query_expression> ) } --> Left side { EXCEPT | INTERSECT } { <query_specification> | ( <query_expression> ) } --> Right side INTERSECT Returns any distinct valu...
Today I want to challenge you to solve a problem that I faced recently. I can tell you that was a hell of a challenge algorithm. I found myself with 2 possible solutions. I'm here to listen your solutions and take them in consideration "against" my solution. Here is the problem: Lets say that 3 technicians are sent to customer's house to solve a task. Each technician has a level (1,2,3...and so on). The total task time will be the SUM that each technician took to solve the task, but will not be ...
Some times is very useful to gather information about report execution, for example: How often a report is requested Report duration time (slowest reports) User that ran more reports What formats are used the most This information can be achieved from ExecutionLog table, from reportserver database. Running this query: select c.Name as [Report Name], e.InstanceName, e.UserName, e.Status, e.Format, e.Parameters, e.Timestart, e.TimeEnd, datediff(mi,e.Timestart,e.TimeEnd) ExecutionTime, e.TimeDataRe...
The ROLLUP operator is useful when summarizing data. Its useful when generating reports that contain subtotals and totals. I use it a lot in my audit/daily queries, because table/matrix/tablix has subtotals. Basically ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns. For this example, I picked the actual FIFA Top 20 World ranking below: So, for now, I will just include WITH ROLLUP clause and group by Country and Continent. So, I will get the s...
*** Edited *** Videos now available with title Ranking Functions: http://www.sqlshare.com/profiles/26681/MarcoFrancisco.aspx In SQL Server there is 4 ranking functions: ROW_NUMBER Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. RANK Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that ...
In my previous post, I've shown how you could extract the time duration of a DTS. Assuming that you have selected the DTS witch is the slowest among the others, how could you know wich of the steps is the worst? Imagine that you have 20 steps inside the DTS, 19 are fast and just 1 is taking the whole time? I use the logging provided in the DTS. Just right click on control flow, select "Logging...", Add a new "SSIS log provider for text files" and select the log file in "Configuration"as shown be...
Hi. Sometime is very useful to know DTSs duration. In big DWs, we often get performance issues. Just for example, in one of my datamarts I have more than 110 DTS packages running each day. And with that information I can focus where to improve. I have 2 ways to do this: inside and outside DTS. In this thread I will only talk about the outside way. To achieve this information, every of my DTS package writes into a table. When DTS starts it open a log entrie and the log is closed when package fini...
Hi. This is my very 1st blog share. I'm here to talk about subscriptions and how to manage them, either manually or automatically. I will show you how to see all information from existing subscriptions (like last run status, last run time, who created, who modified, etc) and will also show you how to know all running subscriptions . This last one is very useful, as I will show you further. I've developed a video with a tutorial. I've uploaded to sql share on day 13. Watch him first: http://www.s...
Hi. My name is Marco. I'm from Portugal and I am 26 years old. I've been working in BI in the past 4 years. Less than a year with oracle and the last 3 with SQL Server. I've a deep knowledge with SQL Server BI tools (SSIS, SSRS and SSAS). I've worked in a wide variety of bussinesses, from automotive to communications, among others. Currently I'm working in Lisbon at the biggest communications company in Portugal, with a huge DW. We have a lot of datamarts like Billing & Payments, Sales, Qual...