Cardinality showing the nature of relationships among tables Lets say there's a table named Department and a table named Instructor. We've conditions that says :

A Department can have many instructors.
An instructor can be associated to only single department.

Now the point is, if seen from the Department's angle, the relationship is of type "one to many"(coz one department can accommodate many instructors ) but if seen from instructor's angle, then the relationship is of type "one to one"(coz, one instructor can join only one department).

If we implement "one to many"( from department's angle) by making a foreign key in instructor table that would refer to department table, e.g. :

Instructors table :

create table instructors(inst_id number primary key not null, inst_name varchar2(30) not null,
dept_id number constraint dept_id_fk references department(dept_id) constraint not null);

Department Table :

create table department(dept_id number primary key not null, dept_name varchar2(15) not null);

Does these statements sufficiently satisfies both "one to one"( from instructor's table angle ) and "one to many"(from department's angle) ?

or

Do we need to change or SQL code to introduce "one to one" aspect too ?

The code you've presented satisfies the requirements.

If we think about querying the tables starting from each "side", we can see this. (Assume the supplied parameters are suitably valid.)

Return all instructors in a given department:

SELECT i.*
    FROM department d
    INNER JOIN instructors i ON i.dept_id = d.dept_id
    WHERE d.dept_id = @departmentId;

We specify a value for the primary key of department, which restricts rows from that table to just 1 row. When we join to instructors, we need to find all instructors with that dept_id, which could be all of them, none of them, or somewhere in between. For one department, we can find many (or, more accurately, zero-to-many) instructors. (Note: yes, the query can be simplified; I'm just trying to demonstrate a point.)

Return all departments for a given instructor:

SELECT d.*
    FROM instructors i
    INNER JOIN department d ON d.dept_id = i.dept_id
    WHERE i.inst_id = @instructorId;

Again, because we specify a unique value for the primary key, this restricts the rows returned from instructors to just 1 row. In that single row, there is exactly 1 dept_id. Because dept_id is also unique in department, when joining to that table, we can only get back a single department. For one instructor, we will find exactly one department.

If you follow this process for all the relationships you create, it's easy to figure out if the requirements/design specs are met or not. You'll start to see these more easily (without writing queries) once you get more experience under your belt.


Select rows from table using conditions across multiple rows in left joined table I have 3 (or more) tables as follows in MySQL
persons

id
name
gender

1
nim
male

2
nam
female

visits

id
person_id
date

1
1
2020-12-10

2
1
2020-12-16

3
1
2020-12-17

4
2
2020-12-20

5
2
2020-12-21

selected_options

id
visit_id
option_a
option_b
option_c
option_d

1
1
yes
no
yes
yes

2
2
no
no
no
no

3
3
yes
yes
no
no

4
4
yes
yes
yes
yes

5
5
yes
no
yes
yes

Given the above table structure, I am looking to write a query that selects people who have selected "yes" to a set of options at during their visits.
E.g If the options to be queried are option_a = 'yes', option_b = 'no', option_c = 'no'; The query should return person with id 1 since he selected option_a as 'yes' at visit 1, option_b as 'no' in visits 1 and 2 and option_c as 'no' in visits 2 and 3. It should not return person 2 because person 2 selected option_a as 'yes' in visit 4, option_b as 'no' in visit 5 but never selected option c as no in any of their visits.
I have tried the following:
SELECT  *
    FROM  persons
    LEFT JOIN  visits  ON person.id = visit.person_id
    LEFT JOIN  selected_options  ON visit.id = selected_options.visit_id
    WHERE  (option_a, option_b, option_c) IN (('yes', 'no', 'no'))

However it does not match any values.

Another approach (Bits and Booleans)
If the "options" are exactly yes/no (not "unknown" or "maybe"), then you could use a "bit" and put those bits into a SET or INT.  This would lead to a much more compact dataset, though the WHERE code would be messier.
INT would allow Boolean operations with which you could compute things that you seem to need:

OptionA was "yes" for any visit by person-1.
OptionA was "yes" for all visits by person-1.

options BIGINT UNSIGNED lets you handle 64 options, no more.  You could (with added complexity) use more than one column if you need more than 64 options.
The gist of the code is to GROUP BY person and do BIT_AND(options) to combine every column in order to test for "yes" at all visits.  BIT_OR would give you yes at any visit.  Other expressions would handle any/all "no".
Masking would let you select the, say, 3 options that the query is interested in.
To find visits with a particular set of yes/no values, just masking and equality suffices.  So (option_a, option_b, option_c) IN (('yes', 'no', 'no')) maps to (assuming a is bit 0, b is bit 1, etc):
WHERE options
    & ((1<<0) | (1<<1) | (1<<2))  -- check bits 0,1,2
    =  (1<<0)                     -- for 0 being YES; the rest being NO

Back to what might be the desired query:
WHERE BIT_OR(options) & (1<<0)  -- option_a is YES for _any_ visit
  AND BIT_AND(options) & (1<<1) = 0  -- b was NO for _any_ visit
  AND BIT_AND(options) & (1<<2) = 0  -- c was NO for _any_ visit

Tips
LEFT seems to be 'wrong', use a regular JOIN.
"Row constructors", in the past (pre 5.7.3), were quite inefficient.  Rewrite the WHERE if you are running an old version.
Please qualify column names so we know which table(s) they are in.
You show 4 options now.  Might there be 5 tomorrow?  And 6 the day after?  This leads to a maintenance question for the schema.


Copy SSRS reports to another server I am new to SSRS and I have been tasked to copy the SSRS reports(from PROD) to another Server(QA).

I found many  documentations that show how to migrate the reports by Restoring ReportServer, ReportServerTempDB and encryption key.

I just want to confirm. After doing above, my main environment(prod) will remain intact and operational?Please confirm. I'm creating a QA environment that I am copying from PROD and I cannot afford for anything to happen to PROD.

You can right click on the report that you want to copy and choose the Download option.  Download it to a file share and then upload it on the new site.


How to create an UTC index from a 'DATE' typed column with Oracle? I have a Oracle database with columns of type 'DATE' which store timestamps in local time zone. I need to query this column with timestamps in UTC timezone.

How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?

To be precise, the date type stores timestamps without any timezone information rather than "in local time zone".

  How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?

You have three options:

index the date and convert your timestamp to date when querying, eg:

select * from foo where foo_date=cast(systimestamp as date) from dual;

create a functional index on the date converted to a timestamp with local time zone, eg:

create unique index i_foo on foo(cast(ts as timestamp with local time zone));
select * from foo where cast(ts as timestamp with local time zone)=
                        cast(X as timestamp with local time zone);

change the type of your columns of type date to timestamp with time zone or timestamp with local time zone

Of these, it is very likely that the last option is the best one - timestamp with local time zone is the 'correct' type for most data that refers to a fixed point in time. (As opposed to 'noon' which is always 12:00 no matter which time zone you are in.)


Slow Trigger performance when big batches (take2) take2 of this post since it was missing information and it was closed prior to the update that I added.

I have an update trigger that inserts into auditing tables. We had no problem until someone decides to update over 1 million records... (That's my bad. I didn't think it would be a problem when developing). Now facing reality, I need to find a solution...

This is a simplified version of the real thing

-- drop trigger PriceHist_trig_U
-- drop table MyPriceTable
-- drop table price_history
Create Table MyPriceTable (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100)
CONSTRAINT [PRICE_TAB_P01] PRIMARY KEY CLUSTERED
(
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Declare @Id int
Set @Id = 1

While @Id <= 1000000
Begin
   insert into MyPriceTable values (right('000000000000' + CAST(@Id as nvarchar(10)),13),'Grp ' + CAST(@Id%10 as nvarchar(10)), @id%3, RAND()*(25-10)+10, 1, 'there are many other columns')
   Print @Id
   Set @Id = @Id + 1
End

-- Drop table   price_history
create table price_history (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100), historyDate datetime, ChangedColumns varchar(Max))
CREATE NONCLUSTERED INDEX price_history_nc1 ON price_history
(
    HistoryDate ASC,
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC
)

go
Create TRIGGER PriceHist_trig_U ON MyPriceTable FOR UPDATE
AS
INSERT INTO price_history (SKU, PriceGroup, PriceLevel, price, Qty, ManyOtherColumns, HistoryDate, ChangedColumns)
            SELECT INS.SKU,INS.PriceGroup,INS.PriceLevel,INS.Price,INS.Qty,INS.ManyOtherColumns, getdate(),
CASE WHEN update(Price) and INS.Price<>DEL.Price THEN 'Price-' ELSE '' END +
CASE WHEN update(Qty) and INS.Qty<>DEL.Qty THEN 'Qty-' ELSE '' END +
CASE WHEN update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns THEN 'other-' ELSE '' END
FROM INSERTED INS
JOIN DELETED DEL ON DEL.sku=INS.sku AND DEL.PriceGroup=INS.PriceGroup AND DEL.PriceLevel=INS.PriceLevel
WHERE  (update(Price) and INS.Price<>DEL.Price)
    OR (update(Qty) and INS.Qty<>DEL.Qty)
    OR (update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns)

/* tests */
update MyPriceTable set price = price-1

I've been doing many tests and researches to try to figure out how to solve my issue of having a trigger perform poorly... I've come to the conclusion that to minimize the bad performance of the "Table Insert" in the execution plan, I need to insert in smaller batches.

The question is: Since I'm not sure of where all the different updates can come from, I'm trying to figure out how I can insert the auditing records in batches within the trigger?

example, The update of the main table for 1 million records would happen and call the trigger, which would insert 100 thousand records at a time in some type of loop.

Is this possible? If so, how do you suggest? If not, how else can I improve the table insert of the execution plan?

Instead of doing all that work in the update trigger, you could just capture the DELETED rows and worry about what actually changed, and formatting a pretty message later.  EG

create table price_history (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100), historyDate datetime, ChangedColumns varchar(Max))
CREATE unique clustered INDEX price_history_nc1 ON price_history
(
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC,
    HistoryDate ASC
)

go
Create or alter TRIGGER PriceHist_trig_U ON MyPriceTable FOR UPDATE
AS
INSERT INTO price_history (SKU, PriceGroup, PriceLevel, price, Qty, ManyOtherColumns, HistoryDate)
            SELECT DEL.SKU,DEL.PriceGroup,DEL.PriceLevel,DEL.Price,DEL.Qty,DEL.ManyOtherColumns, getdate()
FROM DELETED DEL

Or try skipping the trigger and using Change Tracking, Change Data Capture, or Temporal Tables instead.


Use if exists in postgresql I know about the exists feature,  but that does not help me with the given situation. What I want is:

Check if some row exists, and if exists then check for an another row if    row exists.

Here's the sqlFiddle.

Description:

A location table:

| ID |   NAME | ADDRESS |
|----|--------|---------|
|  1 | India1 |  (null) |
|  2 | India2 |  (null) |
|  3 | India3 |  (null) |
|  4 | India4 |  (null) |
|  5 | India5 |  (null) |

Then a location_flag table:

| ID | LOCATION_ID | FLAG_ID | VALUE | PARENT_ID | DELETED |
|----|-------------|---------|-------|-----------|---------|
|  1 |           1 |       1 |   YES |    (null) |  (null) |
|  2 |           1 |       2 |   YES |    (null) |  (null) |
|  3 |           2 |       1 |   YES |    (null) |  (null) |
|  4 |           2 |       2 |    NO |    (null) |  (null) |

What I want is to get all locations except location with id=2.

In procedure, it'd be like:
if(locationWithFlag1='YES'){
 if(locationWithFlag2='YES'){
//make this row to be appear in result
}
}
else{
//make this row appear in result
}

Is it possible without bringing in plpgsql?

select l.*
from location as l                             --- find all locations
where not exists                               --- where there isn't
      ( select *
        from location_flag as lf               --- a flag
        where lf.location_id = l.id
          and lf.flag_id = 1                   --- with 1
          and lf.value = 'YES'                 --- and YES
          and not exists                       --- without
              ( select *
                from location_flag as lf2      --- another flag
                where lf2.location_id = l.id
                  and lf2.flag_id = 2          --- with 2
                  and lf2.value = 'YES'        --- and YES
              )
      ) ;

or:

select l.*
from location as l
where not exists
      ( select *
        from location_flag as lf
        where lf.location_id = l.id
          and lf.flag_id = 1
          and lf.value = 'YES'
      )
   or exists
      ( select *
        from location_flag as lf2
        where lf2.location_id = l.id
        and lf2.flag_id = 2
        and lf2.value = 'YES'
      ) ;


Is it possible to have passwords configured per database or per host in .my.cnf I have the following in my ~/.my.cnf

[client]
password="somepass"

but this is not the password I use for every user@host/database I connect to. Is there some way to specify in the config different passwords for different things so I don't have to type them in?

As I answered here, you can add a section for each user/host/db you connect to using the syntax in your ~/.my.cnf:

[clienthost1]   # Note: client + host1
user=myuser
password=mypass
database=dbname
host=server.location.com

Once this is in your user's .my.cnf, you can utilize it by doing this on a command line:

$ mysql --defaults-group-suffix=host1


Linked server issues and administration We have around 15 linked servers in each of 2 servers and application is querying the views and stored procedures using these linked servers.

I would like to know

Is there a way to find from SQL(2008) Side, how many linked servers are being used and how many are not.. as application team want us to remove the unnecessary linked servers.
Is there any post/document which gives us more information on how to troubleshoot Linked server issues.
are there any DMV's or any Views where the historical information of Linked servers is maintained.

Are there any DMVs where linked server info is maintained
  select * from sys.servers gives info about what are the linked servers configured .please see below msdn link for more details  on usage
https://msdn.microsoft.com/en-IN/library/ms178530.aspx

Is there any post/document which gives us more information on how to troubleshoot Linked server issues.
This seems like a generalized question,depending on error message,my basic analysis would start from access issues ,sql error log...If you want to troubleshoot performance issues with linked servers there are many links in SO as well as online to start with.

http://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/

Is there a way to find from SQL(2008) Side, how many linked servers are being used and how many are not.. as application team want us to remove the unnecessary linked servers.

This has been already answered here by Mr denny.
https://dba.stackexchange.com/a/5520/31995


Speed Up Index Creation in MySQL In MySQL 5.5, I have a table which is recreated on a daily basis and for which the process of adding an index is taking about 50 minutes which I'm hoping to reduce.  At the time when the index is created the table usually has about 25 million records and there is plenty of disk space and RAM.
While the index is being added the process status is "copy to tmp table" for most of the time.

my.ini file settings

[mysqld]
basedir = C:/Bitnami/WAMPST~1/mysql
datadir = D:/mysql\data
key_buffer_size = 384M
max_allowed_packet = 16M
tmpdir = E:/mysql/tmp/
tmp_table_size = 1024M
max_heap_table_size = 1024M
table_cache = 256
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
net_buffer_length = 8K
myisam_sort_buffer_size = 8M

Table structure:

CREATE TABLE `log_access` (
  `_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type_id` int(11) NOT NULL,
  `building_id` int(11) NOT NULL,
  `card_id` varchar(15) NOT NULL,
  `user_key` varchar(35) DEFAULT NULL,
  `user_name` varchar(25) DEFAULT NULL,
  `user_validation` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`_id`),
  KEY `log_access__user_key_timestamp` (`user_key`,`timestamp`)
  KEY `log_access__timestamp` (`timestamp`)
) ENGINE=MyISAM

