Statistics update with automatic sampling messes up density vector and histogram I have a table that contains 22 millions of records.

I have noticed that statistics for a column were way off, even if that column has a constant distribution: in fact, every value is repeated twice.

To help you visualize this scenario, think of a table with check-in and check-out dates (2 distinct records) for every unique id of another table.

These are the statistics with a full scan:

These are the statistics with automatic sampling:

Why this weird behaviour? Is there some rule of thumb to determine a correct sampling rate or is the fullscan necessary? Should I create a statistics update job with fullscan for some statistics? And if that's the case, how do I know which are the statistics that need this kind of treatment?

Additional informations:

There is no foreign key constraint on that column
The column is a numeric(14,0) NULL

Why this weird behaviour?

Suppose someone gave you a book of a million integers and told you to make a guess about the overall distribution based on ten sampled numbers. The ten sampled numbers that you receive are 1, 1, 125000, 125000, 250000, 250000, 375000, 375000, 500000, and 500000. One perfectly reasonable guess is that the book contains 500000 unique integers in pairs ranging from 1 to 500000. Another equally valid guess is the book contains five unique integers with each integer present 200000 times. For your data distribution, SQL Server is making an inference about your data based on the sample that's closer to the latter interpretation. The algorithms to build histograms don't work well for all possible sampled data distributions, including one with each unique integer showing up exactly twice. I was able to easily reproduce your described issue in SQL Server 2017.

  Is there some rule of thumb to determine a correct sampling rate or is the fullscan necessary?

The only rule of thumb that I'm aware of is that the correct sampling rate is at least equal to the sampling rate that gives you acceptable query or system performance. I've found DBAs to use a 100% sampling rate to avoid that analysis, and it's a perfectly reasonable shortcut to take for tables that aren't huge.

You could consider replacing your table with an updatable view built on two underlying tables. Each underlying table could contain one half of the data. SQL Server will build separate statistics objects for each table and you may get better performance without having to tweak statistics. I cannot say if this is a good approach for your specific scenario.

  Should I create a statistics update job with fullscan for some statistics?

That is a common solution to the problem that you're describing. For another option, SQL Server 2016 SP1 CU4 introduces the PERSIST_SAMPLE_PERCENT keyword for STATISTICS commands. You can use it to instruct SQL Server to always sample statistics at a certain rate for this column whenever an auto-stats update is triggered. If async stats updates are disabled then keep in mind that query compilation times may occasionally run long for queries that use your column.

  And if that's the case, how do I know which are the statistics that need this kind of treatment?

The usual answer is to monitor your workload for unacceptable query performance, determine which queries have statistics issues, and to fix those issues with statistics changes. If you wish to be proactive you could consider analyzing statistics on a copy of production. First get sampled statistics for all relevant columns on the database and save all of the density vectors to a table. You can use DBCC SHOW_STATISTICS WITH DENSITY_VECTOR to do that. Then update all of the same statistics with FULLSCAN and save those density vectors as well. You can then compare the density vectors to find the statistics objects with the largest differences. This won't find all statistics issues, but it will find those similar to what you observed in this question.


Prevent compilation of a stored procedure to a linked server I have a stored procedure, that queries a view on a linked server.

This works fine on prod, but our development server has some firewall restrictions, that prevent the compilation. This is no problem datawise (since it is not very important on dev), but I can't even ALTER the procedure on the development server, because the SQL server tries to compile it / check the syntax / used column names etc. when I alter the procedure (and I receive a timeout).

Is there a way (option, trace flag etc.) that I could use to prevent this syntax check and compile the procedure on the dev server (similar as when a table used in a procedure did not exists, where the SQL Server only throws a warning)?

This can't be done. Deferred Name Resolution only works for table objects. All other objects must exist at the time of creation/modification. See this answer and this older documentation for further info.

As a workaround, you could do one of the following

Restore a copy of the database from the linked server to DEV and create a loopback linked server (the same name as PROD but points to DEV).
Script out the database objects from the linked server database, deploy this 'empty' database to DEV and create a loopback linked server. Alternatively, export a DACPAC (schema-only export) of the DB.
Remove references to the linked server and linked server DBs, essentially remove the 1st and 2nd parts of the four part names. This will only work if no columns in those tables are actually in use by the procedure.
If there is a DEV copy of the linked server, create a linked server in DEV to point to it.


Validation of Partitioned table Performance We are testing partitioning on a table which contains a datetime column (DateColumn).

Partition function I am using is:

-- 138623  +  16774 records
CREATE PARTITION FUNCTION pf_TableName_DateColumn (datetime)
AS RANGE LEFT
FOR VALUES ('20160101', '99991231');

How do I verify that partitioning the table is improving query performance?
There is one Clustered index on PARTITION SCHEME, do remaining non clustered indexes get statistics of partition?

I am testing it by running the same stored procedure with date parameters on two databases (real and replica). One contains a partitioned table (two partitions) whereas the other is simple. Profiler doesn't show any difference in CPU, Duration, Rows, or Reads.

Why would you expect performance to be different with partitioning? Partitioning improves manageability but performance depends greatly on the workload and queries.  For example, if you have a useful index on the DateColumn, you will likely get the same performance with and without partitioning.  With no index on the column, performance may be better with partitioning as long as only the needed partitions are scanned due to elimination.  But if an index can prevent the scan, that would provide the greatest performance benefit.


SQL - Spilt timestamp into multiple rows I am dealing with data that needs to be looked at on a shift-to-shift basis (8:00:00 to 20:00:00 and its reciprocal are the two shifts) There are instances where a timestamp (one row) will span longer than a shift. Below is an example of what I am looking for.
----------------------------------------------------------------------------------------------------------
                                           Original Timestamp Data
----------------------------------------------------------------------------------------------------------
   START_TIME             END_TIME
2020-07-16 04:54:50  2020-07-27 06:36:14

----------------------------------------------------------------------------------------------------------
                                           Updated Timestamp Data
---------------------------------------------------------------------------------------------------------
-
   START_TIME             END_TIME
2020-07-16 04:54:50  2020-07-16 08:00:00
2020-07-16 08:00:00  2020-07-16 20:00:00
2020-07-16 20:00:00  2020-07-17 08:00:00
2020-07-17 08:00:00  2020-07-17 20:00:00
        .                      .
        .                      .
        .                      .
2020-07-26 20:00:00  2020-07-27 06:36:14

Here is the code I have tried but I am only able to split the data into two rows. SOmething tells me that the "Start Roll" and "End Roll" Columns within #T1 are not going to work in a situation like this.
Declare @DayTurn as DATETIME, @NightTurn As DATETIME, @TodaysDate As DATETIME, @DateCheck As DATETIME, @TimeChange As Integer, @MidNight As DATETIME

Set @DayTurn = '8:00:00'
Set @NightTurn = '20:00:00'
SET @TodaysDate = GETDATE()
SET @DateCheck = CASE WHEN DATEPART( WK, @TodaysDate) >= 7 THEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
    ELSE DATEADD(Week,-6,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
END;

SELECT
      (Case
        When cast(Activity.[START_TIME_UTC] as time) >= cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@NightTurn as time) and cast(Activity.[START_TIME_UTC]) as time) < cast(@NightTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), DATEADD(hour, @TimeChange, Activity.[END_TIME_UTC]) , 112) + ' ' + CONVERT(CHAR(8), @NightTurn, 108))
        else CONVERT(datetime, Activity.[START_TIME_UTC]) end) as 'START_TIME'
      ,(Case
        When cast(Activity.[START_TIME_UTC] as time) < cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC]) as time) <= cast(@NightTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@DayTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), Activity.[START_TIME_UTC] , 112) + ' ' + CONVERT(CHAR(8), @DayTurn, 108))
        else CONVERT(datetime, Activity.[START_TIME_UTC]) end) as 'Start Roll'
      ,(case
        When cast(Activity.[START_TIME_UTC] as time) < cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC]) as time) <= cast(@NightTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@DayTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), Activity.[START_TIME_UTC], 112) + ' ' + CONVERT(CHAR(8), @DayTurn, 108))
        else CONVERT(datetime, Activity.[END_TIME_UTC]) end ) As 'END_TIME'
      ,(Case
        When cast(Activity.[START_TIME_UTC] as time) >= cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@NightTurn as time) and cast(Activity.[START_TIME_UTC] as time) < cast(@NightTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), Activity.[END_TIME_UTC] , 112) + ' ' + CONVERT(CHAR(8), @NightTurn, 108))
        else CONVERT(datetime, Activity.[END_TIME_UTC]) end) as 'End Roll'

      into #T1

  FROM [MY_DATA] as Activity
  Where Activity.[START_TIME_UTC] >= @DateCheck

SELECT * INTO #T2 from(
Select
    temp.[START_TIME]
    ,temp.[END_TIME]
From #T1 as temp
UNION
Select
    temp.[Start Roll]
    ,temp.[End Roll]
From #T1 as temp
) as temp;

SELECT
  *
FROM #T2
Order By START_TIME;

Drop Table #T1
Drop Table #T2

Any and all help is greatly appreciated.
Cheers!


Why is there a PostgreSQL Turtle? I was just checking out the propaganda page for PostgreSQL and I found this little piece of art:

It is the "Turtle Style" PostgreSQL logo. How old is this, and did PostgreSQL ever officially use a turtle instead of an elephant? What is the story here?

As stated in the official Identity Guidelines

  In Japan, a turtle logo is used in place of the elephant due to domestic terrorist symbolism.

The elephant has negative connotations in Japan because members of a cult which uses an elephant as its logo were responsible for some terrorist attacks.

The group in question wore blue elephant masks while campaigning for Japan's parliament a few years before their murder spree, rather than it being exactly a logo. Personally, I would rather not post links to pages about their exploits, but if you want to go looking it doesn't take two minutes on a google search to find articles if you use words like: elephant deadly sarin gas tokyo

You can see the turtle iconography used quite extensively on the Japanese Language PostgreSQL site

While other Japanese companies like Zojirushi may retain use of the Blue Elephant iconography, that company in particular dates back to 1918 and its branding dates from the 60's. PostgresSQL's use of elephant iconography dates from only the late 90's which corresponds very nearly with the horrible events previously mentioned and would have no doubt been a contributing factor in the community decision to not use the imagery in Japan.


How to move SSISDB database I have a SQL 2016 instance installed. It has SSISDB for SQL server integration services installed.
Now because it is taking up all the space on our system drive, I need to move its data and log files to a separate disk. To do that I will need to take the db offline.
Is there any repercussions you guys can think of after taking it offline. I have coordinated a time with application guys already.

It is more or less the same as moving the data files for a user database, but
you can follow the steps in the documentation To Move the SSIS Database as it has some additional good precautions noted:

Follow the instructions for moving user databases. For more information, see Move User Databases.
Ensure that you back up the master key for the SSISDB database and protect the backup file. For more information, see To Back up the SSIS Database.


transaction not processing? I'm trying to test out an update prior to running it on our production server, however when running my transaction in sqlplus, I don't seem to be able to commit the transaction or get any data back from my select, what am I doing wrong?

