How I prevent deadlock occurrence in my application? I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database.
All the tables in the database have innodb engine. I also created indexes on each tables.
Now I am doing load testing using Jmeter version 2.9 locally for 200 users concurrently. During the load testing, in a specific page action I got Deadlock Found error. I changed my original query to the new one but again same error is occurring.

I have written save_interactions function which takes four parameters interaction array,module_id,course_id,user_id & is been called so many times by the AJAX script.
The following script inserts the record if the specific interaction_id is not present in that table otherwise the update query will get fire.

public function save_interactions($interaction_array,$modid,$cid,$uid)
{
    foreach($interaction_array as $key=>$interact_value)
    {
        $select_query = $this->db->query("SELECT COUNT(*) AS total FROM `scorm_interactions` WHERE `mod_id`='".$modid."' AND `course_id`='".$cid."' AND `user_id`='".$uid."' AND `interaction_id`='".$interact_value[0]."'");
        $fetchRow = $select_query->row_array();

        if($fetchRow['total']==1)
        {
            $update_data = array(
                        "interaction_type"=>$interact_value[1],
                        "time"=>$interact_value[2],
                        "weighting"=>$interact_value[3],
                        "correct_response"=>$interact_value[4],
                        "learner_response"=>$interact_value[5],
                        "result"=>$interact_value[6],
                        "latency"=>$interact_value[7],
                        "objectives"=>$interact_value[8],
                        "description"=>$interact_value[9]
            );
            $this->db->where('mod_id', $modid);
            $this->db->where('course_id', $cid);
            $this->db->where('user_id', $uid);
            $this->db->where('interaction_id', $interact_value[0]);
            $this->db->update('scorm_interactions', $update_data);
        }else
        {
            $insert_data = array(
                        "user_id"=>$uid,
                        "course_id"=>$cid,
                        "mod_id"=>$modid,
                        "interaction_id"=>$interact_value[0],
                        "interaction_type"=>$interact_value[1],
                        "time"=>$interact_value[2],
                        "weighting"=>$interact_value[3],
                        "correct_response"=>$interact_value[4],
                        "learner_response"=>$interact_value[5],
                        "result"=>$interact_value[6],
                        "latency"=>$interact_value[7],
                        "objectives"=>$interact_value[8],
                        "description"=>$interact_value[9]
            );
            $this->db->insert('scorm_interactions', $insert_data);
        }
    }
}

I got this type of error:

Deadlock found when trying to get lock; try restarting transaction

UPDATE `scorm_interactions` SET
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
WHERE
    `mod_id` =  '4' AND
    `course_id` =  '5' AND
    `user_id` =  '185' AND
    `interaction_id` =  'Question2_1'
;

Filename: application/models/user/scorm1_2_model.php Line Number: 234

Can anyone please suggest me how to avoid Deadlock?

DISCLAIMER : Not a Codeigniter Person, Just a MySQL DBA

Looking at the flow of control in the PHP. It says to me:

Check the following mod_id,course_id,user_id,interaction_id
If not found, INSERT the new row
If found, UPDATE all the other columns

There are three(3) mechanisms provided for INSERT and UPDATE scenarios

MECHANISM #1 : REPLACE INTO

REPLACE INTO `scorm_interactions` SET
    `mod_id` =  '4',
    `course_id` =  '5',
    `user_id` =  '185',
    `interaction_id` =  'Question2_1',
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
;

or

REPLACE INTO `scorm_interactions`
(`mod_id`,`course_id`,`user_id`,`interaction_id`,
`interaction_type`,`time`,`weighting`,`correct_response`,
`learner_response`,`result`,`latency`,`objectives`,`description`) VALUES
('4','5','185','Question2_1','choice','10:45:31','1','Knees*',
'Knees*','correct','0000:00:02.11','Question2_1','');

This mechanically performs DELETE and INSERT on mod_id, course_id , user_id, interaction_id

MECHANISM #2 : INSERT ... ON DUPLICATE KEY

INSERT INTO `scorm_interactions`
(`mod_id`,`course_id`,`user_id`,`interaction_id`,
`interaction_type`,`time`,`weighting`,`correct_response`,
`learner_response`,`result`,`latency`,`objectives`,`description`) VALUES
('4','5','185','Question2_1',
'choice','10:45:31','1','Knees*',
'Knees*','correct','0000:00:02.11','Question2_1','')
ON DUPLICATE KEY UPDATE
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
;

MECHANISM #3 : SELECT ... FOR UPDATE

Did you know you can perform an exclusive lock on rows you intend to update with precision?

According the MySQL Documentation, you can run a SELECT query on the very row and issue a row lock along the way. For example:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

This will lock the entire table and allow an UPDATE of the counter_field column.

In your case, simply run the two queries back-to-back

SELECT * FROM `scorm_interactions`
WHERE `mod_id` =  '4',
AND `course_id` =  '5',
AND `user_id` =  '185',
AND `interaction_id` =  'Question2_1'
FOR UPDATE ;
UPDATE `scorm_interactions` SET
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
WHERE `mod_id` =  '4'
AND `course_id` =  '5',
AND `user_id` =  '185',
AND `interaction_id` =  'Question2_1'
;

EPILOGUE

As a safety cushion, try increasing innodb_lock_wait_timeout if the traffic is write-heavy. See my StackOverflow post : How to debug Lock wait timeout exceeded?

Give it a Try !!!


GET_LOCK([name], 0) behaviour and multiple GET_LOCK() order I read from the documentation that the behavior of distributing a lock requested by many connections after RELEASE_LOCK is called from the connection holding the lock is undefined. I was curious: say I have one procedure p1 which requests a lock with GET_LOCK("lock", 0); so that multiple calls to this procedure don't interfere with each other. The procedure releases the lock before terminating. I have another procedure p2 which requests a lock using GET_LOCK("lock", 10);.

Suppose there is a flood of calls to p1, and somewhere in the middle, p2 is called, like this:

...
CALL p1();
CALL p1();
CALL p2();
CALL p1();
#lock released here from first p1() call: who gets it?
...

Theoretically, the second procedure shouldn't be affected much: it waits for the lock to be released and since GET_LOCK in p1 has a timeout of 0, p2 should be the only one requesting the lock when it is released. However, I don't think I can expect this ideal behavior to be true. GET_LOCK from the first procedure ends up taking some small but non-zero amount of time of course, and since the lock isn't assigned in order of request, is its possible for the lock to be passed down successive calls to p1 and p2 has to wait?

OBSERVATIONS
In your question, you said

I read from the documentation that the behaviour of distributing a lock requested by many connections after RELEASE_LOCK is called from the connection holding the lock is undefined.

You are overlooking something far more basic
MySQL Documentation clear says the following on GET_LOCK()

A negative timeout value means infinite timeout.
If multiple clients are waiting for a lock, the order in which they will acquire it is undefined. Applications should not assume that clients will acquire the lock in the same order that they issued the lock requests.

The Documentation says nothing about a zero(0) timeout value. Thus, that behavior, in itself, is simplified. If it acquires the lock, it immediately returns 1.  If it does not acquire the lock, it immediately returns zero(0).
You said p2 has a 10-second timeout (because you said you are using GET_LOCK("lock",10)).
If you are expecting p1() to pause execution, using a non-positive will not accomplish this.
SUGGESTION
You may want to execute a loop in your Stored Procedure to attempt to acquire the lock with some arbitrary number of seconds to pause between lock attempts:
SET @seconds_to_pause = 5;
SET @lock_acquired = GET_LOCK('lock',0);
WHILE @lock_acquired = 0 DO
    SET @x = SLEEP(@seconds_to_pause);
    SET @lock_acquired = GET_LOCK('lock',0);
END WHILE;

Give it a Try !!
YOUR ORIGINAL QUESTION

is it possible for the lock to be passed down successive calls to p1 and p2 has to wait?

If you do not have a loop to acquired the lock as I suggested,the answer would have to be no.


Better way to update table with value from another table I'm trying to rewrite the following query to make it more efficient:

update c set c.cartype = (select Id from carTypes where carType = 'Coupe')
from cars c
inner join doorcount dc
    on c.Id = dc.carId and dc.Value=2
       and c.carType= (select Id from carTypes where carType = 'Sedan')

What I've come up with so far is:

update c set c.cartype = ct.id
from cars c
inner join cartypes ct on ct.carType='Coupe'
inner join doorcount dc on c.Id = dc.carId and dc.Value=2
inner join carTypes ct2 on c.carType = ct2.Id and ct2.carType = 'Sedan'

This looks better to me, but is performing almost exactly the same.

Is there a more efficient way to perform this update?

You could also remove any doubt about the impact of the subqueries using:

DECLARE @CoupeID INT, @SedanID INT;

SELECT @CoupeID = MAX(CASE carType WHEN 'Coupe' THEN Id END),
       @SedanID = MAX(CASE carType WHEN 'Sedan' THEN Id END)
FROM dbo.carTypes WHERE carType IN ('Coupe','Sedan');

UPDATE c SET c.carType = @CoupeID
FROM dbo.cars AS c
WHERE c.carType = @SedanID
AND EXISTS (SELECT 1 FROM dbo.doorcount AS dc
  WHERE dc.Value = 2 AND dc.carId = c.Id);

Of course appropriate indexes on specific columns mentioned here would be useful for performance (again, if you post the plans somewhere, we can probably help identify opportunities). But in general, an update is an update, and unless these tables are massive, even the slowest way to find the rows to update will pale in comparison to the cost of the update itself.

Please also always use schema prefix and be very careful about CaSe SenSiTivIty.


What throughput should I expect with MPIO? Dell PowerEdge 2950 with two 1 Gbps NIC's going to two 1 Gbps ports on a switch which then goes to a NetApp with four 1 Gbps NIC's that present as one virtual interface. 24 drives, 7200k SATA, NetApp RAID-DP. I've mapped each host NIC to the NetApp using MPIO in the Microsoft iSCSI initiator. Testing with SQLIO my write throughput appears reasonable at about 200 MBs, but my reads are closer to 100 MBs.

Shouldn't my reads be closer to 200 MBs just like my writes? Is this a configuration problem or is there a fundamental storage issue I don't understand?

Update:
Here are IOPS for the random workload. The reads make sense, though, I'm not sure what to make of 20000 for the writes. SAN cache is 3.2 GB. SQLIO tests are against a 25 GB file.