Statement to add the index

ALTER TABLE `log_access` ADD INDEX `log_access__user_key` (`user_key` ASC);

Use InnoDB because it supports Fast Index Creation.*

If you have plenty of RAM, configure a tmpfs partition and point your MySQL tmpdir to that location.

* There are many other reasons besides that to stop using MyISAM. MyISAM is known for its table-level locking, tendency to corrupt data, and lack of support for ACID and referential integrity. It's also slower than InnoDB under most workloads.


Will Transactional Replication Work? I have multiple site servers and a central server.  Each has a Widget table with identical schema.  There is currently a custom process that sends new Widget records from the site server that generated it to the central server.  If at any point a Widget needs to be sent from one site to another for further processing, there is a process that copies the Widget record from the central server to the necessary site server.

At this point Site A will have all of its own Widget records, plus x number of Widget records from Site B.  Site B will have only its own Widget records.

If I were to set up Site A and Site B to replicate the Widget table to the central server, will adding these new records to Site A (and that already exist in the central server) have any adverse effects on replication?  We are using SQL Server 2008 R2 SP2.

Correct me if I'm wrong, but this is how I understand the process of copying widgets.

Copy widgets from the site servers (A and B) to the Central Server
If a widget needs additional processing, copy that widget from the central server to its site server A or B
At this point, site A has its own records plus X site B records
Transaction replication is sending widgets from A and B to the Central server
The Site B records on Site A may already exist on the Central server because Site B may have already sent them to the Central server via transactional replication.
As published tables must have a primary key, the replication would fail when Site A attempts to replicate Site B's widgets to the central server.

You could skip those errors via the Agent Profile, if you had the specific error number.

From SSMS
1. Right Click on Replication

Select Launch Replication Monitor
Expand My Publishers
Expand the Publisher you want to Modify
Select the Publication.
In the subscriptions window to the right, right click on the Subscription and select Agent Profile.
Create a new profile and select skip_errors
In the SkipErrors row, add in the error numbers seperated by : that you wish to skip.

That is probably not what you are trying to accomplish, as the replication would crash every time you copied widgets from Site B to Site A.

Question, are you trying to partially or fully replace the existing process of copying widgets between sites with transactional replication?

Note

There may be a more elegant way to do this, such as using TSQL to copy records in batches from Site A and Site B to Central, if they don't yet exist. You could accomplish that using OPENQUERY or SSIS.

Here is a crude example using OPENQUERY