SQL> select activity_message, regexp_replace(activity_message, 'null', new_value) from organization_activity_log where event = 'ADDED' and activity_item = 36 and activity_message = 'Added: null';

ACTIVITY_MESSAGE
--------------------------------------------------------------------------------
REGEXP_REPLACE(ACTIVITY_MESSAGE,'NULL',NEW_VALUE)
--------------------------------------------------------------------------------
Added: null
Added: 1234567891

Added: null
Added: 1234567890 Expires: 12/31/2016

SQL> begin;
 2  select activity_message, regexp_replace(activity_message, 'null', new_value) from organization_activity_log where event = 'ADDED' and activity_item = 36 and activity_message = 'Added: null';
 3  commit;
 4

Oracle's transactions are implicit in sqlplus you're already in one and don't have to start it. This means you can just do

select ...
   update ..
   rollback ...
   select...

if you do an exit without doing exit rollback it will commit on exit. begin is not necessary here.


ms-access: issues with form and subform interaction There is a main form bound to the main table, public_OUT_Detalle_PMC. And there is a form bound to a smaller table, public_ficha_negocio, which is a selection of fields in the bigger table. This second form is used as a subform, and I want to save the edits made to the subform onto the main table.

I have tried to use a simple SQL Update query, but for some unkown reason I can't make it work and it is driving me crazy.

    Dim db As DAO.Database
    Dim qdfFN As DAO.QueryDef
    Dim rsFN As DAO.Recordset
    Set db = CurrentDb
    Set qdfFN = db.CreateQueryDef("")
    qdfFN.sql = "select * from [public_ficha_negocio] where [Codigo_RQM_Necesidad]='" & Me.Codigo_filtro.value & "';"
    qdfFN.ReturnsRecords = True
    Set rsFN = qdfFN.OpenRecordset
    Dim qdf As DAO.QueryDef
    Dim sSQL As String
    sSQL = "UPDATE [public_OUT_Detalle_PMC] SET " & _
              {a lot of fields are updated, I cut them out of the paste for simplicity}
              "WHERE [public_OUT_Detalle_PMC].[Codigo_RQM_Necesidad] = '" & rsFN!Codigo_RQM_Necesidad & "');"
    Set qdf = db.CreateQueryDef("", sSQL)
    qdf.Execute dbFailOnError
    qdf.Close
    qdfFN.Close
    rsFN.Close
    Set qdfFN = Nothing
    Set rsFN = Nothing
    Set qdf = Nothing

The query qdfFN is used to retrieve the record of the smaller table which data I want to "passthrough" to the bigger table. The query qdf is the one that, either doesn't work or I am not understanding how to use these vba objects.

I have used Debug.print to check the values of rsFN and they are correct, I mean, the values I want to pass onto the bigger table are there! This is cracking my head

Since I am a VBA and Access newbie I didn't know you have to single quote 'text' values, not quote numeric values and quote with hashes date values when doing an insert into in vba.


Syntax Error on Create Trigger CREATE TRIGGER new_trx_tmp_on_transaction_update AFTER UPDATE ON transaction
FOR EACH ROW
BEGIN
    IF NEW.trx_status = 1 AND ( NEW.cat_id = 'CASHBACK' || NEW.cat_id = 'REFUND') THEN
        INSERT INTO trx_tmp( `id`, `trx_id`, `user_id`, `trx_merchant_trxid`, `trx_amount`, `amount_left`, `cat_id`, `created_at`, `updated_at` ) VALUES ( NULL, NEW.id, NEW.userid, NEW.trx_merchant_trxid, NEW.trx_amount, NEW.trx_amount, NEW.cat_id, NOW(), NOW() );
    END IF;
END

I can't find where I'm doing wrong:

  [ERROR in query 1] You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near '' at line 5

You just need to change the command delimiter that the client is looking for in order to split commands:

delimiter $$
CREATE TRIGGER new_trx_tmp_on_transaction_update AFTER UPDATE ON transaction
FOR EACH ROW
BEGIN
    IF NEW.trx_status = 1 AND ( NEW.cat_id = 'CASHBACK' || NEW.cat_id = 'REFUND') THEN
        INSERT INTO trx_tmp( `id`, `trx_id`, `user_id`, `trx_merchant_trxid`, `trx_amount`, `amount_left`, `cat_id`, `created_at`, `updated_at` ) VALUES ( NULL, NEW.id, NEW.userid, NEW.trx_merchant_trxid, NEW.trx_amount, NEW.trx_amount, NEW.cat_id, NOW(), NOW() );
    END IF;
END
$$

The default is a semi-colon, hence it complaining at the end of the INSERT line when you try and create your trigger.

It's all explained in the documentation here.


Transaction basics. What is the result of 2 transactions running concurrently? I have a rather basic question on transactions.
Assume a table Account with a column amount. Now we have the following code:

BEGIN
$amount = SELECT amount FROM ACCOUNT WHERE id = 123;
$amount = $amount - 100;
UPDATE ACCOUNT SET amount = $amount WHERE id = 123;
COMMIT

Now if this snippet runs concurrently by 2 transactions, T1 and T2 what will happen?
I know that in MySQL by default the isolation level is REPEATABLE READ.
So let's say that amount initially is 500.
So when the 2 transactions finish will the final amount be 300 or 100?
I mean when they start both read from table which I guess is a shared lock.
When one updates the table it gets an exclusive lock right? So what happens to the other transaction? Will it proceed with the amount it "view" when it started i.e. 500?

There is no implicit shared lock in REPEATABLE READ isolation level.
You need to change the transaction sequence as follows:
ISOLATION LEVEL
You could switch to the SERIALIZABLE isolation level and disable autocommit. That way, the row is locked in shared mode implicitly.
If you want to stay with REPEATABLE READ or READ COMMITTED, you will have to call SELECT ... FOR UPDATE manually before doing the UPDATE.
QUERY (Optional Suggestion)
You should also experiment with
UPDATE ACCOUNT SET amount = amount - 100 WHERE id = 123;

along with disabled autocommit and SERIALIZABLE isolation level.
I suggested this because you are doing two queries (SELECT and UPDATE) to change the amount. You could just do it with one query.
Your Question
Since locking reads and not implicit with REPEATABLE READ, there is a possibility of 500 being used twice.
You asked

Is it possible to set the isolation level at a connection level?I.e. not touch the global isolation level?

Yes, you can set the isolation level within the session:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL   {
    REPEATABLE READ
  | READ COMMITTED
  | READ UNCOMMITTED
  | SERIALIZABLE    }


Replacing too many tables with too many columns? Background

We have a small MySQL database, which logs numeric data from devices. Each device has ~100 variables.

List of devices is stored in single table. deviceId, type and version are stored here.

Data is stored in tables, which have a name in the form of data_<deviceId>_<variable>. Each of these tables stores basically 2 pieces of data: timestamp and a variable value (double type).

What above means that each time new device is added, system creates ~100 new tables automatically. There are currently several thousand MyISAM tables in the database. Needless to say, making changes to these tables is difficult.

Example

For simplicitys sake, let's say that we have 3 devices with only a few variables:

Device 1 has variables A and B. (Device type 1, version 1)
Device 2 has variables A and C. (Device type 1, version 2)
Device 3 has variables D, E and F. (Device type 2, version 1)

Given above, database has following tables:

devices, with columns (deviceId, type, version)
data_1_A, with columns (timestamp, value)
data_1_B, with columns (timestamp, value)
data_2_A, with columns (timestamp, value)
data_2_B, with columns (timestamp, value)
data_3_D, with columns (timestamp, value)
data_3_E, with columns (timestamp, value)
data_3_F, with columns (timestamp, value)

Problems

Too many tables
New tables are created on the fly

A better way

To reduce the amount of tables, consider following:

Solution #1: Device version specific tables

There would be unique table for each device type. So we'd have tables:

devices, with columns (deviceId, type, version)
data_type_1_version_1, with columns (timestamp, deviceId, valueA, valueB)
data_type_1_version_2, with columns (timestamp, deviceId, valueA, valueC)
data_type_2_version_1, with columns (timestamp, deviceId, valueD, valueE, valueF)

Problem is that we have to add new table each time new device type or version is introduced. We also don't have generic way of reading values from tables as each table has different columns. Won't work.

Solution #2: 1 Data table with generic columns

devices, with columns (deviceId, type, version, varName1, varName2, varName3, ...)
data, with columns (timestamp, deviceId, value1, value2, value3, ...<enough columns for worst case>)

You would need to read devices table to understand what each value column contains. Should work.

Solution #3: Single data column and extra table for variable info.

devices, with columns (deviceId, type, version)
variables, with columns (varId, deviceId, varName)
data, with columns (timestamp, varId, value)

To read data from DB, you need to find out varId first from the variables table, and then read the actual data from the data table. Should work.

Question

Solution 2 seems simpler than 3, as it has less tables to deal with. But using solution 2, I would have 2 tables with over 100 columns each. Is that any improvement over original problem of having too many tables?

Solution 3 is the best solution provided that you move the deviceId from the variables table into the data table.

The reason why it is better is that when you need to add a variable to one device you can do this without changing your devices and data tables in solution 2. If you want to query the data in the solution 2 way then you can create views that look like the devices and data tables in solution 2.


Postresql, max returns all rows with same ids System

PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg19.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit

Issue

Need return one max value from table for every product_id (1 product_id has 10 store_ids) based on available_count.

Sql

select
    product_id, store_id, max(available_count)
from
    j_product_store_availability
group by
    product_id,
    store_id,
    available_count
order by
    product_id,
    available_count desc,
    store_id
--limit 1

Result

product_id store_id available_count

130475  4   7
130475  1   4
130475  8   4
130475  2   3
130475  5   3
130475  7   3
130475  10  3
130475  3   2
130475  6   2
130475  9   0
130479  1   0
130479  2   0
130479  3   0
...

It returns rows for every store_id. Need to return just one max value based on available_count.

Expected result

130475  4   7
130479  1   0
(next product_id)
...

Thanks.

Solution (Laurenz solution)

SELECT DISTINCT ON (product_id)
product_id,
store_id,
max(available_count)
from
j_product_store_availability
--where store_id = '7'
group by
product_id,
store_id,
available_count
ORDER by
product_id,
available_count desc;

If using a non-standard PostgreSQL construct is no problem, the easiest way would be

SELECT DISTINCT ON (product_id)
       product_id, store_id, available_count
FROM j_product_store_availability
ORDER BY product_id, available_count DESC, store_id;


Improve Parameterized Query Performance I have been tasked to improve the performance of a SQL Server 2008 R2 query (within a stored procedure) shown in the query execution plan link below.
It currently executes in around 7 seconds and needs to complete in 1-2 seconds if possible. Each execution of the slow query is always around 7 seconds. The results of the query trickle through. The tables being queried are large but not billions of rows.
Row counts

Depending on the parameters being passed the results can either be a few hundred rows (which executes sub second) to > 300K rows (this is slow).
I have included the slow query execution plan and indices that are being utilised by the optimiser. This is the plan for the quicker execution and STATISTICS IO, TIME:

