Update a table faster than an INNER JOIN I have a SQL Server 2012 stored procedure with an inner join that takes 7,387ms to execute (I saw it in Activity Monitor):

[...]
declare @childrenIds as dbo.CodeIdList;

[...]
Update
    dbo.Code
set
    dbo.Code.CommissioningFlag = 21
From
    dbo.Code
INNER JOIN
    @childrenIds c
ON
    dbo.Code.CodeId =  c.CodeId

CodeIdList is:

CREATE TYPE [dbo].[CodeIdList] AS TABLE (
    [CodeId] INT NULL
);

Is there a faster way to update the Code table than using an INNER JOIN?

I'm not a dba so I don't know if you need more details about my question. If you need them, please ask.

I have run sp_helpindex on Code table with these results:

index_name     | index_description
PK_CODE        | clustered, unique, primary key located on PRIMARY   | CodeId
UC_CODE_SERIAL | nonclustered, unique, unique key located on PRIMARY | Serial

Code table creation script:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [ ...]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)
)

At this moment there are 1.006.896 rows in the Code table.

Execution plan:

XML version at http://pastebin.com/9yXsRfva

SQL Server version info:

Microsoft SQL Server Management Studio                      11.0.6020.0 Herramientas cliente de Microsoft Analysis Services         11.0.6020.0 Microsoft Data Access Components (MDAC)                      6.1.7601.17514
Microsoft MSXML                                              3.0 4.0 6.0
Microsoft Internet Explorer                                  9.11.9600.18314
Microsoft .NET Framework                                     4.0.30319.42000
Sistema operativo                                            6.1.7601

Select @@VERSION:

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Oct 20 2015 15:36:27
Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on
Windows NT 6.1  (Build 7601: Service Pack 1)

I have added this index and it has sped up the join to 0ms average duration:

CREATE INDEX [IDX_FLAG_LEVEL_CODE]
     ON Code (CommissioningFlag, AggregationLevelId)
     INCLUDE (Serial, CodeId);

I found the solution by chance, while resolving my previous question.


The column order of composite clustered primary key on a partitioned table? I have the following table.

create table T (K1 date, K2 int references S(Id), ....) on partitionScheme(K1)

The table will be partitioned by K1 (K1 has low selective. the data will be appended in the order by K1). Which of the following primary keys (the column order is different) is preferred?

alter table T add primary key clustered (K1, K2)
alter table T add primary key clustered (K2, K1)

Or should the PK be non-clustered and create another clustered index?

A lot of the queries look like:

select ....
from T join S on S.Id = T.K2
where ....

Which of the following primary keys (the column order is different) is preferred?

Like all indexing decisions, much depends on how the table will be queried.

All partitioned indexes (for SQL Server 2008 and later) have the partition ID (not partitioning key value) as a hidden leading key column in each partitioned index, so the effective competing definitions are:

PartitionID, K1, K2
vs.
PartitionID, K2, K1

This affects the utility of each index for different types of queries, as one would expect. The main extra consideration is that inequality seeks on the first real key (K1 or K2) are still supported, regardless of any inequality seek and/or partition elimination operations on the PartitionID column.

For example, the (K1, K2) index specification can seek to a range of partitions, and a range of K1 values simultaneously:

SELECT T1.*
FROM dbo.T1 AS T1
WHERE 1 = 1
AND T1.K1 >= CONVERT(date, '20080711', 112)
AND T1.K1 <= CONVERT(date, '20100711', 112);

The same query on a table with (K2, K1) as the clustered index key could seek find the range of partitions, but it would have to fully scan each qualifying partition to locate rows that match the K1 predicates exactly. To be clear, the test of K1 values would be applied as a residual predicate, not as a seeking operation.

This will show as a Clustered Index Scan in showplan, with a partition-eliminating seek, and residual predicate on K1 values:

A subtle point when using the date data type as the partitioning key is that you must be careful to use explicit date types in your queries if you expect partition elimination to occur reliably. Using other types, such as datetime, is easily done (by accident) but will often prevent elimination, where it would logically be expected.

For example, this query will touch all partitions:

DECLARE @dt datetime = '20080711';

SELECT *
FROM dbo.T1 AS T1
WHERE T1.K1 = @dt;

Whereas this query will touch only a single partition:

DECLARE @dt date = '20080711';

SELECT *
FROM dbo.T1 AS T1
WHERE T1.K1 = @dt;

Both queries look superficially identical in graphical showplan (a clustered index seek). You need to check the operator properties in detail to check if static or dynamic partition elimination is being applied.

For the example join query given in the question: Both indexing strategies include the K2 column, but neither can generally provide rows in K2 order without a sort. As a result, either index is equally good for a hash or nested loops join, but neither can provide the input order required for a merge join on K2.

This might seem counter-intuitive for the (K2, K1) index, but remember the leading PartitionID key. Each partition has rows in (K2, K1) order. Unless exactly one partition is specified in the query, a sort will be necessary to return rows in K2 order. The (K1, K2) index could only return rows in K2 order for a single partition and a single given value of K1.

The proposed clustered primary key (K1, K2) has the potential advantage of minimizing base table page splits if appended data is actually sorted by the clustering key during the insert operation. For the (K1, K2) index, this would mean rows sorted by (PartitionID, K1, K2). For (K2, K1), it would be (PartitionID, K2, K1).

Related reading: Query Processing Enhancements on Partitioned Tables and Indexes


Adding an index very slow...is there a mysql cmd to get an ETA or show progress? I'm currently running an alter query on my table (20M entries)
in order to add an index. it's running for more than 3 days already
(stuck on 'copy to tmp table').

is there a way I can see the progress of the query or in other word
is there a way I can get an estimation time of completion ?

thanks.

When mysql alters a table, it essentially makes a copy of of it and then swaps the copy in. This way if you cancel the update in the middle, the table is still in a stable state. So you can look in the mysql data directory (/var/lib/mysql/ ?) to see how large the new file is, that will tell you how far along it is. This is a bit more difficult with Innodb, but there is a tmp table being created somewhere.

You can significantly reduce the amount of a time an index takes by increasing your sort buffer variables (myisam_sort_buffer_size, sort_buffer_size). Make those as large as you can with memory you have. You could cut a few days off of the modification time, even get it down to a few hours depending on how much memory you have. I've done a 150M record table in about 3 hours.


Insert value into table without knowin column name I'm trying to update values in table using column name from another table, why it is not working?

You can see that I have in table TableOfDocuments, in column WORD1, the name DANA.

Now I need to go to the other table where column name is DANA and add 1
this is my code:

 UPDATE BagOfWords
 SET (SELECT WORD1 FROM TableOfDocuments WHERE ID=1)=1
 WHERE USER_ID=1

with this code I'm getting the column name in the other table (Dana):

SELECT WORD1 FROM TableOfDocuments WHERE ID=1

BagOfWords Table

USER_ID|DANA|LOVES|EAT
----------------------
   1  |  0  |  0  |  0

TableOfDocuments Table

ID|WORD1|WORD2|WORD3
--------------------
1 | Dana| loves |pizza

So, after getting help is it possible to do something like this in order not to type WORD1---WORD5 separately? Obviously I tried but this doesn't work, what do I need to add or change in order to make this work?

 declare
      l_col varchar2(128);
    begin
      select word1,word2,word3,word4,word5 into l_col from TableOfDocuments WHERE ID=1;
      execute immediate 'UPDATE BagOfWords SET ' || l_col || ' = 1 WHERE USER_ID=1';
    end;

You can not do that, you need dynamic SQL, and be careful with that. Below is a simple example (without any error handling, for educational purpose only).

declare
  l_col varchar2(128);
begin
  select word1 into l_col from TableOfDocuments WHERE ID=1;
  execute immediate 'UPDATE BagOfWords SET ' || l_col || ' = 1 WHERE USER_ID=1';
end;
/


SQL Server with small database size and huge log file size I have several databases, for some log file size is growing more than normal even though very little data is added to few table around 50-100 rows a month. i.e. Database 12 data file size is 79MB and it log file size is 833MB, and over a week log file size has increased by almost 100MB while only about 100-150 records were added into one of the contact table using web form which only have 5-6 fields.

Similar issue with database 1 , database size is 16MB and log file now is around 554MB, database 1 is a test database and no data was added to this DB over last 1 year.

We have tried shrinking database that didn't make much difference...

Not sure what should i do next & why database log file is so huge. Also when we take backup .bak file those files are also large as log files example Database 10 .bak file is also around 800MB.

Production server is SQL Server 2014 Standard edition

Recovery model for Database 10 is Simple and for all other databases its Full

could this be the reason for large log files

Could this be the reason for large log files

Yes, particularly with FULL recovery model and if there is NO frequent LOG backups as the in-active VLFs in LOG file cannot be truncated without backup in full recovery model.

With Simple recovery model LOG backup is not the case, most probably at one point of time there might have been a heavy transaction/Index maintenance online performed and to accommodate that heavy operation LOG size increased, from that moment on-wards the in-active VLF of log file would be truncated automatically in simple recovery model but that would free-up the space in LOG file not reduce size of LOG file unless the truncation performed manually (DBCC SHRINKFILE). To check used and free space of LOG: DBCC SQLPERF ('Logspace')

Shrinking file (in your case with simple recovery) is not recommended as there might possibility same amount of space required in LOG file to accommodate upcoming heavy transaction.

  We have tried shrinking database that didn't make much difference.

Assuming you tied DBCC SHRINKFILE to shrink the LOG file size in full recovery model, if there is no impact with SHRINKFILE command there must still active portion on LOG which would not allow us to truncate, usually when you look at message section after DBCC execution it clearly indicates. Also you can check what stopping the SHRINK operation with following query

Descriptions of log_reuse_waits

select  name,
        log_reuse_wait_desc,
        is_distributor,
        is_merge_published,
        is_published,
        is_cdc_enabled
from sys.databases

You may want read detailed explanation on LOG SIZE GROWTH

If you're not concerned on point in time recovery

Perform LOG backup of a database
Change recovery mode to SIMPLE (ALTER DATABASE YourDBName SET RECOVERY SIMPLE)
Shrink the file size (DBCC SHRINKFILE (2, 1024))

  2 indicates file ID
  1024 indicates new size of file (keep enough space as per your workload)

Since the LOG backups are not maintained, keep the database in simple recovery mode so that it wouldn't grow rapidly


MySQL: Optimize UNION with "ORDER BY" in inner queries I just set up a logging system which consists of multiple tables with the same layout.

There is one table for each data source.

For the log viewer, I want to

UNION all the log tables,
filter them by account,
add a pseudo column for identification of the source,
sort them by time,
and limit them for pagination.

All tables contain a field called zeitpunkt that is an indexed date/time column.