/* From Central server,
insert all records from SITEA except those that already
exist on Central
*/
INSERT INTO widgets (widgetID, widgetName, widgetDate, widgetDesc)
SELECT widgetID, widgetName, widgetDate, widgetDesc
FROM openquery(SITEA_SERVER, '
 select widgetID, widgetName, widgetDate, widgetDesc
 from widgets_database.dbo.widgets
')
EXCEPT
SELECT widgetID, widgetName, widgetDate, widgetDesc
FROM widgets WITH (nolock)


SSRS HA Deployment Advice wanted to ask if my design is on the right track for installing SSRS.
Currently i have 2 nodes in an Always ON AG (2017 Enterprise Edition).
There is an application update coming up that will require the use of SSRS.  No users will access the SSRS portals and the application will display the reports.
If the SSRS server is not available the application will work unless you go to the section that shows the reports, but everything else will work.
My question is, should I stand up SSRS on the two SQL Nodes in a scale out deployment and have them sitting behind my Net Scaler load balancer?
Should I just setup a 3rd box and get SSRS installed on a separate box to make the config simple like a 3rd node (Sync or Async) with manual failover to host the ssrs server and its databases?
Just trying to see what is the best angle to go here to give me the lease amount of headaches in the future.
I think the scale out deployment is a good way to go due to not having yet another server to manage, but i then need to depend on the net scaler admin to make sure they are doing their jobs in the setup, but i would like to get some feedback from you guys.
Thanks,

You have to take in count that a 3rd box with SSRS counts more license.
In that case you have to place the SSRS databases on the alwayson cluster and the SSRS website on the 3rd box. You will not have HA solution for it and you will suffer downtime for server maintenance.
Otherways, if you choose the scale out deployment on the alwayson cluster with a net balancer in front of it you will have redundancy and no increase in license costs.
But you need a new component in the architecture, a network balancer. You can't enable microsoft NLB on the clusters' nodes because it is not compatible with the cluster itself.


Execution time high for Query in sp_executesql I have a query generated from a .net application and parameters are linq parameters. The procedure I followed to tune the query is as below.

I run the application and capture the query using profiler.
The query is of the form

exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[GroupId] AS [GroupId],
[Extent1].[Agency] AS [Agency],
[Extent1].[Group] AS [Group],
[Extent1].[Claim] AS [Claim],
[Extent1].[StartDate] AS [StartDate],
[Extent1].[ExpireDate] AS [ExpireDate],
[Extent1].[IsActive] AS [IsActive]
FROM [dbo].[Permissions] AS [Extent1]
WHERE ([Extent1].[GroupId] = @p__linq__0) AND (([Extent1].[ExpireDate] IS NULL)
OR ([Extent1].[ExpireDate] > @p__linq__1)) AND ([Extent1].[IsActive] <> 1)',
N'@p__linq__0 int,@p__linq__1 datetime2(7)',
@p__linq__0=15,@p__linq__1='2017-05-10 00:00:00'

This is just a sample query. The actual query is much bigger and includes lots of joins involving multiple views and table etc.

My question here is:

When the run the query as it is, the execution times is around 15 sec
When I take out the query alone from sp_executesql and apply the filter values to the query, it runs in less than 1 sec.

I understand that since the values are hard-coded into the query it will be faster, but why is that much difference in execution time? Also the execution plans are different for both:

SQL Execution plan with sp_executesql

Execution Plan with hard-coded SQL

I am the only user working on this server and I execute same parameters in both scenarios. I have applied OPTION(RECOMPILE) to the end of the sp_executesql and it executes in 1 sec compared to 15 sec. But the execution plan is still not similar to the hard-coded plan.

Anyways OPTION(RECOMPILE) makes the query run fast in one second as it forces the query to compile and create new plan each time. But I am not supposed to hack the code and change the query. I will suggest OPTION(RECOMPILE) as a last resort to my application team.

The query optimizer is able to use parameter sniffing for the dynamic query as can be seen in the actual plan:

<ParameterList>
  <ColumnReference Column="@p__linq__5" ParameterDataType="varchar(8000)" ParameterCompiledValue="'MI3300287'" ParameterRuntimeValue="'MI3300287'" />
  <ColumnReference Column="@p__linq__4" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2009-06-11 23:59:00.0000000'" ParameterRuntimeValue="'2009-06-11 23:59:00.0000000'" />
  <ColumnReference Column="@p__linq__3" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2008-06-11 00:00:00.0000000'" ParameterRuntimeValue="'2008-06-11 00:00:00.0000000'" />
  <ColumnReference Column="@p__linq__2" ParameterDataType="varchar(8000)" ParameterCompiledValue="'MI3300287'" ParameterRuntimeValue="'MI3300287'" />
  <ColumnReference Column="@p__linq__1" ParameterDataType="int" ParameterCompiledValue="(90495)" ParameterRuntimeValue="(90495)" />
  <ColumnReference Column="@p__linq__0" ParameterDataType="int" ParameterCompiledValue="(90495)" ParameterRuntimeValue="(90495)" />
</ParameterList>

Those sniffed values can influence the query plan that's chosen. However, the query plan still needs to be safe for all possible values of the input variables. Let's look at one difference between the plans, the access on the IncidentDetailsPage_Header table. In the slower, dynamic plan you get a scan and a hash match which in total takes about 2.4 seconds:

In the faster plan with the hardcoded values you get an index seek instead which takes about 0 ms:

Here's the T-SQL that corresponds to that part of the plan:

 AND (([Extent1].[agencyori] = @p__linq__5)
      OR (([Extent1].[agencyori] IS NULL)
          AND (@p__linq__5 IS NULL)))

Hardcoding the values results in a single use query plan just for those parameter values. With your parameter values, that T-SQL can be simplified to this:

([Extent1].[AgencyOri] = 'MI3300287')

That is eligible for an index seek. The other code is not. Avoid kitchen sink queries when you can.


What happens when you have two events scheduled to run after identical n seconds? I am running a survey and i am getting lots of data and i need to process that data quickly from within mysql.I am using a mysql events to help me process the hundreds of rows that i have.

I have written this routine that i am calling from an event.

DELIMITER $$
CREATE PROCEDURE survey_task()
BEGIN

select tel_number, text_message, id INTO @t_number, @t_message, @t_id from incoming_messages where t_status = 0 limit 1;

insert into surveyed_lot(telephone_number, survey_answer,date_of_insert )
        values(@t_number, @t_message,now());

UPDATE incoming_messages SET t_status = 1 WHERE id = @t_id;

END $$
DELIMITER ;

When i process a row, i mark that row with 1 to show that is read and the ones that are not read read 0.

This is the event i am calling the routine from

CREATE EVENT survey_processor_1
    ON SCHEDULE EVERY 10 SECOND
    DO
       call survey_task();

My question is,if i have another identical event that calls survey_task at exactly the same time as survey_processor_1

CREATE EVENT survey_processor_2
    ON SCHEDULE EVERY 10 SECOND
    DO
       call survey_task();

will survey_task be called twice or does mysql have a way of making one of the two events to acquire some form of a lock and thus allow one to execute ahead of the other?.

You could stagger survey_task yourself by calling the following functions

IS_FREE_LOCK()
GET_LOCK()
RELEASE_LOCK()

This supports the paradigm known as cooperative or advisory locking.
Use in the survey_task as follows:
DELIMITER $$
CREATE PROCEDURE survey_task()
BEGIN

WHILE IS_FREE_LOCK('survey_task_lock') = 0 DO
    SELECT SLEEP(0.1) INTO @x;
END WHILE;
SELECT GET_LOCK('survey_task_lock',10) INTO @x;

select tel_number, text_message, id INTO @t_number, @t_message, @t_id from incoming_messages where t_status = 0 limit 1;

insert into surveyed_lot(telephone_number, survey_answer,date_of_insert )
        values(@t_number, @t_message,now());

UPDATE incoming_messages SET t_status = 1 WHERE id = @t_id;

SELECT RELEASE_LOCK('survey_task_lock') INTO @x;

END $$
DELIMITER ;

The idea is to have the other invocation of survey_task poll every 0.1 seconds until the lock is free. Then acquire the lock and release it when done.
GIVE IT A TRY !!!


Data warehouse: Can I tell any details from Source System on delays or blocks? I have a MS SQL Server 2012 machine that is acting as our data warehouse currently.  We are having issues with long running jobs, and am curious if there is a way to get detailed historical logging on reasons that a job is being held up/running long?

I know this is kind of a vague question and I apologize.

Thanks, Drew

Okay, I am going to assume that we are talking about ETL operations for loading data into your data warehouse. One great option that you have available to you since you are using SQL Server 2012 is creating a SSIS Catalog and migrate your ETL jobs to be hosted there. This offers you tons of reporting and performance counters that you can use to track and troubleshoot various ETL issues.

After you identify a baseline it really is performance tuning on your long running ETL's, SQL Server Data Tools shows performance statistics and you can manually run them to see where it it taking the most of your time. Start with the query to the source system and make sure it is tuned properly, then check your lookups and inserts and updates. There are many ways to increase performance of your ETL's. Basically ask for the minimum amount to get done what you need done, make sure you are doing set based operations for your updates as opposed to row based.

There is much more performance tuning tips depending on what you find by digging into your ETL's.


How can I get nodetool without Cassandra I have Cassandra pool across machines in LAN. I need nodetool on machine without Cassandra installed. How can I get it?

Is the installing Cassandra and disabling db service is the best way?

The easiest (non-invasive) way is probably to download the tarball installation (you'll need to select either a Mac or Linux-based OS for it to allow you to download the tarball).  Based-on your mention of disabling the service, I'm going to guess that you want to accomplish this on Windows.  If that's not the case, please indicate so in the comments.

Un-tar dsc-cassandra-2.0.8-bin.tar.gz to the location you want to run Nodetool out of.  ex:

$ cd /tools
$ tar -zxvf dsc-cassandra-2.0.8-bin.tar.gz

Note: You may have a different application you use for tarballs.  I ran this from a Cygwin terminal.

Find the location of your JRE/JDK (not the bin directory) and set that as your "JAVA_HOME" (System) environment variable.  When you have it set properly, you should be able to query it via CMD:

>echo %JAVA_HOME%
C:\Program Files (x86)\Java\jre7

Once you have JAVA_HOME set, it should work from either CMD or Powershell:

C:\tools\dsc-cassandra-2.0.8\bin>nodetool -h 192.168.1.85 status
Starting NodeTool
Note: Ownership information does not include topology; for complete information, specify a keyspace
Datacenter: datacenter1
========================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address       Load       Tokens  Owns    Host ID                               Rack
UN  192.168.1.85  506.29 MB  256     100.0%  cd39f0fe-ed67-40cf-b6bd-504cedabf497  rack1

This way, you can run nodetool without messing with an installer or services.


How to make an union on 2 joined tables without affecting the sum on the column? I have the following tables:

cant:
id      CANT_VAL    COD_VAL fk_id_chest c1    c2
18059   18.56       R12     1           100   11
18060   11          R10     2           200   22
18061   15          R11     3           300   33

col:
FK_ID_COL_DMA   CANT_VAL    COD_VAL
18059           1134        R10
18059           1234        R3
18061           1111        R5

to look like that:

id      CANT_VAL    COD_VAL    fk_id_chest   c1      c2
18059   18.56       R12        1             100     11
18059   1134        R10        1             0       0
18059   1234        R3         1             0       0
18060   11          R10        2             200     22
18061   15          R11        3             300     33
18061   1111        R5         3             0       0

Note that the ID should be common on all rows united by id and c1 - c2 not, because it's not ok for a sum on the column.

If I make an:

    select CANT.ID,CANT.CANT_VAL,CANT.COD_VAL,CANT.FK_ID_CHEST,CANT.C1,CANT.C2
        from cant
    union
    select COL.FK_ID_COL_DMA,COL.CANT_VAL,COL.COD_VAL,CANT2.FK_ID_CHEST,CANT2.C1,CANT2.C2
        from col
            inner join cant2 on cant2.id=COL.FK_ID_COL_DMA

I will get an:

    id      CANT_VAL    COD_VAL    fk_id_chest   c1      c2
    18059   18.56       R12        1             100     11
    18059   1134        R10        1             100     11
    18059   1234        R3         1             100     11
    18060   11          R10        2             200     22
    18061   15          R11        3             300     33
    18061   1111        R5         3             300     33

and if someone would want to know the sum on c1, it will be a bad result!
Thanks!

Ok, I solve it like this:

select CANT.ID,CANT.CANT_VAL,CANT.COD_VAL,CANT.FK_ID_CHEST,CANT.C1,CANT.C2
        from cant
    union
    select COL.FK_ID_COL_DMA,COL.CANT_VAL,COL.COD_VAL,CANT2.FK_ID_CHEST,0,0
        from col
            inner join cant2 on cant2.id=COL.FK_ID_COL_DMA

Thanks!


postgres read query resample time series data for hourly averages I am new to SQL trying to learn how to do read queries on time series data. Can someone give me a tip on how to resample interval time series data to hourly averages on the postgres read query?
My table is named building_data where there are a few columns named time, metric, value, kv_tags, m_tags
time is my date/time stamp column where I am trying to see if I can resample the data in the value column into hourly averages. The WHERE in the query below is to filter out for a specific device that I am interested in looking at the data. I apologize if that doesn't make sense.
For a first timer this sql query appears to work but its not incorporating some process to resample the data in hourly averages, any tips greatly appreciated.
SELECT
  "time" AS "time",
  metric AS metric,
  value,
  kv_tags,
  m_tags
FROM building_data
WHERE kv_tags->'equip_name' = '["35201"]' AND
  m_tags IS NOT NULL
ORDER BY time desc limit 1000

You can use time_bucket function to make it.
Example:
SELECT
  time_bucket('1 hour', "time") AS hour,
  metric,
  avg(value)
FROM building_data
WHERE kv_tags->'equip_name' = '["35201"]' AND
  m_tags IS NOT NULL
GROUP BY hour, metric
ORDER BY hour limit 1000

Note that I'm simplifying the query and if you want to include kv_tags and m_tags you'll need to also use aggregation functions or add them to the group by clause.


Prevent overlapping TSRANGE I have the following PG (12) reservations table

id               uuid
reservable_id    uuid
reserved_between tsrange

How can I ensure there are no 2 reservations for the same reservable_id overlapping each other?

A "simple" UNIQUE INDEX on reservable_id,reserved_between works only (as expected) only with equal ranges.

You need the btree_gist extension so that you can create a GiST index on an uuid:

CREATE EXTENSION btree_gist;

Now you can create an exclusion constraint:

ALTER TABLE reservations ADD EXCLUDE USING gist (
   reservable_id WITH =,
   reserved_between WITH &&
);

&& is the “overlaps” operator for ranges.


SQL Server mixed mode authentication not being set I have a local developer instance of SQL Server installed on my machine. I set the mixed mode authentication:

Open SSMS
Connect to localhost (windows auth)
Right click localhost connection, right click -> properties
Security tab
Choose "SQL Server and Windows Authentication mode"

I restarted my computer, and verified the above settings are selected.

I cannot login using a username and password I created. Windows Event Viewer shows

  Login failed for user 'ctx'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: ]

Why does event view say "Integrated authentication only" when SQL Server shows "SQL Server and Windows Authentication mode"?

-- Windows 10 Enterprise

SELECT @@VERSION
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 17763: )