There are two queries being executed. The first one is not the issue. It's the second one that I need assistance with. Although needed, removing the non SARGable predicate (AND FT.TripDistance < ( CONVERT(NUMERIC(10,0),FT.TripTime) * 83.33 )) makes slight difference to the speed

Removing the function [FN_GetLocalTime_FromUTC_BasedOnTZId] makes slight difference to the speed, here's the STATISTICS IO TIME removing both non SARG where clause and function: Here's the execution plan

Here's the output for the unchanged query except for adding a LOOP JOIN hint. This is slower.

I have noticed that in the slow unchanged query plan, the actual number of rows (300330) for the Index Seek on the FACT_trip_Statuses table is close to the final output (299887). However, the actual number of rows (4.87m) on the index seeks on the xFactTrip_Annex, FACT_Trip, and FACT_Trip_Attributes is way out. How do I fix this?  Adding OPTION RECOMPILE make little difference
I tried adding trace flag 4199 DBCC TRACEON (4199, -1); and retrying with and without JOIN hints, didn't help.
HASH join hint and TF 4199

LOOP join hint and TF 4199

No join hints and TF 4199

Indices
CREATE NONCLUSTERED INDEX IDX_FACT_Trip_StartDateUTC_VehicleKey_Includes ON dbo.FACT_Trip
(
    StartDateUTC ASC,
    VehicleKey ASC,
    EndDateUTC ASC
)
INCLUDE (   DriverKey,
    DrivingTime,
    TripDistance,
    TripTime)

CREATE NONCLUSTERED INDEX IX_xFactTrip_Annex_StartDateUTC_MonthlyProcessing ON dbo.xFactTrip_Annex
(
    StartDateUTC ASC
)
INCLUDE (   VehicleKey,
            Spd20Count, Spd20Distance,
            Spd30Count, Spd30Distance,
            Spd40Count, Spd40Distance,
            Spd50Count, Spd50Distance,
            Spd60Count, Spd60Distance,
            Spd70Count, Spd70Distance,
            SpdCat1,SpdCat2,SpdCat3,
            TotalIdling,
            PTOTime,
            TripFuel)

CREATE NONCLUSTERED INDEX IX_FACT_Trip_Attributes_StartDateUTC_VehicleKey_Includes ON dbo.FACT_Trip_Attributes
(
    StartDateUTC ASC,
    VehicleKey ASC,
    EndDateUTC ASC
)
INCLUDE (   Attribute0Distance,
    Attribute1Distance,
    Attribute2Distance,
    Attribute3Distance,
    Attribute4Distance,
    Attribute5Distance,
    Attribute6Distance,
    Attribute7Distance,
    Attribute8Distance,
    Attribute9Distance,
    Attribute10Distance)

CREATE NONCLUSTERED INDEX IDX_FACT_Trip_Statuses_StartDateUTC_VehicleKey_Includes ON dbo.FACT_Trip_Statuses
(
    VehicleKey ASC,
    StartDateUTC ASC,
    EndDateUTC ASC
)
INCLUDE (   HarshAccelerationCount,
    HarshBrakeCount,
    HarshBumpCount,
    HarshCorneringCount,
    ExcessIdleDuration,
    ExcessIdleCount,
    OverspeedCount)

CREATE NONCLUSTERED INDEX IX_StartDateUTC_VehicleKey_IsBP ON dbo.FACT_TripComments
(
    StartDateUTC ASC
)
INCLUDE (   VehicleKey, IsBusinessPrivate)

Probably a clustered index over the #xMobiles temp table can help you in the last Join

CREATE CLUSTERED INDEX IDX_xMobiles_VehicleKey ON #xMobiles
(
    VehicleKey ASC,
)

The best option is to create the index after populating the table.


SSRS 2008 Report toggling between images for various field values What is the best way for toggling between images on a SSRS report?

On the report I'm creating, there are lots of questions with Yes Or No answers and based on the value I've got for the question, I'd like to show the correct image.

For example we have gender:

  Famale
  Male

This report is for output only and is not going to be used for performing data entry.

For each record I have the value in the column 'Gender' whether the person is male or female.  I want to show a square with a tick mark in it next to the correct gender, while keeping the other option box empty (an empty square image).  This should function like a radio button.

So if the record has gender = 'Female', I should show a tick image next to Female text box and an empty square next to Male text box and, vice-versa, if gender = 'Male' on the record.

Any suggestions?

You can use expressions for this type of conditional work. To do this click on the image and select the properties tab in BIDS. Find the expression button (fx) and click it. Enter something like the below to get what you want:

  =Iif(Fields!Gender.Value = "Female", "Female.jpg", "Male.jpg")

This is assuming that you have have set up the images to be available as global resources to your report. If not, replace the image names with the paths to the images etc...

A good reference for expressions is below:

http://msdn.microsoft.com/en-us/library/ms157328.aspx

I hope this helps you.


User can alter procedures without permission I have a sql server user that our application is connecting as.  The user has been granted db_datareader and db_datawriter as well as Grant Execute on each stored procedure that it needs to use.

These are the permissions I see when I veiw the usermapping on the Login and when I view the user under the database.

Given these permissions I don't think the user should be able to alter a procedure; however, when my developer logs in as that user they are able to update the stored procedures.

Am I wrong about what these permissions allow?  Is there someplace I can look to find out about permissions that may not being showing up in the UI?  Or do I need to explicitly deny alter on the stored procedures for that user?

Do you see anything that has been granted or role membership that shouldn't exist?

EXECUTE AS USER = N'your user';
GO
SELECT * FROM sys.fn_my_permissions(NULL, NULL);
GO
REVERT;
GO

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(N'your user');
GO

SELECT r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your user';

SELECT r.name
FROM sys.server_role_members AS m
INNER JOIN sys.server_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.server_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your login';

It is possible these developers are in the db_owner role without your knowledge or have been granted ALL on the dbo or another schema.

Make sure you execute these statements in the right database, and also validate that your users are logging in as who they say they are logging in as. Perhaps they have the sa password and you don't know it. Finally, you should make sure that the database principal maps to the correct server-level login.

SELECT dp.name, sp.name
FROM sys.database_principals AS dp
FULL OUTER JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE
(
  dp.type <> 'R'
  OR sp.type NOT IN ('C', 'K')
)
ORDER BY
  dp.name COLLATE DATABASE_DEFAULT + sp.name COLLATE DATABASE_DEFAULT DESC;


Possible attack on my SQL server? Checking my SQL Server log I see several entries like this:

Date: 08-11-2011 11:40:42
Source: Logon
Message: Login failed for user 'sa'. Reason: Password did not match for the login provided. [CLIENT: 56.60.156.50]
Date: 08-11-2011 11:40:42
Source: Logon
Message: Error: 18456. Severity: 14. State: 8.

Date: 08-11-2011 11:40:41
Source: Logon
Message: Login failed for user 'sa'. Reason: Password did not match for the login provided. [CLIENT: 56.60.156.50]
Date: 08-11-2011 11:40:41
Source: Logon
Message: Error: 18456. Severity: 14. State: 8.

And so on..
Is this a possible attack on my SQL Server from the Chinese???!
I looked up the IP address at ip-lookup.net which stated it was Chinese.

And what to do?

Block the IP adress in the firewall?
Delete the user sa?

And how do I protect my web server the best?!

Thanks in advance!

If you have a firewall, why is the database server exposed to the internet?

The firewall should block all access to BOTH servers apart from the ports required. Typically this would be 80 (http) and 443 (https) to the web server ONLY.
If (and only if) an external service requires access to the SQL Server, permit access to the specific IP addresses required at the firewall. This should occur via a VPN connection, not openly exposed 1433.
Create a new administrator account and disable the default 'sa'.
Preferably, switch from using "mixed mode" authentication to windows accounts.


Minimizing Indexed Reads with Complex Criteria I'm optimizing a Firebird 2.5 database of work tickets.  They're stored in a table declared as such:

CREATE TABLE TICKETS (
  TICKET_ID id PRIMARY KEY,
  JOB_ID id,
  ACTION_ID id,
  STATUS str256 DEFAULT 'Pending'
);

I generally want to find the first ticket that hasn't been processed and is in Pending status.

My processing loop would be:

Retrieve 1st Ticket where Pending
Do work with Ticket.
Update Ticket Status => Complete
Repeat.

Nothing too fancy.  If I'm watching the database while this loop runs I see the number of indexed reads climbs for each iteration.  The performance doesn't seem to degrade terribly that I can tell, but the machine I'm testing on is pretty quick.  However, I've received reports of performance degradation over time from some of my users.

I've got an index on Status, but it still seems like it scans down the Ticket_Id column each iteration.  It seems like I'm overlooking something, but I'm not sure what.  Is the climbing number of indexed reads for something like this expected, or is the index misbehaving in some way?

-- Edits for comments --

In Firebird you limit row retrieval like:

Select First 1
  Job_ID, Ticket_Id
From
  Tickets
Where
  Status = 'Pending'

So when I say "first", I'm just asking it for a limited record set where Status = 'Pending'.

The degradation over time occurs due to the increased number of items that are in the "Complete" status. Think about this for a second - you won't get any performance degradation when testing as you probably have a small number of rows with the status as "Complete". But in production, they may have millions of rows with the "Complete" status and this number will increase over time. This, essentially, makes your index on Status less and less useful over time. As such, the database probably just decides that because Status almost always has the value 'Complete', it will just scan the table instead of using the index.

In SQL Server (and maybe other RDBMS's?), this can be worked around using Filtered Indexes. In SQL Server you would add a WHERE condition onto the end of your index definition to say "apply this index only to records with a Status <> 'Complete' ". Then any query using this predicate will most likely use the index on the small amount of records not set to 'Complete'. However, based on the documentation here: http://www.firebirdsql.org/refdocs/langrefupd25-ddl-index.html, it does not look like Firebird supports filtered indexes.

A workaround is to put 'Complete' records in an ArchiveTickets table. Create a table with the exact same definition (though without any auto generated ID) as your Tickets table and maintain rows between them by pushing 'Complete' records to the ArchiveTickets table. The Index on your Tickets table will then be over a much smaller number of records and be much higher performance. This will likely mean you will need to change any reports etc that reference 'Complete' tickets to point to the Archive table or perform a UNION across both Tickets and ArchiveTickets. This will have the advantage of not only being fast, but will also mean that you can create specific indexes for the ArchiveTickets table to make it perform better for other queries (for instance: Give me the average time to completion for complete tickets) which are not needed on the Tickets table.

You should be concerned with this if your production is going to go into the thousands of rows. Performance will degrade over time and negatively impact your user experience.


How to do 10 G Database recovery without archive logs but available redo logs, ctl files and .dbf files My client's Oracle 10g database crashed abruptly on Win 2003 NT. The oracle was installed on C drive and redo logs, ctl files and .dbf files were in D:
It was unable to backup C: but successfully backup D:
Later the system was rebuild with updated RAM and high disk size. Oracle is re_install in C: as it was and latest .dbf, CTL and redo logs in D:

Not sure if the below steps would solve the purpose. Please suggest ASAP.

SQL> startup nomount;
SQL> alter system set control_files
Then you mount the database so control file can be read and written
SQL> alter database mount;
SQL> alter database rename file for .dbf files
SQL> alter database rename file for redo logs

If it would be using PFILE then will use Init.ora to update the location.

SQL>startup mount
SQL> ALTER DATABASE OPEN;

Alright, let's assume you have full backup without archivelog. And your backup is output from rman backup such as rman> BACKUP DATABASE;.

So, I'll explain how to restore it.

1) Make sure your DBID is same as your database. (Note : this process is used if you have full backup datafiles,controlfiles,spfile, and copy archivelog if you have.)

