Browsing the SQL Server category

sql server query takes longer to run with parameters

(I also initially searched “sql query doesn’t run with parameters“)

I have a SQL query that I was running in Microsoft Management Studio and it took ~20 seconds to run.  It was a rather complicated query, with UNIONs and a Common Table Expression, and a Pivot.  But it ran.

I had some hard-coded values that, when I used parameters instead, the same query took too long to run (i.e. after 20 seconds, I gave up on it).

I changed my hard-coded values to be dynamic values, using this:

declare @first_of_this_month datetime;

declare @date_start varchar(20)

declare @date_end varchar(20)

 

SET @first_of_this_month = CONVERT(datetime, STR(MONTH(getdate())) + ‘/1/’ + STR(YEAR(getdate())));

SET @date_start = DATEADD(m,-5, @first_of_this_month);

SET @date_end =  DATEADD(m,10, @first_of_this_month);

 

And even though this runs in seconds,

select @date_start

select @date_end

select @first_of_this_month

 

the full query took much longer.

 

The solution: just add this to the end of the query:

 

OPTION (RECOMPILE)

 

Not really sure what it does, but it worked!  Note: this was for an SSRS report with two datasources. Even though the “long” query ran okay in SSMS and one query was instant, I had to add this onto BOTH queries (in each dataset) for it to work.

Read the article

import into sql server from excel more than 255 characters

I had data in MS Excel where some cells had more than 255 characters in it.  I needed to import this data into my SQL Server.

I was using the “Import/Export Wizard” and also trying to just copy directly into MS Access (via ODBC connection), but that wasn’t working.

What I ended up doing was, using the Import/Export Wizard, continue through until you get to the section for  “Select Source Tables and Views” – in there, click on the worksheet.  Then click “Edit Mappings”.  On the field that has a lot of characters, change it from the default to TEXT.  You’ll see the default was 255 characters.

Read the article

SQL Server INSERT (append) statement doesn’t work

I had a typical INSERT statement in SQL that followed the format

INSERT INTO [table]

VALUES [values]

The problem was that I was inserting values from a SQL statement.  The “VALUES” clause is not needed:

INSERT INTO [table]

SELECT * FROM [other_table]

Read the article

Users don’t do what you expect

I have an HTML form with a jQuery plugin for a date picker.  If the user doesn’t know what the date should be, they should leave the field blank.  The data is stored in a “date” format and will default to 1/1/1900 (this is in SQL Server).  If that date is stored, I know to replace it with nothing (“”) instead.

If the user put in “N/A” (text) instead of leaving it blank, then the database will give an error in that it can’t convert text (“N/A”) to a date.

The solution: have server side AND client side to assure that the input is in a “date” format (or blank).

Read the article

Set default database in Microsoft SQL Server Management Studio

I was under the impression that the “default” database was (only) set when you create a new “user” in MSSMS.  I found out there’s a setting you can change in MSSMS:

 

–          On the login screen (where there is a dropdown that you are connecting to the Server Type of “Database Engine”), hit “Options”

–          In “Options”, go to the “Connection Properties” tab

–          There is a drop-down for “Connect to Database” that you can specify the default there

Read the article

sql running totals

Might be specific for SQL Server:

SELECT
CONVERT(datetime, STR(MONTH(the_month)) + ‘/1/’ + STR(YEAR(the_month))) AS the_date,
COUNT(1) as TheCount,
SUM(COUNT(1)) over (order by the_month rows unbounded preceding) as cumulative_count
from dbo.MyTble
GROUP BY the_month
ORDER BY the_month

Afterward, I wanted to have a placeholder for months that didn’t have a value.  I can generate the SQL with my server-side language, and I’d just add in UNION queries; so I ended up with this:

 

SELECT the_month,
SUM (TheCount) AS TheCount ,
SUM(TheCount) over (order by the_month rows unbounded preceding) as cumulative_count
FROM (

SELECT
CONVERT(datetime, STR(MONTH(date_field_here)) + ‘/1/’ + STR(YEAR(date_field_here))) AS the_month,
COUNT(1) as TheCount
FROM dbo.MyTable
WHERE date_field_here IS NOT NULL
GROUP BY date_field_here

UNION SELECT CONVERT(datetime, ‘2016-01-01’) AS the_date, 0 as MyCount
UNION SELECT CONVERT(datetime, ‘2016-03-01’) AS the_date, 0 as MyCount

) as qryMain
GROUP BY the_month, TheCount
ORDER BY the_month

 

Read the article

Get data from SQL Server in Excel

You can use this VBA code to query a SQL Server database, right from Excel:

 

https://gist.github.com/stevesohcot/47d73642ff0c9475d99e89d0a2d141d1

Read the article

Be careful with division in SQL (unexpected truncation)

My SQL statement was doing some division:

(SUM(field_one) *  SUM(field_two) / 100)  AS CalculatedValue

 

But I wasn’t getting the expected result.  Specifically, “field_two” had a value of 0.33 and “field_one” was 12.  Multiplying it, you’d expect it would be 4.  Instead, it was 3.96, which was being truncated to 3.

The solution was to add in decimal places when I divided it by 100.  Then I also rounded it to zero decimal places:

                ROUND(SUM(field_one) *  SUM(field_two) / 100.00),0)  AS CalculatedValue

Read the article

Be wary of uncommon data

I had an Excel macro that manipulated data such that it grouped similar data into its own spreadsheet.  I looped through each row to find the start/end row for that section and copied the data accordingly.

In my loop, I simply “added 1” from the last row of one group to determine the starting row of the next group.  But this caused an issue where a group only had one row of data.  I had to back-track and say “check if the next row in Excel you’re about to put in matches; otherwise, do something else”

 

    If ActiveCell.Offset(1, 0).Value <> varValueImLookingFor Then

‘ don’t do anything!

Else

Do

ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Offset(1, 0).Value <> varValueImLookingFor

End If

Read the article

SQL Server Pivot table example

Template 

SELECT Row_Heading, “Col_One” AS Name_One, “Col_Two” AS Name_Two

FROM (

SELECT Row_Heading, Col_Heading, Value

From Some_Table

 

) AS qryMain

 

PIVOT (

MathFuntion( Value ) FOR Col_Heading IN (“Col_One”, “Col_Two”)

) PIV

 

Example:

SELECT RES,  “xx” AS SomeCategory, “yy” AS SomeOtherCategory

FROM (

SELECT dbo.table_name_here.field_name_a, dbo.table_name_here.field_name_b, dbo.table_name_here.HOURS AS the_hours

FROM dbo. table_name_here

) AS qryMain1

PIVOT (

SUM(the_hours) FOR field_name_b IN (“xx”, “yy”)

) PIV

Read the article