Disk writes are actually going to memory (NVRAM) on the filer, to be flushed to disk later - on an idle filer, these will be incredibly fast, and iops of 20,000 are quite believable (you'll see similar speeds from most SSDs).

Reads, on the other hand, need to come from disk unless they already happen to be in the filer's read cache (which, unlike writes, are on volatile memory).

It's hard to pin storage vendors down on iops for spinning disks, but for a 7200RPM drive, 80-120 iops is quite believable. Considering that you've probably lost a couple of disks to NetApp's RAID-DP and/or spares, 2,200 iops is close to what you could expect from 22 disks performing around 100 iops each.

This may not explain your read speeds (your disks may not be doing the full 2200 iops when you're performing a sequential read), but it may at least help explain your write performance.


Delete data from a specific partition in MS SQL 2012 I would like to delete data from a specific partition using the partition ID. I got queries to truncate data from specific partition for SQL 2016 but did not find any query for lower versions.

I tried below query to delete only data from partitions with partition id 14 and 15.

delete from  partitiontable1 WITH (PARTITIONS (14 to 15))

Will the delete statement remove the partition itself along with data?

I want to keep the partition intact and remove data from specific partitions. Say, I have created partitions on a table, it has 5 partitions (1,2,3,4,5) and I want to remove data only from 2nd and 3rd partition.

This delete statement is invalid SQL Server 2012 syntax so it will do nothing but raise an error.

Removing a partition and it's associated boundary is done with MERGE DDL; DML statements like DELETE never remove partitions.

The most efficient way to remove all rows from a partition in SQL 2014 and earlier versions is with SWITCH (SQL 2016 allows specific partitions to be specified with a TRUNCATE). To use SWITCH, create a similarly partitioned staging table with identical schema and indexes. You can then switch data between tables as a meta-data operation and then TRUNCATE the staging table.

ALTER TABLE dbo.YourTable
    SWITCH PARTITION 1 TO dbo.YourTable_Staging PARTITION 1;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 2 TO dbo.YourTable_Staging PARTITION 2;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 3 TO dbo.YourTable_Staging PARTITION 3;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 4 TO dbo.YourTable_Staging PARTITION 4;
ALTER TABLE dbo.YourTable
    SWITCH PARTITION 5 TO dbo.YourTable_Staging PARTITION 5;
TRUNCATE TABLE dbo.YourTable_Staging;


How does one scale out with master-slave replication? To my understanding, a master-slave replication might be set up where clients can read from multiple databases. However, they can only write to the master. I believe this helps guarantee ACIDity.

I also understand one-way replication seems generally preferred to two-way for the same reasons.

I don't quite understand how this would work in terms of application development though.

How do you tell an application to write to one database and read from another? Does this mean this actually has to be programmed into the application layer?

This isn't too big a deal. However, let's say it takes 60 seconds for that write to come back down to the local database. How does the client compensate for latency when he reloads the page and it re-queries the local db (missing that write)? This seems so error-prone!
How would this then scale? Understood that reads dominate a majority of a database's work. But what happens when you scale enough that one server can't handle all the writes? What options do you have?

I'm trying to wrap my head around the subject but I'm missing answers to these basic questions.

I have to keep my answer very generic as you have not mentioned any particular database system and what type of master slave implementation you want to know about.

However, they can only write to the master.

True in most cases but there are solutions where you can write to more than one target at the same time. Your data will be replicated to both nodes, synchronized and conflicts will be resolved.

Oracle has multi master implementation. Generic idea explained here.

SQL Server has merge and peer to peer replication. In merge replication where you can update on the subscriber side and data will be merged between two. Details here. In peer to peer replication there is no conflict resolution compare to merge replication.  Therefore it is recommended only in configurations where only one copy of a given data element is updated. For example, if Peer-to-Peer replication is used to maintain the inventory of a chain of food stores, only the store that owns an inventory entry would be allowed to update that entry. Details here.

I also understand one-way replication seems generally preferred to two-way for the same reasons.

I would not say it is preferred but definitely used less compare to one way replication. It will depend on your need. Main challenge with two way replication is conflict between 2 or more masters. Conflicts might occur and, when they do, you need the ability to detect and resolve them.

How do you tell an application to write to one database and read from another? Does this mean this actually has to be programmed into the application layer?

Depends. As an example SQL Server Always On Availability Group you do it by configuring your secondaries available to read by clients. Then you set up your connection string in such a way that your reads will be automatically routed to read_only nodes. Details here.

Whereas in MongoDB config server keeps track of primary and secondary. Each Mongos has this information. Mongos will redirect the traffic to the correct node based on your Read Preference setting. Details here.

How does the client compensate for latency when he reloads the page and it re-queries the local db (missing that write)? This seems so error-prone!

In SQL Server this is dictated by the setting of isolation level and implemented by lock/latch on different objects (database, table, page, row etc).  This is a good reading about this topic. I do not know the details for other DBMS, but I am sure it will be something similar.

How would this then scale? Understood that reads dominate a majority of a database's work. But what happens when you scale enough that one server can't handle all the writes? What options do you have?

In general by Sharding your objects horizontally. Sharding can be implemented in may different ways. I worked with MongoDB sharding for many years and it works perfectly. Details here. Some scaling out idea using SQL server explained here.


Indexing from start or when performance problem arises? My question is regarding use of indexes.

Should I start indexing right from the start or when performance problem arises?
We can also create temporary index while executing a query. What are the pros and cons of such techniques?

Should I start indexing right from the start or when performance
  problem arises?

Indexing strategy tends to evolve as usage patterns emerge. That said, there are also strategies and design guidelines that can be applied up front.

Choose a good clustering key. You can usually determine the appropriate clustered index at design time, based on the expected pattern of inserts to a table. If a compelling case emerges for a change in the future, so be it.
Create your primary and other unique constraints. These will be enforced by unique indexes.
Create your foreign keys and associated non-clustered indexes. Foreign keys are your most frequently referenced join columns, so index them from the start.
Create indexes for any obviously highly selective queries. For query patterns you already know will be highly selective and likely to use lookups rather than scans.

Beyond the above, take a gradual and holistic approach to implementing new indexes. By holistic, I mean assess the potential benefit and impact to all queries and existing indexes when evaluating an addition.

A not uncommon problem in SQL Server circles is overindexing, as a result of guidance from the missing index DMVs and SSMS hints. Neither of these tools evaluate existing indexes and will merrily suggest you create a new 6 column index rather than add a single column to an existing 5 column index.

-- If you have this
CREATE NONCLUSTERED INDEX [IX_MyTable_MyIndex] ON [dbo].[MyTable]
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
)

-- But your query would benefit from the addition of a column
CREATE NONCLUSTERED INDEX [IX_MyTable_MyIndex] ON [dbo].[MyTable]
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
    , [col6] ASC
)

-- SSMS will suggest you create this instead
CREATE NONCLUSTERED INDEX [IX_MyTable_AnotherIndexWithTheSameColumnsAsTheExistingIndexPlusCol6] ON [dbo].[MyTable]
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
    , [col6] ASC
)

Kimberly Tripp has some excellent material on indexing strategy that while SQL focused is applicable to other platforms. For the SQL Server folk, there are some handy tools for identifying duplicates like the example above.

  We can also create temporary index while executing a query. What are
  the pros and cons of such techniques?

This usually only applies for rarely run queries, typically ETL. You need to assess:

Does the time taken to create the index reduce the execution time of the query.
Does the maintenance overhead of leaving the index in place outweigh the time taken to create/drop when it's needed.


Ordering of a dimension in sql server datawarehouse Built and deployed a simple cube with a date dimension.  The dimension has things like day of month.  How do i sort it?  it always shows in the reports as
1, 10, 11, 12, ... 19, 2, 20, 21

Design.

DimDate (3 columns)

DateID  KEY
Date    DATETIME  {Values  1/1/2000, 1/2/2000, ect}
DAY     VARCHAR(2)   {Values 1,2,3,4,5,6,7,ect}

FactAppointment (one column)

DateID  {Values key to dim}

I set the dims sort column to key or date and neither works.

When excel, or any other bi tool is connected and the date dim is a column, a simple count is the value the dates are not sorted.

Picture:  Column headers are not ordered properly.
1, 10,11,12,...,19,2,20

Analysis Services will set the data type of the dimension attribute to be the same as the source column. It is sorted exactly as you asked for it. Your days are declared as a character type. Therefore, they will be sorted according to character sorting (1, 10, 11) and not numeric sorting (1,2,3).

You didn't specify whether this was multi-dimensional or tabular model but both will support a sort order property. In cases where I need to present character data, like month name, but need it sorted in month order, I will have columns like MonthName and MonthSortOrder and not present the SortOrder suffixed fields to the consuming tools.

In a multi-dimensional model, another option is to edit the Data Source View and replace the source table with a named query that includes a cast to integer for the "Day" column.


Does my choice of pg_dump format impact restore speed? According to the docs for my version of Postgres (8.4.2), pg_dump can "output in script or archive file formats."  The script-formatted dump (4GB) takes 1.5 hours to restore, and I'm wondering if the archive format is any faster.

My script-formatted dump is using COPY commands, by the way, so it's not like I'm doing one insert at a time.

Thanks in advance.

Archive format has small impact on restore time.

That's because actually, when you restore from plain format, it is equivalent to:

cat backup.file | psql emptydb

and when you restore from "compressed" format, it is equivalent to:

pg_restore backup.file | psql emptydb

, and the cost of decompression with pg_restore is usually negligible, compared to write-intensive COPY/CREATE INDEX commands which happen during restore.

For large dumps, restoring from compressed dumps may be a bit faster (less disk reads).

It might be even faster with -j (--jobs) option. It can be used only with "compressed" format, and it allows paralell restore with N concurrent jobs (sessions).

For more details about -j option, see http://www.postgresql.org/docs/8.4/static/app-pgrestore.html. Example:

pg_restore -j 4 -d emptydb backup.file


ALTER TABLE ALTER COLUMN cannot run inside a transaction block; I am trying to alter column in redshift from varchar(30) to Varchar(100) Its not working througing an below error.

Command used:

alter table t_name alter column c1 type varchar(300);

Error:

[Amazon](500310) Invalid operation: ALTER TABLE ALTER COLUMN cannot run inside a transaction block; [SQL State=25001, DB Errorcode=500310]

From AWS Documentation Check if you are violating any conditions..

  ALTER COLUMN column_name TYPE new_data_type A clause that changes the
  size of a column defined as a VARCHAR data type. Consider the
  following limitations:

  You can't alter a column with compression encodings BYTEDICT,
  RUNLENGTH, TEXT255, or TEXT32K.

  You can't decrease the size less than maximum size of existing data.

  You can't alter columns with default values.

  You can't alter columns with UNIQUE, PRIMARY KEY, or FOREIGN KEY.

  You can't alter columns within a transaction block (BEGIN ... END).
  For more information about transactions, see Serializable isolation.