To start, even though the gui shows mixed mode authentication, running

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode]

gives

'Windows Authentication'

Two related small things resulted in this issue. (1) My windows domain account is not an administrator, so I can't restart the SQL Server instance from the Configuration Manager. (2) Restarting windows does not reload the SQL Server configuration options, or at least, it won't change the authentication setting.

A windows domain account with administrative privileges on my local machine was able to stop and restart SQL Server from the Configuration Manager. I can now login with the SQL account I created.

After that, the above query gives

'Windows and SQL Server Authentication'

So, even though I can change the authentication options, and SQL Server displays in the GUI that the options have been changed, the options are not actually changed until the SQL Server instance is manually restarted by an admin (rebooting doesn't do it).


InnoDB "holes" in large table 200 Million Rows? I have a large MySql InnoDB table with about 200 million rows.  One of the columns has some data in it that I really don't need any more, so I'm thinking about removing that column, or setting all the cells in that column to NULL.

This column contains a text value of about 300 characters, so by removing it I'll save about 300 bytes x 200 million rows = 60GB.

But, will this leave a lot of "holes" in my data?  Can MySql effectively and efficiently re-use this space without some sort of de-fragmentation?  Or does that just not matter since it's InnoDB and on an SSD drive?

Thanks!

Update:  The table has about columns total, but most of them are INT values.  This text field is by far the biggest.  Each row only takes up about 4KB of space total.

InnoDB stores data (and indexes) in 16KB blocks.

It sounds like you are shrinking each row by an average of 7% (300/4K).

If the shrinkage had been more than 50%, then probably InnoDB would have noticed adjacent half-full blocks and coalesced them, thereby freeing up some of the predicted savings.  (A block is 16KB.)  I say only "some" because it is not aggressive at packing the blocks after UPDATEs or DELETEs.

If you had not done the OPTIMIZE, future inserts/deletes/updates, if they were scattered around the table, would have filled in the 'holes' and/or led to blocking coalescing.  If, on the other hand, the main activity is to add new rows at the 'end' of the PRIMARY KEY values, then this subsequent cleanup would not happen, and the OPTIMIZE would be desirable.

How much did the OPTIMIZE benefit you?  Performance probably changed very little.  Sure, you freed up some space, but, if you are not running out of disk space, that does not matter much.

Another issue...  Tablespaces.  The old default was to put all tables into a single tablespace (the file named ibdata1).  It would grow but never shrink.  Any freed blocks would be available for future database work, but would not be given back to the OS.

The new default (innodb_file_per_table) has the same problem.  However, an explicit OPTIMIZE rebuilds the table, and does give the freed space back to the OS.  Caveat:  During the process, you need 2x the disk space.   So, if you were very tight on space, OPTIMIZE would fail.  However...

OPTIMIZE TABLE (for InnoDB) is "Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables with FULLTEXT indexes. The operation uses the INPLACE algorithm, but ALGORITHM and LOCK syntax is not permitted. " -- https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html


Remove accidental duplicates in PK table, cascade changes to FK table Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants.  There is a PK-FK relationship between plants.id and samples.tree, and a constraint on table plants that every genus-species combination must be unique.  But some, of the entries in table plants are actually just misspelled versions of other records in the same table.  So:

Table: plants

id  |  genus   | species
-------------------------
1   |  Acer    | rubrum
2   |  Acer    | rubra
3   |  Quercus | albaa
.....

Table: samples

sampid | tree | size
-----------------------
1      | 1    | 34
2      | 1    | 30
3      | 2    | 16
4      | 2    | 34
5      | 3    | 20
...

Table: plant_name_mapping

oldgenus | oldspecies | newgenus | newspecies
-----------------------------------------------
Acer     | rubra      | Acer     | rubrum
Quercus  | albaa      | Quercus  | alba
...

I want to update table plants based on table plant_name_mapping.  If the update creates a new name that wasn't in plants already, great.  However, if the update creates a name that is already in the table plants, I want to delete that duplicate record, and than change the foreign key in the table plants to match the name that was originally there all along.  So the output should look like:

Table: plants

id  |  genus   | species
-------------------------
1   |  Acer    | rubrum
3   |  Quercus | alba
.....

Table: samples

sampid | tree | size
-----------------------
1      | 1    | 34
2      | 1    | 30
3      | 1    | 16
4      | 1    | 34
5      | 3    | 20
...

I'm working with a postgresSQL-9.3 database.  Obviously I have many more records than this example.  Let me know what kind of update statement or stored procedure could do this.  I'm having trouble describing what I want to do, but I think the examples illustrate it pretty clearly.  Thanks for the help.

This solution requires first that there aren't any conflicting or cascading information in plant_name_mapping; to work, the following query must return no records:

select *
from plant_name_mapping o
join plant_name_mapping n ON ((n.newgenus, n.newspecies) = (o.oldgenus, o.oldspecies));

The queries to update your tables are:

create temp table plantschanged as
with
  renamed as (
    select o.*, n.newgenus, n.newspecies, n.newspecies is not null as namechanged
    from plants o
    left join plant_name_mapping n on ((n.oldgenus, n.oldspecies) = (o.genus, o.species))
      ),
  newplants as (
    select id, coalesce(newgenus, genus) as genus, coalesce(newspecies, species) as species, namechanged
    from renamed
    ),
  plantsnewid as (
    select *, min(id) over(partition by genus, species) as new_id
    from newplants
    )
select *
from plantsnewid
where namechanged or new_id <> id;

with
  plantsrenamed as (
    update plants p set genus = pc.genus, species = pc.species
    from plantschanged pc
    where p.id = pc.id and pc.id = pc.new_id
    returning p.id
    )
update samples s set tree = pc.new_id
from plantschanged pc
where s.tree = pc.id and pc.id not in (select * from plantsrenamed);

delete from plants
where id in (select id from plantschanged where new_id <> id);

The table plants may have more dependencies than just the table samples. You may check through the following query if you have any further tables that would require updating (perform the same query as update samples with each of these, prior to delete from plants):

select conrelid::regclass, conname
from pg_constraint
where confrelid = 'plants'::regclass and contype = 'f';


Justified multi-value field in MySQL? I have a table of data that contains 10 point values based on a score for an event (0 - 9 points). If a time is within the range, the point is awarded. There are 20 possible events. Is this a valid use of a multi-value field?

example:

Event 1A times 18.50, 19.11, 19.77, 20.03
Event 2C times 58.50, 60.91, 62.55, 63.99

If the event is 1A and the time is 19.66, this fall into the 2nd and 3rd position - the score is then 2

If the event is 2C and the time is 63.77, this fall into the 3rd and 4th position - the score is then 3

Regards

More Detail:

Key|Event|Type|PointTable (seconds)
1|200E|R|93.86, 94.06, 94.25, 94.45, 94.65, 94.85, 95.05, 95.25, 95.45, 95.65
2|200A|I|98.26, 98.44, 98.63, 98.81, 98.99, 99.18, 99.37, 99.55, 99.74, 99.94
3|200E|I|110.74, 110.94, 111.15, 111.35, 111.56, 111.76, 111.97, 112.18, 112.38, 112.59

Key = unique key, Event is the EventCode, Type is not important, PointTable is the list of seconds....

I have another table that contains RESULTS which includes an EventCode and a TIME. To produce a query, I want a score identified for that TIME. So I use the EventCode to find the corresponding list of times... and where the TIME fits in the array of times produces a 'SCORE'

The time for 200E (1 key) would be in a record for a results of an athletic activity and there needs to be a determination what that time's relative score would be 0=93.86 or less, 1=94.06 or less, 2=94.25 or less... etc. So taking a time from an event 200E with a time of 94.01 would fall into "1=94.06 or less" and therefore the score given to the event with a time of 94.01 would be given "1" ... each event has 10 'scores' and the point value represents the position in the list of scores. Does this help?

-lookup is used to determine the position in the list of values to determine the score...


unable to start ORACLE database My application is running on Oracle Database which hosts 3 database instances. All of sudden one of my database instance is not running. when connected through sysdba it says "connected to idle instance". Here are the activities performed

Oracle version:Release 11.2.0.4.0

sudo su - oracle
export ORACLE_SID=testdev1
sqlplus / AS SYSDBA

  Connected to an idle instance.

shutdown immediate

  ORA-01034: ORACLE not available

  ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2:
  No such file or directory

startup

  ORA-01078: failure in processing system parameters

  ORA-01565: error in identifying file
  '+DATADG/testdev1/spfiltestdev1.ora'

  ORA-17503: ksfdopn:2 Failed to open file
  +DATADG/testdev1/spfiletestdev1.ora

  ORA-29701: unable to connect to Cluster Synchronization Service

can anyone help me what is the issue ?

unfortunately my database ASM service is not running. I restarted and everything worked. thanks all for your help


Cascade delete on foreign key Imagine 2 tables :

CREATE TABLE a (a_id serial primary key, a_text text);
CREATE TABLE b (
    b_id serial primary key,
    b_aid int not null REFERENCES a ON DELETE CASCADE);

If I delete from b

 DELETE FROM b WHERE b_id=3

I though that the record in a was going to delete too. But in fact it's not. I though I understand this feature until now... Is there a way to achieve this without trigger ?

I didn't find anything about this elsewhere but maybe I didn't search well. So feel free to mark as duplicate if this question have already been answered.

EDIT: Some ask some more details

I have 26 tables (let's call it a,b,c,d,...,z) that reference to a table named log (this table is going to be partitioned by table name soon). ex :

CREATE TABLE a (
  a_id uuid primary key default uuid_generate_v4(),
  a_somefield text
  a_someotherfield int
  a_log uuid not null REFERENCES log (log_id) default f_create_log(a::name)
);

CREATE TABLE log (
 log_id uuid primary key default uuid_generate_v4(),
 log_creation_date timestamptz not null default now(),
 log_creation_user text not null default user::text,
 log_update_date timestamptz
 log_update_user text,
 log_delete_date timestamptz,
 log_delete_user text,
 table_name text
);

The result that I would like to have is "When I delete a record in "a" it suppress the log record too". If you are wondering why I have a log_delete_* then, this because I accessed the data mostly from view and not the actual table (so when you delete a record in the view, there is a trigger that record the user and the time you suppress the record).

I hope this is more clear by now. I now that this design is not the best one, but it is the simplest I found when I started and I needed a really simple solution.

It is the other way around, if you delete a row in a, the corresponding row in b will be deleted to. If a is a child table of b you should declare the foreign key in a.

Given your updated scenario, I guess an AFTER DELETE trigger for each table a,b,c,d,... is the best choice.

However, I would consider using 1 log-table per table together with an insert, update and delete trigger. Let the triggers do all the work manipulating the log-table, and let the application handle just the tables.


Is it possible to resume an interupted mysql import from a backup file? I created a backup file of my database with phpMyAdmin and checked the following data dump option:

include column names in every INSERT statement
      Example: INSERT INTO tbl_name (col_A,col_B,col_C) VALUES (1,2,3)

For now I get an error because some tables rows at the beginning of the import already exist, results of the incomplete import.

Is it possible to resume the import of this file, which has already been started but not finished ? I would like to do it without deleting the database content, nor modifying too much of the backup file, is it possible ?

I haven't worked with PHPAdmin for a very long time, but it should be able to generate insert ... ignore instead of just insert. If my memory serves me well, there is a checkbox 'using insert ignore' on export/backup page.


Is there a tool to check mysql/mariadb database is all OK? Today, Mariadb went down on one of my servers. The specific issue was something to do with ibdata1 file being locked. I found a website that showed how to fix it, and so all was OK. So I thought.

30 minutes later it went down again, same issue, same fix.

Its now running OK but I am worried it will happen again. I dont know why this would just happen when its been running fine for so long. Its a Linode server that runs Virtualmin for LAMP stack website hosting. Quite straightforward.

So I wondered if there is some sort of tool that can check over the DB and files to check there is nothing corrupt or untoward. Much like you do for operating systems, checking the disk is OK, defragging windows etc. Anything that gives the DB a once over to check all is OK.

Verifying that a MariaDB or MySQL instance is "OK" is a complicated task, not least because there are different levels of "OK", and one man's "OK" is another man's "not OK". There are various tools you can use, though. To name a few in no particular order:

Percona Toolkit's pt-variable-advisor
SHOW ENGINE ... e.g. SHOW ENGINE INNODB STATUS\G,
Activate and check the slow query log
PMM - Percona Monitoring & Management
MONyog
mysql-tuner, see e.g. article here.


After upgrade to SQL Server 2019 some stored procedures fail We just upgraded from SQL Server 2008 R2 to SQL Server 2019(Compability lvl 150).
We have two different stored procedures that started failing after the upgrade, with error messages like this:
Msg 8632, Level 17, State 2, Procedure BuildSelfSaleStats, Line 14 [Batch Start Line 4]
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Whats really strange is that this particular stored procedure doesnt take any arguments, and when we simply execute the body of the SQL code in SSMS, it works fine(!?).
What might cause some SQL code that works fine when executed in SSMS, to suddenly start failing when its wrapped in a stored procedure?
We managed to isolate the error to a date formatting function we had:
This works when run in SSMS:
select dbo.fn_formatdatetime(DATEADD(month, -6, GETDATE()), 'yyyy-mm')
output:
(No column name)
2019-12

But when called in testproc, it fails:
CREATE PROCEDURE testproc
AS
BEGIN
    SET NOCOUNT ON;
    select dbo.fn_formatdatetime(DATEADD(month, -6, GETDATE()), 'yyyy-mm')
END

exec testproc

Msg 8632, Level 17, State 2, Procedure testproc, Line 9 [Batch Start Line 11]
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

This looks to be a problem with UDF inlining. As @ScottHodgin suggested, the first step is to install the latest CU to see if the problem persists. If that doesn't resolve the problem, your options are:

Turn off TSQL_SCALAR_UDF_INLINING for the database:
 USE YourDatabase;
 ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Add INLINE OFF to the problem function header. This will allow the benefits of scalar inlining for other functions.

Refactor the problem query as to avoid the error.


MongoDB replica set: members with different versions of mongod The primary is running mongod 3.0.6 and the secondaries running mongod 3.0.7

is a mixing of versions allowed in the same replica set?

For upgrade it's best practise to not all nodes the same time.

For minor versions its not an issue, although i would suggest to keep the same minor version across all nodes to avoid different behavior (due to different bugs and optimization patches between versions) after a stepdown. For major versions is fine when the primary is one major version behind the secondaries. Its not a good idea to have a primary on different major version from a secondary.


What is the "Default Timeout" value in SQLite connection string used for? I have looked at the SQLite documentation and I can't find anything clear about the value "Default Timeout".

Looking at other websites, it seems to refer to some sort of "Connection timeout", while other sites indicate that it is more of a "Command timeout"

Some quick tests don't seem to indicate that it has anything to do with Commands.

Can anybody confirm/reference what the purpose of that value in the connection string is for?

Edit this is using the official SQLite .NET library

I found this out by looking at the source code: The timeout value of a command is initialized to DefaultTimeout and passed to the internal SQLite3.Prepare function.

This is used to control a loop that automatically retries statements that fail with a Locked or Busy error.  When the timeout has expired (as measured by Environment.TickCount), then an exception is thrown with whatever the most recent error code was.  Otherwise, it sleeps for a random amount of time (between 1 and 150 ms) and tries again.


Create and fill temp table I have a stored procedure that selects some data and returns it. Now I also want to add a temp table, fill it with data and return it with the stored procedure.

I define it as follows:

create table #tempDateTable
(
    Name varchar,
    TempDate datetime
)

INSERT INTO #tempDateTable(Name, TempDate) values ('Smith', @myDate)
SELECT * FROM #tempDateTable

@myDate is parameter passed to the stored procedure.

When I execute the stored procedure it gives me:

Msg 8152, Level 16, State 14, Procedure date_samples_procedure, Line 90
String or binary data would be truncated.

What I find strange is that Line 90 is not any of the above shown lines, but if I remove all the code for this #tempDateTable, the stored procedure works normally.

You should specify the length of the varchar column e.g. varchar(2000).


Store fields that are subset of other fields on same table Just wondering what the optimal design pattern would be for the following data I'm trying to model.
I have a table that stores information for Orders that have been placed by customers.
Orders can be either Invoiced or Opened.
Currently, the table stores data about the total Order information (so...invoiced+open).
I.e. totalAmount, totalCost, etc...
The Invoiced and Open information has to be calculated in code and then Updated per row. Technically speaking, this information can just exist in memory of the app after the calculations. But..I figure...storage is cheap and it'll probably be useful to store this data in the table for later on.
However, I feel like it's kinda awkward and can lead to large rows.
I.e I'd have
totalAmount,
totalCost,
openAmount,
openCost,
invoicedAmount,
invoicedCost
Are there any better ways to do this?
Thanks!

Generally in a Sales system, Sales Orders and Invoice Orders are two different entities related to each other. That's probably a good way to normalize the columns you're referring to. In general, it's better to normalize your tables to reduce data redundancy and improve query efficiency, as opposed to very wide Tables.
Also generally Orders tables (both Sales and Invoices respectively) are generally broken out into two tables themselves: 1) OrderHeader and OrderLine (you can name these more specifically as appropriate).
The Header tables store general information such as the SalesDate (or InvoicedDate for InvoiceHeader) and customer information. It can also store the totals as well but isn't necessary. The Line tables store individual lines for each item of the Order and the information specific to that item. Each line would have the individual cost of the item (e.g. SalesCost for SalesLine, InvoicedAmount for InvoiceLine).


Odd execution plan behaviour from variations in WHERE...IN(List) (reposted from stack-overflow by request)

I've been looking at trying to optimise (or at least change) some EF code in C# to use stored procedures, and found what seems to be an anomaly (or something new to me) when finding rows matching a constant list.
The typical manually-generated short query I'd use would be something like...

SELECT Something FROM Table WHERE ID IN (one, two, others);

We had an EF query that we were replacing with a stored procedure call, so I looked at the output, saw that it was complex and thought my simpler query (similar to the above) would be better. It wasn't. Here is a quick demo that reproduces this, trying a couple of intermediate variations to see where the change-over in plan behaviour occurs.

Can anyone explain why the execution plans for the final version - with the

...WHERE EXISTS(... (SELECT 1 AS X) AS Alias UNION ALL...) AS Alias...)