SQL> SELECT DBID,OPEN_MODE FROM V$DATABASE; (if you can access your database)
RMAN> SHUTDOWN IMMEDIATE;STARTUP NOMOUNT;
RMAN> SET DBID=your_db_id

ex : G:\rman_backup is your backup location.
If you just to restore your controlfile and datafile, ignore process number 1, continue to process 2.

2) Make sure your autobackup location of CONTROLFILE, SPFILE and DATAFILE also ARCHIVELOG ex : "D:\FLASH_RECOVERY_AREA\your_sid\AUTOBACKUP\2016_06_16\O1_MF_S_914709345_CP5HHNCS_.BKP" or"G:\rman_backup\O1_MF_S_914709345_CP5HHNCS_.BKP".

In this example i assume you have activate your autobackup controlfile, if you don't need to restore your spfile skip this. you can next to restore control file.

RMAN> RESTORE SPFILE TO 'C:\app\Administrator\SPFILESIMKARI.ORA' FROM 'G:\rman_backup\O1_MF_S_914709345_CP5HHNCS_.BKP';
RMAN> SHUTDOWN IMMEDIATE;STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM 'G:\rman_backup\O1_MF_S_914709345_CP5HHNCS_.BKP';
RMAN> SHUTDOWN IMMEDIATE;STARTUP MOUNT;
RMAN> LIST BACKUP;
RMAN> CATALOG START WITH 'G:\rman_backup'; -- This is optional, if your control file can show "list backup" you don't need to run this. If not, you must to run this script.
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN; -- If you need to reset your sequence, or error you can "alter database open resetlogs", if not "alter database open noresetlogs";
RMAN> exit