Is "SET IDENTITY_INSERT" required to insert arbitrary GUID into [uniqueidentifier] column? SQL Server 2016 and 2017, Standard Edition

I need to insert raw data (from another table) into a table that has two uniqueidentifier columns.

Does this require that I use SET IDENTITY_INSERT Carrier ON?

Here is the table:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Parkingspace](
    [ParkingspaceId] [uniqueidentifier] NOT NULL,
    [AccountId] [uniqueidentifier] NULL,
    [ParkingspaceType] [smallint] NULL,
 CONSTRAINT [PK_ParkingspaceId] PRIMARY KEY CLUSTERED
(
    [ParkingspaceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Will the following work without the IDENTITY_INSERT option?  (I think it should but the DBA here is very married to the IDENTITY_INSERT option.)

INSERT INTO Parkingspace
  (ParkingspaceID,AcountID,ParkingspaceType)
  SELECT * FROM PRODDBCopy.Parkingspace

IDENTITY_INSERT is only used for columns with the IDENTITY property.

UNIQUEIDENTIFIER is merely a data type.

Your insert statement will work.


SQL Server AG Worker Threads So, I'm currently trying to wrap my head around worker threads for availability groups, more specifically, the number of threads used by each secondary database. The information I've read is different to what I'm seeing. For example:
I'm using the sys.dm_hadr_db_threads and sys.dm_hadr_ag_threads DMVs.
"Each Secondary replica uses 1 redo thread for each secondary database" - but my secondary replica databases are using either 3 or 4 threads:

It's a very inactive AG so I wouldn't expect to see any active threads (again from what I've read, after 15 seconds or so, the threads are released for re-use) I don't see any active threads on the primary which is what I would expect:

It's not causing an issue (yet), it's just something I'm curious about. Have I mis-understood how it works? I just don't want to it be over consuming threads as eventually there's going to be quite a large number of databases added into the AG, and I'm just trying to figure out if we'll have enough worker threads for the AG and daily database usage.
Config:
SQL Server 2019
1 AG
1 primary
1 secondary

So I believe what you read is dated information specifically pertaining to the first version of Availability Groups when it was released with SQL Server 2012. I found what I'm guessing is the RedGate Article written in 2014 that mentions that quote "Each Secondary replica uses 1 redo thread for each secondary database". This was known as the serial redo model.
The source article from Microsoft which that RedGate article references has since been updated (in 2020) and no longer states that's how the secondary replica works.
Furthermore I found the following Microsoft article which mentions how they've switched to multiple parallel redo worker threads per database by default, aka parallel redo model, starting with SQL Server 2014. This must be what you're experiencing.

When availability group was initially released with SQL Server 2012,
the transaction log redo was handled by a single redo thread for each
database in an AG secondary replica. This redo model is also called as
serial redo . In SQL Server 2016, the redo model was enhanced with
multiple parallel redo worker threads per database to share the redo
workload.

This is a feature that is probably not often tweaked, so I would personally recommend leaving it as is until you run into a problem you're sure is related to the parallel redo model. If you do run into an issue, then it is possible to switch back to the original serial redo model as the above article states:

To switch to serial redo model, TF 3459 needs to be enabled. But after
the SQL Server instance is running in serial redo model, the only way
to change it back to parallel redo is to restart the SQL Server
service.

Nice question by the way. :)


Can I tell if a stored procedure has executed against a particular database? As a result of a release a database is behaving "unusually".   I suspect a stored procedure was missed during a recent release, however my DBA is telling me 100% it was executed.

Is there any way to definitively prove it was or wasn't?

Starting from SQL Server 2008,use can sys.dm_exec_procedure_stats which gives you the status  on when was the stored procedure last executed

select db_name(database_id) as dbname,last_execution_time
from sys.dm_exec_procedure_stats


mongodb replication node stuck at “STARTUP2” with optimeDate as 1970 i have just setup replica sets with three nodes . the third node is stuck at stateStr STARTUP2 with "optimeDate" : ISODate("1970-01-01T00:00:00Z"). However its showing no error message. Is this alright. On primary rs.status() yeilds

{
    "set" : "qdit",
    "date" : ISODate("2013-06-18T22:49:41Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 0,
            "name" : "q.example.com:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 2940,
            "optime" : {
                "t" : 1371593311,
                "i" : 1
            },
            "optimeDate" : ISODate("2013-06-18T22:08:31Z"),
            "self" : true
        },
        {
            "_id" : 1,
            "name" : "q1.example.com:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 457,
            "optime" : {
                "t" : 1371593311,
                "i" : 1
            },
            "optimeDate" : ISODate("2013-06-18T22:08:31Z"),
            "lastHeartbeat" : ISODate("2013-06-18T22:49:40Z"),
            "lastHeartbeatRecv" : ISODate("2013-06-18T22:49:40Z"),
            "pingMs" : 0,
            "syncingTo" : "twitnot.es:27017"
        },
        {
            "_id" : 2,
            "name" : "q2.example.com:27017",
            "health" : 1,
            "state" : 5,
            "stateStr" : "STARTUP2",
            "uptime" : 300,
            "optime" : {
                "t" : 0,
                "i" : 0
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2013-06-18T22:49:40Z"),
            "lastHeartbeatRecv" : ISODate("2013-06-18T22:49:41Z"),
            "pingMs" : 7
        }
    ],
    "ok" : 1
}

also

db.printSlaveReplicationInfo() on yields

source:   qdit1.queuedit.com:27017
     syncedTo: Tue Jun 18 2013 22:08:31 GMT+0000 (UTC)
         = 2894 secs ago (0.8hrs)
source:   qdit2.queuedit.com:27017
     syncedTo: Thu Jan 01 1970 00:00:00 GMT+0000 (UTC)
         = 1371596205 secs ago (380998.95hrs)

Is this alright. Also how can i test my replication especially the third node

No, this is not OK, STARTUP2 should only be a state that a secondary is in briefly on its way to a full sync and SECONDARY status (via RECOVERING usually) - see the states table for more.  However, without seeing log files it's impossible to say why it is stuck.  The 1970 date you are seeing in the optime is basically the epoch/Unix time version of zero, indicating that it has not applied any ops.

The basic method for restoring this secondary would be to shut it down, wipe out its data files (all files in the dbpath), and restart it.  That will restart the initial sync process and it should get to SECONDARY.  If it gets stuck again, then look at the logs for more information as to why it is happening - the most common causes would be some sort of resource issue or possibly not being able to talk to the primary in the set reliably.


Error in function creating query There is an error in the following function creating query:

CREATE FUNCTION `_increase_num` () RETURNS int(11)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN IF(@counter, @counter:=@counter+1, @counter:=1);
END

Any idea how to fix?
Can I avoid using ; in the query above? The development framework doesn't allow multiple queries in one call and identifies this one as multiple queries.

EDIT1:
The aim of the function to make each row unique, like an autoincreased primary key. But for some development reason I can't use autoincremented primary keys.

EDIT2:
The error message is usual, that query failed. You have an error in your query.

EDIT3:
I use the function like this way:
SELECT
    _increase_num() as rownr,
    content
FROM mytable

WORKING SOLUTION:

CREATE FUNCTION _increase_num()
returns int
    return IF(@counter, @counter:=@counter+1, @counter:=1)

Here's a version that does not give a syntax error:

delimiter !!
drop function _increase_num !!
CREATE FUNCTION _increase_num()
returns int
begin
    return IF(@counter, @counter:=@counter+1, @counter:=1);
end !!
delimiter ;

A bit of warning though, whenever counter looses its value it will start from 1. Also, I would not put any money on how well this works when the function is called in parallel from several processes.

Edit:
";" has a special meaning in stored procedure language, it separates statements within the procedure. This is why you normally change the statement terminator to something else:

delimiter @

However, for this trivial procedure, we don't need the begin/end block. That is:

CREATE FUNCTION _increase_num()
returns int
    return IF(@counter, @counter:=@counter+1, @counter:=1);

will do.


Do I need to add more memory to my SQL Server 2008 R2 instance? I've been asked if one of our SQL Server instances has sufficient memory. This is a SQL Server 2008 R2  Standard Edition running on a x64 machine with 40 CPUs and 64 GB of RAM.

The total size of all the databases running under this instance is slightly more than 300 GB.

The instance has been configured with 4096 MB for the Minimum Server Memory and 24000 MB for the Maximum Server Memory.

So far, I've managed to gather the below information:

Physical memory in use:

SELECT physical_memory_in_use_kb,
   process_physical_memory_low,
   process_virtual_memory_low
FROM sys.dm_os_process_memory

physical_memory_in_use_kb process_physical_memory_low process_virtual_memory_low
------------------------- --------------------------- --------------------------
25657180                  0                           0

Performance Counters:

SELECT counter_name , cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Total Server Memory (KB)',
'Target Server Memory (KB)', 'Granted Workspace Memory (KB)',
'Maximum Workspace Memory (KB)',
'Memory Grants Outstanding', 'Memory Grants Pending')

 counter_name                                cntr_value
-----------------------------------     --------------------
  Granted Workspace Memory (KB)                1024
  Maximum Workspace Memory (KB)                17714520
  Memory Grants Outstanding                    1
  Memory Grants Pending                        0
  Target Server Memory (KB)                    24576000
  Total Server Memory (KB)                     24576000

Buffer Pool:

select count(*) AS Buffered_Page_Count
 ,count_big(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors

Buffered_Page_Count Buffer_Pool_MB
------------------- --------------------
2499693             19528

Buffer Pool Size Graph:

BPHR:

PLE:

Plan Cache Size:

Page Reads Per Second

Page Writes Per Second

Total IO Wait Time:

My questions are:

What are the other things that I should be looking at?

If the above is sufficient, do I have to recommend adding more memory (maybe make it 50 GB) because PLE shows a degradation over time (while Total IO Wait Time shows going up)?

Target Server Memory is equal to the maximum memory set. Is it an indication that the instance can utilise more memory if it is configured with?

So, how do I know if my SQL Server needs more memory?
or maybe it doesn't

What are the other things that I should be looking at?

First few remarks

  This is a SQL Server 2008 R2 Standard Edition running on a x64 machine
  with 40 CPUs and 64 GB of RAM.

Why is your max memory only at 24000MB when you have 64GB of phyisical memory? Are there other instances / services / ... running on it?

If the answer to this is no, then you could set max memory to about 56GB = 57344 MB.

As a side note, sql server 2008 R2 is out of extended support. It would be best to be looking towards migrating to a newer version.

Which objects are consuming buffer pool memory

Since your PLE has always been pretty low and has gotten even lower over time, you could look into what exactly is using this BP memory at different points during the day.

First of all look into which databases consume the most:

-- get memory usage per database
SELECT
[DatabaseName] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
COUNT_BIG(*) [Pages in Buffer],
COUNT_BIG(*)/128 [Buffer Size in MB]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id]
ORDER BY [Pages in Buffer] DESC;

Afterwards you can dig deeper on a database level to see which objects are in memory and how many pages these objects hold in memory.

-- get memory usage for objects in a database

select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc;

If the more granular query reports less, you would have to remove and bd.page_type in ('data_page', 'index_page') to include LOB data pages.

Next steps

You should put this information together with queries running on your system, you might be able to optimize certain queries / add indexes so less data is needed in memory.

If you want to go even further, ROW / PAGE compression could be looked at if you have the CPU power to do so.

Compressed data remains compressed in memory.

Also note that if you are on sql server 2008 R2 standard edition, you could only go to 64GB of max memory. Starting from sql server 2014 this goes up to 128GB of max memory. This is also another reason to migrate.

  If the above is sufficient, do I have to recommend adding more memory
  (maybe make it 50 GB) because PLE shows a degradation over time (while
  Total IO Wait Time shows going up)?

Solely based on PLE, which is not the only factor to look at, I would say that your pages get flushed too frequently out of memory. Either optimize your queries or increase your memory.

  Target Server Memory is equal to the maximum memory set. Is it an
  indication that the instance can utilise more memory if it is
  configured with?

  Target Server Memory (KB) is the amount of memory that SQL Server is
  willing (potential) to allocate to the buffer pool under its current
  load. Total Server Memory (KB) is what SQL currently has allocated.

Source

It has allocated all possible memory to the buffer pool as allowed by max memory, it will not go over this amount for buffer pool allocations.

  So, how do I know if my SQL Server needs more memory? or maybe it
  doesn't

Again, looking at what objects are in memory and if the queries responsible for it can be optimized would be a good start.

Additionally I would increase either max memory if you have the room to do so (if you in fact have 64GB of memory on the machine) or increase the memory on the machine.

Again remember that there is a limit of 64GB of max memory for sql server 2008 R2 standard edition and that this is increased to 128GB starting from sql server 2014 standard edition.

There is no limit on enterprise edition.


Setting up SQL Server to handle queries from external domains I am not a SQL Server admin. I have tried to google this but can't seem to use the right words to get the right info and they all talk about remote connections to a SQL Server in terms of admin and remote access.

I have a database on one domain, that needs to accept connections and queries etc... from web code (page) I am hosting on another domain. I am using Windows 2012 servers on AWS in the case of both domains, and I have port 1433 opened on the domain server with the database, but open only to the ip address of this other domain. The SQL Server is using the default windows based auth.

Can someone tell me where to find a guide to set up the SQL Server to accept these external requests with a proper user name and password ?

It depends on what you mean by a proper user name and password.

Windows Authentication

You will need to setup the Domain trust relationship between each domain. Your domain hosting the database server will need to trust the domain holding the web application. This should allow the domain accounts access the web application can connect to the database server, without having to use a separate account.

SQL Login

If you do not want to deal with the domain relationship you can simple configure SQL Server in mixed mode. You can find those instructions here. You would just create a SQL Login and grant the required access to the database(s) for the web application.


"Deploy Database to SQL Azure" Task missing I recently installed SQL Server 2014 Express on a workstation I have and am trying to deploy to SQL Azure. I've done this before on a previous workstation using SQL Server 2012 Express by doing the following:

Right click the database > Tasks > "Deploy Database to SQL Azure"

On SQL Server 2014, the option is completely missing and is instead replaced a new option: "Deploy Database to a Windows Azure VM"

I can't figure out why the option for deploying to SQL Azure is missing. Is there something that needs to be installed separately now for SQL Server 2014? From what I've read online, both options should be present for 2014 editions. Did Microsoft remove this option for SQL Server 2014 Express?

Microsoft has changed the procedure. What you need to do is Export/Import a Data-tier Application via a BACPAC file. Microsoft explains it here (in the section Backup Package (.bacpac):
http://msdn.microsoft.com/en-us/library/ee210546.aspx

Here it is in a nutshell:

Use SQL Server Management Studio 2014
Right-click your local database name, then Tasks / Export Data-tier Application
Follow the wizard to create a BACPAC file on your local disk. This will contain your
complete DB schema as well as your data.
In SSMS, connect to your existing Azure SQL account (tcp:xxxxxxxxxx.database.windows.net,1433)
Under the Azure account, right-click on the word Databases and
select Import Data-tier Application
Using the wizard, import your BACPAC file "from local disk" When it is finished, you will have an Azure-based replica of your original database, schema, data and all.


Why aren't some spid values showing up in sys.dm_exec_requests? I am executing the following dmv query against SQL Server 2014 to find all activity on a server and any blocking activities:

SELECT Distinct
   ...
FROM sys.dm_exec_requests er
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
    LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
    LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
WHERE st.text IS NOT NULL And DB_Name(er.database_id) IN ('Vincent', 'Vincent_Audit')
ORDER BY BlockingSessionId Desc, SessionId

Sample results of this query are as follows:

You can see that session 120 is blocked by 112 and 112 is blocked by 69. However, process 69 doesn't appear because it doesn't exist in sys.dm_exec_requests.

So, the following query returns no results:

SELECT *
FROM sys.dm_exec_requests
Where session_id = 69

Anyone know why this would be?

Sessions can exists without an active request, but still block other sessions.

Consider if you have one window open in SSMS where you run this:

BEGIN TRANSACTION
INSERT INTO dbo.SomeTable DEFAULT VALUES;

Then in another window you run:

BEGIN TRANSACTION
SELECT * FROM dbo.SomeTable;

The first session will be holding locks on dbo.SomeTable, without it showing up in sys.dm_exec_requests.  You will see the session in sys.dm_exec_sessions, and you'll be able to see the open transaction in sys.dm_tran_session_transactions.

I'd recommend Adam Machanic's sp_whoisactive, however if you aren't allowed to download scripts to run against your server, you may consider something like the following code that can identify open transactions without an active request that are blocking other sessions:

/*
    This query shows sessions that are blocking other sessions, including sessions that are
    not currently processing requests (for instance, they have an open, uncommitted transaction).

    By:  Max Vernon, 2017-03-20
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --reduce possible blocking by this query.

USE tempdb;

IF OBJECT_ID('tempdb..#dm_tran_session_transactions') IS NOT NULL
DROP TABLE #dm_tran_session_transactions;
SELECT *
INTO #dm_tran_session_transactions
FROM sys.dm_tran_session_transactions;

IF OBJECT_ID('tempdb..#dm_exec_connections') IS NOT NULL
DROP TABLE #dm_exec_connections;
SELECT *
INTO #dm_exec_connections
FROM sys.dm_exec_connections;

IF OBJECT_ID('tempdb..#dm_os_waiting_tasks') IS NOT NULL
DROP TABLE #dm_os_waiting_tasks;
SELECT *
INTO #dm_os_waiting_tasks
FROM sys.dm_os_waiting_tasks;

IF OBJECT_ID('tempdb..#dm_exec_sessions') IS NOT NULL
DROP TABLE #dm_exec_sessions;
SELECT *
INTO #dm_exec_sessions
FROM sys.dm_exec_sessions;

IF OBJECT_ID('tempdb..#dm_exec_requests') IS NOT NULL
DROP TABLE #dm_exec_requests;
SELECT *
INTO #dm_exec_requests
FROM sys.dm_exec_requests;

;WITH IsolationLevels AS
(
    SELECT v.*
    FROM (VALUES
              (0, 'Unspecified')
            , (1, 'Read Uncomitted')
            , (2, 'Read Committed')
            , (3, 'Repeatable')
            , (4, 'Serializable')
            , (5, 'Snapshot')
        ) v(Level, Description)
)
, trans AS
(
    SELECT dtst.session_id
        , blocking_sesion_id = 0
        , Type = 'Transaction'
        , QueryText = dest.text
    FROM #dm_tran_session_transactions dtst
        LEFT JOIN #dm_exec_connections dec ON dtst.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
)
, tasks AS
(
    SELECT dowt.session_id
        , dowt.blocking_session_id
        , Type = 'Waiting Task'
        , QueryText = dest.text
    FROM #dm_os_waiting_tasks dowt
        LEFT JOIN #dm_exec_connections dec ON dowt.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
    WHERE dowt.blocking_session_id IS NOT NULL
)
, requests AS
(
SELECT des.session_id
    , der.blocking_session_id
    , Type = 'Session Request'
    , QueryText = dest.text
FROM #dm_exec_sessions des
    INNER JOIN #dm_exec_requests der ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE der.blocking_session_id IS NOT NULL
    AND der.blocking_session_id > 0
)
, Agg AS (
    SELECT SessionID = tr.session_id
        , ItemType = tr.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = tr.session_id)
        , BlockedBySessionID = tr.blocking_sesion_id
        , QueryText = tr.QueryText
    FROM trans tr
    WHERE EXISTS (
        SELECT 1
        FROM requests r
        WHERE r.blocking_session_id = tr.session_id
        )
    UNION ALL
    SELECT ta.session_id
        , ta.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = ta.session_id)
        , BlockedBySessionID = ta.blocking_session_id
        , ta.QueryText
    FROM tasks ta
    UNION ALL
    SELECT rq.session_id
        , rq.Type
        , CountOfBlockedSessions =  (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = rq.session_id)
        , BlockedBySessionID = rq.blocking_session_id
        , rq.QueryText
    FROM requests rq
)
SELECT agg.SessionID
    , ItemType = STUFF((SELECT ', ' + COALESCE(a.ItemType, '') FROM agg a WHERE a.SessionID = agg.SessionID ORDER BY a.ItemType FOR XML PATH ('')), 1, 2, '')
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , TransactionIsolationLevel = il.Description
FROM agg
    LEFT JOIN #dm_exec_sessions des ON agg.SessionID = des.session_id
    LEFT JOIN IsolationLevels il ON des.transaction_isolation_level = il.Level
GROUP BY agg.SessionID
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , il.Description
ORDER BY
    agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.SessionID;


Speeding up group by and max query In my iot use case, I have a table which stores sensor readings. I am using the timescaledb extension.

I have the following table definition:

CREATE TABLE readings (
  id          BIGSERIAL      PRIMARY KEY,
  time        TIMESTAMPTZ       NOT NULL,
  value       DOUBLE PRECISION  NOT NULL,
  device      VARCHAR(255)      NOT NULL,
  sensor      VARCHAR(255)      NOT NULL,
  resolution  VARCHAR(255)      NOT NULL,
  UNIQUE(time, device, sensor, resolution)
);

I want last reported time for every (device,sensor) pair. Here is the query I am running:

select MAX(time), device, sensor from readings group by device, sensor

This query takes a significant time to complete. Is there any way to improve the performance? Or any better query to achieve the same result?

I tried doing this and it did not help:

create index if not exists readings_idx on readings (time, device, sensor, resolution);

(Timescale cofounder here)

It sounds like you are trying to perform a "last point" query, e.g., find the latest record submitted by each (device, sensor).  The challenge with the query as above is that the database doesn't know when it has found the last device/sensor, so needs to scan back to the start of time to conclude there are no new devices around.

Much better is the following approach, which performs a LATERAL SCAN from a list of devices against the time-series records:

https://docs.timescale.com/latest/using-timescaledb/reading-data#last-point

Good luck, and you might also find our Slack group at https://slack.timescale.com useful for these types of questions.


How to Override a column in table.* select (column1+column2) AS column1, table1.*
from table1

both column1 and column2 are columns in table1. Is there anyway that when I run the query I can get only the new column1 is shown and the old one is ignored?

My current option is spell out every column in the table that I actually do want to show up

Edit: This is with SQL 2008 R2, but I'd be interested if other SQL versions have a solution as well

Specify only the columns you want to see.

select (column1+column2) AS column1,
       column3,
       column4,
       column5
from table1

It is bad form to use table1.*, as your SQL code will be vulnerable to changes in the table structure.  It is a good habit to always specify the exact columns you want.


Access Sub Query Speed I have three tables in Access:

employees
----------------------------------
id (pk),name

times
----------------------
id (pk),employee_id,event_time

time_notes
----------------------
id (pk),time_id,note

I want to get the record for each employee record from the times table with an event_time immediately prior to some time.  Doing that is simple enough with this:

    select employees.id, employees.name,
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC) as time_id
from employees

However, I also want to get some indication of whether there's a matching record in the time_notes table:

select employees.id, employees.name,
(select top 1 time_notes.id from time_notes where time_notes.time_id=(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC)) as time_note_present,
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC) as last_time_id
from employees

This does work but it's SOOOOO SLOW.  We're talking 10 seconds or more if there's 100 records in the employee table.  The problem is peculiar to Access as I can't use the last_time_id result of the other sub-query like I can in MySQL or SQL Server.

I am looking for tips on how to speed this up.  Either a different query, indexes.  Something.

you can use link to create index on MS-Access.

Hope this query work ok in MS-Access ,

select employees.id, employees.name, last_time_id
,(select top 1 time_notes.id from time_notes where time_notes.time_id=last_time_id)
time_note_present
from
(
select employees.id, employees.name,
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC)
as last_time_id
from employees
)t4

Thanks @Perkins.I have edited my answer.


select in (select) allow to get duplicate values I need help in one SQL query.

I have select in select:

select column1 from table where column2 in (
--there I have select command what returns me 609 rows with duplicate values
)

and I need to get in first select 609 rows with duplicates. But I only get 12 rows because the select in command ignores duplicate values in second select.

I need to get certain values (609rows) not only to find what values I get two or more times.
I will join this result to another result.

My query looks like this :

This is the first select I was talking about

select  serialnumber from ffPackage where ID in (

and this is the select from what I get 609 rows.

select parentID from ffPackage where SerialNumber in (
select ffPackage.SerialNumber from ffEmployee, ffHistory, ffSerialNumber, ffPackage, ffUnitDetail
where ffHistory.UnitID in (
select UnitID from ffUnitDetail where OutmostPackageID in (
select ID from ffPackage where SerialNumber = 'xxxxxxxx'))
and ffHistory.EmployeeID = ffEmployee.ID
and ffHistory.UnitID = ffSerialNumber.UnitID
and ffHistory.UnitStateID = 'xxxx'
and ffUnitDetail.UnitID = ffHistory.UnitID
and ffUnitDetail.InmostPackageID = ffPackage.ID
))

The query that returns 609 rows apparently has only 12 distinct parentID values. If you use it as an IN subquery matched against a unique column (which I am assuming ffPackage.ID is), you can only get 12 rows as the result.

If you want to get as many rows as the subquery returns, one way is to use the subquery as a derived table and join it to ffPackage:

SELECT
  serialnumber
FROM
  dbo.ffPackage AS p
  INNER JOIN
  (
    -- the 609 row subquery
    select parentID from ffPackage where SerialNumber in (
    select ffPackage.SerialNumber from ffEmployee, ffHistory, ffSerialNumber, ffPackage, ffUnitDetail
    where ffHistory.UnitID in (
    select UnitID from ffUnitDetail where OutmostPackageID in (
    select ID from ffPackage where SerialNumber = 'xxxxxxxx'))
    and ffHistory.EmployeeID = ffEmployee.ID
    and ffHistory.UnitID = ffSerialNumber.UnitID
    and ffHistory.UnitStateID = 'xxxx'
    and ffUnitDetail.UnitID = ffHistory.UnitID
    and ffUnitDetail.InmostPackageID = ffPackage.ID
    ))
  ) AS sub ON p.ID = sub.ParentID
;

Incidentally, I would like to suggest that you rewrite the comma joins in the subquery as explicit joins – for consistency and maintainability's sake:

SELECT
  SerialNumber
FROM
  dbo.ffPackage AS p
  INNER JOIN
  (
    -- the 609 row subquery
    SELECT
      ParentID
    FROM
      dbo.ffPackage
    WHERE
      SerialNumber IN
      (
        SELECT
          p.SerialNumber
        FROM
          dbo.ffEmployee AS e
          INNER JOIN dbo.ffHistory AS h ON h.EmployeeID = e.ID
          INNER JOIN dbo.ffSerialNumber AS sn ON h.UnitID = sn.UnitID
          INNER JOIN dbo.ffUnitDetail AS ud ON ud.UnitID = h.UnitID
          INNER JOIN dbo.ffPackage AS p ON ud.InmostPackageID = p.ID
        WHERE
          h.UnitID IN
          (
            SELECT
              UnitID
            FROM
              ffUnitDetail
            WHERE
              OutmostPackageID IN
              (
                SELECT ID FROM ffPackage WHERE SerialNumber = 'xxxxxxxx'
              )
          )
          AND h.UnitStateID = 'xxxx'
      )
  ) AS sub ON p.ID = sub.ParentID
;

I would also argue that using better formatting and short (but meaningful) table aliases contributes to maintainability of your queries too. And you may want to consider getting into the habit of always qualifying your tables with the schema name as well as that of using statement terminators.


Recover database until cancel? I'm trying to use the command restore database until cancel, but I'm getting the following erro:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "cancel": expecting one of: "scn, sequence, time"
RMAN-01007: at line 1 column 24 file: standard input

Is this command deprecated?

My Oracle database version is the 11g R2

It's "recover", not "restore":

recover database until cancel;

See here: http://dba-oracle.com/bk_recover_database_until_cancel_tips.htm


Indexing a SHA hash in place of a VARCHAR In Is it a good idea/approach to index a VARCHAR column? the concept was using VARCHAR as the column.

I was wondering, if we store the SHA-1 hash or SHA-256 hash (if I am paranoid) as a BINARY(20) column and index on that column.

Performing a SHA-1 computation on a short string is fast enough on the application side and we just query by the SHA value instead.

Likely I am thinking the length of the VARCHAR would hover around 10 to 30 characters with some going longer but lower probability.

This question has the [Performance] tag so I suspect that you may be thinking of a hash index. In SQL Server, the maximum key length for nonclustered indexes is 1700 bytes. It is not possible to create a nonclustered index with a long string column as a key column. For example, for the following table:

DROP TABLE IF EXISTS #HASH_INDEX_DEMO;

CREATE TABLE #HASH_INDEX_DEMO (
    ID BIGINT NOT NULL,
    BIG_COLUMN_FOR_U VARCHAR(8000) NOT NULL,
    SMALL_COLUMN VARCHAR(10) NOT NULL
);

INSERT INTO #HASH_INDEX_DEMO WITH (TABLOCK)
SELECT RN, REPLICATE(CHAR(65 + RN % 26), (RN % 43) * (RN % 119)), 'SMALL'
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

Attempting to create this index:

CREATE INDEX I ON #HASH_INDEX_DEMO (BIG_COLUMN_FOR_U);

Fails with this error:

  Msg 1946, Level 16, State 3, Line 19 Operation failed. The index entry
  of length 1701 bytes for the index 'I' exceeds the maximum length of
  1700 bytes for nonclustered indexes.

If you need to do an equality search on that column then the query optimizer must do a table scan. For example, the following query takes about 0.6 seconds on my machine:

SELECT ID, SMALL_COLUMN
FROM #HASH_INDEX_DEMO
WHERE BIG_COLUMN_FOR_U = 'A'
OPTION (MAXDOP 1);

One alternative is to create a hash index on the column and to perform the equality search on both the hash index and the column itself. CHECKSUM() is probably the best choice because you don't need any cryptographic security for this and a small number of collisions is acceptable. You primary want something small and fast. The code below adds a computed column and creates an index on that column:

ALTER TABLE #HASH_INDEX_DEMO ADD BIG_COLUMN_FOR_U_CHECKSUM AS CHECKSUM(BIG_COLUMN_FOR_U);

CREATE INDEX I ON #HASH_INDEX_DEMO (BIG_COLUMN_FOR_U_CHECKSUM);

The query below returns the same results as the initial query but SQL Server is able to use the index. It finishes in 0.01 seconds on my machine.

SELECT ID, SMALL_COLUMN
FROM #HASH_INDEX_DEMO
WHERE BIG_COLUMN_FOR_U_CHECKSUM = CHECKSUM('A') AND BIG_COLUMN_FOR_U = 'A'
OPTION (MAXDOP 1);

Hash indexes are a good choice when the key length is too long to allow for a nonclustered index or disk space is at an absolute premium. In your question you estimate that the length of the column will be about 10 to 30 characters to the added complexity probably isn't worth it for your scenario.


How to design index for faster deletes I want to speed up following piece of code

delete from ssn_sdo
where
           (art_id=@art_id and skl_id=@skl_id and @level=0 )
        or (art_id=@art_id and skl_id=@skl_id and @level=1 and tip=@tip)
        or (art_id=@art_id and skl_id=@skl_id and doc_id = @doc_id)

My server is MS SQL-Server 2005.

art_id,skl_id and doc_id are integers, while tip is varchar(10) type of.

I want to make an index(es) on ssn_sdo table so this delete is going to faster.
What I was considering to do is to make three indexes, each one for every case:

doc_id (ASC), art_id (ASC), skl_id (ASC)
skl_id (ASC), art_id (ASC), tip (ASC)
skl_id (ASC), art_id (ASC),

Or is there better way to make one index which will include all three cases.
I am careful with indexes because I do not want to slow down inserts in this table.

Bit akward, I can't see how to comment but I wanted to ask why your condition isn't:

delete from ssn_sdo
where
       art_id=@art_id
       and skl_id=@skl_id
       and (@level = 0 or (@level = 1 and tip=@tip) or doc_id = @doc_id)

Not sure that helps at all, before or after you add any further indexes, but it'd interesting to see the executions plans (actual) and see if any benefit is realised.


Translate query using APPLY to run under compatibility level 80 I want to use the following query:

SELECT
    SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,
     ( ( CASE qs.statement_end_offset
           WHEN -1 THEN DATALENGTH(qt.TEXT)
           ELSE qs.statement_end_offset
         END - qs.statement_start_offset ) / 2 ) + 1) AS [Text] ,
 qs.execution_count ,
 qs.total_logical_reads ,
 qs.last_logical_reads ,
 qs.total_logical_writes ,
 qs.last_logical_writes ,
 qs.total_worker_time ,
 qs.last_worker_time ,
 qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,
 qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,
 qs.last_execution_time ,
 qp.query_plan
FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC

I took the code snippet from How to find CPU intensive queries? on the SQL Server Central blogs.

I need to run this query on database which is in compatibility mode 80 and APPLY is not available there.

The results from that query will not be scoped to particular database. They will show activity across the whole instance.

In general, it would be quite difficult to provide information at this granularity, because queries can access data from more than one database (Azure SQL Database aside).

In non-trivial queries, accounting for costs on a per-database basis (e.g. after joins and aggregations have been performed) would be pretty much impossible. Some DMVs do provide information about the context database at the time a query was executed, but again, the query might have accessed objects in an entirely different database(s).

The easiest answer, therefore, is to simply run the query in the context of a database that is set to compatibility level 90 or higher, such as tempdb or master. It will not affect the results (aside from not producing an error).

If this is not a suitable solution, please edit your question to clarify.


Finding the previous value in a table I have table queue with values.

id name
--------
1  apple
2  mango
3  banana -- I assumed this name, don't remember it exactly

But I accidentally changed it to be

id name
---------
1  apple
2  mango
3  John

So I reverted it back to

id name
----------
1  apple
2  mango
3  banana

But now I came to know that name is not banana for id = 3.

How can I know what used to be there before I changed it?

Naturally, if you have a backup before the change, you could simply restore that backup to another database and get the previous value from that.  Otherwise, you 'might' be able to take advantage of the free trial of this
http://www.apexsql.com/sql_tools_log.aspx


Drop table taking too long I have a table with approx. 200 million rows (approx. 0.5 TB) and I want to drop it, but it is taking a really long time.

It's been running for 2 days now. I suspect the rollback function to be the reason for this.

Is there a way to avoid the rollback function, so that I can speed this process up? I just want to get rid of the table, because the data in there are of no value any more.

You can list the running backends with

SELECT * FROM pg_stat_activity;

identify the process trying to rollback something which is working on this table.

Find its pid.

You can terminate a backend with the query SELECT pg_terminate_backend(64738), where 64738 is the pid column in your previous SELECT.

After that, you can likely DROP that table.

If even that won't work, then restart postgresql, but most likely it will.


Possible Inconsistency Among System Tables Is there a reason that there would be a significant difference in the number of records in the DBA_USERS table and the USER$ table?

USER$ is a table, DBA_USERS is a view.

USER$ contains user and roles while DBA_USERS selects only the users.


Sum purchased & sold quantities for all items on daily/weekly I have 4 tables Items, DimDate, Purchase and Sales for items definitions, calendar table, purchased and sold Items respectively. Tables structure here

I need to outer join sum(Purchased_Qty) and sum(Sales_Qty) with a date range, and also with the Items table so that the results would display as follows:

Date, Items.Product_Name, Sum(purchased_Qty), Sum(sold_Qty)
group by [Date], Items.Product_Name.

I need to display purchased & sold quantities for all items and for all days of a date range.

THIS is an example of a possible starting point query, i tried several combinations of joins & sub queries on query in this example, but none of them is correct in logic as i tested them.

i need to display purchased & sold quantities for all items and for all days of a date range.

According to this I applied Cartesian product between dimDates and Items using OUTER APPLY, that should return a combination of all dates and all items.

Then I've used two subqueries in the SELECT but you could LEFT JOIN Purchases and Sales tables.

SELECT d.[Date],
       i.Product_Code,
       i.Product_Name,
       COALESCE((SELECT SUM(Purchased_Qty)
                 FROM   dbo.Purchase
                 WHERE  Product_Code = i.Product_Code
                 AND    Purchase_Date = d.Date), 0) Purchased,
       COALESCE((SELECT SUM(Sale_Qty)
                 FROM   dbo.Sales
                 WHERE  Product_Code = i.Product_Code
                 AND    Sale_Date = d.Date), 0) Saled
FROM   [dbo].[DimDate] d
OUTER APPLY (
            SELECT Product_Code, Product_Name
            FROM   dbo.Items
            ) i
WHERE  d.[Date] >= '20180115'
AND    d.[Date] <= '20180116';
GO

Date                | Product_Code | Product_Name | Purchased | Saled
:------------------ | :----------- | :----------- | :-------- | :----
15/01/2018 00:00:00 | P1           | ProductA     | 26        | 20
15/01/2018 00:00:00 | P2           | ProductB     | 5         | 20
15/01/2018 00:00:00 | P3           | ProductC     | 20        | 40
15/01/2018 00:00:00 | P4           | ProductD     | 0         | 0
15/01/2018 00:00:00 | P5           | ProductE     | 0         | 0
16/01/2018 00:00:00 | P1           | ProductA     | 15        | 21
16/01/2018 00:00:00 | P2           | ProductB     | 37        | 22
16/01/2018 00:00:00 | P3           | ProductC     | 23        | 3
16/01/2018 00:00:00 | P4           | ProductD     | 0         | 0
16/01/2018 00:00:00 | P5           | ProductE     | 0         | 0

DECLARE @Start_Date date = '20180115';
DECLARE @End_Date date = '20180116';

SELECT d.[Date],
       i.Product_Code,
       i.Product_Name,
       COALESCE(p.Purchased_Qty, 0) Purchased_Qty,
       COALESCE(s.Sale_Qty, 0) Sale_Qty
FROM   [dbo].[DimDate] d
OUTER APPLY (
            SELECT Product_Code, Product_Name
            FROM   dbo.Items
            ) i
LEFT JOIN (SELECT Product_Code, Purchase_Date, SUM(Purchased_Qty) Purchased_Qty
           FROM   dbo.Purchase
           WHERE  Purchase_Date >= @Start_Date
           AND    Purchase_Date <= @End_Date
           GROUP BY Product_Code, Purchase_Date) p
ON p.Product_Code = i.Product_Code
AND p.Purchase_Date = d.[Date]
LEFT JOIN (SELECT Product_Code, Sale_Date, SUM(Sale_Qty) Sale_Qty
           FROM   dbo.Sales
           WHERE  Sale_Date >= @Start_Date
           AND    Sale_Date <= @End_Date
           GROUP BY Product_Code, Sale_Date) s
ON s.Product_Code = i.Product_Code
AND s.Sale_Date = d.[Date]
WHERE  d.[Date] >= @Start_Date
AND    d.[Date] <= @End_Date
ORDER BY d.[Date], i.Product_Code;
GO

Date                | Product_Code | Product_Name | Purchased_Qty | Sale_Qty
:------------------ | :----------- | :----------- | :------------ | :-------
15/01/2018 00:00:00 | P1           | ProductA     | 26            | 20
15/01/2018 00:00:00 | P2           | ProductB     | 5             | 20
15/01/2018 00:00:00 | P3           | ProductC     | 20            | 40
15/01/2018 00:00:00 | P4           | ProductD     | 0             | 0
15/01/2018 00:00:00 | P5           | ProductE     | 0             | 0
16/01/2018 00:00:00 | P1           | ProductA     | 15            | 21
16/01/2018 00:00:00 | P2           | ProductB     | 37            | 22
16/01/2018 00:00:00 | P3           | ProductC     | 23            | 3
16/01/2018 00:00:00 | P4           | ProductD     | 0             | 0
16/01/2018 00:00:00 | P5           | ProductE     | 0             | 0

dbfiddle here


Select all rows with same ID when the last record of that ID meets the criteria I need help with a SQL query.
I want to return all rows when the last record (date) of the ID match the criteria.
Here is an example :
Let's call this table "Test"

ID
date
status

1
10/01/2021
Closed

1
09/01/2021
Resolved

1
08/01/2021
Awaiting Input

1
01/01/2021
Open

2
15/01/2021
Open

2
16/01/2021
Awaiting Input

2
17/01/2021
Open

3
07/01/2021
Open

3
08/01/2021
Awaiting Input

What i want to do is to return all the rows with the same ID if the last record (date) of that ID match the criteria.
I tried :
 SELECT * FROM test AS r
 WHERE r.ID in (SELECT
    ID FROM test
    WHERE status = 'Open');

But that will return all the records (ID) having "Open" in the database.
What I want is all the rows (ID) with status"Open" (or whatever condition) and all their records if the last record meets the conditions..
For example for status "Open" the result should be:

ID
date
status

2
15/01/2021
Open

2
16/01/2021
Awaiting Input

2
17/01/2021
Open

Indeed, the last record (ID) 17/01/2021 as status 'Open' so it match the condition. --> It could be order by date desc or asc ... It's just an example.
How can I dot it?
It seems easy on paper but i'm struggling with this one.
Note that I'm using postgresql.
Thanks for your help.

try with this:
with
last_row as (
select
ID,
max(date) as date
from test
group by ID
),

last_row_open as (
select
ID
from test
where ID in (select ID from last_row)
  and status='Open'
)

select
*
from test
where ID in (select ID from last_row_open)


Why are there any Innodb_buffer_pool_reads for a small db? I have a small DB, < 400MB, using MariaDB 10.4 on a machine with 16GB ram. I've set the innodb_buffer_pool_size to 2G while running some query performance tests.

With the size set to 2G, more than 4x the total size of the DB, why would I get any Innodb_buffer_pool_reads? Why wouldn't all reads be found in the buffer pool?

After a test run I get
  Innodb_buffer_pool_read_requests = 280,980,648
  Innodb_buffer_pool_reads = 27,089

I don't mind the hit rate; I'm just wondering if I misunderstand something about the buffer pool.

These are counts since the server was started.

The pages must have been read at some stage to get into the buffer pool.

As a test, try a mysqldump --all-databases --skip-locks to ensure every row is read. And then look at the Innodb_buffer_pool_reads. Then see if this value increases over the next few days.


Shrink Tempdb log file when reaching a specific size how I can shrink tempdb log file when reaching a specific size?? I'm scheduling job to shrink tempdb but still I face a problem of disk space due to tempdb is full!

Isn't it better to just accept that the requirement for your SQL Server is a certain disk space and make sure you have the budget to allocate that amount of space? Keep shrinking seems like a futile fight in the end. Makes me think of the old blog post I wrote a decade ago about file shrinking and leaking roof: http://sqlblog.karaszi.com/leaking-roof-and-file-shrinking/. Anyhow, you could crate an SQL Server Agent alert for this based on a performance monitor counter, that kicks off an Agent job to do the shrink. But again, I wouldn't do it that way.


Column to be populated using other columns values I would like to add a new column in my table using SQL code in PostgreSQL / PostGIS, the values in this column must be filled from the other eight columns and updated with a trigger as I insert data to a new line for the first rows. As I show in the attached image.

I have used this code to alter the table,

ALTER TABLE table_name ADD COLUMN NEW_COLUMN varchar;

UPDATE table_name SET NEW_COLUMN = "PP" || '.' || "MM" || '.' || "DD" || '.' || "BB" || '.' || "QQ" || '.' || "LL" || '.' || "ID"::varchar || '.' || "UU";

But I am having a problem using this trigger because it does not accept:

CREATE OR REPLACE FUNCTION update_en()
RETURNS TRIGGER AS
$$
BEGIN
NEW.codigo :=;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER update_en
BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
FOR EACH ROW
EXECUTE PROCEDURE update_en();

The question on why you think you need that aside, you can simplify the concatenation using concat_ws()which you can easily use in the trigger as well:

CREATE OR REPLACE FUNCTION update_en()
RETURNS TRIGGER AS
$$
BEGIN
  NEW.codigo := concat_ws('.', new."PP", new."MM", new."DD", new."BB", new."QQ", new."LL", new."ID", new."UU");
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;


Is there a penalty for using BINARY(16) instead of UNIQUEIDENTIFIER? I've recently inherited a SQL Server database that uses BINARY(16) instead of UNIQUEIDENTIFIER to store Guids. It does this for everything including primary keys.

Should I be concerned?

Should I be concerned?

Well, there are a couple of things here that are a little concerning.

First: while it is true that a UNIQUEIDENTIFIER (i.e. Guid) is a 16-byte binary value, it is also true that:

All data can be stored in binary form (e.g. INT could be stored in BINARY(4), DATETIME can be stored in BINARY(8), etc), hence #2 ↴
There is probably a reason for having a separate datatype for GUIDs outside of mere convenience (e.g. sysname as an alias for NVARCHAR(128)).

The three behavioral differences that I can find are:

Comparing UNIQUEIDENTIFIER values in SQL Server, for better or for worse, is not actually done the same way as comparing BINARY(16) values. According to the MSDN page for Comparing GUID and uniqueidentifier Values, when comparing UNIQUEIDENTIFIER values in SQL Server:

  the last six bytes of a value are most significant

While these values are not frequently sorted, there is a slight difference between these two types. According to the MSDN page for uniqueidentifier:

  ordering is not implemented by comparing the bit patterns of the two values.

Given that there are differences in how GUID values are handled between SQL Server and .NET (noted in the "Comparing GUID and uniqueidentifier Values" page linked above), pulling this data out of SQL Server into app code might not be dealt with properly in the app code if needing to emulate the SQL Server comparison behavior. That behavior can be emulated by converting to a SqlGuid, but would a developer know to do that?

Second: based on the following statement

  It does this for everything including primary keys.

I would be concerned in general for system performance by using GUIDs as PKs instead of as Alternate Keys along with using an INT or even BIGINT as the PK. And even more concerned if these GUID PKs are the Clustered Indexes.

UPDATE

The following comment, made by the O.P. on @Rob's answer, brings up an additional concern:

  it was migrated from I think MySQL

GUIDs can be stored in 2 different binary formats. So, there could be cause for concern depending on:

what system the binary representation was generated on, and
if the string values were used outside of the original system, such as in app code or given to clients to use in import files, etc.

The issue with where the binary representation was generated has to do with the byte ordering of the first 3 out of the 4 "fields". If you follow the link above to the Wikipedia article, you will see that RFC 4122 specifies to use "Big Endian" encoding for all 4 fields, yet Microsoft GUIDs specify using "Native" Endianness. Well, Intel architecture is Little Endian, hence the byte order for the first 3 fields is reversed from systems following the RFC (as well as Microsoft-style GUIDs generated on Big Endian systems). The first field, "Data 1", is 4 bytes. In one Endianness it would be represented as (hypothetically) 0x01020304. But in the other Endianness it would be 0x04030201. So if the current database's BINARY(16) field was populated from an import file using 0x01020304 binary notation and that binary representation was generated on a system following the RFC, then converting the data currently in the BINARY(16) field into a UNIQUEIDENTIFIER will result in a different GUID than what was originally created. This does not really pose a problem IF the values never left the database, and the values are only ever compared for equality and not ordering.

The concern with ordering is simply that they won't be in the same order after converting to UNIQUEIDENTIFIER. Fortunately, if the original system really was MySQL then ordering was never done on the binary representation in the first place since MySQL only has a string representation of UUID.

The concern with the string values being used outside of the database is more serious, again, if the binary representation was generated outside of Windows / SQL Server. Since the byte ordering is potentially different, then the same GUID in string form would result in 2 different binary representations, depending on where that conversion took place. If app code or customers were given a GUID in string form as ABC coming from a binary form of 123 and the binary representation was generated on a system following the RFC, then that same binary representation (i.e. 123) would translate to a string form of DEF when converted to a UNIQUEIDENTIFIER. Likewise, the original string form of ABC would convert to a binary form of 456 when converted to a UNIQUEIDENTIFIER.

So, if the GUIDs never left the database then there isn't much to be concerned about outside of ordering. Or, if the import from MySQL was done by converting the string form (i.e. FCCEC3D8-22A0-4C8A-BF35-EC18227C9F40) then it might be ok. Else, if those GUIDs were given to customers or in the app code, you can test to see how they convert by getting one and converting via SELECT CONVERT(UNIQUEIDENTIFIER, 'value found outside of the database'); and see if you find the expected record. If you can't match records then you might have to keep the fields as BINARY(16).

In all likelihood there won't be an issue, but I am mentioning this because under the right conditions there could be an issue.

And how do new GUIDs get inserted anyway? Generated in the app code?

UPDATE 2

If the previous explanation of the potential issue related to importing binary representations of GUID generated on another system was a little (or a lot) confusing, hopefully the following will be a little clearer:

DECLARE @GUID UNIQUEIDENTIFIER = NEWID();
SELECT @GUID AS [String], CONVERT(BINARY(16), @GUID) AS [Binary];
-- String = 5FED23BE-E52C-40EE-8F45-49664C9472FD
-- Binary = 0xBE23ED5F2CE5EE408F4549664C9472FD
--          BE23ED5F-2CE5-EE40-8F45-49664C9472FD

In the output shown above, the "String" and "Binary" values are from the same GUID. The value beneath the "Binary" line is the same value as the "Binary" line, but formatted in the same style as the "String" line (i.e. removed "0x" and added the four dashes). Comparing the first and third values, they aren't exactly the same, but they are very close: the right-most two sections are identical, but the left-most three sections are not. But if you look closely, you can see that it is the same bytes in each of the three sections, just in a different order. It might be easier to see if I show only those first three sections, and number the bytes so it is easier to see how their order differs between the two representations:

String  =  15F2ED3234BE – 5E562C – 7408EE
Binary = 4BE3232ED15F – 62C5E5 – 8EE740 (in Windows / SQL Server)

So within each grouping, the ordering of the bytes is reversed, but only within Windows and also SQL Server. However, on a system that adheres to the RFC, the binary representation would mirror the sting representation because there would not be any reversal of the byte order.

How was the data brought into SQL Server from MySQL? Here are a few choices:

SELECT CONVERT(BINARY(16), '5FED23BE-E52C-40EE-8F45-49664C9472FD'),
       CONVERT(BINARY(16), 0x5FED23BEE52C40EE8F4549664C9472FD),
    CONVERT(BINARY(16), CONVERT(UNIQUEIDENTIFIER, '5FED23BE-E52C-40EE-8F45-49664C9472FD'));

Returns:

0x35464544323342452D453532432D3430
0x5FED23BEE52C40EE8F4549664C9472FD
0xBE23ED5F2CE5EE408F4549664C9472FD

Assuming it was straight binary-to-binary (i.e. Convert #2 above), then the resulting GUID, if converted to an actual UNIQUEIDENTIFIER, would be:

SELECT CONVERT(UNIQUEIDENTIFIER, 0x5FED23BEE52C40EE8F4549664C9472FD);

Returns:

BE23ED5F-2CE5-EE40-8F45-49664C9472FD

Which is wrong. And that leaves us with three questions:

How was the data imported into SQL Server?
What language is the app code written in?
What platform is the app code running on?


What is the syntax for querying a spatial column? What is the right syntax to get all the points within the bounding box? The table consists of a lat and lng column and a spatial column "pt" generated from the lat/lng values.

calculated the topleft and bottomright coords for the MBR

tested with the following query ( without the haversine )

SELECT *
FROM tb_gps
WHERE (lng BETWEEN 4.88263070241 AND 4.90030729759)
  AND (lat BETWEEN 52.3677890788 and 52.3785809212);

testing the spatial column generates an error

SELECT *
FROM tb_gps
WHERE MBRWITHIN(pt, MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605))

This will give null results

SELECT * FROM tb_gps as t WHERE MBRCONTAINS( GeomFromText( 'MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)' ) , t.pt )=1;

The mysql version is

protocol_version    10
version     5.1.68-cll

It could be that I am using the wrong syntax, am not sure. Could someone help me out with this

( the coords in the second query are different, but that's not really the point)

edit

I had success with this query(at least I got some results back, so I am going to work on that a little more)

SELECT  *
    FROM    tb_gps
    WHERE   MBRContains
                    (
                    LineString
                            (
                            Point
                                    (
                                    4.8914691 + 10 / ( 111.1 / COS(RADIANS( 52.373185))),
                                     52.373185 + 10 / 111.1
                                    ),
                            Point
                                    (
                                    4.8914691 - 10 / ( 111.1 / COS(RADIANS(52.373185))),
                                     52.373185 - 10 / 111.1
                                    )
                            ),
                    pt
                    )

I think you want MBRContains
I have an example from a previous post : Less restrictive query return less result due to simple removing one additional constraint
Give it a Try !!!
UPDATE 2013-05-08 17:05 EDT
You have this:
SELECT *
FROM tb_gps
WHERE MBRWITHIN(pt, MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605))

Put the MULTIPOINT in quotes
SELECT *
FROM tb_gps
WHERE MBRWITHIN(pt, 'MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)')

I did this in my example
UPDATE 2013-05-08 17:39 EDT
If MULTIPOINT is not working for you, try using POLYGON
SELECT *
FROM tb_gps
WHERE
    IF(CONTAINS(GeoFromText('POLYGON(52.3641917981 4.87673850395,
    4.87673850395 4.90619949605,4.90619949605 52.3821782019,
    52.3821782019 52.3641917981)'pt),1,0)
;

You know something? It just dawned on me. You have the parameters backwards.
You have
SELECT *
FROM tb_gps
WHERE MBRWITHIN(pt,'MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)')

Try reversing the parameters:
SELECT *
FROM tb_gps
WHERE MBRWITHIN('MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)',pt)

Give it a Try !!!


Get equivalent SQL query for any Postgres meta-command I hope the title is self-descriptive.
I want to be able somehow to translate any Postgres meta-command into its corresponding/underlying SQL query, at least to learn more about Postgres and the way it stores meta-info in its tables.

Any ideas if this is possible?

E.g.:

When connected to the database EXAMPLE,
\dt and SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name; return the same result.

I want to find, if possible, a way to obtain the value SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name; when inputting \dt to the function/macro/whatever.

Easy and very useful: you can launch psql with the proper switch (-E) to get the information.

me@mystation:~/ > psql -E
psql (9.3.11)
Type "help" for help.
me@mystation # \d
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Have a look at http://www.postgresql.org/docs/current/static/app-psql.html for more information. Once in psql, you could also set the ECHO_HIDDEN variable.


SUM() ignores GROUP BY and sums up 4 rows instead of 2 I'm having difficulty with GROUP BY in MySQL.

My database setup:

client_visit
    - id
    - member_id
    - status_type_id (type_of_visit table)
    - visit_starts_at
    - visit_ends_at

member
    - id

schedule_event
    - id
    - member_id
    - starts_at
    - ends_at

type_of_visit
    - id
    - type (TYPE_BOOKED, TYPE_PRESENT etc)

For the purpose of this question: a member teaches a class or leads an activity (a schedule_event) at a given time. A client signs up for this class or activity.

For example:

Client A, B and C book visits and those go to client_visit table which consists of schedule_event_id and member_id, so we know what class and what member is teaching/or having activity.

Now, we want to know the total time a given member spent teaching/leading events that clients signed up for (based on the client_visit type_of_visit column equivalent to "Booked" or "Present"). We'll take member ID 82 as our test case.

Member ID 82 had 4 clients in two different classes, so if each class took 2h 15 minutes (8100 seconds), that means total time should be 16200 seconds.

Here's my query first:

SELECT cv.member_id AS `member_id`,
       sch.id AS `scheduleId`,
       cv.visit_starts_at AS `visitStartsAt`,
       TIMESTAMPDIFF(SECOND, sch.starts_at, sch.ends_at) AS `totalTime`
  FROM `schedule_event` AS `sch`
         LEFT JOIN `client_visit` AS `cv` ON cv.schedule_event_id = sch.id
         INNER JOIN `type_of_visit` AS `tov` ON tov.id = cv.status_type_id
 WHERE (tov.type = 'TYPE_BOOKED' OR tov.type = 'TYPE_PRESENT') and cv.member_id = 82

The result is as follows:

This shows me the clients for the first class, and the one for the second. I just want two rows, one for each class. So, I add this:

 GROUP BY sch.id

Now, the result is as follows:

So far so good,

I know that there are two schedule ids for this member, so I modified the group by to pull those together into one:

 GROUP BY sch.id AND cv.member_id

I expect that it will first merge based on sch.id (the result it's already shown in the picture above) and cv.member_id (we got two rows, so after merging, should be one)

and the result is (I modified scheduleId by adding GROUP_CONCAT, so we can see both schedule IDs are there):

Now, just like I pulled together the two schedule IDs, I want to add up the time for the two scheduled classes.

I modify the query now:

SUM(TIMESTAMPDIFF(SECOND, sch.starts_at, sch.ends_at)) AS `totalTime`

And the result is:

I got 32400! For some reason, the SUM is still seeing all 4 rows instead of just the unique 2.

I expected that the final result would be

+-----------+------------+
| member_id | total_time |
+-----------+------------+
| 82        | 16200      |
+-----------+------------+

All other columns are not needed, I just made them to see what is happening

What's wrong?

I believe you have a misunderstanding of what GROUP BY does. Not surprising, I had issues myself when first learning, in large part because the MySQL manual doesn't actually explicitly say what GROUP BY does, at least not that I can find (and I searched a lot, just now; plenty of caveats and special behavior, not so much an actual definition).

My (on the fly) Definition:

  GROUP BY condenses your SELECT results so that only 1 row is returned
  for each distinct combination of values for the columns specified in
  the GROUP BY clause. In that sense, it is similar to DISTINCT, but
  works on the columns in the GROUP BY instead of the SELECT statement.

In non-MySQL land, you can only SELECT columns you specify in your GROUP BY clause, PLUS any aggregate functions you want. Those aggregate functions, including SUM, operate on a per row basis, reporting a result ONLY for the now "hidden" extra rows.

As you can see, that is what your query is actually doing (or would be, but I think you gave an inaccurate example, as ypercube points out in the comments). It is summing up all the now-hidden extra rows, and reporting their total, for the given sch.id.

If you want the total of only distinct values of each sch.id, you'll have to do things differently to get the information you want.

One reason that it is not simple, is MySQL has no idea WHICH row you want to include in the sum. They may be all the same in your example (8100), but there's no guarantee of that.

Since MySQL allows you to select columns that are neither specified in the GROUP BY clause nor are aggregate functions, it essentially chooses one at "random" and displays it to you. While not actually random, it is non-deterministic, and can change at any time for the same query and data, even if it appears to you to always give the same result.

So, before you can proceed, you need to decide how you want to determine which row for each sch.id contains the value you want to sum.

If you know the values to always be the same, then one simple (although not necessarily optimized) solution is to wrap your original GROUP BY query in another query (making the original a subquery) and then use the SUM function in the outer query, without a GROUP BY clause. The subquery will remove your duplicates, and the outer query will sum up the total of the de-duplicated rows.


Grant a login of Sql server authentication the same permission of a Windows group login with Windows authentication? Update:
I am trying to make it easier for the login to have the permissions on multiple databases.

I already have a Windows group login with Windows authentication. The Windows users in the group can access multiple databases on a server.

Now I need to create an Sql server login and I want it have the same permission of the Windows group login (so it will have the permissions on mulitple database). Is it possible to do it without grant all the permissions to the SQL server separately?

I tried to create a credential for the Windows group login and add the credential to the SQL login but it doesn't work.

create credential WinGroupLogin with identity = 'MyServer\WinGroupLogin'
ALTER LOGIN [sql_login] ADD CREDENTIAL [WinGroupLogin]

At the database level, you could simply add both to the same database role. Of course, when you create the role, you'll have to grant it all the same permissions as the existing group, but you only have to do that once.

At the server level, to do that without re-applying all of the permissions (which you could probably script without a whole lot of effort - you can get some ideas here), you'll have to wait for SQL Server 2012, unless you can use a fixed server role.


Sort results of a query by the order of OR clause I have a string as input, that represents an hostname, and a table that contains suffixes (e.g. co.uk, uk, etc).

I'd like the database to return the records based on the order of the where clause. For example, let's assume the suffixes table contains the following entries:

| id | name   |
|----|--------|
| 1  | co.uk  |
| 2  | uk     |
| 3  | foo.uk |

Assuming the input is example.co.uk, I will produce a query like the following one:

SELECT *
FROM suffixes
WHERE
  name = "example.co.uk" OR
  name = "co.uk" OR
  name = "uk"
LIMIT 1

The result should be:

| 1  | co.uk  |

However, from tests I executed, it is not guaranteed that PG follows the order of the OR conditions by defaults. In some cases, when there are multiple entries matching the OR conditions (e.g. in this case co.uk and uk), it may very well return uk.

In fact, if I take the same query and swap the OR conditions:

SELECT *
FROM suffixes
WHERE
  name = "uk" OR
  name = "co.uk"
LIMIT 1

in most cases it returned the same result of the previous query.

How can I tell PostgreSQL to return the first match of the OR query, in the order specified in the query?

There are two ways to do this in Postgres:

Join your table against a virtual table build from a values list:

with names (idx, name) as (
  values
    (1, 'example.co.uk'),
    (2, 'co.uk'),
    (3, 'uk')
)
select s.*
from suffixes s
  join names n on n.name = s.name
order by n.idx
limit 1;

Another shorter version would be to join against an array:

select s.*
from suffixes s
  join unnest(array['example.co.uk', 'uk', 'co.uk']) with ordinality as n(name, idx)
    on n.name = s.name
order by n.idx
limit 1

The second version has the advantage, that you don't need to supply the additional index because that's implicitly defined through the order of the values inside the array.


Send data from SQL to a serial port or IP address I've been asked to integrate SQL transaction data with a video monitoring system in real-time. The video system's application is only capable of receiving data by listening on a serial port or a specific IP address and port.

Is it possible to write/send data from SQL to a serial port or IP? How?

The transaction data is stored in a Microsoft SQL Server 2005 database.

I can't find anything around this on Google or this site. Every search returns how to pick up data from serial and write to SQL dbs, not the other way around.

I may be going around this the wrong way - any related relevant ideas are welcomed.

No SQL Server will not be able to write directly to a serial port or TCP socket.  You'll need to either write an application that pulls the data from the SQL Server, or a SQL CLR procedure that calls out to the TCP IP address and socket.

I'd recommend using an app to query the SQL Server database then write that data to the serial port or TCP socket.