construct is better - seemingly because it omits the costly SORT operation, even though the plan includes TWO index scans (or more with more list members) rather than the one of the simpler query.

Here's a self-contained example script (I hope)...

USE SandBox;  -- a dummy database, not a live one!
-- create our dummy table, dropping first if it exists
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Test')
    DROP TABLE Test;
CREATE TABLE Test (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, FormId INT NOT NULL, DateRead DATE NULL);
-- populate with some data
INSERT INTO Test VALUES (1, NULL), (1, GETDATE()), (1, NULL), (4, NULL), (5, NULL), (6, GETDATE());

-- Simple query that I might typically use
-- how many un-read entries are there for a set of 'forms' of interest, 1, 5 and 6
-- (we're happy to omit forms with none)
SELECT  T.FormId, COUNT(*) AS TheCount
  FROM  Test AS T
 WHERE  T.FormId IN (1, 5, 6)
   AND  T.DateRead IS NULL
 GROUP BY T.FormId;

-- This is the first step towards the EF-generated code
-- using an EXISTS gives basically the same plan but with constants
SELECT  T.FormId, COUNT(*) AS TheCount
  FROM  Test T
 WHERE  EXISTS (    SELECT NULL
                      FROM (VALUES (1), (5), (6)
                            ) AS X(FormId)
                     WHERE X.FormId = T.FormId
               )
   AND  T.DateRead IS NULL
 GROUP BY T.FormId;

-- A step closer, using UNION ALL instead of VALUES to generate the 'table'
-- still the same plan
SELECT  T.FormId, COUNT(*) AS TheCount
  FROM  Test T
 WHERE  EXISTS (    SELECT NULL
                      FROM (    SELECT 1
                                UNION ALL
                                SELECT 5
                                UNION ALL
                                SELECT 6
                            ) AS X(FormId)
                     WHERE X.FormId = T.FormId
               )
   AND  T.DateRead IS NULL
 GROUP BY T.FormId;

-- Now what the EF actually generated (cleaned up a bit)
-- Adding in the "FROM (SELECT 1 as X) AS alias" changes the execution plan considerably and apparently costs less to run
SELECT  T.FormId, COUNT(*) AS TheCount
  FROM  Test T
 WHERE  EXISTS (    SELECT NULL
                      FROM (    SELECT 1 FROM (SELECT 1 AS X) AS X1
                                UNION ALL
                                SELECT 5 FROM (SELECT 1 AS X) AS X2
                                UNION ALL
                                SELECT 6 FROM (SELECT 1 AS X) AS X3
                            ) AS X(FormId)
                     WHERE X.FormId = T.FormId
               )
   AND  T.DateRead IS NULL
 GROUP BY T.FormId;

Can anyone help me to understand why and (importantly for my learning) if there is a benefit in wider use for using this kind of query format?

I looked around for anything special in (SELECT 1 AS X) stuff and though many show it as being common in EF output, I couldn't see anything about this particular apparent benefit.

For the query that you have I don't see a measurable performance advantage or disadvantage to adding the extraneous derived tables. I'm testing against SQL Server 2017 but I can easily reproduce the behavior that you're seeing. I'm going to use a slightly simpler reproduction:

CREATE TABLE Test (FormId INT NOT NULL);

INSERT INTO Test
SELECT TOP (2580) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

-- query 1
SELECT  T.FormId, COUNT(*) AS TheCount
 FROM  Test T
 WHERE  EXISTS (    SELECT NULL
                      FROM (    SELECT 1
                                UNION ALL
                                SELECT 2
                            ) AS X(FormId)
                     WHERE X.FormId = T.FormId
               )
GROUP BY T.FormId;

-- query 2
SELECT  T.FormId, COUNT(*) AS TheCount
  FROM  Test T
 WHERE  EXISTS (    SELECT NULL
                      FROM (    SELECT 1 FROM (SELECT 1 x) AS X1 -- added
                                UNION ALL
                                SELECT 2
                            ) AS X(FormId)
                     WHERE X.FormId = T.FormId
               )
GROUP BY T.FormId;

With 2580 rows in the table I get different plans as you did. However, it takes just five logical reads to read all data from the table. For both queries I wasn't able to get SET STATISTICS IO, TIME ON to output anything other than:

  CPU time = 0 ms,  elapsed time = 0 ms.

The query plans may be different, but the amount of data in the table is so tiny that it doesn't matter.

Now I'm going to truncate the table and insert 10 more rows than before.

TRUNCATE TABLE Test;

INSERT INTO Test
SELECT TOP (2590) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2

OPTION (MAXDOP 1);

With that minor change I get effectively the same plans for both queries. Both queries do one scan of the data and one sort:

As far as I can tell the plan that does multiple scans is not a legal plan option for the first query. I was unable to force it with the USE XML hint, but I could have made a mistake. The second query is able to choose between both plans on a cost basis. The query rule responsible for the multiple scan approach appears to be JNonUNIA. You can see this in action with the undocumented query hint QUERYRULEOFF JNonUNIA. The Google does not give me any results when searching for this optimizer rule, but based on the description it's an abbreviation for "Join Union All -> Union All Join". In other words, take a join to a UNION ALL and switch it to a UNION ALL of joins. That's exactly what we see in the query plan with multiple scans.

I understand that you're using an EF so you don't have complete control over the code. But just because you see a certain plan today doesn't mean that you'll get that plan in all future versions of SQL Server, especially for something as obscure as what you're observing. If for some reason you need to pick one plan over the other for a query I recommend being as direct as possible. For example, in the current version of SQL Server the following query appears to always result in a single scan:

SELECT
  FormId
, COUNT(*) AS TheCount
FROM Test T
WHERE FormId IN (1, 2)
GROUP BY FormId;

And the following query seems to always result in two scans:

SELECT
  1 AS FormId
, COUNT(*) AS TheCount
FROM Test T
WHERE FormId = 1

UNION ALL

SELECT
  2 AS FormId
  , COUNT(*) AS TheCount
FROM Test T
WHERE FormId = 2
OPTION (MAXDOP 1);

Of course, that behavior isn't guaranteed either. It still feels more reliable, if that makes sense.

It is possible to think of modified queries in which using the UNION ALL approach will give a performance boost. Suppose that you have a million rows in the table and you want to put any rows with a FormId between 1 and 600000 into bucket 1 and any rows with a FormId between 600001 and 1000000 into bucket 2.

The following query takes around 200 ms to execute on my machine:

SELECT
  CASE WHEN FormId <= 600000 THEN 1 ELSE 2 END FormBucket
, COUNT(*) AS TheCount
FROM Test T
WHERE FormId >= 1 AND FormId <= 1000000
GROUP BY CASE WHEN FormId <= 600000 THEN 1 ELSE 2 END
OPTION (MAXDOP 1);

Here's the plan:

However, this query executes in around 100 ms:

SELECT
  1 AS FormBucket
, COUNT(*) AS TheCount
FROM Test T
WHERE FormId >= 1 AND FormId <= 600000

UNION ALL

SELECT
  2 AS FormId
, COUNT(*) AS TheCount
FROM Test T
WHERE FormId >= 600001 AND FormId <= 1000000
OPTION (MAXDOP 1);

Here's the plan:


whether a big (but not used) ibdata1 slow anything down Hi I have an ibdata1 that is around 10 gig.  I have gradually migrated to 'file per table' using the innodb_file_per_table=1 option such that there are no big tables in ibdata1 anymore.

I recently read how to clean things up :

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

My question is, does anyone know if I just leave the big ibdata1 file there, does that cause any kind of performance hit?  I know I can reclaim disk space by cleaning things up, but that is not an issue right now.  Not taking the system down for a day would be a bigger problem.  So, more concerned with server performance or any other side affects.

Thanks!

Don

There are things to keep in mind if you decide not to shrink ibdata1.

First of all, if you switch to innodb_file_per_table and you do not perform the Cleanup, the InnoDB tables will still reside in ibdata1. Any future tables you create will be stored in .ibd files.

As a side note, since you are using innodb_file_per_table, you will need to increase innodb_open_files.

The only way to know which InnoDB tables are still in ibdata1, you would have to run this:

SELECT table_schema,table_name
FROM information_schema.tables
WHERE engine='InnoDB';

then go into each DB Folder of /var/lib/mysql (or datadir) and see if a table has both .frm and .ibd. Those will both are outside ibdata1. Those with .frm only are still in ibdata1.

If you would like to extract all InnoDB tables out of ibdata1 without performing the Cleanup, simply run the following:

SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=''InnoDB'';')"
SQL="${SQL}" FROM information_schema.tables WHERE engine='InnoDB'"
mysql -u... -p... -ANe"${SQL}" > ConvertInnoDB.sql
mysql -u... -p... -A < ConvertInnoDB.sql

This will also defrag all InnoDB tables that are already outside ibdata1.

INSERTs and  UPDATEs against those tables inside ibdata1 can still make ibdata1 grow. Is that bad? If ibdata1 resides in ext3 disk, there is OS-dependent size limit of 2TB. If you are nowhere near 2TB, then don't worry about performance. Switching to innodb_file_per_table spares you any growth spurts of ibdata1. If ibdata1 ever approaches 2TB, you need to

service mysql stop
In /etc/my.cnf, change innodb_data_file_path from ibdata1:10M:autoextend to ibdata1:2047G:ibdata2:10M:autoextend
service mysql start

Here is another point: What lives in ibdata1 besides table data, table indexes, table metadata? Objects for MVCC and Transaction_isolation. Here are the things that ibdata1 provides for MVCC

Double Write Buffer (to avoid caching to the OS)
Insert Buffer (for processing Changes to Secondary Indexes)
Undo Tablespace (used to revert changes of Uncommitted Transactions)
Rollback Segments (List of pointers into Undo Tablespace)

These things can make ibdata1 grow but not to any dangerous levels. Over time, I have seen ibdata1 grow to 10G at worst in an environment that featured constant dropping and recreating of InnoDB tables.

Performance should be OK. With a bloated ibdata1, you can probably handle big transactions in terms of rollback and insert buffering. I would just increase innodb_log_buffer_size to compensate.


Trouble deciding what/how many tables to use; normalisation suggests one More of a conceptual question for the community, as I am not "thinking with databases" as much as I would like to just yet. I am using Oracle Database 18c Express Edition with SQL Developer.

I have been asked to build a database to log sales in a shop. The things to log are;

Date
Day
Time
Value [grouped, either Low, Medium, or High, rather than absolute monetary value]
Location [on a (-5,-5) to (5,5) grid]

If we throw in an incremental UID as the Primary Key for each sale, I can't see why I would need any more than the one table to do this. I need one entity, Sale, and it would consist of six attributes (the UID and the list above). It would have a primary key, no repeating groups, no composite PK, and no non-key fields dependent on another non-key field.

Queries I have been asked to generate (which I don't need help with, yet, but might influence the design) are;

Find quietest day
Find busiest hour
Find highest income location
Generate a report on sales by value range
Follow on report of purchases by hour in each value range
Identify locations with no income

There are obviously some constraints I need to deal with (shop isn't open Sundays, only open between x and y hours, escalator to get to the floor is at (0,0), etc), but these are relatively simple and I am confident that some more reading and tweaking will get me there. Technical problems are much easier to work though, in my experience.

My real drama comes from trying to come up with the database design. Normally I would expect normalisation to do most of the grunt work for me, directing me to where I need to make new tables to separate off different attributes in to their own entities. The task specifies data needs to be 3NF (and then also goes on to say there needs to be no normalising, confusingly). I am reading through "Systems Analysis : A Beginner’s Guide" by Kevin Bowman as my main point of reference, and based on my understanding of it, the data I am expecting to log is already 3NF, and doesn't need more than the one table.

This seems an awful lot too simple, and I suspect I am simply not getting it at some point in the process. A colleague has said he plans on making a table for each attribute, which just seems mad and unnecessary to me.

Does anyone have any thoughts, or see any issues with my reasoning?

One point you might consider is to store the actual sales value any way. If you store ranges a user must convert from value to range and might make errors. If you create a table storing the ranges, the system can do this task. You will also be able to changes ranges later.

Normalisation also means not storing redundant information. As you can derive the day from a date (with Format) you do not need both. It might also cause inconsistent data.

Last thing you could do is to create a location table. Finding locations without sales is hard if the system does not know which locations exist. It will also help validate the user input.


UPDATE STATISTICS options fail in Amazon RDS SQL Server In Amazon RDS SQL Server (any version/edition), create a temp table and update statistics on it. This works fine:

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t;
GO
CREATE TABLE #t (id INT NOT NULL);
GO
UPDATE STATISTICS #t
GO

But try to specify the rowcount on the statistics:

UPDATE STATISTICS #t
  WITH ROWCOUNT = 100000000;
GO

And you get an error:

  Msg 1088, Level 16, State 12, Line 1 Cannot find the object "#t"
  because it does not exist or you do not have permissions.

The STATS_STREAM, ROWCOUNT, and PAGECOUNT options are documented, but not officially supported. Is there a workaround for AWS RDS SQL Server so that they work?

It is true that either sysadmin OR db_owner privileges are required to use those options, per SQL code.  We know we do not have sysadmin privileges.  For internal tables, sysadmin permissions would be required.  Since temp tables are not internal, this does not apply.  So that leaves one possibility - we must have db_owner privileges.  Do we?  In the SQL Code, it is being evaluated not at the current DB context, but of the database of the object we are attempting to alter:  TempDB.

Now that we know why this fails, we can evaluate possible workarounds.
1. If Amazon RDS grants privileges to modify the tempdb database roles, adding the user to db_owner would do the trick.  I haven't tested this, but based on this article believe it is likely doable.  It would be the easiest and perhaps most elegant solution.
2. Populate the temp table with junk that matches the stats profile you're targeting, update stats with NORECOMPUTE, then truncate and populate/use it in the way originally intended.

PROOF:
Login with sysadmin; verify

SELECT SUSER_SNAME() [CurrentLogin], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()) GO

Create test database and use it

CREATE DATABASE [Test] GO USE [Test] GO

Confirm that this issue doesn't happen with sysadmin

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t; GO CREATE TABLE #t (id INT NOT NULL; GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Create a user that doesn't have sysadmin privileges and use it

CREATE LOGIN [UpdateStatsLogin] WITH PASSWORD = 'DontPeek!'
CREATE USER [UpdateStatsUsr] FROM LOGIN [UpdateStatsLogin]
SETUSER 'UpdateStatsUsr' GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME())

Repro the error

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Demonstrate that db_owner of the current database doesn't work

SETUSER GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO ALTER ROLE [db_owner] ADD MEMBER [updatestatsusr] GO SETUSER 'updatestatsusr' GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO

Repro the error

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Now add to db_owner in tempdb switch back to sysadmin & verify

SETUSER GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO USE TEMPDB GO CREATE USER [updatestatsusr] FROM LOGIN [updatestatslogin] GO ALTER ROLE [db_owner] ADD MEMBER [updatestatsusr] GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO

And watch the error disappear

USE [Test] GO SETUSER 'updatestatsusr' GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Remove from db_owner in Test to show that it's tempdb that matters

SETUSER GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO ALTER ROLE [db_owner] DROP MEMBER [UpdateStatsUsr] GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO


Can a trigger access the query string? I'm thinking about using triggers as a logging mechanism in MySQL (v. 5.1) and therefore I'd like my trigger to get the query string in order to store it in another tabe.

I couldn't find anything like that in the MySQL documentation so I expect the answer to be no – but I still hope that I've overlooked something.

PS: this question is related to IcarusNM's answer to my question "Log MySQL DB changing queries and users"

I have a working theory (LaForge would say to Capt Picard)...
Since you are using MySQL 5.1, you have access to the table INFORMATION_SCHEMA.PROCESSLIST.
You also have access to the ID of the current process the trigger is running on. The function to get that process ID is CONNECTION_ID.
You could try to fish out the query like this:
SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID();

Keep in mind that the info is LONGTEXT:
mysql> show create table information_schema.processlist\G
*************************** 1. row ***************************
       Table: PROCESSLIST
Create Table: CREATE TEMPORARY TABLE `PROCESSLIST` (
  `ID` bigint(4) NOT NULL DEFAULT '0',
  `USER` varchar(16) NOT NULL DEFAULT '',
  `HOST` varchar(64) NOT NULL DEFAULT '',
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) NOT NULL DEFAULT '',
  `TIME` int(7) NOT NULL DEFAULT '0',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Within the trigger, you could use a local variable to hold the query
DECLARE original_query VARCHAR(1024);

and then capture the query
SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID();

Give it a Try !!!
If it works, then
Make it so (Capt Picard would quip back to LaForge)


What is DBCC CHECKIDENT current column value DBCC CHECKIDENT output includes a "current identity value" and a "current column value". I can see that "current identity value" is the identity that will be assigned to the next row. But what is the "current column value" referring to?

My routine works like this (pseudo):

dbcc checkident ('TableA')
set identity_insert on
insert TableA select TableB where status = 'X'
delete TableB where status 'X'
set identity_insert off
dbcc checkident ('TableA')

The current identity value of both DBCC statements is the same. The current column value seems to be the highest ID recovered. But what does it mean?

Not sure what you mean by "recovered" - current column value tells you the maximum value in the table. It's meant to show you when the maximum value is greater than the value SQL Server assigned last (and from which it determines what it thinks it's going to assign next, even if in practice this isn't always true). Example:

SET NOCOUNT ON;
CREATE TABLE dbo.smudge(id INT IDENTITY);
GO
-- insert 11 rows
INSERT dbo.smudge DEFAULT VALUES;
GO 11
DBCC CHECKIDENT('dbo.smudge'); -- 11, 11
GO
-- delete the largest id
DELETE dbo.smudge WHERE id = 11;
GO
DBCC CHECKIDENT('dbo.smudge'); -- 11, 10
GO
-- reseed the table
DBCC CHECKIDENT('dbo.smudge', RESEED, 1);
GO
DBCC CHECKIDENT('dbo.smudge'); -- 1, 10
GO
-- insert another row
INSERT dbo.smudge DEFAULT VALUES;
GO
DBCC CHECKIDENT('dbo.smudge'); -- 11, 11
GO
-- insert a much higher row
SET IDENTITY_INSERT dbo.smudge ON;
INSERT dbo.smudge(id) VALUES(255);
SET IDENTITY_INSERT dbo.smudge OFF;
GO
DBCC CHECKIDENT('dbo.smudge'); -- 255, 255
GO
DROP TABLE dbo.smudge;
GO


Can SQL Server start taking backups by itself? We have a SQL Server which started to take backups by itself every now and then. It started for a few days ago.

When it fails an error is logged saying that source is "Microsoft SQL Server Native Client 10.0"
It also says:

  "SQLSTATE: 42000, Native Error: 3201 Error state: 7, Severity: 16
  Error message: Cannot open backup device
  '{94C30FE5-1C62-45C7-8E28-4F67BCF89210}682'."

How can this happen? Can I find out what part of the code which does this?

It is not a part of any jobs or similar, the time for the backup is different from day to day, it does not try to take a backup every day but most of them. If it fails it retries a lot of times and after a veil its stops.

Edit:
The first clue to find out why these backups were taken I found from Kevin3NF advise, were I could confirm that it was the SQL Server VSS Writer which were taken the backups.
Then John Eisbrener commented that if it is VSS writer it is probably when one takes a backup of the entire OS. I then found that someone has had a similar problem: Why SQL Server Express 2012 take automatically backup in "Virtual Device" without virtual device configuration?
We have now confirmed that it happens when our new 3rd party tool is taking a backup of the entire OS.

Most likely it is a 3rd party backup solution such as NetBackup that your sysadmins have pointed to your instance.

Try reading the default trace to see if the application name column gives you the specifics:

-- read all available traces.
-- including current
DECLARE @current VARCHAR(500);
DECLARE @start VARCHAR(500);
DECLARE @indx INT;

SELECT @current = path
FROM sys.traces
WHERE is_default = 1;

--remove the rollover number from the filename
--(i.e., log_157.trc becomes log.trc)
SET @current = REVERSE(@current)
SELECT @indx = PATINDEX('%\%', @current)
SET @current = REVERSE(@current)
SET @start = LEFT(@current, LEN(@current) - @indx) + '\log.trc';

-- CHANGE FILTER AS NEEDED
SELECT
    TextData,
    ApplicationName,
    StartTime,
    CASE EventClass
        WHEN 46 THEN 'Object:Created'
        WHEN 47 THEN 'Object:Deleted'
        WHEN 164 THEN 'Object:Altered'
        ELSE 'Other'
    END as EventClass,
    DatabaseName,
    ObjectName,
    ObjectType, -- https://msdn.microsoft.com/en-us/library/ms180953.aspx
    HostName,
    LoginName
FROM
    ::fn_trace_gettable(@start, DEFAULT)
WHERE 1=1
    AND DatabaseID <> 2 --eliminate tempdb
    AND DatabaseName = 'model'--Like '%master%'
    AND Textdata like 'Backup Database%'
ORDER BY StartTime DESC


How to get hostname in postgresql? Is there any way where I can get hostname/servername in postgresql like @@ServerName in SQL Server?

You need to execute following query to get the hostname:

SELECT boot_val,reset_val
FROM pg_settings
WHERE name='listen_addresses';;


PostgreSQL doesn't log I'm using ArchLinux and PostgreSQL 9.4.4 and have enabled the logging in the config file:

$ sudo egrep -v "^[[:blank:]]*($|#|//|/\*| \*|\*/)" /var/lib/postgres/data/postgresql.conf

max_connections = 1024          # (change requires restart)
shared_buffers = 128MB          # min 128kB
dynamic_shared_memory_type = posix  # the default is the first option
logging_collector = on      # Enable capturing of stderr and csvlog
log_directory = '/tmp'      # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0644            # creation mode for log files,
log_error_verbosity = verbose       # terse, default, or verbose messages
log_statement = 'all'           # none, ddl, mod, all
log_timezone = 'Asia/Jakarta'
datestyle = 'iso, dmy'
timezone = 'Asia/Jakarta'
lc_messages = 'en_CA.UTF-8'         # locale for system error message
lc_monetary = 'en_CA.UTF-8'         # locale for monetary formatting
lc_numeric = 'en_CA.UTF-8'          # locale for number formatting
lc_time = 'en_CA.UTF-8'             # locale for time formatting
default_text_search_config = 'pg_catalog.english'

then restart or reload the service:

sudo systemctl restart postgresql
sudo systemctl reload postgresql

the loaded settings:

postgres=# SELECT name, setting FROM pg_settings WHERE name LIKE '%log%';
            name             |            setting
-----------------------------+--------------------------------
 log_autovacuum_min_duration | -1
 log_checkpoints             | off
 log_connections             | off
 log_destination             | stderr
 log_directory               | /tmp
 log_disconnections          | off
 log_duration                | off
 log_error_verbosity         | verbose
 log_executor_stats          | off
 log_file_mode               | 0644
 log_filename                | postgresql-%Y-%m-%d_%H%M%S.log
 log_hostname                | off
 log_line_prefix             |
 log_lock_waits              | off
 log_min_duration_statement  | -1
 log_min_error_statement     | error
 log_min_messages            | warning
 log_parser_stats            | off
 log_planner_stats           | off
 log_rotation_age            | 1440
 log_rotation_size           | 10240
 log_statement               | all
 log_statement_stats         | off
 log_temp_files              | -1
 log_timezone                | Asia/Jakarta
 log_truncate_on_rotation    | off
 logging_collector           | on
 syslog_facility             | local0
 syslog_ident                | postgres
 wal_log_hints               | off
(30 rows)

the /tmp permission:

$ ll /tmp/
total 11656
drwxrwxrwt 22 root root       580 Jun 29 10:05 ./
drwxr-xr-x 17 root root      4096 Jun 10 08:04 ../
...

the /tmp usage:

$ df | grep /tmp
tmpfs            8159996    31204   8128792   1% /tmp

doing some query on psql, but the /tmp/postgresql-* still doesn't show up. What can I do to find which part that prevent log from being created?

EDIT 1

the result of journalctl -u postgresql

Oct 08 09:13:53 xxx postgres[19861]: LOG:  00000: redirecting log output to logging collector process
Oct 08 09:13:53 xxx postgres[19861]: HINT:  Future log output will appear in directory "/tmp".
Oct 08 09:13:53 xxx postgres[19861]: LOCATION:  SysLogger_Start, syslogger.c:645
Oct 08 09:13:55 xxx systemd[1]: Started PostgreSQL database server.

The logging collector tries to capture stderr and write it to a file.  But it looks like systemd wins the redirecting war, and sends the output to the journal.  You can check the systemd journal with:

journalctl -u postgresql

The systemd configuration is in usr/lib/systemd/system/postgresql.service. I've tried various settings for StandardError, but none seems to allow the regular Postgres logging to do its work.


How to solve Error 927 'can not open database' while configuring DB mirroring Steps I followed:

Create DB on primary.
Took full backup and log backup.
Restored full and log on secondary in NORECOVERY mode
(now tb shows restoring satus on secondary I guess this is because NORECOVERY mode).
Tried configuring mirror from primary.

I was able to configure it successfully with windows 2012 and SQL server 2012 using SSMS 17.6 but it's giving above error with Win2k16 and SQl server 2016

It seems there are bugs with SSMS (2016) to configure mirroring
https://stackoverflow.com/questions/38008208/sql-server-2016-cant-start-mirror

Try to use TSQL or SSMS 2014

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-establish-session-windows-authentication


How to add the Debug button to SSMS v18? The Debug button is present on this version of SSMS:

But it is not present on version 18, preview 4:

I have tried in several ways to add the Debug button to my SSMS but not successful.

Is there a way to add the Debug button to SSMS v18?

Debugger is deprecated in SSMS18 as @LowlyDBA said in his answer.

For those who look for an alternative, you can use SQL Server Data Tools (SSDT)/Visual Studio to debug a query.

Connect to a SQL server (in Tools menu)
Open a new query (Server explorer, right click, new query)
Type in your query
Execute with Debugger

From the SQL menu

From the Drop down list next to the execute icon in the query window

From the context menu in the query wondow


SQL Server : primary keys advice to my whitepaper needed I've tired to explain to every new junior developer in our r&d team why he should use Primary Keys and how to do that. So I decided to write small whitepaper, which every new developer should read. Here is the draft of it.

DISCLAIMER:

I know and understand the difference between clustered index and primary key
In the following question PRIMARY KEY means PRIMARY KEY CLUSTERED, and WITHOUT PRIMARY KEY means WITHOUT PK AND CLUSTERED INDEX
Note that it is a whitepaper for junior and not SQL programmers
All the things that they do - reviewed before entering to the main development branch
I'm not going to explain to them - when there will be benefit of using clustered index and NONclustered primary key - else I will fall to premature optimizations hell

The question is - what else should I add to the document? May be - what I should change in it? And what to explain more in detail?

Here comes the draft:

Primary keys have to constrain any table within database.
Without PK a table considered as a heap and SQL Server has very limited uses of this type of data - the only thing I should say it is suitable - a buffer for fast bulk-loading data from the outside of SQL Server's engine.
Avoid using natural primary keys. Primarily because of theirs natural (gauss) distributions. For example in PhoneBook table with primary key based on Family and Name will be many Smiths and Wilsons and much fewer Zimmerbergs and this states, that pages containing Smiths and Wilsons will be splitted more often, than other pages, and queried also more often - which multiplies the performance impact. That leads to performance degradation, because of primary key's page fullness and most of searches will hit PK's sparsed pages.

Moreover, even using SSN or ID Number, which have (I hope) flat distribution, as PK does not solve the problem of PK's page splitting, because this numbers are not in any organized order.

Secondary - natural PKs are often composite. That creates composite foreign keys and wide indexes and as a result hurts performance. So, ...
Avoid using composite PKs. Better using simple surrogate PK and composite UNIQUE index, than all-in-one composite PK because it leads to composite FK and wide indexes due to statement, that every secondary index on table with PK has to include whole PK within.
Avoid using surrogate primary keys other than integer or uniqueidentifier types
During database design phase it is very important to identify entities and corresponding tables which may have in perspective "zillions" of rows, or tables which keys have to be NOT ONLY table-wide unique, but DB or even world unique, or which have to be joined with other tables by this key over several hop-tables - this tables better to have UUID PK, others - ordinary integer, because SQL Server very well fine tuned to use integer PKs.
These two types can guarantee both vector distribution (IDENTITY or NEWSEQUENTIALID()) and monotonous sequence.
Main database design rule - Twenty minutes spent to well thought-out design will save days or even weeks during production database's maintenance

You are confusing various concepts here, the major error being primary key != clustered key. Because of this misunderstanding, the majority of the guidance is incorrect. To be brutally honest, you are probably not well placed to be writing these guidelines.

Primary key != clustered key
Unique identifiers make for very poor clustered keys
SQL Server is not "very well fine tuned to use integer PKs". The case for a narrow clustered key is covered in the link above, it is not a matter of SQL Server being optimised for integer clustered keys.