For additional information, you can startup unmount if your SPFILE is configured properly. And `startup mount' if your controlfile is configured properly.
Hope this help you out, please comment if you've trouble.


Database design for threading messages I am not sure whether this question is suitable for this forum, but since it says Database Administrator I hope I am right. So my question is, whats the database design or business logic for creating an app for messaging between users?

I am having difficulties with choosing how to approach the relationship between each Conversation of User Thread.

If anyone could guide me through as to how all this threading message works (like Gmail or Facebook), I will be really grateful.

Edit:

With such a helpful guide and the tables below, I came up with my models. But having just a slight confusion, as to how to start a new thread.

Tables:

My models:

class Thread(models.Model):
    pass

class ThreadParticipant(models.Model):
    thread = models.ForeignKey(Thread)
    user = models.ForeignKey(User)

class Message(models.Model):
    thread = models.ForeignKey(Thread)
    sent_date = models.DateTimeField(default=datetime.now)
    body = models.TextField()
    user = models.ForeignKey(User)

class MessageReadState(models.Model):
    message = models.ForeignKey(Message)
    user = models.ForeignKey(User)
    read_date = models.DateTimeField()

As the Thread class has nothing in it, how do I create a new thread? Do I place an integer in Thread and increment it? Or do I use the Primary Key itself as the attribute to create a new thred? Please kindly explain it to me. Thank you agian!

A very similar question was asked on StackOverflow.  Have a look at my answer there.  I recommend a data model and give specific advice about the SQL to list of all messages in a thread, with read state and get all threads with latest message in each thread for a given user.


Pagination performance, with subquery, inner join and where I am trying to optimize following query (this is most simplified version I could came up with):

SELECT tr.Id, StatusDate
FROM (
    SELECT tr.Id, tr.StatusDate
    FROM mon.ArchivedTaskResults_201504 as tr WITH (NOLOCK)
    INNER JOIN mon.ViewDevicesWithGroups dev WITH (NOLOCK) ON tr.DeviceId = dev.Id
    WHERE tr.ClientId = 4 AND dev.Deleted = 0
) AS tr
ORDER BY StatusDate DESC
OFFSET 1000000 rows
FETCH NEXT 25 ROWS ONLY

The problem is, query performance is directly proportional to OFFSET - for offset=0 query executes in 0.0s, but for offset=1000000 execution time is about 23s (and with even greater offset, it can take up to few minutes).

I'm almost sure that my problem could be solved with appropriate clustered index on ArchivedTaskResults table, but after trying for few hours I still haven't found good index yet.

ArchivedTaskResults tables are really big, having about 50000000 rows (50 M)

Additional information:

I would be extremally happy if someone could solve problem I described above, but to be honest, my REAL query is even more bizarre (disclaimer: I am not the one that designed this database):

SELECT tr.Id, StatusDate
FROM (
    (
        SELECT tr.Id, StatusDate
        FROM mon.TaskResults as tr WITH (NOLOCK)
        INNER JOIN mon.ViewDevicesWithGroups dev WITH (NOLOCK) ON tr.DeviceId = dev.Id
        WHERE tr.ClientId = 4 AND dev.Deleted = 0
    ) UNION ALL (
        SELECT tr.Id, tr.StatusDate
        FROM mon.ArchivedTaskResults_201504 as tr WITH (NOLOCK)
        INNER JOIN mon.ViewDevicesWithGroups dev WITH (NOLOCK) ON tr.DeviceId = dev.Id
        WHERE tr.ClientId = 4 AND dev.Deleted = 0
    ) UNION ALL (
        SELECT tr.Id, tr.StatusDate
        FROM mon.ArchivedTaskResults_201505 as tr WITH (NOLOCK)
        INNER JOIN mon.ViewDevicesWithGroups dev WITH (NOLOCK) ON tr.DeviceId = dev.Id
        WHERE tr.ClientId = 4 AND dev.Deleted = 0
    )
) AS tr
ORDER BY StatusDate DESC
OFFSET 1000000 ROWS
FETCH NEXT 25 ROWS

This is much more complex, because even queries with offset=0 have long execution time (I guess its because sql server have no idea that ArchivedTaskResults_201505 comes 'after' ArchivedTaskResults_201504, and tries to sort them in memory, but it's only my blind shot).

It would be beyond my wildest dreams if someone managed to help me with THAT query, but if it's impossible because of strange database design, I guess I could solve it with software (I could query tables one at a time from my application, instead of doing everything in SQL. Or using stored procedure for that) - but only if my first query could be improved.

Thanks in advance.

I don't think you'll be able to get good performance while using OFFSET.  The database must search through 1,000,025 rows of output from the inner query; even if you have a good clustered index on TaskResults the system doesn't know for certain that it can skip ahead to date X.

But you do!  Assuming this is for some kind of GUI, make a note of the earliest StatusDate from the previous query, then use it to fitler next page:

SELECT
    tr.Id, StatusDate
FROM
    (
    SELECT tr.Id, tr.StatusDate
    FROM mon.ArchivedTaskResults_201504 as tr WITH (NOLOCK)
        INNER JOIN mon.ViewDevicesWithGroups dev WITH (NOLOCK) ON tr.DeviceId = dev.Id
    WHERE tr.ClientId = 4 AND dev.Deleted = 0
        AND
            (
            -- Retrieve only records from before the previous page
            tr.StatusDate < @PrevStatusDate
            OR (tr.StatusDate = @PrevStatusDate AND tr.Id < @PrevID)
            )
    ) AS tr
ORDER BY StatusDate, Id DESC
FETCH NEXT 25 ROWS ONLY

So if page #123 ends with 2015/05/01, record #234, you want to consider all records that are from 2015/04/30 or earlier, or which are also from 2015/05/01 but are for records #1 .. #233.

This should work well with your more complex UNION query, but "real" partitioning would probably be easier than this roll-yer-own partitioning..

If StatusDate is unique, or it's acceptable to occasionally show the same record on two adjacent pages, you can drop the @PrevID and ORDER BY Id bits.  If Id is always-increasing, you can filter off of it and skip StatusDate.

Keep in mind that retrieving pages like this can easily skip a record or include the same record twice if records are being adding, removed, or reordered in the underlying data.  But that's another topic.


Can I use the same filepath for recurring backups? I need to set a back up plan to cover a dev/pre-production server for a bit until a real DBA comes along. My plan is to run 3 T-SQL Jobs that basically do run the following statements

Nightly:

BACKUP DATABASE [MyDB]
TO DISK = 'F:\MSSQLSERVER\BU\MyDB.bak'
WITH CHECKSUM;

Hourly:

BACKUP DATABASE [MyDB]
TO DISK = 'F:\MSSQLSERVER\BU\MyDBDiff.bak'
WITH DIFFERENTIAL;

Every 5 minutes

BACKUP LOG [MyDB]
   TO  DISK = N'L:\MSSQLSERVER\Logs\MyDB.trn';

I am concerned that overwriting the last differential back up / log back up file is bad practice because I haven't been able to confirm that the new log/diff back up will cover the entire period from the last full db backup.

Can anyone confirm how this works in terms of what time range is covered by what back up file?

My hope is that in case of a 9:54 AM failure

MyDB.bak brings me up to midnight
MyDBDiff.bak Brings me up to 9
MyDB.TRN can bring me up to any time between 9 AM and 9:50 AM

Even though I have only one file from each job.

Am I crazy or on the right track?

As mentioned in the comments, backing up to an existing file name will, by default, append the latest backup to that file. For full and differential backups, at least, you don't want to do that.

For your purposes:

Once you have a new full backup, you don't need previous full, differential, or transaction log backups.
Once you have a new differential backup, you don't need previous differential backups or transaction log backups. Each differential backup contains all changes since the last full backup.
Once you have a new transaction log backup, you still need previous transaction log backups. Transaction log backups record new log entries since the last transaction log was taken.

Note that, for maximum safety, you should keep your previous full and differential backups until a new one is finished. If you overwrite your previous full backup with the new one, then as soon as SQL Server starts writing your full backup until it finishes you have no backup. With the full backup they are based on gone, differential and transaction log backups are useless.

If you really want to take that risk: To write to the same backup file, overwriting any existing backups in it, use the following options:

WITH INIT, SKIP, FORMAT

My recommendation: Just as noted by sp_BlitzErik in the comments, use Ola Hallengren's free maintenance scripts, as they will do all the "heavy lifting" for you.

Just to cover the option, depending on the edition of SQL Server you're using, keep in mind that you probably have the option to set up SQL Server maintenance plans to do these backups. Those include a task that will remove files older that however much time you specify. However, I'm assuming that you haven't mentioned those because you either don't have access to use those, or don't want to use them.

If both of those options are out, but you can write a script (in the language of your choice) to delete files more than 2 days old, and can hold up to two days' worth of your backup files on disk:

Set up your jobs as planned, with one change: append the type of backup and the current date and time to the file names. For example:

BACKUP DATABASE [MyDB]
TO DISK = 'F:\MSSQLSERVER\BU\MyDB_FULL_'
          + REPLACE(REPLACE(REPLACE(CONVERT(varchar(30), GETDATE(), 120),'-',''),':',''),' ','_')
          + '.bak'
WITH CHECKSUM;

This would create a full backup file named F:\MSSQLSERVER\BU\MyDB_FULL_20170928_191216.bak in your chosen directory (if run today, at 7:12:16 PM).

I would use MyDB_DIFF_ as the prefix for differential backups, and MyDB_TRAN_ for transaction logs. Note that full and differential backups should be have the extension .bak, while transaction log backups would have the extension .trn.
Then, set up a "clean-up" job to run right before you run your full backup. This will run your script that deletes any files in the F:\MSSQLSERVER\BU\ folder that are more than 48 hours old. This should ensure that you have at least one full backup, and the differential and t-log backups that depend on it, even if the script was accidentally run at noon instead of midnight.

By running it right before you run your full backup, you also ensure that you have the maximum available space for that full backup.

You can always choose to be fancier with the "clean-up" script; since each of the three file types starts with the same prefix, you could delete differential and transaction log backups more than (say) 3 hours old, and still have a good cushion.

Unless you already have a script that would do the deletions for you around to use, I again highly recommend you use Ola Hallengren's scripts. Keep in mind that, unlike whatever you could whip up on your own, they've already been tested by a lot of people the world over.


How to link rows in a many-to-many relationship where types have parents, grandparents etc In PostgreSQL 10.4, I have a model/table category with a Foreign Key to itself. The hierarchy can extend to any number of levels: parent, grandparent etc.

I have a model/table product. Each product can have multiple categories and each category can have multiple products.

category table:

id | name  | parent_id
---+-------+-----------
1    name1   null
2    name2   1
3    name3   2
4    name4   1
5    name5   4
6    name6   null

cat_prod relation table:

id | name | product_id | category_id
---+------+------------+-------------
1    name1  1            1
2    name1  1            2
3    name2  2            1
4    name3  3            1
5    name4  4            2
6    name5  5            2
7    name6  6            3

product table:

id | name
---+-------
1    name1
2    name2
3    name3
4    name4
5    name5

If I want to get all products that 'are' in category with id 1 (including subcategories) I need to get products from 1 and 2, 3, 4, 5 because they are children, grand_children of 1.

If I link/add a product to a category (ex:5), what is the best approach?

Every time I add a product to a subcategory add it also to the
parent, grandparent etc

Ex- add it also in 4 and 1 - this adds a lot of rows in database for the product category relation, but needs one query to retrieve products.
Add link just to 5 and use recursive

Just one row in database, but many calls to a db at retrieval.
Another approach

I need also to take in consideration that a Product can be in multiple categories.

Option 2.

Only save a row for the assigned category, no extra rows for category parents. Don't store redundant information in your tables. That's bad for general performance and for maintenance. (Also don't save the name of the product in table prod_cat redundantly)

A recursive query on category is cheap and simple. You might encapsulate it in a function for convenience:

CREATE OR REPLACE FUNCTION f_cats_of_id(_id int)
  RETURNS TABLE (category_id int) AS
$func$
WITH RECURSIVE cte AS (
   SELECT $1 AS parent_id  -- not checking existence, cheapest

   UNION ALL
   SELECT c.id
   FROM   cte
   JOIN   category c USING (parent_id)
   )
TABLE cte
$func$  LANGUAGE SQL;

And this query to ...

  ... get all products that 'are' in category with id 1 (including subcategories)

SELECT DISTINCT p.*  -- assuming you want all columns of table product.
FROM   f_cats_of_id(1)
JOIN   cat_prod cp USING (category_id)
JOIN   product p ON p.id = cp.product_id;

DISTINCT because ...

  ... a Product can be in multiple categories

The same product might be listed more than once, via category and sub-category. DISTINCT folds duplicates.

db<>fiddle here


SQL Server Simplify Racing condition query We import multiple records per second from different flat files. Sometimes we encounter a racing condition, and duplicate Unique error constraint. We are inserting and retrieving records,

I wanted to simplify the following query, so I do not have to select for customerId twice, a local variable is not working in the Exists statement

CREATE TABLE dbo.Customer
(
    RowId bigint IDENTITY(1,1) NOT NULL,
    CustomerId guid NOT NULL,
    Name varchar(255) NOT NULL,
    CONSTRAINT PK_RowId PRIMARY KEY CLUSTERED([RowId] ASC)
)
create unique nonclustered index [UN_CustomerId] ON [dbo].[Customer] ([CustomerId] ASC) include (Name)
create nonclustered index [UN_Name] ON [dbo].[Customer] ([Name] ASC) include (CustomerId)

BEGIN TRANSACTION

IF NOT EXISTS
(
    SELECT top 1 CustomerId
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE Name = @Name
)
BEGIN
    INSERT dbo.Customer(CustomerId, [Name])
    OUTPUT @CustomerId AS CustomerId
    VALUES (@CustomerId, @Name);
END
ELSE
BEGIN
    SELECT CustomerId FROM dbo.Customer WHERE [Name] = @Name;
END

COMMIT TRANSACTION;

Option with local variable not working:

declare @CustomerIdVar uniqueidentifier

BEGIN TRANSACTION

IF NOT EXISTS
(
    SELECT @CustomerIdVar = CustomerId  --- this was causing compilation error
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE Name = @Name
)
BEGIN
    INSERT dbo.Customer(CustomerId, [Name])
    OUTPUT @CustomerId AS CustomerId
    VALUES (@CustomerId, @Name);
END
ELSE
BEGIN
    SELECT @CustomerIdVar
END

COMMIT TRANSACTION;

This is not valid syntax:

IF NOT EXISTS
(
    SELECT @CustomerIdVar = CustomerId  --- this was causing compilation error

You could try:

DECLARE @CustomerIdVar uniqueidentifier;

BEGIN TRANSACTION;

    SELECT @CustomerIdVar = @CustomerId
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE [Name] = @Name

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT dbo.Customer(CustomerId, [Name])
        OUTPUT @CustomerId AS CustomerId
        VALUES (@CustomerId, @Name);
    END;
    ELSE
    BEGIN
        SELECT @CustomerIdVar AS CustomerId;
    END;

COMMIT TRANSACTION;

Or try the merge option I posted in answer to your previous question SQL Server Concurrent Updlock Serializable vs Try Catch.

But these sorts of micro-optimizations are not always as useful as you might think. Test carefully to make sure you actually get better performance.

And avoid nonsense like:

IF NOT EXISTS
(
    SELECT top 1 CustomerId
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE Name = @Name
)

The EXISTS already implies finding at most one row. The top 1 and CustomerId are entirely pointless, and the same as:

IF NOT EXISTS
(
    SELECT *
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE Name = @Name
)


Functional dependencies (hopefully simple question) Consider the simple problem of keeping track of Students:

STUDENT(
   StudentNumber,
   StudentName,
   Dorm,
   RoomType,
   DormCost,
   Club,
   ClubCost,
   Sibiling,
   Nickname
)

Would StudentName functionally determine StudentNumber? Is there not enough information? Is there a rule of thumb about this kind of problem?

No, the name of a student doesn't determine his or her identity and hence the student number.

There can be more than one person with the same name, but they still would have a different student number.

This is the reason why databases usually have artificial numeric identifiers like that, and the reason why you have customer IDs, social security numbers and the like.

When choosing a candidate for a primary key, use the following rules of thumb:

Does the attribute uniquely identify the row? Names will not.
Can the attribute ever be changed? Names can be changed, e.g. when you marry.

Mutable attributes are no good choice for primary keys in a database, because they make referential integrity via foreign keys a mess.

In most practical cases, the primary key of a table will be an artificially generated number.


Can I optimize for the zIIP processor? Is it possible to change SQL in a z/OS mainframe COBOL application so that it becomes eligible to be directed to the IBM System z Integrated Information Processor (zIIP)?

I posted this answer in response to the same question on StackOverflow, so here is a re-post:

An important distinction to make is that according to IBM, zIIP is only available for "eligible database workloads", and those "eligible" loads are mostly targeted for large BI/ERP/CRM solutions that run on distributed servers, which are connecting through DDF (Distributed Data Facility) over TCP/IP.

IBM has a list of DB2 workloads that can utilize zIIP.  These include:

DDF server threads that process SQL requests from applications that
access DB2 by TCP/IP (up to 60%)
Parallel child processes. A portion of each
child process executes under a dependent enclave SRB if it processes
on behalf of an application that originated from an allied address
space, or under an independent enclave SRB if the processing is
performed on behalf of a remote application that accesses DB2 by
TCP/IP. The enclave priority is inherited from the invoking allied
address space for a dependent enclave or from the main DDF server
thread enclave classification for an independent enclave. (Versions up
to 11 allowed 80% to run on zIIP, v12 upped this to 100% eligible).
Utility index build and maintenance processes for the LOAD, REORG, and
REBUILD INDEX utilities.

And if you're on DB2 v10, you can also use zIIP with:

Remote native SQL procedures.
XML Schema validation and non-validation parsing.
DB2 utility functions for maintaining index structures.
Certain portions of processing for the RUNSTATS utility.
Prefetch and deferred write processing for the DB2 buffer pool

Version 11 added the following:

Asynchronous enclave SRBs (service request blocks) that execute in the
Db2 ssnmMSTR, ssnmDBM1 and ssnmDIST address spaces, with the exception
of p-lock negotiation processing. These processes include Db2 buffer pool
processing for prefetch, deferred write, page set castout, log read, and
log write processing. Additional eligible processes include index
pseudo-delete and XML multi version document cleanup processing.

Version 12 allowed parallel child tasks to go 100% to zIIP after a certain threshold of CPU usage.

So, if you're using COBOL programs, it would appear that IBM does not intend for you to use zIIP with these workloads.  You can still take advantage of zIIP with utilites (LOAD, REORG), and some steps of the RUNSTATS utility, so it may still be worthwhile to have some zIIP.


Deleted rows but database size is still large I am using SQL Server 2008 Express which has maximum database size limit of 10 GB. Every month I hit the limit and the application can't store more data.

To overcome this problem we run a job every month which takes backup of the database for last 30 days and deletes the data for those 30 days from the respective tables. After delete also the size remains high.

Shrinking the database, truncating the tables, table clean up, Shrink the database and rebuild the index, delete the tables and recreate them. Every time shrinking the database is not good so kept as a last option.

Apart from this is there anything else that I can try?

Do all tables have clustered indexes? Without this, rebuilding indexes won't help you.

If you haven't and don't want to add them, your only option is to use DBCC SHRINKFILE with NOTRUNCATE to force the data pages to compact


T-SQL Update Random String Value in Column I have a table that contains a file path column containing data like this.

C:\ABC\Files\AA.dat
C:\ABC\Files\AA.Unbound.qp
C:\DEF\AA\GGG Build 1 Modules\Random123.qpp
C:\DEF\AA\GGG Build 1 Modules\Random456.qpp
C:\GH\DC.Random789.qpp
C:\IJK\Random987.qpp

I need to replace the file path to a generic path before the actual file name, so everything before the final \ needs to change but keep the same file names, something like this

D:\FILES\AA.dat
D:\FILES\AA.Unbound.qpp
D:\FILES\Random123.qpp
D:\FILES\Random456.qpp
D:\FILES\DC.Random789.qpp
D:\FILES\Random987.qpp

Table has around 2000 rows and if possible I want to update in a single script. Running SQL 2008R2 and this is first stage of finally upgrading so any help would be appreciated.

One possible approach is to reverse the full filenames and find the actual file names.

Table:

CREATE TABLE Data ([FileName] varchar(100))
INSERT INTO Data([FileName])
VALUES
    ('C:\ABC\Files\AA.dat'),
    ('C:\ABC\Files\AA.Unbound.qp'),
    ('C:\DEF\AA\GGG Build 1 Modules\Random123.qpp'),
    ('C:\DEF\AA\GGG Build 1 Modules\Random456.qpp'),
    ('C:\GH\DC.Random789.qpp'),
    ('C:\IJK\Random987.qpp'),
    ('Random000.qpp')

Statement:

UPDATE Data
SET [FileName] = CONCAT(
   'D:\FILES\',
   REVERSE(LEFT(REVERSE('\' + [FileName]), CHARINDEX('\', REVERSE('\' + [FileName])) - 1))
)

Result:

FileName
D:\FILES\AA.dat
D:\FILES\AA.Unbound.qp
D:\FILES\Random123.qpp
D:\FILES\Random456.qpp
D:\FILES\DC.Random789.qpp
D:\FILES\Random987.qpp
D:\FILES\Random000.qpp


SnapManager Restore Wizard Slow I don't know if NetApp SnapManager is a niche product.  But the Restore Wizard is very slow, and I was wondering if anyone else had encountered this problem.  I kill the SPID after 15 minutes of waiting.  For DR, we have would to completely avoid the restore wizard, and instead do a clone copy to development, and copy data from there.

The waiting happens on the screen that says "Double-click to select a backup under the database you want to restore."  After double-clicking, that's when the waiting starts.  Looking at the database activity, SnapManager is gathering the database names from each backup in the snapshot. ( RESTORE FILELISTONLY )

The problem is that there are 38 databases listed under the snapshot, with 40 items each, which means it is trying to restore file lists 1,500 times.

The only reason I would use this feature is in an emergency situation, but it's so slow that it won't cut it.

Example:
Under backup "sqlsnap__SQL_09-15-2013_23.36.08__Daily", it looks like this on the right pane:

Database - 1 Admin
Admin 09-15-2013_23.36.08
Admin 09-15-2013_23.56.19
Admin 09-16-2013_00.11.20
Admin 09-16-2013_00.26.15
Admin 09-16-2013_00.41.15
Admin 09-16-2013_00.56.16
...and so on... on to the next database
Database - 38 WHD
WHD 09-15-2013_23.36.08
WHD 09-15-2013_23.56.19
WHD 09-16-2013_00.11.20
WHD 09-16-2013_00.26.15
WHD 09-16-2013_00.41.15
WHD 09-16-2013_00.56.16

For a total of over 1,500 items.

Did we configure something incorrectly?

You can kill the task on the SQL Server that is verifying the log files, then you will be able to click Next to get to the next screen in the Wizard.

Just use the query below to find the task running under smsql, and use the kill command to remove the SPID.

The wizard will show a popup error, but will still allow you to continue to the next screen.

Here is a query to help find the SPID to kill:

DECLARE @who2 TABLE(
    [SPID] INT,
    [Status] VARCHAR(200),
    [Login] VARCHAR(200),
    [HostName] VARCHAR(200),
    [BlkBy] VARCHAR(20),
    [DBName] VARCHAR(200),
    [Command] VARCHAR(200),
    [CPUTime] BIGINT,
    [DiskIO] BIGINT,
    [LastBatch] VARCHAR(20),
    [ProgramName] VARCHAR(200),
    [SPID2] INT,
    [RequestID] INT
)

INSERT @who2 EXEC sp_who2

select SPID, [Login], HostName, DBName, Command, LastBatch, [@who2].ProgramName from @who2
where [LOGIN] like '%smsql%'


PostgreSQL: Separate tables vs single table to perserve disk space? I have 2 tables with the below schemas with equal number of rows. When I run the SELECT relname, relpages FROM pg_class ORDER BY relpages DESC command, they show up as 23GB each even though the data types (total number of bytes) for thew two schemas result in different sizes . What is the best way to figure out how much space I would save by combining tables? Also, is there any way to determine, how much space each row is actually taking up?

                                 Table "public.table1"
Column        |           Type           |                      Modifiers
--------------+--------------------------+-----------------------------------------------------

 field1       | smallint                 |
 field2       | smallint                 |
 field3       | integer                  |
 field4       | smallint                 |
 timestamp    | timestamp with time zone |
 user_id      | integer                  |
 status       | boolean                  |
 id           | integer                  | not null default

                                  Table "public.table2"
 Column          |           Type           |                     Modifiers
 ----------------+--------------------------+----------------------------------------------------
 user_id         | integer                  |
 begin_timestamp | timestamp with time zone |
 end_timestamp   | timestamp with time zone |
 field           | smallint                 | not null
 id              | integer                  | not null default

Because the resulting combined table would have unused rows for records from each respective table, the likelihood is that you would not gain space, but lose it.

More importantly than that, I would highly discourage making an architecture decision of that nature for reasons of disk-space. Disks are cheap--a well designed and functional application is not. If there is an issue of run-time performance (ie all 46 gb is being loaded into memory/swap space) then you may have an optimization and tuning issue on your hands.


Why PostgreSQL can't create user? Use this ansible playbook to install PostgreSQL and create a user:

---
- name: Download PostgreSQL 10
  get_url:
    url: https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    dest: /usr/local/src/pgdg-redhat-repo-latest.noarch.rpm

- name: Install PostgreSQL 10 rpm
  yum:
    name: /usr/local/src/pgdg-redhat-repo-latest.noarch.rpm
    state: present

- name: Install epel-release rpm
  yum:
    name: epel-release
    state: present

- name: Install PostgreSQL 10
  yum:
    name: ['postgresql10-server', 'postgresql10']
    state: present
    update_cache: yes

- name: Update .bash_profile
  copy:
    src: bash_profile
    dest: /var/lib/pgsql/.bash_profile
    owner: postgres

- name: Source bash_profile
  shell: source /var/lib/pgsql/.bash_profile
  become: true

- name: Initialize the Database
  command: /usr/pgsql-10/bin/postgresql-10-setup initdb
  become: true
  ignore_errors: yes

- name: Set postgresql.conf
  copy:
    src: postgresql.conf
    dest: /var/lib/pgsql/10/data/postgresql.conf
    owner: postgres

- name: Set pg_hba.conf
  copy:
    src: pg_hba.conf
    dest: /var/lib/pgsql/10/data/pg_hba.conf
    owner: postgres

- name: Install repmgr repository
  get_url:
    url: https://dl.2ndquadrant.com/default/release/get/10/rpm
    dest: /var/lib/pgsql/repmgr

- name: Install repmgr rpm
  shell: bash /var/lib/pgsql/repmgr

- name: Install repmgr
  yum:
    name: repmgr10
    state: present

- name: Enable and Start PostgreSQL
  systemd:
    name: postgresql-10
    state: started
    enabled: yes

- name: Create repmgr user
  command: createuser -s repmgr
  become: true
  become_user: postgres
  ignore_errors: yes

It pending for a longtime, no response. What's the reason?

Even I login with psql try to create user by:

CREATE USER testuser;

The same it got pending without any response.

There must be a lock on a system catalog preventing the update. This is a pretty unusual occurrence.

Check pg_stat_activity for long running transactions that are not in state idle. Cancel those.
Look for prepared transactions in pg_prepared_statements and roll them back.

If both don't help, have a look at pg_locks while your CREATE USER hangs. It will show you for which transaction the statement is waiting.


SQL Server AG databases went into 'Synchronizing/In recovery' after upgrading to 2014 We have 3 replica AG setup:

2 replicas are in sync/automatic failover
the other(DR Server, different subnet) in asynchronous/manual mode
All these replicas were on SQL Server 2012.

Recently we upgraded the DR server to SQL Server 2014. Since then we've had a problem with the AG databases in the 2014 instance going into ‘Synchronizing/ in recovery’ state.

The SQL Server error log has the message:

  The recovery couldn’t start for the database ‘XYZ’

We tried to create a new database and add it to AG, which it works for the other two 2012 replicas, but on 2014 we see the same issue.

Is there any workaround to fix it, or is upgrading other replicas to 2014 the only available solution?

The Availability Group prerequisites all indicate that you must be using Enterprise Edition and be using the same version of SQL Server.

See the MSDN link for SQL Server 2012

https://msdn.microsoft.com/en-us/library/ff878487(v=sql.110).aspx

Under the topic:  Checklist: Prerequisites (Server Instance) it says:

  Each server instance must be running the Enterprise Edition of SQL
  Server 2012.

Likewise 2014 and 2016 have the same prerequisites for only those versions.

While my response was focused on the requirement in general as noted in MSDN. However, Kin in his comment added details on upgrade path:

"In OP's case, he was upgrading secondary to a higher version which should be fine (once failover is initiated, it will be one way ONLY - no role changes are allowed), and then he might upgrade the primary to 2014. This is how upgrades should be done."


Routinely deleting aged postgresql rows via cron I have a Django website with a postgresql 9.3.10 backend. The website contains dynamic user-generated content (think of it as a form of 9gag). There are some tables from which I routinely delete aged rows. The database is called dbname and I log into it via the user postgres. The following are two examples of such routine deletion:

begin;
DELETE FROM links_groupseen WHERE which_reply_id IN (SELECT id FROM links_reply where "submitted_on" < now() - interval '7 days');
DELETE FROM links_reply WHERE "submitted_on" < now() - interval '7 days';
commit;

begin;
DELETE FROM links_vote WHERE link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour');
DELETE FROM links_photoobjectsubscription WHERE which_link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour');
DELETE FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour';
commit;

There are ~10 other such operations I run. At the moment, I do everything manually - I want to automate these to run at a certain low-utlization time of day. How do I do that? Can someone give me an illustrative example?

As per my own rudimentary knowledge, I'm thinking I should first save the following shell script to /etc/cron.daily:

#!/bin/sh

dbname="dbname"
username="postgres"
psql $dbname $username << EOF
begin;
DELETE FROM links_groupseen WHERE which_reply_id IN (SELECT id FROM links_reply where "submitted_on" < now() - interval '7 days');
DELETE FROM links_reply WHERE "submitted_on" < now() - interval '7 days';
commit;
begin;
DELETE FROM links_vote WHERE link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour');
DELETE FROM links_photoobjectsubscription WHERE which_link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour');
DELETE FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour';
commit;
EOF

And then add the said script to crontab like so:

30 4 * * * /etc/cron.daily/mypgscript

And then service crond restart

A method that I find useful is to implement the cleanup code in my main programming framework (so Django, I guess, in your case) and expose that function via a URL. Then in my crontab I use wget or curl to invoke the cleanup URL.  That way the cron file is only responsible for the scheduling part, while the code that does the cleanup is kept together with the code that creates the data that needs to be cleaned up.


The rows column of EXPLAIN output is an estimate of the rows to be examined. Thus, value of this column and number of rows returned on executing the command can be different.


When to break large delete queries up? I've got an auto generated join table (three columns, two of them keys to other tables), which was recently corrupted during an aborted migration. As a result there are around 1 million duplicate rows that need to be removed. I've been reading into how to best do this and I've run into conflicting advice. I've managed to get a list of the duplicates which need to be deleted using this query:

SELECT MIN(id)
FROM my_join_table
WHERE site_id=42    -- The migrations targeted a single site.
GROUP BY content_id
HAVING COUNT(*)>1;

However, when I tried to run a simple delete query with the above as an inner query, the command timed out after several hours.

DELETE FROM my_join_table
WHERE id IN ( <insert above query here> );

So my question is two part:

Would breaking that large (~1 Million ids) set up into smaller delete queries be more efficient?
If so, what's a good candidate for smaller set sizes? From what I can see online, no one suggests breaking up sets of fewer than 100 elements, but I'm hoping that I can get away with 1,000 or even 10,000.

from experience i can say that one large delete is usually better. however, there can be some corner cases with IN, which might invalidate my statement but basically this is true.
Make sure your got enough work_mem around to allow PostgreSQL to nicely hash the IN.


SSMS Suggested Indexes and Compability Mode I am running an old production application which has a backend DB hosted on SQL2008R2 in Compatibility Mode 80 (SQL2000). When I run bits of TSQL to view actual execution plans SSMS is giving me suggested indexes which are only legal for SQL2005+ e.g. ones with included columns. How can I prevent it from doing this? I would like to view sensible indexes it might suggest.

NB: The Compatibility Mode can't be changed. Yes we are replacing the application, this will remove the need for the DB entirely but not for some time.

NB: Already tried, SSMS is configured "Script for server version" as SQL Server 2000.

I also have databases stuck at CL80, also on 2008r2: I've just tested and they allow indexes with INCLUDEed columns to be defined. I've not explicitly tested that they are properly used though so before relying upon this check appropriate queries to see what plans get created.

If the included columns are not used properly to avoid lookups, you could translate them to used columns, i.e. CREATE INDEX ix_test_u1_i2 ON tbl_test (i1) INCLDUE (i2) would become CREATE INDEX ix_test_u1_i2 ON tbl_test (i1, i2). Do benchmark the result though, or account for it in your index maintenance plans, because if the later columns are fairly random you could increase the level of index fragmentation (through page splits on insert/update) noticeably. - edit: as per Paul's comment, indexes created with INCLUDEed columns are used correctly by the query planner so this comment is moot. Of course if your application has instances that are still actually running on SQL2000 rather than just having CL set to 80, that changes this back...

As an aside: the missing index information displayed by SSMS should only ever be taken as a suggestion that you consider something - the suggestions can potentially be detrimental to the application overall especially if you follow all of them (the extra space used could be rather large).


UNION ALL implicit conversion for VARCHAR(MAX) column Is an implicit conversion in a UNION ALL when one of the data type is VARCHAR(MAX) something to worry about?

e.g.

DECLARE @value1 VARCHAR(1)= 'a';
DECLARE @value2 VARCHAR(MAX)= 'a';

SELECT @value1
UNION ALL
SELECT @value2;

If you look at the execution plan there will be a CONVERT_IMPLICIT warning:

  (Scalar Operator(CONVERT_IMPLICIT(varchar(max),[@value1],0))), (Scalar
  Operator([@value2]))

Is this going to behave as a normal conversion i.e. convert all the VARCHAR(1) columns to VARCHAR(MAX) and have an impact on performance? Is there a way to avoid it other than changing the table structure

Have a look at MS DOCS about Guidelines for Using Union:

Quoted from docs:

All select lists in the statements that are being combined with UNION must have the same number of expressions (column names, arithmetic expressions, aggregate functions, and so on).
Corresponding columns in the result sets that are being combined with UNION, or any subset of columns used in individual queries, must be of the same data type, have an implicit data conversion possible between the two data types, or have an explicit conversion supplied. For example, a UNION between a column of datetime data type and one of binary data type will not work unless an explicit conversion is supplied. However, a UNION will work between a column of money data type and one of int data type, because they can be implicitly converted.

 declare @v1 varchar(1) = 'a';
 declare @v2 varchar(max) = 'a';

 set statistics profile on;
 select @v1
 union all
 select @v2;

 select cast(@v1 as varchar(10))
 union all
 select cast(@v2 as varchar(10));
 GO

Rows | Executes | StmtText                                                                      | StmtId | NodeId | Parent | PhysicalOp    | LogicalOp     | Argument                                                  | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList  | Warnings | Type     | Parallel | EstimateExecutions
:--- | :------- | :---------------------------------------------------------------------------- | -----: | -----: | -----: | :------------ | :------------ | :-------------------------------------------------------- | :------------ | :----------- | :--------- | :---------- | ---------: | :--------------- | :---------- | :------- | :------- | :------- | :-----------------
2    | 1        | select @v1
union all
select @v2                                        |      1 |      1 |      0 | null          | null          | null                                                      | null          | 2            | null       | null        |       null | 2.157E-06        | null        | null     | SELECT   | False    | null
2    | 1        |   |--Constant Scan(VALUES:((CONVERT_IMPLICIT(varchar(max),[@v1],0)),([@v2]))) |      1 |      2 |      1 | Constant Scan | Constant Scan | VALUES:((CONVERT_IMPLICIT(varchar(max),[@v1],0)),([@v2])) | null          | 2            | 0          | 2.157E-06   |       4035 | 2.157E-06        | [Union1001] | null     | PLAN_ROW | False    | 1

Rows | Executes | StmtText                                                                                | StmtId | NodeId | Parent | PhysicalOp    | LogicalOp     | Argument                                                               | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList  | Warnings | Type     | Parallel | EstimateExecutions
:--- | :------- | :----------------------------------------------------------------------------------------- | -----: | -----: | -----: | :------------ | :------------ | :--------------------------------------------------------------------- | :------------ | :----------- | :--------- | :---------- | ---------: | :--------------- | :---------- | :------- | :------- | :------- | :-----------------
2    | 1        | select cast(@v1 as varchar(10))
union all
select cast(@v2 as varchar(10))            |      2 |      1 |      0 | null          | null          | null                                                                   | null          | 2            | null       | null        |       null | 2.157E-06        | null        | null     | SELECT   | False    | null
2    | 1        |   |--Constant Scan(VALUES:((CONVERT(varchar(10),[@v1],0)),(CONVERT(varchar(10),[@v2],0)))) |      2 |      2 |      1 | Constant Scan | Constant Scan | VALUES:((CONVERT(varchar(10),[@v1],0)),(CONVERT(varchar(10),[@v2],0))) | null          | 2            | 0          | 2.157E-06   |         16 | 2.157E-06        | [Union1002] | null     | PLAN_ROW | False    | 1

dbfiddle here


How to move data from Primary Data File to other data file without destroying replication I have one Database which is replicated using transactional replication. Now, I want do spare some data of that database to another partition, because my server running out of disk space.

I was trying to move file to another filegroup trough SSMS using GUI tolls by clicking design at table and at properties tab changing "Regular Data Space Specification" to "secondary". When I try to save desing of table I get error Cannot drop the table 'dbo.MYTABLE' because it is being used for replication.

Here are a few questions:

Should I create data files at another partition in already existing filegroup "PRIMARY"
or it is better to I open new filegroup and than in that new "secondary" filegroup to open new datafile?
Is there other way to I tell SQL to save data to another file besides dropping the table?
Is there way to I can temporarily exclude some tables from replication while I move them to another filegroup and then put them again in snapshot without having to reinitialize the subscriber ?

SSMS is creating a copy of the table, populating it, dropping the original. hence the error.

I'd create a new filegroup, rather then adding files to the primary filegroup because of how the files are used within a file group

You can move a table "in-situ" to a new filegroup by rebuilding the clustered index using CREATE INDEX .. DROP_EXISTING

CREATE NONCLUSTERED INDEX PK_Mytable
    ON dbo.Mytable(PKColumn)
    WITH (DROP_EXISTING = ON)
    ON NewFileGroup


Counting NULLs as zeroes and filtering with the WHERE statement I have a SQL puzzle:

How can we get a list of customers who have purchased 3 or fewer socks (including those who haven't purchased any socks) as well how many socks those customers have purchased?

I am running into a problem where I ended up with NULL values that need to be displayed as zeroes. However, when I use CASE statement to select NULLs as zeroes and then use WHEN statement to filter below certain threshold zeroes do not show up for some reason.

Here is my schema and some dummy values:

create table customers (
  cust_id TEXT,
  name TEXT
);

create table orders (
  order_id TEXT,
  cust_id TEXT,
  order_date DATE,
  num_socks INT
);

insert into customers values ('1001', 'Rudy');
insert into customers values ('1002', 'Jonny');
insert into customers values ('1003', 'Romeo');

insert into orders values ('1', '1001', '2015-05-01', 4);
insert into orders values ('2', '1001', '2015-04-03', 2);
insert into orders values ('3', '1003', '2015-04-10', 8);
insert into orders values ('4', '1003', '2016-05-10', 3);

Here is my query:

SELECT name, CASE WHEN socks_bought IS NULL THEN 0 ELSE socks_bought END AS socks_bought
FROM customers
LEFT JOIN
  (SELECT cust_id, sum(num_socks) AS socks_bought
   FROM orders
   GROUP BY cust_id) AS t1
    ON t1.cust_id = customers.cust_id
where socks_bought < 3;

However, it does not produce the desired output:

name | socks_bought
-------------------
Jonny | 0

What am I doing wrong? What is the query to produce the intended results? Thanks.

SELECT name, coalesce(socks_bought,0) AS socks_bought
FROM customers
LEFT JOIN
  (SELECT cust_id, sum(num_socks) AS socks_bought
   FROM orders
   GROUP BY cust_id) AS t1
    ON t1.cust_id = customers.cust_id
where coalesce(socks_bought,0) < 3;

+-------+--------------+
| name  | socks_bought |
+-------+--------------+
| Jonny | 0            |
+-------+--------------+


How do I recover my database after losing a tablespace with indexes? I'm thinking of moving all the indexes in my PostgreSQL 9.3 database to a tablespace on an SSD for improved performance. This SSD is not in a RAID array and I need to consider a scenario where it fails and the tablespace is lost.

The docs (and various blogs) warn that losing any tablespace probably leaves you with a database broken beyond recovery. Is this really the case, if I'm only losing my indexes?

I'm assuming that after failure I'll have a slow but functioning database which I can then safely reindex, perhaps using my default tablespace (or a new one on another SSD). Am I wrong? What would actually happen to my database, and how easy would it be to restore it to its pre-fail state?

How much improvement are you expecting from the SSD? It is surprising how little physical I/O actually happens on busy indexes and I don't think I've ever solved an actual performance problem with physical I/O devices.

I don't know enough about PostgresSQL to comment on buffering or recoverability and the like but I will say that choosing performance over recoverability is probably not a good practice.

I never want to find out that I have found a previously unknown recovery bug when my data is sitting in a tape on a shelf. I learned the DBA craft in the days before RAID - every night was a sleepless night. Always, always, always use RAID's.

If a user is complaining about performance, tell them you need a dozen SSD's to put in a 10 disk RAID5 array with hot failover. He'll leave with his head spinning but he might come back with a purchase order.

Good luck.


Nested aggregation and grouping on multiple columns in MySQL Consider the following product table (which is highly trimmed down):

`id` int AUTO_INCREMENT
`category_id` int
`subcategory_id` int
`vendor_id` int
`price` decimal(6,2)
`inserted_at` timestamp

For a given category ID, I am attempting to retrieve a list containing the vendor with the lowest latest price for each subcategory. With "latest" I mean that vendors may have multiple prices for a given category ID/subcategory ID combination, so only the most recently inserted price for that category ID/subcategory ID/vendor ID should be used. If there's a tie between 2 or more vendor's prices, the lowest id should be used as the tie-breaker.

For example, with this data:

id | category_id | subcategory_id | vendor_id | price | inserted_at
---------------------------------------------------------------------------
1  | 1           | 2              | 3         | 16.00 | 2015-07-23 04:00:00
2  | 1           | 1              | 2         |  9.00 | 2015-07-26 08:00:00
3  | 1           | 2              | 4         | 16.00 | 2015-08-02 10:00:00
4  | 1           | 1              | 1         |  7.00 | 2015-08-04 11:00:00
5  | 1           | 1              | 1         | 11.00 | 2015-08-09 16:00:00

So, first find the most recent prices for every subcategory/vendor combination (row with price=7.00 would be removed because it's not the most recent for that vendor in that subcategory). Then for subcategory 1 the lowest price would be 9 (so vendor_id = 2) and for subcategory 2 the lowest price is 16 (two vendors tie ()ids 3 and 4) so we choose the one with lowest vendor_id = 3).

I would expect the following results for category_id = 1:

subcategory_id | vendor_id | price
----------------------------------
1              | 2         |  9.00
2              | 3         | 16.00

Here's what I have so far. I feel like it's already starting to get out of hand and this doesn't even account for ties between 2 or more vendor's prices.

SELECT c.subcategory_id, c.vendor_id, c.price
FROM products AS c
JOIN
(
    SELECT MIN(a.price) AS min_price,
           a.subcategory_id
    FROM products AS a
    JOIN
    (
        SELECT  MAX(`inserted_at`) AS latest_price_time,
                vendor_id,
                subcategory_id
        FROM    products
        WHERE   category_id = 1
        GROUP BY vendor_id, subcategory_id
    ) AS b
    ON a.inserted_at = b.latest_price_time AND a.vendor_id = b.vendor_id AND a.subcategory_id = b.subcategory_id
    WHERE a.category_id = 1
    GROUP BY a.subcategory_id
) AS d
ON c.price = d.min_price AND c.subcategory_id = d.subcategory_id
WHERE c.category_id = 1

Before I go any further, I wanted to see if there was an easier way. When it comes to grouping/aggregating results of additional groupings/aggregations, is there a method that will give me the best performance (most important) and/or be easier to read (less important)?

This should work:

SELECT
    d.subcategory_id,
    d.vendor_id,
    MIN(d.price) AS price,
    d.inserted_at
FROM product AS d
JOIN (SELECT
        b.category_id,
        b.subcategory_id,
        b.vendor_id,
        a.last_iat
    FROM product AS b
    JOIN (SELECT
            a.category_id,
            a.subcategory_id,
            a.vendor_id,
            a.price,
            MAX(a.inserted_at) AS last_iat
        FROM product AS a
        GROUP BY a.category_id,a.subcategory_id,a.vendor_id
        ) AS a
        ON (a.category_id=b.category_id AND a.subcategory_id=b.subcategory_id AND a.vendor_id=b.vendor_id)
    GROUP BY b.category_id,b.subcategory_id,b.vendor_id) AS c
    ON (c.category_id=d.category_id AND c.subcategory_id=d.subcategory_id AND c.last_iat=d.inserted_at)
WHERE d.category_id=1
GROUP BY d.category_id,d.subcategory_id;

Test:

mysql> SELECT
    ->     d.subcategory_id,
    ->     d.vendor_id,
    ->     MIN(d.price) AS price,
    ->     d.inserted_at
    -> FROM product AS d
    -> JOIN (SELECT
    ->         b.category_id,
    ->         b.subcategory_id,
    ->         b.vendor_id,
    ->         a.last_iat
    ->     FROM product AS b
    ->     JOIN (SELECT
    ->             a.category_id,
    ->             a.subcategory_id,
    ->             a.vendor_id,
    ->             a.price,
    ->             MAX(a.inserted_at) AS last_iat
    ->         FROM product AS a
    ->         GROUP BY a.category_id,a.subcategory_id,a.vendor_id
    ->         ) AS a
    ->         ON (a.category_id=b.category_id AND a.subcategory_id=b.subcategory_id AND a.vendor_id=b.vendor_id)
    ->     GROUP BY b.category_id,b.subcategory_id,b.vendor_id) AS c
    ->     ON (c.category_id=d.category_id AND c.subcategory_id=d.subcategory_id AND c.last_iat=d.inserted_at)
    -> WHERE d.category_id=1
    -> GROUP BY d.category_id,d.subcategory_id;
+----------------+-----------+-------+---------------------+
| subcategory_id | vendor_id | price | inserted_at         |
+----------------+-----------+-------+---------------------+
|              1 |         2 |  9.00 | 2015-07-26 08:00:00 |
|              2 |         3 | 16.00 | 2015-07-23 04:00:00 |
+----------------+-----------+-------+---------------------+
2 rows in set (0.00 sec)

mysql>

Explain:

I used the index recommendation from @ypercube.

mysql> EXPLAIN SELECT d.subcategory_id, d.vendor_id, MIN(d.price) AS price, d.inserted_at FROM product AS d JOIN (SELECT b.category_id, b.subcategory_id, b.vendor_id, a.last_iat FROM product AS b  JOIN (SELECT a.category_id, a.subcategory_id, a.vendor_id, a.price, MAX(a.inserted_at) AS last_iat FROM product AS a GROUP BY a.category_id,a.subcategory_id,a.vendor_id ) AS a ON (a.category_id=b.category_id AND a.subcategory_id=b.subcategory_id AND a.vendor_id=b.vendor_id) GROUP BY b.category_id,b.subcategory_id,b.vendor_id) AS c ON (c.category_id=d.category_id AND c.subcategory_id=d.subcategory_id AND c.last_iat=d.inserted_at) WHERE d.category_id=1 GROUP BY d.category_id,d.subcategory_id;
+----+-------------+------------+-------+---------------+------+---------+--------------------------------------------+------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref                                        | rows | Extra                                        |
+----+-------------+------------+-------+---------------+------+---------+--------------------------------------------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL                                       |    4 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | d          | ALL   | q_ix          | NULL | NULL    | NULL                                       |    5 | Using where; Using join buffer               |
|  2 | DERIVED     | <derived3> | ALL   | NULL          | NULL | NULL    | NULL                                       |    4 | Using temporary; Using filesort              |
|  2 | DERIVED     | b          | ref   | q_ix          | q_ix | 15      | a.category_id,a.subcategory_id,a.vendor_id |    1 | Using where; Using index                     |
|  3 | DERIVED     | a          | index | NULL          | q_ix | 19      | NULL                                       |    5 |                                              |
+----+-------------+------------+-------+---------------+------+---------+--------------------------------------------+------+----------------------------------------------+
5 rows in set (0.00 sec)

mysql>


Copy On Write Transparent database for MySQL It is possible to create a transparent database to another production database server? I mean, testing new functions in a production database is too dangerous. What if we create a database which based on Copy On Write mechanism (because don't want to copy a big amount of data every time), and make some modification on that?  The original production database will not modified, only the COW database. After the test, the COW database can be dropped or saved, etc., but the original can operate without any kind of danger.

Doe this solution exist?  Has anyone implemented it?

I am not aware of anything doing this kind of operation within mysql itself. For Oracle there is something, I think it is called Oracle Workspace Manager, but I am not sure, there are a lot of options available.

For mysql I think you need to do this on storage level, e.g. use a filesystem with good cloning functionality like ZFS. Or a SAN.

Put the data part of the mysql instance on a seperate filesystem
Run prod instance like usual
Clone the data fs when needed (does not distured prod instance)
Configure a second mysql instance which uses the cloned fs as data dir
Fire up second instance, which will do a recovery now
Use second instance for testing

I have not setup this myself, so minor things might be missing, but generally this is a way to do this - and its not too complicated I think.

It will not help you for things like load testing as prod and test share the same storage subsystem, but for the usual tests it is fine.


Get attribute value of XML I went through at least 5 question on SE, but I must be doing something very bad out of frustration already.

I have a stored procedure that returns an XML like this:

<pmsg:Messages>
    <pmsg:Message Info="blah" />
</pmsg:Messages>

I need to get the value of Info.

DECLARE @xmlMessage XML;

EXEC procReturnsXml @xmlMessage OUTPUT;

 -- returns 0 rows
WITH XMLNAMESPACES('pmsg' AS pmsg)
SELECT msgs.msg.value('@Info', 'nvarchar(max)')
FROM   @xmlMessage.nodes('pmsg:Messages/pmsg:Message') msgs(msg);

 -- returns NULL
WITH XMLNAMESPACES('pmsg' AS pmsg)
SELECT @xmlMessage.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)');

-- returns NULL
WITH XMLNAMESPACES('pmsg' AS pmsg)
SELECT t.x.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)') AS INFO
FROM   @xmlMessage.nodes('/*') t(x);

Generally, I don't understand what these mean at all.
Can you provide an example exactly on this case, please?

I believe the reason you're getting these results is because the namespace for the xml is not the same as in the select statements.
I've altered your example to briefly explain each case.

declare @xmlMessage xml

set @xmlMessage ='<pmsg:Messages xmlns:pmsg="pmsg">
    <pmsg:Message Info="blah"/>
    <pmsg:Message Info="blah"/>
</pmsg:Messages>'

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT msgs.msg.value('@Info', 'nvarchar(max)') FROM @xmlMessage.nodes('pmsg:Messages/pmsg:Message') msgs(msg);
-- return twos rows 'blah'

The nodes() expression provides a set of two pmsg:Message nodes, and the select reads the value of @Info from each row.

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT @xmlMessage.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)');
--Returns a single rowset of one row containing 'blah'

This will only return a single row 'blah' because the [1] in the query restricts to the first item found.  Actually in this form the [1] is required. SQL will throw exeception of you dont include it becuase the expression must evaluate to a single value.

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT t.x.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)') as INFO FROM @xmlMessage.nodes('/*') t(x);
--Returns a rowset of one row containing 'blah'

Like the previous example, the xpath query evaluates to a single item and the nodes() expresson provides a row set on the top level node:


How to use timestamp/rowversion data types in Visio? I am using Visio 2010 to create a ER diagram. When I insert the column names, I can't find timestamp or rowversion data types.

Is there an alternative?