My first attempt was:

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
 'hp' AS source FROM is_log AS l WHERE l.account_id = 730)

UNION

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
 'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730)

ORDER BY zeit DESC LIMIT 10;

The optimizer cannot use the indexes here because all rows from both tables are returned by the subqueries and sorted after the UNION.

My workaround was the following:

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
 'hp' AS source FROM is_log AS l WHERE l.account_id = 730
 ORDER BY l.zeitpunkt DESC LIMIT 10)

UNION

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
 'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730
 ORDER BY l.zeitpunkt DESC LIMIT 10)

ORDER BY zeit DESC LIMIT 10;

I was expecting the query engine would use the indexes here since both subqueries should be sorted and limited already prior to the UNION, which then merges and sorts the rows.

I really thought this would be it, but running EXPLAIN on the query tells me the subqueries still search both tables.

EXPLAINing the subqueries themselves shows me the desired optimization but UNIONing them together it does not.

Did I miss something?

I know that ORDER BY clauses inside UNION subqueries are ignored without a LIMIT, but there is a limit.

Edit:
Actually, there probably will also be queries without the account_id condition.

The tables already exist and are filled with data. There might be changes in the layout depending on the source so I want to keep them divided.  Additionally, the logging clients use different credentials for a reason.

I have to keep a kind of layer between the log readers and the actual tables.

Here are the execution plans for the whole query and the first subquery as well as the table layout in detail:

https://gist.github.com/ca8fc1093cd95b1c6fc0

Just out of curiosity, can you try this version? It may trick the optimizer to use the same indices that the subqueries would use separately:

SELECT *
FROM
(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
 'hp' AS source FROM is_log AS l WHERE l.account_id = 730
 ORDER BY l.zeitpunkt DESC LIMIT 10)
    AS a

UNION ALL

SELECT *
FROM
(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
 'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730
 ORDER BY l.zeitpunkt DESC LIMIT 10)
    AS b

ORDER BY zeit DESC LIMIT 10;

I still think that the best index you could have is the compound (account_id, zeitpunkt). It would yield the 10 rows fast, and no tricks would ne needed.


How to query given grants on Oracle? On a database I had to execute the following two statements:

GRANT CREATE ANY TABLE TO MyUSER;
GRANT DROP ANY TABLE TO MyUSER;

What I miss is the query which tells me that those grants are given. I don't see them in

select * from USER_TAB_PRIVS ORDER BY 2 ;
select * from USER_ROLE_PRIVS ORDER BY 1, 2;


Is MSDTC required for SQL Server 2012 Fail Over Cluster? I am making a 2 node SQL Server 2012 Failover Cluster; do I need to install the MSDTC component?

If yes, can both be installed on single shared disk?

MSDTC is not required for a SQL Server 2012 fail over cluster.  However, if you plan to use Linked Servers, then you will need to create a clustered MSDTC resource.  The good news is that can be setup after the cluster is already built and after SQL Server has been installed.


Blocking automatic vaccum I have a table message. I saw that queries for this table where long running and caused a memory leak (scheduled job in java app).
In pg_stat_activity I saw:
wait_event_type wait_event       state
IO              DataFileRead    active

So I increased available disk space, but it didn't solve this problem.
I saw that there was long running autovaccum worker for this table. I checked table size and it was about 90% of available RAM. I renamed this table and created new one with same schema.
So now I have a new table with few records, but the long running autovaccum worker for this table still occurs.
Do you have any ideas what to check, how to tune this table?

You have two problems:

High I/O load:
This may or may not be caused by autovacuum, but since autovacuum is an important system process, the solution is not to subdue it.
Tune your queries so that they cause less I/O. If that doesn't suffice, get a faster I/O system or more RAM.

Long running autovacuum workers:
This can be exacerbated by I/O contention, but the problem is almost always that autovacuum is working too slow. Decrease autovacuum_vacuum_cost_delay to make it faster (but of course that will cause more I/O).

Renaming a table won't make autovacuum go away (dropping would). You will have to let it finish.
Make sure that you have no long running transactions or stale replication slots, otherwise autovacuum cannot do its job properly and keep trying again and again.


Warning in Execution Plan I have a T-SQL query with four inner joins, I executed the query in SSMS with Actual Execution Plan selected. When I review the generated plan I saw the warning icon on Sort operator.
What is the reason for this warning in Sort operator?

The Query Plan URL: https://www.brentozar.com/pastetheplan/?id=HJULL9H4N

I provided the screen shot of Execution Plan.

The Warning:

What is the reason for this warning in Sort operator?

You can see the warning description in properties of the element. I'm sure you will see information like below:

  Operator used tempdb to spill data during execution.....

It means that memory which was granted for the query turned out to be less than it needed. Two possible reasons for that:

Wrong cardinality estimation so the query asked for the less memory grant than it really needed
Lack of memory so the query asked enough memory but got less

Detailed information about memory grant can be found in the properties window of SELECToperation from your query plan.


is it possible to use AAG with two 2-Node Clusters across two datacenters? Is the following scenario possible?

2-Node Automatic Failover Cluster in DC1, shared disk
2-Node Automatic Failover Cluster in DC2, shared disk
employ a 'Node and Disk Majority'
employ AAG across datacenters
failover will be manual; availability mode asynchronous

The datacenters are up to 15.5-miles apart.

Thanks for the help!

Simply put, if you have, or can create, a Windows Server Failover Cluster setup across the data centres as a stretch cluster, then there is no reason why this is won't be possible. The fact the DC's are geographically dispersed is irrelevant. Good networking can bring them together as if they were not.

You could also do this as a SQL Cluster, with replication across the disks, and the disks presented to the cluster as a shared cluster resource.

I think you need to read up on the Pro's and Con's of each solution, and maybe speak to a Windows Server and/or Networking professional.


In sql server replication when stored procedures get replicated, it creates MSins, MSdel and MSupd procedures for all tables in the database I have single sql instance and I have created replication of my database in same instance. Now when I have inserted data into the tables I found out that replication of insert stored procedure is getting created in the replica database. This happens for delete and update also. So now my replica database has MSins, MSupd and MSdel for all of my tables available in database. How to stop this?

That's the default design for SQL Server subscribers. Those procedures get created and hidden as system stored procedures, so, they don't normally display. If you're doing queries and picking them up then be sure to exclude system objects.

You can modify it to use INSERT/UPDATE/etc which is the default for non-SQL Server subscribers by following the instructions from BOL.

  On the Properties tab of the Article Properties -  dialog
  box, in the Statement Delivery section, specify the propagation method
  for each operation using the INSERT delivery format, UPDATE delivery
  format, and DELETE delivery format menus.

... or ...

execute sp_addarticle
@ins_cmd - controls the replication of INSERT commands.
@upd_cmd - controls the replication of UPDATE commands.
@del_cmd - controls the replication of DELETE commands.

  When specifying a value of SQL for any of the above parameters, commands of that type will be replicated to the Subscriber as the appropriate Transact-SQL command.


Pivoting rows into columns dynamically in Oracle I have data like the following in an Oracle database

select * from TABLE_1

ID       Field      Value
----     -----   -----
  1      name    Bob
  1      age     30
  1      gender  male
  2      name    Susan
  2      status  married

I need the result in below format

ID       NAME    AGE    GENDER  STATUS
----     -----   -----  ------  --------
  1      Bob      30     male
  2      Susan                   married

I wrote a query

select
   ID,

    coalesce(sum(case when Field = 'name' then value end), 0) as "Name",
    coalesce(sum(case when Field = 'age' then value end), 0) as "Age",
    coalesce(sum(case when Field = 'gender' then value end), 0) as "Gender"
 from "TABLE_1"
group by ID;

I am getting a conversion error:

  ORA-00932: inconsistent datatypes: expected CHAR got NUMBER.

What am I doing wrong?

Column value is a string type (CHAR, VARCHAR2).
You can not SUM strings. Use another aggregate function, e.g. MIN. And you do not need COALESCE.

select
   ID,
    min(case when Field = 'name' then value end) as "Name",
    min(case when Field = 'age' then value end) as "Age",
    min(case when Field = 'gender' then value end) as "Gender",
    min(case when Field = 'status' then value end) as "Status"
 from "TABLE_1"
group by ID;

        ID Name  Age   Gender Status
---------- ----- ----- ------ --------
         1 Bob   30    male
         2 Susan              married


Where are sql profiler trace files stored? Can't seem to find an answer to this..

During a recent sql profile I noticed C:\ was rapidly filling up.

Does anyone know exactly where these files are stored? is it part of SSMS?

If you create a new trace and check the 'Save to file' box, you will see a save as dialogue box which defaults to Libraries\Documents.

If your goal is to locate the trace file that you already saved, simply try opening the file in profiler. If you still cannot find it:

Download WinDirStat (https://windirstat.info/)
Scan your C: drive.
Look for the trace file extension on the top right of the UI (.trc) and select it. Doing this will highlight the location of the file(s). Right click and "explorer here" will do the trick.

Retracing your steps should suffice though.


How to model double relationships? Background: I have designed a solution for Filemaker to input data into a Question Database.

After we are done with inputting data into Filemaker, we will be exporting the data as excel and transferring it into an online system probably running on ASP.NET or PHP

Part of it looks like this:

Knowledge Table:
Knowledge ID (Primary Key),
Description,
Knowledge Type

Method Table:
Method ID (Primary Key),
Step,
Step Knowledge ID

For example. The "Find Acceleration method" Knowledge can have 3 steps:

Find unknown velocity
Find unknown time
Apply acceleration formula

Each of these steps is also a record in the Knowledge Table, i.e., the steps are knowledges in Knowledge Table.

How do I model the double relationship between Knowledge Table and Method Knowledge Step Table?

Currently, I thought of 2 ways:

Knowledge Table::Knowledge ID -- Method Table::Method ID relationship AND Knowledge Table::Knowledge ID --< Method Table::Step Knowledge ID
Creating another instance of Knowledge Table and link the Knowledge ID separately to Method Table: Step Knowledge ID.

I'm really not too sure of what is the best way of doing it.

Since a Knowledge might be a Method, I'm wondering if it would make more sense to store them both in the same table:

KnowledgeAndMethods
--------------------
  ID - unique primary key
  Description
  parent_knowledge - nullable (for methods that are both a Method and a Knowledge, this is the Knowledge that the Method belongs to)
  knowledge_type
  Step
  Step_ID

Based on your example, I think the data would be stored as:

ID Description                  Parent_knowledge  Step      Step ID
-------------------------------------------------------------------
1 "Find Accel. Method"
2 "Find unknown velocity"         1               "Step 1"     S1
3 "Find unknown time"             2               "Step 2"     S2
4 "Apply acceleration formula"    3               "Step 3"     S3
5 "Land rocket ship"

With recursive queries, it could be very easy to get data back from this kind of table.

You could have a KnowledgeSubtype or MethodSubtype column if you want, but I'm not sure if you would gain anything.

Based on comments, it sounds like a many-to-many relationship is wanted. So here's another try at it:

KnowledgeAndMethods
--------------------
  ID - unique primary key
  Description
  parent_knowledge - nullable (for methods that are both a Method and a Knowledge, this is the Knowledge that the Method belongs to)
  knowledge_type

Methods_Steps
  Parent_Knowledge_ID - the ID of the parent Knowledge
  Step
  Step_ID - The ID local within a set of Steps
  Method_ID  - The ID of the Method that is a Knowledge

So now the previous example might look like:

KnowledgeAndMethods:

ID Description
--------------------------------
1 "Find Accel. Method"
2 "Find unknown velocity"
3 "Find unknown time"
4 "Apply acceleration formula"

Methods_Steps:
Knowledge_ID  Step      Step_ID    Method_ID
--------------------------------------------
1             "Step 1"  1          2
1             "Step 2"  2          3
1             "Step 3"  3          4
5             "Step 9"  6          2   <---- Here's an example of "Find Unknown Velocity" being a different "Step" in a diffent "Knowledge"


Set all images name of a product to the same field in other table separated by comma in MySQL I'm using MariaDB v10.1.31. I have 2 tables:

Product_images (created from a .csv import).

product_reference | name

REF.001           | example_image_1.jpg
REF.001           | example_image_2.jpg

Importer* (created to store all the data I need from others tables).

sku     | images

REF.001 | null

How could I update Importer to show the following?

sku     | images

REF.001 | example_image_1.jpg,example_image_2.jpg

*Data in Importer would be to export in .csv and be able to import in WooComerce, this is why I need to import the images by this way.

You can use GROUP_CONCAT().

UPDATE importer
JOIN (
    SELECT product_reference, GROUP_CONCAT(name) AS name
    FROM Product_images
    GROUP BY product_reference
) pi ON importer.sku= pi.product_reference
SET importer.images = pi.name;

I should mention though, that your schema design may not be the best, depending on what you want to do. Read about normal forms, if you don't know what that is. A OLTP database usually is in at least 3rd normal form. Comma separated values violate the 1st normal form already.


How to concurrency will be handled in this case? Say I have employee table with 100 records . Out of 100 records, 60 records having middle_name as null

Say in two separate sessions(say Session_A, Session_B),  I execute below query at same time

    update employee middle_name ='test_middle_name' where middle_name is null

My question is is there a possibility Session_A can work on X records where Session_B work on Y records where X+Y=60 on is it always
single session will get lock on all 60 records and there will be nothing to process for  another session ?

I understand both process will try to get the exclusive row level lock on applicable rows but  process will try to acquire those lock row by row or in one go.

My Understanding:-

My understanding is It is possible that Session_A can work on X records where Session_B work on Y records where X+Y=60. Reason is each process
will get some CPU cycles(say in round robin fashion) and pick the records based on available records whose middle_name is null

The processes can theoretically work concurrently on different records, row locks are obtained as needed not in advance. However, in this case they are bound to run into each other sooner or later. If both have locked at least one record when that happens they will deadlock (both wait for the other to release a lock that it needs) and the database will kill one. Alternatively one will get the locks first and the other will wait until it commits; then it will update the records again.

In other words you can never end up with a scenario where A has updated some records and B some records. A will either update all or fail (due to deadlock/timeout) and B will also either update all or fail.

EDIT: if you do want to share load in this way you can get that effect using a select ... for update skip locked. Then locked records are simply ignored and the sessions can split the work, often quite useful in polling scenarios. Or use a parallel hint for the update and make a single session run in parallel.


Automatically partition a table in mysql (pymysql) everyday for new data Im still figuring out partitioning in SQL.

Is it possible to automatically partition a table everyday for new data? For example, on Monday, I have one partition. On Tuesday, automatically create a new partition, insert that day's data into the new partition and so on... Is reorganize partition the right way to go?

Yes REORGANIZE of a "future" partition is the right way.  See http://mysql.rjweb.org/doc.php/partitionmaint for a discussion of why, plus sample Perl code.

Do not create more partitions that you need immediately; it leads to inefficiencies.  Instead, check to see if you need a new partition more often than simply "just in time".


list of views that depend on a given table and column I need a script to check if a given column in a given table is used in any views.  Ideally I would like to get the name of the view(s) as well.
I am using T-SQL, and I need to run the script to check this inside an application I am developing.

SQL Server provides a system view, sys.sql_dependencies, which maps the dependencies consumed by objects such as views.
So, to view all the tables and columns associated with the views in your database, use the following query:
;WITH deps
AS
(
    SELECT sd.object_id
        , sd.referenced_major_id
    FROM sys.sql_dependencies sd
    WHERE sd.class = 0
        OR sd.class = 1
    GROUP BY sd.object_id
        , sd.referenced_major_id
)
SELECT ViewName = v.name
    , ReferencedObjectName = o.name
    , ReferencedColumns = STUFF((SELECT N', ' + c.name
                                 FROM sys.columns c
                                    INNER JOIN sys.sql_dependencies sd1 ON c.object_id = sd1.referenced_major_id
                                        AND c.column_id = sd1.referenced_minor_id
                                 WHERE c.object_id = sd.referenced_major_id
                                 ORDER BY c.object_id FOR XML PATH('')
                                 ), 1, 2, N'')
FROM sys.views v
    INNER JOIN deps sd ON v.object_id = sd.object_id
    INNER JOIN sys.objects o ON sd.referenced_major_id = o.object_id
ORDER BY v.name;

Consider this simple test:
DROP VIEW dbo.my_view_2;
DROP VIEW dbo.my_view;
DROP TABLE dbo.my_table;
DROP TABLE dbo.my_table_2;
CREATE TABLE dbo.my_table (Column_I int NOT NULL, Column_J int NOT NULL);
CREATE TABLE dbo.my_table_2 (Column_J int NOT NULL, Column_K int NOT NULL, Column_L int NOT NULL);
GO
CREATE VIEW dbo.my_view AS SELECT mt.Column_I FROM dbo.my_table mt INNER JOIN dbo.my_table_2 mt2 ON mt.Column_J = mt2.Column_J;
GO
CREATE VIEW dbo.my_view_2 AS SELECT mt.Column_I FROM dbo.my_table mt;
GO

The results of the query above show:
╔═══════════╦══════════════════════╦══════════════════════════════╗
║ ViewName  ║ ReferencedObjectName ║      ReferencedColumns       ║
╠═══════════╬══════════════════════╬══════════════════════════════╣
║ my_view   ║ my_table             ║ Column_I, Column_I, Column_J ║
║ my_view   ║ my_table_2           ║ Column_J                     ║
║ my_view_2 ║ my_table             ║ Column_I, Column_I, Column_J ║
╚═══════════╩══════════════════════╩══════════════════════════════╝
This would limit the output to only views that depend on a specific table:
;WITH deps
AS
(
    SELECT sd.object_id
        , sd.referenced_major_id
    FROM sys.sql_dependencies sd
    WHERE sd.class = 0
        OR sd.class = 1
    GROUP BY sd.object_id
        , sd.referenced_major_id
)
SELECT ViewName = v.name
    , ReferencedObjectName = o.name
    , ReferencedColumns = STUFF((SELECT N', ' + c.name
                                 FROM sys.columns c
                                    INNER JOIN sys.sql_dependencies sd1 ON c.object_id = sd1.referenced_major_id
                                        AND c.column_id = sd1.referenced_minor_id
                                 WHERE c.object_id = sd.referenced_major_id
                                 ORDER BY c.object_id FOR XML PATH('')
                                 ), 1, 2, N'')
FROM sys.views v
    INNER JOIN deps sd ON v.object_id = sd.object_id
    INNER JOIN sys.objects o ON sd.referenced_major_id = o.object_id
WHERE o.name = N'my_table_1'
ORDER BY v.name;

This could be used to show views that depend on any column in the database with a specific name column_j:
;WITH deps
AS
(
    SELECT sd.object_id
        , sd.referenced_major_id
    FROM sys.sql_dependencies sd
    WHERE sd.class = 0
        OR sd.class = 1
    GROUP BY sd.object_id
        , sd.referenced_major_id
)
SELECT ViewName = v.name
    , ReferencedObjectName = o.name
    , ReferencedColumns = STUFF((SELECT N', ' + c.name
                                 FROM sys.columns c
                                    INNER JOIN sys.sql_dependencies sd1 ON c.object_id = sd1.referenced_major_id
                                        AND c.column_id = sd1.referenced_minor_id
                                 WHERE c.object_id = sd.referenced_major_id
                                 ORDER BY c.object_id FOR XML PATH('')
                                 ), 1, 2, N'')
FROM sys.views v
    INNER JOIN deps sd ON v.object_id = sd.object_id
    INNER JOIN sys.objects o ON sd.referenced_major_id = o.object_id
WHERE EXISTS (
        SELECT 1
        FROM sys.columns c
        WHERE c.object_id = sd.referenced_major_id
        AND c.name = N'Column_J'
        )
ORDER BY v.name;


Constraint to enforce "at least one" or "exactly one" in a database Say we have users and each user can have multiple email addresses

CREATE TABLE emails (
    user_id integer,
    email_address text,
    is_active boolean
)

Some sample rows

user_id | email_address | is_active
1       | foo@bar.com   | t
1       | baz@bar.com   | f
1       | bar@foo.com   | f
2       | ccc@ddd.com   | t

I want to enforce a constraint that every user has exactly one active address. How can I do this in Postgres? I could do this:

CREATE UNIQUE INDEX "user_email" ON emails(user_id) WHERE is_active=true;

Which would protect against a user having more than one active address, but wouldn't, I believe, protect against all of their addresses being set to false.

If possible I'd prefer to avoid a trigger or a pl/pgsql script, as we don't currently have any of those & it would be difficult to set up. But I would appreciate knowing "the only way to do this is with a trigger or pl/pgsql", if that is the case.

You don't need triggers or PL/pgSQL at all.
You don't even need DEFERRABLE constraints.
And you don't need to store any information redundantly.

Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the chicken-and-egg problem of inserting a user and his active email, but using data-modifying CTEs we don't even need that.

This enforces exactly one active email per user at all times:

CREATE TABLE users (
  user_id  serial PRIMARY KEY
, username text NOT NULL
, email_id int NOT NULL  -- FK to active email, constraint added below
);

CREATE TABLE email (
  email_id serial PRIMARY KEY
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE
, email    text NOT NULL
, CONSTRAINT email_fk_uni UNIQUE(user_id, email_id)  -- for FK constraint below
);

ALTER TABLE users ADD CONSTRAINT active_email_fkey
FOREIGN KEY (user_id, email_id) REFERENCES email(user_id, email_id);

Remove the NOT NULL constraint from users.email_id to make it "at most one active email". (You can still store multiple emails per user, but none of them is "active".)

You can make active_email_fkey DEFERRABLE to allow more leeway (insert user and email in separate commands of the same transaction), but that's not necessary.

I put user_id first in the UNIQUE constraint email_fk_uni to optimize index coverage. Details:

Is a composite index also good for queries on the first field?

Optional view:

CREATE VIEW user_with_active_email AS
SELECT * FROM users JOIN email USING (user_id, email_id);

Here's how you insert new users with an active email (as required):

WITH new_data(username, email) AS (
   VALUES
      ('usr1', 'abc@d.com')   -- new users with *1* active email
    , ('usr2', 'def3@d.com')
    , ('usr3', 'ghi1@d.com')
   )
, u AS (
   INSERT INTO users(username, email_id)
   SELECT n.username, nextval('email_email_id_seq'::regclass)
   FROM   new_data n
   RETURNING *
   )
INSERT INTO email(email_id, user_id, email)
SELECT u.email_id, u.user_id, n.email
FROM   u
JOIN   new_data n USING (username);

The specific difficulty is that we have neither user_id nor email_id to begin with. Both are serial numbers provided from the respective SEQUENCE. It can't be solved with a single RETURNING clause (another chicken-and-egg problem). The solution is nextval() as explained in detail in the linked answer below.

If you don't know the name of the attached sequence for the serial column email.email_id you can replace:

nextval('email_email_id_seq'::regclass)

with

nextval(pg_get_serial_sequence('email', 'email_id'))

Here's how you add a new "active" email:

WITH e AS (
   INSERT INTO email (user_id, email)
   VALUES  (3, 'new_active@d.com')
   RETURNING *
   )
UPDATE users u
SET    email_id = e.email_id
FROM   e
WHERE  u.user_id = e.user_id;

SQL Fiddle.

You might encapsulate the SQL commands in server-side functions if some simple-minded ORM isn't smart enough to cope with this.

Closely related, with ample explanation:

How to deal with mutually recursive inserts
Complex foreign key constraint in SQLAlchemy

Also related:

Enforcing constraints "two tables away"

About DEFERRABLE constraints:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

About nextval() and pg_get_serial_sequence():

PostgreSQL SELECT primary key as "serial" or "bigserial"
Duplicate row with Primary Key in PostgreSQL


General tool to load dump files I am a big fan of Postgres both for its price but also for its features.
I am going to have to need to upload into it both Oracle dump and SQL Server files.
I will try to ask and beg for plain .csv for schema DDL but I suspect that I will be given dmp files.

Is there a tool, most preferably open source one, that would allow me to read, profile and possibly load Oracle/SQL Server files into Postgres?

Your question is confusing. Are you looking for an ETL tool? Most connect to heterogeneous sources (i.e. Oracle, SQL Server, flat files, etc.) These tools also allow you to examine the structure of the source system. Informatica and SSIS are two that come to mind. Profiling data may take some manual effort via queries in the source system.

When you say plain txt for DDL: Any RDBMS will allow you to export the DDL of tables. .sql files should be usable, but be aware of datatype differences between RDBMS.

If you are provided dumps or backups you must restore the backups or dumps into the appropriate RDBMS. From the restored databases you will be able to design an ETL into Postgres. Your ETL could be as simple as dump all the source tables to CSV files. Oracle and SQL Server both have excellent command line export utilities.


Is there a way to SELECT n ON (like DISTINCT ON, but more than one of each) I have a table us_customers that looks like this (with hundreds of thousands of rows):

+----------+----------+
|    id    | us_state |
+----------+----------+
| 12345678 | MA       |
| 23456781 | AL       |
| 34567812 | GA       |
| 45678123 | FL       |
| 56781234 | AZ       |
| 67812345 | MA       |
| 78123456 | CO       |
| 81234567 | FL       |
+----------+----------+

... and I want to SELECT a sample of n customers from each us_state.

Is there a way to do that cleanly in PostgreSQL 9.3?

I can get one customer from each us_state easily with:

SELECT DISTINCT ON (us_state) id
FROM us_customers
ORDER BY us_state;

But if I want, say, three customers from each state, is there a way I can do this without running the same query multiple times?

You can number and order id by us_state using the ROW_NUMBER() Window Function  and only keep the n first values:

SELECT *
FROM (
  SELECT *
    , ROW_NUMBER() OVER(PARTITION BY us_state ORDER BY id) as n
  FROM data
) as ord
WHERE n <= 2
ORDER BY us_state
;

Or you can CROSS JOIN with a subquery:

SELECT l.*
FROM (
  SELECT DISTINCT us_state FROM data
) as s
CROSS JOIN LATERAL (
  SELECT *
  FROM data d
  WHERE d.us_state = s.us_state
  ORDER BY id
  LIMIT 2
) as l
ORDER BY l.us_state
;

Sample SQL Fiddle here
I used a small sample of 1 to 3 rows per state. Therefore I limit only to 2 values
I ordered it by ids but you can change that and order by whatever works best for you

Output with my small sample:

       id | us_state | n
      123 |       AL | 1
      456 |       AL | 2
 56781234 |       AZ | 1
 78123456 |       CO | 1
 45678123 |       FL | 1
 81234567 |       FL | 2
 34567812 |       GA | 1
      123 |       MA | 1
      456 |       MA | 2

Note that n is the result of ROW_NUMBER and doesn't exist in the second query.
On a big table, an index on the partition (us-state) and order (id here) columns will help.

Sample used:

CREATE TABLE data
    ("id" int, "us_state" varchar(2))
;

INSERT INTO data
    ("id", "us_state")
VALUES
    (12345678, 'MA'),
    (123, 'MA'),
    (456, 'MA'),
    (23456781, 'AL'),
    (123, 'AL'),
    (456, 'AL'),
    (34567812, 'GA'),
    (45678123, 'FL'),
    (56781234, 'AZ'),
    (67812345, 'MA'),
    (78123456, 'CO'),
    (81234567, 'FL')
;


Select Column value based on two values in another column I am using the below table:

1   0051ML66220600132482    06:00:00        06:00:00        1538    100 0   1
2   0051ML66220600132482    06:00:00        06:00:00        1540    200 0   0
3   0051ML66220600132482    06:00:00        06:00:00        1541    300 0   0
4   0051ML66220600132482    06:01:00        06:01:00        1542    400 0   0
5   0051ML66220600132482    06:01:00        06:01:00        1543    500 0   0
6   0051ML66220600132482    06:02:00        06:02:00        1544    600 0   0
7   0051ML66220600132482    06:03:00        06:03:00        1546    700 0   0

Table structure is like below:

------------------------------------------------------------------
--  TABLE stop_times
------------------------------------------------------------------
CREATE TABLE stop_times
(
    id int(12),
    trip_id varchar(100),
    arrival_time varchar(8),
    arrival_time_seconds int(100),
    departure_time varchar(8),
    departure_time_seconds int(100),
    stop_id varchar(100),
    stop_sequence varchar(100),
    pickup_type varchar(2),
    drop_off_type varchar(2)
);

I am attempting to get DISTINCT trip_id if it matches both the destination and arrival stop_id

I have tried the below SQL with no luck:

select DISTINCT trip_id from stop_times where stop_id=1538 AND stop_id =1540;

Where it should produce: 0051ML66220600132482

I have also tried an INNER JOIN like below:

SELECT
    t.trip_id,
    start_s.stop_name as departure_stop,
    end_s.stop_name as arrival_stop
FROM trips t
INNER JOIN stop_times start_st ON t.trip_id = start_st.trip_id
INNER JOIN stops start_s ON start_st.stop_id = start_s.stop_id
INNER JOIN stop_times end_st ON t.trip_id = end_st.trip_id
INNER JOIN stops end_s ON end_st.stop_id = end_s.stop_id
WHERE
   start_s.stop_id = 1538
  AND end_s.stop_id = 1540;

But it is sooo slow, and takes about 8-15 seconds for this simple query.

Explain Added:

What is the fastest/best way to write this query?

If I understand, you want to get all trip_id values that appear in the table, against one stop_id on one or more rows, and against another stop_id on other rows. This should do it:

select trip_id
from stop_times
where stop_id = 1538
or stop_id = 1540
group by trip_id
having count(distinct stop_id) > 1;

Example: http://sqlfiddle.com/#!9/e78aa/1

Notice that trip_id 0051ML66220600132483 matches on one stop but not the other, so is not returned.


How do I draw the erd to model this scenario? Customers are billed for the cost for the activity itself and also any "extra" items which are used as part of an activity.

So I have the customers entity (identified by customer id) and an item entity(identified by item id). Each of these has a cost attribute to determine the cost of each item/activity. But how do I model the purchases of this customer? E.g. the customer may pay for an activity and buy a few items for the activity.

Can I add a new entity called billingInfo?Or should there not be a new entity at all?

Consider the "bill" itself as an entity that has associated entities (activities and items). Something like this:

Customers, Items and Activities should all be separate entities, and you should include a separate entity for Sales (Bills) and Sales Line Items (Activities and Items part of the Bill). This way you can define Items and Activities once and include multiple (or no) Items and Activities on a single Sale entity record. Example below.

Setup:

USE master
GO
CREATE DATABASE Sales
GO
USE Sales
GO

CREATE TABLE Customers (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    CustomerName NVARCHAR(255)
)
GO

INSERT INTO Customers (CustomerName)
VALUES ('Joes Appliances'), ('Franks Bar & Grill')
GO

CREATE TABLE Items (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    ItemName NVARCHAR(255),
    ItemPrice DECIMAL(10,2)
)
GO

INSERT INTO Items (ItemName, ItemPrice)
VALUES ('Refrigerator', 599.99),
    ('Oven', 1299.99),
    ('Television', 249.99)
GO

CREATE TABLE Activities (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    ActivityName NVARCHAR(255),
    ActivityPrice DECIMAL(10,2)
)
GO

INSERT INTO Activities (ActivityName, ActivityPrice)
VALUES ('Install (per hour)', 29.99),
    ('Install (flat fee)', 80.00)
GO

CREATE TABLE SalesHeader (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    CustomerID INT NOT NULL,
    SaleDate DATETIME,
    CONSTRAINT FK_SalesHeader_Customer FOREIGN KEY (CustomerID)
    REFERENCES Customers (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
GO

CREATE TABLE SalesActivities (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    SalesHeaderID INT NOT NULL,
    ActivityID INT NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT FK_SalesActivities_SalesHeader FOREIGN KEY (SalesHeaderID)
    REFERENCES SalesHeader (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT FK_SalesActivities_Activity FOREIGN KEY (ActivityID)
    REFERENCES Activities (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
GO

CREATE TABLE SalesItems (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    SalesHeaderID INT NOT NULL,
    ItemID INT NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT FK_SalesItems_SalesHeader FOREIGN KEY (SalesHeaderID)
    REFERENCES SalesHeader (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT FK_SalesItems_Item FOREIGN KEY (ItemID)
    REFERENCES Items (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
GO

Test Data:

-- Sales example #1: Joe's Appliances purchased 1 refrigerator, 1 oven and 5 televisions, plus an install with a flat fee

INSERT INTO SalesHeader (CustomerID, SaleDate)
VALUES (1, '2018-09-02')

INSERT INTO SalesActivities (SalesHeaderID, ActivityID, Quantity)
VALUES (1, 2, 1)

INSERT INTO SalesItems (SalesHeaderID, ItemID, Quantity)
VALUES (1, 1, 1),
    (1, 2, 1),
    (1, 3, 5)

-- Sales example #1: Franks Bar & Grill purchased 3 ovens, 2 refrigerators and 12 televisions, plus an install with an hourly rate for 5 hours

INSERT INTO SalesHeader (CustomerID, SaleDate)
VALUES (2, '2017-02-24')

INSERT INTO SalesActivities (SalesHeaderID, ActivityID, Quantity)
VALUES (2, 1, 5)

INSERT INTO SalesItems (SalesHeaderID, ItemID, Quantity)
VALUES (2, 2, 3),
    (2, 1, 2),
    (2, 3, 12)

Query:

SELECT c.CustomerName,
    sh.ID AS [InvoiceID],
    sh.SaleDate,
    a.ActivityName AS [InvoiceLineType],
    sa.Quantity AS [InvoiceLineQuantity],
    a.ActivityPrice AS [InvoiceLinePrice]
FROM SalesHeader sh
INNER JOIN Customers c ON sh.CustomerID = c.ID
LEFT JOIN SalesActivities sa
    INNER JOIN Activities a ON a.ID = sa.ActivityID
ON sa.SalesHeaderID = sh.ID
UNION
SELECT c.CustomerName,
    sh.ID AS [InvoiceID],
    sh.SaleDate,
    i.ItemName AS [InvoiceLineType],
    si.Quantity AS [InvoiceLineQuantity],
    i.ItemPrice AS [InvoiceLinePrice]
FROM SalesHeader sh
INNER JOIN Customers c ON sh.CustomerID = c.ID
LEFT JOIN SalesItems si
    INNER JOIN Items i ON i.ID = si.ItemID
ON si.SalesHeaderID = sh.ID

Results:

CustomerName        InvoiceID   SaleDate                InvoiceLineType     InvoiceLineQuantity InvoiceLinePrice
----------------------------------------------------------------------------------------------------------------
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Install (per hour)  5                   29.99
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Oven                3                   1299.99
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Refrigerator        2                   599.99
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Television          12                  249.99
Joes Appliances     1           2018-09-02 00:00:00.000 Install (flat fee)  1                   80.00
Joes Appliances     1           2018-09-02 00:00:00.000 Oven                1                   1299.99
Joes Appliances     1           2018-09-02 00:00:00.000 Refrigerator        1                   599.99
Joes Appliances     1           2018-09-02 00:00:00.000 Television          5                   249.99


How to do a SQL Server 2016 continuous upgrade I watched this channel9 video, and it mentioned you could now do a continuous upgrade to MS SQL Server. This allows you have a side by side install and it will keep the databases in sync until you have fully migrated all of your apps. I can't find anything else online about this process. Is this called something else or does anyone have any ideas on how to accomplish this?

The continuous upgrade referenced in that video was specific to the Upgrade Advisor tool. The tool has since been renamed Data Migration Assistant.

More content on upgrades here: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server?redirectedfrom=MSDN&view=sql-server-ver15


Get first and last record for each date and calculate their difference I need to calculate the DATEDIFF in seconds between the First record for each date with EventCode = 1 and the last record for each date with EventCode = 2.

I have posted a sample data set for 2 days here: http://data.stackexchange.com/dba/query/515591/calculate-the-difference-between-first-event-and-last-event-for-each-date

So this is how I am getting the First Record for each Date:

Select
    LogDate,
    StartOfDay = MIN(LogDateTime),
    EventCode
FROM (
        Select
            LogId,
            LogDateTime,
            LogDate = FORMAT(LogDateTime, 'dd-MMM-yyyy'),
            DriverId,
            EventCode
        FROM
            #MyLogs
        WHERE EventCode = 1
     ) A
Group By
        LogDate,  EventCode

And this is how I am getting the Last Record for each date where EventCode = 2

Select
    LogDate,
    EndOfDay = MAX(LogDateTime),
    EventCode
FROM (
        Select
            LogId,
            LogDateTime,
            LogDate = FORMAT(LogDateTime, 'dd-MMM-yyyy'),
            DriverId,
            EventCode
        FROM
            #MyLogs
        WHERE EventCode = 2
     ) A
Group By
        LogDate, EventCode

But how do I get the same in single result set? And I need to get the difference in seconds between the First Record for each date and Last Record for each Date.

Example of Result Set:

| Date        | StartOfDay              | EndOfDay                | Start - End |
|-------------|-------------------------|-------------------------|-------------|
| 20-Jul-2016 | 2016-07-20 04:00:32.000 | 2016-07-20 13:05:19.000 |             |
| 21-Jul-2016 | 2016-07-21 04:55:52.000 | 2016-07-21 14:49:25.000 |             |
|             |                         |                         |             |

@Lennart's answer can be simplified to a single Group By:

SELECT LogDate, StartOfDay, EndOfDay,
    DATEDIFF(s, StartOfDay, EndOfDay) AS ElapsedSeconds
FROM
  (
    SELECT
        -- instead of FORMAT better use a Standard SQL CAST:
        CAST(LogDateTime AS DATE) AS LogDate,

        MIN(CASE WHEN EventCode = 1 THEN LogDateTime END) AS StartOfDay,
        MAX(CASE WHEN EventCode = 2 THEN LogDateTime END) AS EndOfDay
    FROM #MyLogs
    WHERE EventCode in (1,2)
    GROUP BY
        CAST(LogDateTime AS DATE)
        -- Pivot in a single stepby removing EventCode
  ) AS dt;

see query


Trigger to increment a column value after update in a column of same table I have a table with two columns:

C1  |  C2
 1  |   3

Whenever C1 gets updated to 0, C2 should be incremented by 1.

I have developed a trigger:

create trigger increment
after update on T1
begin
  if new.C1=0 then
    update T1 set c2 = c2 +1;
  end if;
end;

But this raises an error whenever I try to update column C1:

  ERROR 1442 (HY000): Can't update table 'T1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I have only one update trigger on this table. How can I make this work?

You can also change values using new. You just have to use not after but before trigger.

delimiter $$
create trigger increment
before update on T1
begin
    if new.C1=0 then
        set new.c2 = new.c2 +1;
    end if;
end $$
delimiter ;


Change permissions of postgres copy to exported file I have asked this question on SO but perhaps that was the wrong location. Apologies for double posting but the question may have a better audience here.

The original question is: https://stackoverflow.com/questions/25619290/change-permissions-of-postgres-copy-to-exported-file

And the text is below. Thanks.

When running the postgresql [9.3] "COPY TO" functionality, the postgres user creates a file. I have told it to put the file in the tmp directory (although this can be changed if needed).

This is run as part of a cron job, the user is an "export" user set up especially for this task. The end goal is for the exported file to end up in /home/export/created_file.csv.

1) postgres can't stash the exported file straight into the home directory of another user without setting up the folder to be overly permissive.

2) the export user can copy the file from /tmp to it's home directory, but then can not clean up (delete) the postgres owned file in /tmp.

This is a daily process and so old files will need to be deleted in some way. I can add a cron job to clean up the files afterwards but I was hoping for a cleaner solution - either a way to change the permissions on the file that postgres creates, or some way to give the export user the additional permission of being able to delete that file in advance of it's creation.

Giving the export user the same permissions as postgres in general however isn't an option, as it would then have free reign over the database.

Any advice, be it unix based or postgres based would be very welcome, thanks.

You'll want to create a directory without the t (sticky) permissions bit set, and have PostgreSQL copy the file into there. You should also make the directory setgid (g+s), as that makes the group inherited from the directory, not the creating process; see setgid directories.

You could put this directory in /tmp but personally I'd put it some neutral location like /var/lib, or mounted volume somewhere, as /tmp gets cleaned out automatically so you'll need to have the directory re-created if it's missing.

Assuming you do use /tmp:

mkdir -p /tmp/pgcsv
chgrp users /tmp/pgcsv
chmod u=rwX,g=rwsX,o= /tmp/pgcsv

then create the csv in there. It'll be automatically created with group-owner users, which will make it readable by your users (unless PostgreSQL's umask is too restrictive). Because the directory is group-writable by users (or whatever group you use) and does not have the t (sticky) bit set your users can delete files they do not own from it.

The reason this doesn't work when using /tmp/ directly is that the sticky bit is set in /tmp for security, so that user1 can't delete a file created by user2 and replace it with a symlink that user2 then writes to, allowing user1 to steal their data / trick them into overwriting things.

Other options:

Run the cron job as user export, using psql's \copy command, so it writes the data over the client/server socket and the file is created as the export user in the first place. This is by far the simplest option, and only slightly slower than a direct COPY.
Have a cron job running as root invoke the PostgreSQL copy then move the data file and change its ownership once the COPY finishes.
Give the export user the ability to run a very restricted command as root via sudo to move/delete.
Use POSIX ACLs to grant the postgres user special permission to write to /home/export (if POSIX ACLs are enabled on your file system)
Set group-owner of /home/export to postgres and make its permissions g=wx. PostgreSQL can add new entries, and overwrite anything within that it has write permission to, but not list entries. I don't like this option much.


SSMS - Can not bulk load because the file could not be opened We have a user who get this error message every time bulk load using network location it throws error below.

"Cannot bulk load because the file could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 15105)."

If users logs on the remote server and runs same query it works fine
FQDN is specified for the location of the file ( \servername\sharename\filename.txt)
When file is copied on the server ( C:\ drive) , query runs fine via SSMS
We are using SQL server 2008 R2

I have checked all NTFS permissions for the shared folder.
I have gone through numerous posts suggesting solution but no luck.

winerror.h lists Operating System Error Code 3 as ERROR_PATH_NOT_FOUND.  This likely indicates that \\servername\sharename\filename.txt either doesn't exist or the SQL Server Service Account does not have access to the file via the UNC.
Use SQL Server Configuration Manager to determine the name of the SQL Server Service Account:

Check the Fileshare Security for the UNC to ensure that account has access to the share.  Check the Filesystem Security for the folder underlying the UNC to ensure the account has access to the file.
T-SQL BULK INSERT requires an understanding of the security architecture.  Taken from that page:

If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.
When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.
To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.


Is it safe to move GPT formatted mdf file to MBR formatted disk? From our current SQL Server, I've copied a .mdf file to another disk (GPT to MBR).

It seems to create fault in whole disk, made MSSQLSERVICE offline.

Additionally, no error logs were unavailable while issue lasts.

However, I cannot specify if the incident was due to moving the file because incident occurred few days after moving the file.

Please help if anyone else had same symptoms or advice about what happens when moving GPT formatted .mdf file to MBR disk

In general SQL Server will not care that the database files are on a GPT or MBR formatted drive. You could use could use a floppy drive if you could find one big enough to hold the database (ouch on performance though! )

The only time it will be an issue is, as @James-Jenkins mentioned, if the size of your database files exceed the limits of what am MBR formatted drive can handle. This is 2TB. For a GPT drive it's 9.4ZB (9400 Terabytes!). Windows restricts this to 256TB.

If the MBR drive is running on a slow interface (USB1?) then you may experience IO performance issues but it shouldn't cause SQL Server to not run.

Investigate Windows system and application Logs in event Event Viewer to see if there are any messages related to the drive or the IO subsystem.

Check the disk using a testing tool to see if it's dying. If it's having problems you don't want to put your database on it.


Error in trigger: table is mutating CREATE OR REPLACE TRIGGER  "TRANSACTION"
before
update of  totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD
for each row
begin
  declare
    mportfolioname varchar2(20);
    mportfoliono number;
    moperationmode varchar2(8);
    mtransactionworth number;
    munits number;

    mnav number;

  begin
    select portfoliono into mportfoliono from sipholder where accno=:new.accno;
    select portfolioname into mportfolioname from sip_price
      where portfoliono=mportfoliono;
    munits:= :new.totalunitsbought - :old.totalunitsbought;

    select nav into mnav from sip_price where portfoliono=mportfoliono;

   if :new.totalunitsbought>:old.totalunitsbought then
      moperationmode:='B';
      mtransactionworth:=munits*mnav;
   else
      moperationmode:='S';
      mtransactionworth:=munits*mnav;
   end if;

   insert into transaction
     (accno, ransdate, modeofoperation, units, transactionworth, portfoliono,
      portfolioname, oldunits, newunits)
    values
      (:new.accno, sysdate, moperationmode, munits, mtransactionworth,
       :new.portfoliono, mportfolioname, :old.totalunitsbought,
       :new.totalunitsbought);

  end;
end;

  ERROR:

  ERROR at line 1:
     ORA-04091: table ORA-04091: table NETBANKING.SIPHOLDER is mutating,
     trigger/function may not see it
     ORA-06512: at "NETBANKING.TRANSACTION", line 12
     ORA-04088: error during execution of trigger 'NETBANKING.TRANSACTION'
     ORA-06512: at "NETBANKING.BUYUNITS_FIFTEEN", line 23
     . is mutating, trigger/function may not see it
     ORA-06512: at "SYS.DBMS_ISCHED", line 150
     ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
     ORA-06512: at line 1

Replace

select portfoliono into mportfoliono from sipholder where accno=:new.accno;

with

mportfoliono := new.portfoliono;

no need to use a SELECT for values of the updated row.


sp_BlitzIndex finding "Unknown" index I am going through cleaning up one of our databases, and after deleting an unused index and adding a primary key to the ARPayments table, sp_BlitzIndex is now reporting an index dbo.ARPayment.Unknown (0). I have refreshed my indexes and there is definitely no sign of any such index.

Here is the message it shows in the output from running sp_BlitzIndex 'db_name', @Mode=4:

  Should this table be a heap? dbo.ARPayment.Unknown (0)

sp_BlitzIndex will list Unknown for the index name when the table is a heap. It's not saying you have an index named Unknown. The primary key that you added was added as non-clustered, and you don't have a clustered index on the table. A heap is a table without a clustered index.

If you don't want your table to be a heap, you could drop the PK and recreate it as a clustered PK.


Is my JOIN syntax not valid? And have I understood JOIN correctly? I've been reading up a bit regarding JOINs. And i just noticed that I've been writing the syntax a little bit different. Although I don't think there's any problem with it, I would like to hear your oppinions.

This is how I see just about every tutorials writes the syntax:

SELECT a.<columns>
    b.<columns>
FROM table_a a
    INNER JOIN table_b b ON a.pk = b.fk_table_a_pk

And this is how I've been doing it:

SELECT a.<columns>
    b.<columns>
FROM table_a a
    JOIN table_b b ON b.fk_table_a_pk = a.pk

As you can see; I've switched around the part where the comparison takes place.
Instead of a.pk = b.fk_table_a_pk, I do
b.fk_table_a_pk = a.pk. I don't know why, but I would guess it's because it made more sense - in my head - to continue "working" on the joined table (JOIN table_b b ON b.fk_table_a_pk =)

I've always gotten the expected result, so I've never thought about it. But is this wrong? Or is there situations, that I'm not aware of yet, where this could fail and return an unexpected result?

An additional question regarding LEFT and RIGHT joins.
Is it correct to say that the joined table (table_b) is the RIGHT table? - and that table_a is the LEFT table - in this case?
And if I continue the code like this:

....
FROM table_a a
    JOIN table_b b ON b.fk_table_a_pk = a.pk
    JOIN table_c c ON c.fk_table_a_pk = a.pk

they're both RIGHT tables relative to table_a?  And if I continue like this:

    ....
    JOIN table_c c ON c.fk_table_a_pk = a.pk
        JOIN table_d ON d.fk_table_c_pk = c.pk

now table_c is still RIGHT relative to table_a, but is now also LEFT relative to table_d - and of course then table_d is RIGHT relative to table_c - in this case?

The = sign represents the symmetric equal operator. It does not matter in which order the to sides are mentioned, they are still equal.

As for the LEFT and RIGHT JOIN, the table order is important here. As you stated correctly, it is:

...FROM LeftTable LEFT JOIN RightTable ON...

...FROM LeftTable RIGHT JOIN RightTable ON...

The LEFT JOIN returns all rows of the LeftTable regardless of a match, the RIGHT JOIN returns all rows of the RightTable regardless of a match.

For more information on how JOINs work, check out my article series here: http://sqlity.net/en/1146/a-join-a-day-introduction/

It is written for Microsoft SQL Server but most of the concepts are RDBMS independent.


How to check the status of log file restore for SQL Server Used this script to restore a full backup (about 30GB of data and 100GB log) on a SQL Server 2012 box:

RESTORE DATABASE [dbname] FROM DISK = N'S:\import\dbname_201505131100.bak' WITH FILE = 1, MOVE N'dbname' TO N'F:\SQL\INST1\DATA\dbname.mdf', MOVE N'dbname_log' TO N'G:\SQL\INST1\LOG\dbname_log.ldf', NOUNLOAD, STATS = 1

98 percent processed.
99 percent processed.
100 percent processed.
... and nothing else for another 20 mins until "RESTORE DATABASE successfully processed...".

All this time, the query window showed "Executing query...", there was no more I/O on the data files drive (F:), but a lot of writing on the log file drive (G:). sys.dm_exec_requests showed percent_complete 100 and status suspended.

Looks like there's no insight into the status of the log file restore. As the log restore takes more that 2 thirds of the total restore time in this scenario, any way to check the progress ?

You can run the following code to see how much longer a backup or a restore will take. It also tells you how long a DBCC checkdb will take and a few other commands as well.

SELECT session_id, command, s.text, start_time, percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
    + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
    + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
    + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
    + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s


SQL Server: Accurate Row Estimates without Histogram? I'm exploring the optimizer in SQL Server, particularly histograms and I'm seeing some strange, results.

If I execute the query below, where I know there is no histogram on the field, SQL Server outputs a row estimate equal to the actual the result. If I were to run the same query where I use DECLARE @i NUMERIC(19,2) = 30.0 instead of a constant WHERE [Rate] < @i, the estimate is 94.8 as I would expect for the shown query since the database has no statistics on the field.

So my question is why is SQL Server outputting the correct result when it has no statistics on the field?

When turning off AUTO_CREATE_STATISTICS as indicated by my answer, I experience some odd behaviour on the row estimates.

A constraint exists on the table restricting [Rate] to be between 6.5 - 200.0. The estimates vary a lot when experimenting depending on if > is used or >= as well as 24.0 or 64 format queries where the predicate shows an implict conversion to typemoney.

For WHERE [Rate] > 6.5 the estimate is 28.44 which then stops at rates above 100.

For WHERE [Rate] > 7 where decimals are excluded, the 28.44 estimate runs all the way up to 255

For WHERE [Rate] >= 6.5 the 28.44 estimate runs up to about 100 then changes to 17.7764 up to 1000 before the estimate drops to 1.

So there doesn't seem to be much consistency. It seems like SQL Server is aware of the constraint based on some of the queries I'm experimenting with, but it also ignores its presence at times.

First, as you noticed, SQL Server will automatically create missing statistics (where possible) when compiling an execution plan, if the database option AUTO_CREATE_STATISTICS is on.

When statistics are not available, the default guess for an unknown inequality predicate is 0.3 (30%). When you use a local variable, the value in the variable cannot be sniffed (unless OPTION (RECOMPILE) is also specified). With a table cardinality of 316, the estimate is 0.3 * 316 = 94.8.

When you use a constant literal, the value is sniffed. Without statistics, it cannot use this sniffed value to check the histogram as it normally would, but it can judge its impact on the CHECK constraint (the one limiting Rate values to between $6.50 and $200).

If the sniffed value does not exclude the check constraint range completely, the estimate is based on the 0.09 (9%) guessed selectivity for a BETWEEN predicate (from the check constraint). 316 * 0.09 = 28.44.

If the sniffed value does exclude the check constraint range completely, the estimate is always 1 row (the cardinality estimator (almost) never produces an estimated number of rows less than 1).

If the query is simple enough to qualify for a trivial plan, and it is considered safe for simple parameterization, the constant literal is replaced with a parameter marker e.g. @1. This happens for a query like:

SELECT *
FROM HumanResources.EmployeePayHistory AS EPH
WHERE EPH.Rate > $200;

The execution plan shows an estimate of 1 row:

And the Scan Predicate shows a parameter marker:

If simple parameterization is prevented, e.g. by adding a clause that compares a constant with a constant:

SELECT *
FROM HumanResources.EmployeePayHistory AS EPH
WHERE EPH.Rate > $200
AND 1 = 1;

Without parameterization, this plan can never be reused with a different value for the parameter, so the optimizer can statically eliminate table access on the basis that the check constraint prevents any rows being returned. The resuting plan is:

Finally, be careful with types. The type of the Rate column is money, not decimal. Conversions can affect cardinality estimation in complicated ways. Specify a money literal with the $ prefix, or use an explicit CAST or CONVERT.

The internal details of cardinality estimation are not documented publicly (probably to avoid endless ever more detailed questions and complaints when things change), but there are a range of resources out there to aid you in this area. Just remember most of this is unoffical and so not supported by anyone. Some aspects are actually dangerous.

Some apply only to the original (pre-2014) cardinality estimator, some explain general principles applicable to both, and some are applicable to the "new" CE only. The following is not meant as an authoritative or complete list, just the ones that came to mind immediately:

The SQL Server 2014 Cardinality Estimator
13 Things You Should Know About Statistics and the Query Optimizer
Cardinality Estimation (SQL Server)
Constant Folding and Expression Evaluation During Cardinality Estimation
Statistics Used by the Query Optimizer
Plan Caching and Recompilation
Nacho Alonso Portillo's blog (MSFT) (search for cardinality)
Dima Piliugin's blog
SQLblog.com posts
SQLperformance.com posts
Microsoft SQL Server Internals (book) Kalen Delaney et al.


SHOW TABLE STATUS very slow on InnoDB Recently we've been migrating from MyISAM to InnoDB and I understand that MyISAM uses meta information against each table to track information and such - however, the database is about 1.8gb with 1+ million records across 400+ or so tables.

The problem comes with software like PHPMyAdmin runs "SHOW TABLE STATUS FROM dbase;" where it can take up to 50 seconds to execute that command. Is there a way to optimise this? (MySQL or MariaDB)

Thanks!

Here is a quick-and-dirty solution:

set global innodb_stats_on_metadata = 0;
show table status;
set global innodb_stats_on_metadata = 1;

You want to turn innodb_stats_on_metadata  on immediately after the show table status; so that metadata is used efficiently for Query Optimizer analysis when evaluating queries involving InnoDB. Leaving it off will provide more stable Query Execution plans, but the index statistics grow stale quickly in a heavy-write environment.

Give it a Try !!!


Primary Key issue in MemSQL doesn't support case sensitive Creating table in MemSQL and creating composite primary key.
Lets create simple table with following structure:

Table User Data
Name userid

Primary Key (userid)

And assume application support case sensitive userid. So that following user id considered to be different: abc ABC

Insert following data:
1) JOHN ABC
b) ALEX abc

Insert fails in MemSQL as it consider 'ABC' same as 'abc'.

Please help me to understand if case sensitive primary key possible in MemSQL table or not.

In order to make primary key to support case sensitive, binary character set helps in this.

To set binary character set at database level, following command can be used:
SET collation_connection = 'utf8_bin';

To set at column level, following command can be used:
create table CaseSensitive ( name varchar(5) binary, age int, primary key(name));


Non-programmer using Sequel Ace, how to resolve "Out of sort memory, consider increasing server sort buffer size" error? I don't know MySQL but I wanted to glance through some data in a table. I imported the database into Sequel Ace (MacOS) and had no trouble browsing through the tables, but when attempting to sort one column in a larger table I received an "Out of sort memory, consider increasing server sort buffer size" error.
How do I make that happen?
I have MySQL installed and running.

see https://forums.mysql.com/read.php?11,366143,376017#msg-376017
how to create a my.cnf file and add
[mysqld]
sort_buffer_size = 1M

And then increase it when it still makes troubles


Improve OR inside INNER JOIN I have this query. this is the second query. the first one was using the left/or outside the subquery, and the query plan was horrible.( Edited the question with the full sintax ):

https://www.brentozar.com/pastetheplan/?id=HJEioh56N

I have a nested loop (inner join) with 97% in the query plan.

I'm sure the problem is the OR inside the second join because I changed something here and there and I could get rid of them but I would like to be sure what would be the best way to deal with data like these. also all those tables has millions of rows.

Table Definition:

CREATE TABLE [DBO].[TABLE1](
    [F1] [int] NOT NULL,
    [F1] [varchar](16) NOT NULL,
    [F3] [money] NOT NULL,
    [F4] [money] NOT NULL,

I know created this index:

CREATE NONCLUSTERED INDEX IX_TB1 ON DBO.TABLE1
(
    F1,
    F2)

That index seek is now 14%, but got a HASH MATCH with 82% cost.

A common query rewrite that helps with OR predicates looks like this:

SELECT tTitulo.CdContaCartao,
       tTitulo.CdStatus,
       MAX(DiariaMaxima)
INTO   #DiariaMaxima
FROM   Sistema.Titulo AS tTitulo
CROSS APPLY
       (
           SELECT MAX(DiariaMaxima)
               FROM (
               SELECT     tTIPM.DtDiaria
               FROM       Sistema.TaxaIndice_PagamentoMensal_ContaCartao AS tTIPM_Cartao
               INNER JOIN Sistema.TaxaIndice_PagamentoMensal AS tTIPM
                   ON tTIPM.CdTaxaIndice_PagamentoMensal = tTIPM_Cartao.CdTaxaIndice_PagamentoMensal
               WHERE      tTIPM_Cartao.CdContaCartao = tTitulo.CdContaCartao

               UNION ALL

               SELECT     tTIPM.DtDiaria
                          FROM       Sistema.TaxaIndice_PagamentoMensal_ContaCartao AS tTIPM_Cartao
                          INNER JOIN Sistema.TaxaIndice_PagamentoMensal AS tTIPM
                              ON tTIPM.CdTaxaIndice_PagamentoMensal = tTIPM_Cartao.CdTaxaIndice_PagamentoMensal
                          WHERE      tTIPM_Cartao.CdContaCartao = tTitulo.CdContaCartao_Visa
               ) AS x (DiariaMaxima)
           ) AS DiariaMaxima (DiariaMaxima)

Apply is not always the best method for this, though I've often had success with it over using a regular derived JOIN.

Some background on similar problems here:

Analysing A Query Plan
How to Optimise Query


Drop and Create Script not working I'm fairly new to databases, but I'm beating my against the wall trying to do something that (I believe) should be fairly trivial.

I'm using SQL Server Management Studio 2012 and am trying to delete all data in my database. This is not a production database, and I am the only one with access. I've got several relations between tables, so after reading some it seems like a DROP and CREATE should be the easiest route to get around the fact that I have foreign keys. So, using "Tasks -> Generate Scripts" and generating a "DROP and CREATE" script, I keep getting the following error (and the script endlessly executes):

  Msg 3702, Level 16, State 4, Line 2
  Cannot drop database "emcb_schema_v1_0_7" because it is currently in use.
  Msg 1801, Level 16, State 3, Line 2
  Database 'emcb_schema_v1_0_7' already exists. Choose a different database name.

I've made sure that I run the script from master, so what gives? In my frustration, I'm blaming Microsoft for making this such a difficult task, but it could easily be an operator error...

The script is blocked because it cannot obtain exclusive access to the database (technically it is not executing, it is waiting). I think it's in Microsoft's best interests to not allow you to drop a database that is currently being used, so I wouldn't really blame them for making this hard. What you need to do is not only ensure you run your drop from master but also that none of your other query windows, Object Explorer, etc. still have a lingering connection to the database. The easiest way to do that is to set the database to SINGLE_USER:

USE [master];
GO
ALTER DATABASE [emcb_schema_v1_0_7] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [emcb_schema_v1_0_7];


Send SSRS Report Many Times Based on Sql Query I have a user who is requesting that a legacy report (email subscription) that runs with multiple categories be modified to run based on which categories exist in the given date range. Thus instead of one email with daily details on:

Widget 1
Widget 2
Widget 3
Widget 4

They would like 4 separate emails with just Widget n.

The kicker is that in any given day, there can be a subset of Widget n details in the schema and they only want an email if details for those widgets exist.

Is this even possible with SSRS? I can do a data-driven subscription I can't quite figure out how to fire the report multiple times for each existing widget in that day.

In a data-driven subscription you specify a query. Each row that the query returns corresponds to a single email, rendering a new report for each one.

So have your query do something like: SELECT WidgetID FROM dbo.Widgets

...or if it's already doing that, use CROSS JOIN dbo.Widgets to make your query return four times as many rows (assuming there are four).

Then use the WidgetID field as a parameter to your report.


how to take output from second table by joining in SQL table 1                              table 2

id  Name  value                    id  status
1  a      1000                     1   open
2  b      2000                     2   closed
3  c      3000                     3   closed
4  d      4000                     4   open
5  e      5000                     5   open

I want the output as the names which are closed. What will be the possible solutions?

Try Something like if you wish to use joins :

 select name from table1 t1 inner join table2 t1 on t1.id=t2.id where t2.status = 'closed';

or

select name from table1 t1, table2 t2 where t1.id=t2.id and t2.status = 'closed';


How do I get the Query Timing with sqlcmd? Running SQL Server 2017, using the interactive REPL client sqlcmd how do I get the time for Query Execution?

You can sp_BlitzErik showing this in his answer here,

SQL Server Execution Times:
  CPU time = 1859 ms,  elapsed time = 321 ms.

I think that what you're looking for are the SET STATISTICS commands:

SET STATISTICS TIME
SET STATISTICS IO

They work for me using sqlcmd on windows:


Bandwidth usage in Sql Server Our compagny has a central Sql Server Database which is connected to many applications (Web, Backend/Frontend services, backoffice, ..) This database is hosted in a small country (for taxing advantages) where bandwidth is expensive.

Is there any way to have a detailed view of bandwidth usage ? such as how bytes are sent per application, per stored procedure, per login...

I want to monitor this at Sql Server because I can not do this at client side (too many clients and it is not suitable to alter client code for that)

You can snap sys.dm_exec_connections which will give you the net IO metrics for any given connection. The associated sys.dm_exec_sessions will identify the connection client (host, application). Since you need aggregated history and the view gives moment in time snapshot you will have to do the usual transformations to convert moment-in-time to aggregate history.


How to check if sysadmin logins have a master database as a default database in T-SQL? I want to check whether all the sysadmin users in my sql server instance have a master database as their default database AND if not change their default database to master database. I tried the following, but it was not successful. Any help would be greatly appreciated.

DECLARE @cnt INT;
SELECT @cnt= COUNT(p.name)
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
AND p.name NOT LIKE '##%'
AND p.default_database_name not in ('master')
AND s.sysadmin = 1

IF (@cnt = 0)
BEGIN
   exec master..xp_cmdshell 'echo '''all sysadmins have master database as their
   default database> C:\test\result.txt'''
END
ELSE
BEGIN
while @cnt > 0
BEGIN
    alter login p.name with default_database = 'master'
END
END
END

The error message I get is

  Msg 102, Level 15, State 1, Line 21 Incorrect syntax near '.'. Msg
  102, Level 15, State 1, Line 24 Incorrect syntax near 'END'

You can get a list of such logins and then run alter login commands in a loop:

DECLARE @Name SYSNAME
DECLARE @Command NVARCHAR(2000)

DECLARE c CURSOR READ_ONLY
FOR
SELECT p.name
FROM sys.server_principals p
    JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
    AND p.name NOT LIKE '##%'
    AND p.default_database_name not in ('master')
    AND s.sysadmin = 1

OPEN c
FETCH NEXT FROM c INTO @Name

WHILE @@FETCH_STATUS = 0 BEGIN

    SET @Command = 'alter login ' + QUOTENAME(@Name) +' with default_database = [master]'
    --print @Command
    EXEC(@Command)
    FETCH NEXT FROM c INTO @Name

END

CLOSE c
DEALLOCATE c


PostgreSQL logical replication slave - shouldn't it be read-only and available only for subscription updates? I have database and table collection_city on one server. It has 21 rows. I have database and table collection_city on a second server. It also has 21 rows.

They both have this row:

tinker=# table collection_city;
 id |     name      |    alias     | postal_code | region_id
----+---------------+--------------+-------------+-----------
  2 | Obrenovac     | obrenovac    |             |         1

The id column is the primary key.

I created a publication on the first server:

CREATE PUBLICATION tinkerpub FOR ALL TABLES;

tinker=# \dRp[+]
                      Publication tinkerpub
  Owner   | All tables | Inserts | Updates | Deletes | Truncates
----------+------------+---------+---------+---------+-----------
 postgres | t          | t       | t       | t       | t
(1 row)

I created a subscription on the second server:

CREATE SUBSCRIPTION tinkersub CONNECTION 'dbname=tinker host=192.168.150.5 user=postgres password=test port=5432' PUBLICATION tinkerpub WITH (copy_data = false);

My publication-subscription process works correctly, but I read somewhere that the slave server in that case should be read only for manual updates.

But if I run an INSERT command for the table collection_city only on the subscription server, I can see that a new row is being added in the table on the subscription server, although of course it does not exist on the publication server.

Is this the correct behavior? Shouldn't the subscription server be read-only for all modifications except from the publication server?

I am of course aware that the publication server should be able to perform insert, update and delete statements on the subscription server, but is it also possible to do manual INSERT statements on the subscription server? Shouldn't it be disabled in some way?

With PostgreSQL logical replication, the table on the standby server (where the subscription is) is available for data modifications. This is different from physical replication, where the standby is a physical copy of the primary and cannot be modified directly. Perhaps your confusion originates there.

While you can modify the table on the standby, it is not necessarily a good idea to do that, because it can lead to replication conflicts. But it is in your own responsibility to not perform any such operations.


MySQL: Using DISTINCT and GROUP BY together? I saw the following MySQL query that that uses both DISTINCT and GROUP BY together:

SELECT  DISTINCT user_id, post_id, post_content
    FROM  some_table
    GROUP BY  post_id, user_id
    HAVING  post_content LIKE '%abc%';

Here is a scenario to go along with the query: Each user has a unique id, user_id, and can make multiple posts which are identified by a unique id, post_id. Each post would contain some text.

I found this confusing(after coming from Oracle DBs) and had below questions:

What is the meaning of using GROUP BY without doing any aggregation?
What is the significance of switching the order of columns in SELECT vs in GROUP BY?
What is the meaning of omitting the third column from GROUP BY?
Why is DISTINCT used along with GROUP BY? Does distinct operation run after all the groupings are done on the final result or before?

ad 1) Old mysql databases and when you disable ONLY_FULL_GROUP_BY , you can make this query and if the post_content are all equal you would notice, that mysql delivers a random not deterministic value back.

ad 2) none what so ever

ad 3) lazy programming and it occurs an error when you enable ONLY_FULL_GROUP_BY

ad 4) No, it would display all post_content that are connected to user_id, post_id similar to addind post_content to the Group by

Like Strawberry already said this query doesn't make any sense


Is it possible to update all spatial index extents in one query? I work for a county government and probably 95% of the data in the GIS database (running on Microsoft SQL Server 2014) is within the county area.

I've noticed that in some of the data the extent of the spatial indices is too large and in a few cases too small, which is causing queries to run slower than they should.

Question

Is there a way I can run down all the spatial indices and update the extent? I have the X/Y min/max for the minimum bounding rectangle of the county plus a 2 mile buffer already to plug in.

In ArcGIS there is a function to rebuild a spatial index but it doesn't alter the extent of the index. I see in the docs that I have to create a new index with the updated properties with the option of dropping the old index that has the same name. Not sure how to code this out.

This is generally the way that I tackle rebuilding spatial indexes in a consistent way.

This assumes that your geometries are all in the same projection and have already been indexed. It will exclude the usual ESRI Geodatabase System tables.

I avoid having ArcGIS create or maintain indexes.  They never seem to get nice extents on the projections that I use.

declare @sqlCmd nvarchar(4000)

declare cSI cursor fast_forward for
    select 'CREATE SPATIAL INDEX ' + quotename(i.name) + ' ON ' +
            quotename(sc.name) + '.' + quotename(t.name) + '(' +  quotename(c.name)  + ') ' +
            'USING GEOMETRY_AUTO_GRID WITH ' +
            '(BOUNDING_BOX = ( xmin=950000, ymin=4500000, xmax=3500000, ymax=7000000 ), ' + -- Alter to suit
            'CELLS_PER_OBJECT = 1000, PAD_INDEX  = ON, DROP_EXISTING = ON)' sqlcmd
    from sys.tables t
        inner join sys.schemas sc on t.schema_id = sc.schema_id
        inner join sys.indexes i on t.object_id = i.object_id
        inner join sys.index_columns ic on i.index_id = ic.index_id and ic.object_id = t.object_id
        inner join sys.columns c on ic.column_id = c.column_id and t.object_id = c.object_id
        inner join sys.spatial_index_tessellations s on i.index_id = s.index_id and i.object_id = s.object_id
    where i.type_desc = 'SPATIAL'  -- spatial indexes
        and (t.name not like 'GDB_%' and t.name not like 'SDE_%') -- exclude ESRI system tables

open cSI
fetch from cSI into @sqlCmd

while @@fetch_status = 0
begin
    print @sqlCmd
    exec(@sqlCMD)
    fetch from cSI into @sqlCmd
end

close cSI
deallocate cSI


How to set host_name() value in a Sql Server Stored Procedure? We have existing triggers which read the workstation id property of the connection string using host_name().   There are existing PowerBuilder client applications connecting to the database directly and this setting was used to pass the identity of the current user to the application (not my design).

We have newer web applications updating the database, and the triggers require the user identity information to be present in host_name.  However, we don't want to set the user id in the connection string due to the affect on connection pooling (connections are not pooled across all users).

We pass the identity of the user via stored procedure parameters and would like to somehow set the host_name in the stored procedure such that the trigger requirements are satisfied.

How might this be accomplished, if at all?

As far as I'm aware, you can't manipulate HOST_NAME() inside of SQL Server. And I think that's a good thing. The fact that you can spoof this in the connection string is something I've long considered a security problem waiting to happen.

Instead, you will need to change the logic in your triggers. For example you could store HOST_NAME() in a variable, then check PROGRAM_NAME() - if it's your PowerBuilder app, overwrite the variable with whatever, and then the trigger logic can check the variable instead of HOST_NAME().

(You can do that in fewer steps, obviously; just illustrating.)


MySQL Import & Replace via CSV I have a MySQL database table named items with 10,000+ records. Of these records, I would like to export only records where item_isbn IS NULL.

SELECT * FROM Items WHERE item_isbn IS NULL produces a table with 500 records, a sample of the data is below;

+---------+------------+-----------+
| item_id | item_title | item_isbn |
+---------+------------+-----------+
| 1       | Jo         | NULL      |
+---------+------------+-----------+
| 24      | Cars       | NULL      |
+---------+------------+-----------+
| 315     | Fun        | NULL      |
+---------+------------+-----------+
| etc     | etc        | etc       |
+---------+------------+-----------+

I have exported this table as a CSV file, and have populated it with the necessary ISBNs via MS Excel. I'm now ready to re-import the data into the itens table.

How can I re-import these 500 records into the items table, whilst overwriting only the same 500 records, and maintaining the same item_id.

Is there a more efficient way of doing this?

I am using PhpMyAdmin.

Any help appreciated.

Simply delete the rows where item_isbn IS NULL and then import the updated CSV file.

While importing, you can disable auto_increment as well in case you're concerned about messing item_id identity.


Different dates Oracle 11g with TOAD I have the following queries:

SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL;
/*this returns 2/1/2049. */

SELECT to_date(to_char(to_date('01-FEB-1949'),'dd-MON-yyyy')) FROM DUAL;
/*this returns 2/1/1949.*/

Why does the first one returns the year 2049 instead of 1949?

By Googling I have found that I can "force" the client date format to be the one desire by changing the keyon the registry:

KEY_OraClient11g_home1
NLS_DATE_FORMAT : YYYY/MM/DD

Thanks in advance!

Both functions TO_DATE and TO_CHAR take three arguments. The two last arguments take a default value if they are unspecified.

The second argument is the format. It will default to your NLS_DATE_FORMAT session parameter value. Since you will potentially lose information during the conversion, there is no reason to assume that the two functions are the inverse of one another.

Let's take an example with the default poor choice of format DD-MON-RR:

SQL> select to_date('01-FEB-1949', 'DD-MON-RR') d from dual;

D
------------------------------
01/02/1949

SQL> SELECT to_char(to_date('01-FEB-1949', 'DD-MON-RR'),
  2                 'DD-MON-RR') c
  3  FROM dual;

C
------------------------------
01-FEB-49

SQL> SELECT to_date(to_char(to_date('01-FEB-1949', 'DD-MON-RR'),
  2                         'DD-MON-RR'),
  3                 'DD-MON-RR') d
  4  FROM dual;

D
-----------
01/02/2049

This shows that we have lost data during the second step: the century was lost! The third step has to make up for it and uses the default rule of RR:

  If the specified two-digit year is 00 to 49, then

  If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
  If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

  If the specified two-digit year is 50 to 99, then

  If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
  If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

We're in the first case, the century chosen is the current century (20XX).

In conclusion:

Don't rely on the session parameters for conversion because they can be changed outside of your control, use explicit conversion rules.
Don't use the RR or YY format because a year has 4 digits (ever heard about the Y2K bug?)
While we're at it, don't use the MON month format, your application won't work if someone uses another regional NLS setting: months use distinct abbreviations in different languages!