contradiction of 3rd normal form on address table I have an Address table and its columns are address_id, address_1, address_2, city, state, country, pin code .Its primary key is address_id . According to 3rd normal form no non-key attribute should depend on other non key attribute. But in this table the non key attribute state is depending on other non key attribute country that means if we know state we don't really require address_id(primary key) to find which country it belongs. So, how should i design the Address table so that it follows 3rd normal form. Also, is 3rd normal form really needed to be applied on database tables? I have attached the image of Address table schema.

You should note that not every country has the same rules for addresses. I would believe that address_id (as an integer) is just a surrogate key for an entry in the Address table.  It is not a reference to the country.

If you want to look into the variations in addresses you might find the following interesting.

FRANK'S COMPULSIVE GUIDE TO POSTAL ADDRESSES

Regarding City/State/Country you will not always have all of these values available to you.  What about countries that do not have states?

If you have a comprehensive data source for every city, state (including NULL cities and states), and country that is maintained, then you could avoid the extra column.

However, you will need to determine your workaround when some elements are missing. (As you can find examples in Frank's web page.)

My personal opinion (not shared by everyone) is that a state and country column are often better than maintaining a large data set.

If you need is extensive enough you could subscribe to a service to maintain you address information. Just do a web search to find a service that fits your budget.


Restricting info to SYS users - Oracle Is it possible to restrict sensitive information from users having sysdba role? Does Data redaction help us in achieving this?

I know we can restrict particular schemas to prevent from viewing from sensitive info by using redaction.. but what about sys user? Can that user still view everything?

Oracle Database Vault was designed for that exact purpose.

Documentation link here.


SQL Server 2005 - Map login to pre-existing user The short of it is that we have several databases that are synchronized between two failover instances. The primary contains logins that are mapped to the users in the databases, and everything's hunky-dory. However, I didn't realize that server logins didn't synchronize between failover instances, so when there is a failover to the backup, nobody can log in. The kicker is that while I can create a login on the backup, I can't map the login to the DBs as a user because a DB user with that name already exists, and the login won't use the existing user, so logins still fail because the login doesn't have rights to connect to the DB.

I think what I have to do is modify the login in the master DB of the backup to have the same GUID as the login from the primary, and manually create the link between the login and user on the backup side, thereby "tricking" the backup instance into thinking the user created on the primary is is its own mapped login as well. However I have no clue how I would go about doing this, and we're having enough trouble with this issue without me flailing at it.

Help?

For a failover-type situation, I use the handy 'sp_help_revlogin' stored procedure as published in http://support.microsoft.com/kb/918992

This procedure, when run on your primary server, will script out each login WITH the SID (this is the important part for making sure your logins are mapped to their respective database users) and hashed password (so the password remains the same on each server.) The resulting script can then be run against your failover server to create the logins. Be sure to review the output before running it.

As-is, it scripts all of the logins in the server, but could be modified to script only the logins you're interested in. It could also be automated to generate a script on a regular basis.


quotename(object_schema_name(@objectId)) returns null when called as a synonym I have a central utility database in each SQL Server instance, called Utility. In it, I have a bunch of centralized functions and procedures that I call from other databases.

A function I have in it is called GetFqn:

create function [dbo].[GetFqn](@objectId bigint)
returns varchar(100)
as
begin
    declare @fqn varchar(100);
    select @fqn = quotename(object_schema_name(@objectId)) + '.' + quotename(object_name(@objectId));
    return @fqn;
end

When I call this function from another database it returns NULL:

select Utility.dbo.GetFqn([object_id])
from sys.tables

What should I do to make it work?

You have to add a parameter which indicates a database:

create function [dbo].[GetFqn](@objectId bigint, @DatabaseId INT)
returns varchar(100)
as
begin
    declare @fqn varchar(100);
    select @fqn = quotename(object_schema_name(@objectId, @DatabaseId)) + '.' + quotename(object_name(@objectId, @DatabaseId));
    return @fqn;
end

Then call:

select Utility.dbo.GetFqn([object_id], DB_ID())
from sys.tables


How to use the COUNT function to count up to the current row for each row? I had trouble phrasing the question, but I think the following example will clear things up.

Context: Trying to create a query for Microsoft Report Builder, need to perform some logic on the data in one of the tables.

I have a SQL Table (let's call it AllEvents) in the database with a format like this:

UserName   |    Grouping    | EventDate | NumberOfEvents
________________________________________________________
Alice      | Red Grouping   | 1/1/2000  | 1
Alice      | Red Grouping   | 1/2/2000  | 2
Alice      | Red Grouping   | 1/3/2000  | 3
Alice      | Blue Grouping  | 1/4/2000  | 1
Alice      | Blue Grouping  | 1/5/2000  | 2
Anderson   | Red Grouping   | 1/3/2000  | 1
Anderson   | Blue Grouping  | 1/5/2000  | 1
Anderson   | Green Grouping | 1/6/2000  | 1
Anderson   | Green Grouping | 1/7/2000  | 2

And I want to select the results such that I get an output of the following form, where it counts the number of events for the UserName before and up to the row's EventDate.

UserName    | EventDate   | EventNumber
_______________________________________
Alice       | 1/1/2000    | 1
Alice       | 1/2/2000    | 2
Alice       | 1/3/2000    | 3
Alice       | 1/4/2000    | 4
Alice       | 1/5/2000    | 5
Anderson    | 1/3/2000    | 1
Anderson    | 1/5/2000    | 2
Anderson    | 1/6/2000    | 3
Anderson    | 1/7/2000    | 4

We can ignore the grouping column, since we're only interested in the rows with the same UserName.

What I have been trying to do is use a subquery like below:

SELECT

   SUM
   (
      CASE WHEN AE.EventDate> AllEvents.EventDate
         THEN 1
         ELSE 0
      END
   )
   OVER (PARTITION BY UserName, Grouping)

FROM AllEvents AS AF

WHERE
   AE.UserName= AllEvents.PatientId
   AND AE.EventDate= AllEvents.EventDate

In the following query:

SELECT
   UserName,
   EventDate,
   [The subquery above] AS EventNumber

FROM
  AllEvents

However, the subquery is returning more than one value and I'm struggling to determine how to troubleshoot.

I have also been toying with the COUNT function, but have failed to have it count conditionally based upon the current row's EventDate value.

Any advice or suggestions would be appreciated.  I have been trying to be creative in my approach, but I don't know if I've hit the best method.

This will do it

select UserName,
       EventDate,
       row_number over (partition by UserName order by EventDate asc) as EventNumber
  from AllEvents


How to limit schema size by a trigger The problem:
I am running on a Webhotel a MySql database with different schemas with a maximum size limit on each schema. I can't change any settings on the DBMS. To be able to keep the schema below the max size i can remove the oldest rows to make space for newer ones, like a LIFO.

The only way to do this I thought was making a trigger. I'm trying to make one like this:

CREATE TRIGGER inventoryproposalprocessed_AINS AFTER INSERT ON iventoryproposalprocessed
    FOR EACH ROW
    BEGIN
      DECLARE size INTEGER;
      SELECT sum( data_length + index_length ) / 1024 / 1024 INTO size FROM information_schema.TABLES where table_schema like 'd40013_x_frodo' ;
      IF size > 99 THEN
         DELETE FROM inventoryproposalprocessed order by _id limit 25;
      END IF;
    END;

But it doesn't work. How should a trigger like that look like?

You cannot change the rest of rows on table with its own trigger- that is by design. Check this answer.

That also provides an alternative method: disable the INSERT privileges on that table and allow inserting only by using a stored procedure, that will do as you want (although I do not like much the logic behind it):

CREATE PROCEDURE insert_inventoryproposalprocessed (...required input values...)
    BEGIN
      INSERT INTO inventoryproposalprocessed VALUES (...input variables...)
      DECLARE size INTEGER;
      SELECT sum( data_length + index_length ) / 1024 / 1024 INTO size FROM information_schema.TABLES where table_schema like 'd40013_x_frodo' ;
      IF size > 99 THEN
         DELETE FROM inventoryproposalprocessed order by _id limit 25;
      END IF;
    END;


Why does the optimizer choose nested loops over merge joins here? I have 3 tables. #a is main one and two secondary tables, #b and #c.

create table #a (a int not null, primary key (a asc)) ;
create table #b (b int not null, primary key (b asc)) ;
create table #c (c int not null, primary key (c asc)) ;

insert into #a (a)
select x*10 + y
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))x(x)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))y(y) ;

insert into #b (b)
select a from #a where a % 5 > 0 ;

insert into #c (c)
select a from #a where a % 4 > 0 ;

If I join main table #a with only one secondary table, there will be Merge Join in query plan.

select *
from #a a
inner join #b b on a = b ;

But if I join main table #a with both secondary tables, there will be only Nested Loops.

select *
from #a a
inner join #b b on a = b
inner join #c c on a = c ;

Why does it work like that and what should I do to get two Merge Joins?

Without inner merge join hint.

Why does it work like that and what should I do to get two Merge Joins?

With three table references (the minimum required), the query qualifies for the Transaction Processing (aka search 0) stage of cost-based optimization.

This stage is aimed at OLTP queries, which normally benefit from a navigational (index-based) strategy. Nested Loops Join is the main physical join type available (hash and merge are only considered if no valid nested loops plan can be found in this stage).

If this stage finds a low cost (good enough) plan, cost-based optimization stops there. This prevents spending more time in optimization that we can expect to save over the best solution found so far. If the cost exceeds a threshold, the optimizer moves on to the Quick Plan (search 1), parallel Quick Plan, and Full Optimization (search 2) phases.

The query with two table references did not qualify for Transaction Processing, and went straight into Quick Plan, where Merge and Hash joins are available.

See my Query Optimizer Deep Dive series for more information.

  Without inner merge join hint.

If you absolutely must hint a physical join type, strongly prefer OPTION (MERGE JOIN). This allows the optimizer to still consider changing the join order.

Join hints like INNER MERGE JOIN come with an implied OPTION (FORCE ORDER), which severely limits the optimizer's freedom, with consequences most people (including experts) do not appreciate.


UPDATE CONCAT without duplicates I would like to concatenate multiple values and remove duplicates (case-insensitive) in the same field with SQL only.

Without using a unique key or stored procedure.

Ex.

Toto;toto1;titi;TOTO;toto1;tata;

in

Toto;toto1;titi;TOTO;tata;

Here is my SQL model:

UPDATE users SET pseudo = CONCAT_WS(';', pseudo, 'NEWS_VALUE') WHERE id = '1';

I can not handle duplicates

Thank you for your help

You need to create a sub-query with distinct values and then join it to your main table and update, like this:

You will need a temporary numbers table:

CREATE TABLE numbers (
  n INT PRIMARY KEY);

INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

UPDATE users u
JOIN (
     SELECT
     ID, GROUP_CONCAT(pseudo SEPARATOR ';') pseudo
     FROM(
     SELECT DISTINCT
      users.id,
      lower(SUBSTRING_INDEX(SUBSTRING_INDEX(users.pseudo, ';', numbers.n), ';', -1)) pseudo
    FROM
      numbers INNER JOIN users
      ON CHAR_LENGTH(users.pseudo)
         -CHAR_LENGTH(REPLACE(users.pseudo, ';', ''))>numbers.n-1
    ORDER BY
      id, n) u
    GROUP BY ID
     ) du
    ON u.id = du.id
SET u.pseudo = CONCAT_WS(';', du.pseudo, 'NEWS_VALUE')
WHERE u.id = '1';

SQL Fiddle that shows how to split using numbers table


Problem with MySQL multiple join on same table with condition Using MySQl 5.6 as my database, I'm trying to join a table twice, on another joined table, but I'm getting duplicate results, rather than the row combined.

SO, I have a checks table, and a check can be assigned to an area or areas, which are stored in the check_assigned_area table. Entries in this table can either be a location or a department. Locations being a parent of departments.

SO, im joining the checks_assigned_area table on to the checks table via the PK (check_id)

AND then im trying to join the areas table twice, once for locations and once for departments, depending on the area_level. 3 being a location and 4 being a department.

But instead of the query returning a single row with the location name and department name. Its returning 2 seperate rows, and it isnt populating the department name.

I've tried all manner of left/inner/right join combinations, distincts, group by's, but I cant get the data correct.

Can I even do it like this? My logic tells me yes, but practically I cant get it to work.

Any ideas if I can get it to output the data i need?

Heres an SQL fiddle of my data and query http://sqlfiddle.com/#!9/63d6ce/4

Checks table:

+----------+---------------+------------+------------+--------+----------+
| check_id | check_type_id |    due     | created_by | status | sign_off |
+----------+---------------+------------+------------+--------+----------+
|    19335 |            43 | 18/09/2017 |          3 |      1 |        1 |
|    19336 |             4 | 19/09/2017 |          3 |      1 |        1 |
|    19358 |            62 | 21/09/2017 |      47336 |      1 |        1 |
+----------+---------------+------------+------------+--------+----------+

Check assigned area table:

+------------------------+---------------+----------+
| check_assigned_area_id | check_area_id | check_id |
+------------------------+---------------+----------+
|                  18801 |             9 |    19335 |
|                  18802 |           816 |    19336 |
|                  18803 |           816 |    19336 |
|                  18804 |          8091 |    19358 |
|                  18835 |          8092 |    19358 |
+------------------------+---------------+----------+

Query:

SELECT `c`.*, `t`.`check_name`,`a`.`check_area_id`,`l`.`area_name` AS 'location_name', `d`.`area_name` AS 'dept_name'
FROM `checks` `c`
LEFT JOIN `check_assigned_areas` `a` ON `a`.`check_id` = `c`.`check_id`
LEFT JOIN `areas` `l` ON `l`.`area_id` = `a`.`check_area_id` AND `l`.`area_level` = '3'
LEFT JOIN `areas` `d` ON `d`.`area_id` = `a`.`check_area_id` AND `d`.`area_level` = '4'
LEFT JOIN `check_types` `t` ON `t`.`check_type_id` = `c`.`check_type_id`
WHERE `c`.`org_id` = '297'
AND `c`.`status` != '0'
AND `c`.`due` <= '2017-09-21'

I tweaked the fiddle you provided, and I believe I now get the results you wanted. Here's the updated fiddle.

The updated query is:

SELECT `c`.*, `t`.`check_name`,`al`.`check_area_id` as 'loc_check_area_id',`l`.`area_name` AS 'location_name',`ad`.`check_area_id` as 'dept_check_area_id', `d`.`area_name` AS 'dept_name'
FROM `checks` `c`
LEFT JOIN (`check_assigned_areas` `al`
             INNER JOIN `areas` `l` ON `l`.`area_id` = `al`.`check_area_id` AND `l`.`area_level` = '3'
          ) ON `al`.`check_id` = `c`.`check_id`
LEFT JOIN (`check_assigned_areas` `ad`
            INNER JOIN `areas` `d` ON `d`.`area_id` = `ad`.`check_area_id` AND `d`.`area_level` = '4'
          ) ON `ad`.`check_id` = `c`.`check_id`
LEFT JOIN `check_types` `t` ON `t`.`check_type_id` = `c`.`check_type_id`
WHERE `c`.`org_id` = '297'
AND `c`.`status` != '0'
AND `c`.`due` <= '2017-09-21'
;

Here's the results (truncated to just show the location and department data).

 check_id | ... | loc_check_area_id | location_name | dept_check_area_id |  dept_name
----------+ ... +-------------------+---------------+--------------------+--------------
   19358  | ... |       8091        |  Luton Office |        8092        | Development
   19357  | ... |      (null)       |     (null)    |       (null)       |   (null)

Since there are two separate check_assigned_areas rows for the check that has a valid location and department, you would logically get two rows for that check. One row represents a location, one a department. So, you got separate rows for those values.

I changed the query, so instead of separate LEFT JOINs for check_assigned_areas, areas that are locations, and areas that are departments, you have a LEFT JOIN for check_assigned_areas and areas that are locations, and a separate LEFT JOIN for check_assigned_areas and areas that are departments. In both of these LEFT JOINs, check_assigned_areas and that set of areas rows are INNER JOINed together.

This means that you'll get one (set of) row(s) for the location(s) that tie to this check, and one for the department(s).

Note that there are two check_assigned_areas.check_area_id values involved, so I show the check_area_id from the location and the check_area_id from the department as two separate columns.

Also note that, if there are multiple areas that are locations tied to the check, and/or multiple departments, you would see a row for each possible combination of location and department found; that's why the previous paragraph is written to allow for such.

However, if there's zero or one locations, and zero or one departments, you'll get one row with the location and department found.


Are multiple column index used in single column where clause I have a table with a defined non clustered index on 3 columns.
CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable]
(
    [Col1] ASC,
    [Col2] ASC,
    [Col3] ASC
)

Will it be used if i do a request with a where clause on a single column or do I need to create a new index on the specified column?
e.g. :
SELECT TOP 1000 * from MyTable WHERE Col2 in (1,2,3)

The index can be SEEKed if you search on Col1 (first column).
It can possibly be scanned if you search for the other columns, but if it is depends on whether it covers the index, estimated selectivity and stuff like that. And even if it would be used, a scan is never as effective as a seek (rest being equal).


Upserting data with existing many-to-many connections This is a strategy question - but one I've been grappling with all week and could really use some wisdom-as-a-service.
Context
I am trying to piece together a clean, actionable PostgreSQL database with sanitized records from a ton of messy, noisy raw source tables.
Say we have contacts, companies, stores. Many contacts can work at a company. Multiple companies can be involved in a store (owner, maintenance, etc.) There are many-to-many (M:N) junctions between contacts_companies and companies_stores.
Table relationships:
contacts >-< contacts_companies >-< companies >-< companies_stores >-< stores

unique on: contacts:email, companies:name, stores:formatted_address

The multiple data sources have all sorts of overlaps between the above tables. Some have just store info, some have some contact and store info, etc. As I go through each data source, data will be cleansed and then upserted to the appropriate table.
Problem
The upsert operation is the problem. Inserting with an ON CONFLICT is fine for a specific table, but what about the junctions between?
To illustrate the problem, say we have a data source that has the following records:
{owner: "Alice A.", email: "A@Alice_stores.net", store_address:"1 main st"}

This record has

a contact {name: Alice A, email: A@Alice_stores.net},
a company {name: Alice Stores}
a store {address: 1 main st}

The database might already have a store for 1 main st, but not a contact/company for Alice.
If we individually upsert the contact, company and store, we won't know what proper IDs to place in the junction tables:

If INSERT, use new id for FK in junction.
If Update, use existing id for fk in junction.

Question
What is the best strategy for upserting multiple tables from various sources when there are M:N relationships across the board?
One strategy that occurred to me is to query all tables/junctions, hold them in memory and perform all conditionals/updates in memory and then have a more forceful upsert. But this can quickly get out of hand...

One way could be using returning  clause with cte to make all upserts in one statement. For illustration purposes I use 1 many-to-many relationship.

--- test tables with m2m
create table test1(test1_id bigserial not null primary key, uq_value1 text ,constraint unique_uq_val1 unique (uq_value1));

create table test2(test2_id bigserial not null primary key, uq_value2 text ,constraint unique_uq_val2 unique (uq_value2));

create table test1_test2 (test1_id bigint not null, test2_id bigint not null,
primary key (test1_id, test2_id),
constraint fk_test1  foreign key (test1_id) references test1(test1_id ),
constraint fk_test2  foreign key (test2_id) references test2(test2_id )
);
------------------------------------
-- insert value into the first table , or do a dummy update if it's already there
with
ins1 as
(insert  into test1(uq_value1) values('foo') on conflict on constraint unique_uq_val1 do update
    set  uq_value1=test1.uq_value1
returning test1_id
)
,
-- insert value into the second table , or do a dummy update if it's already there
ins2 as
(insert  into test2(uq_value2) values('bar') on conflict on constraint unique_uq_val2  do update
    set  uq_value2=test2.uq_value2
returning test2_id
)
,
-- select PK of inserted records (since there is exactly one record in each insert,
-- cross join is used
sel_1 as
(
select test1_id,test2_id
from ins1
cross join ins2
)
-- finally insert into link tables if such a record doesn't exist :
insert into test1_test2(test1_id, test2_id)
select * from sel_1  a
where not exists( select null from test1_test2 b where (b.test1_id, b.test2_id) = (a.test1_id, a.test2_id))


Random row selection but with distinct values for a subset of columns I have a table that looks like this :

Key1 | Key2 | Key3 | Data  |
-----|------|------|-------|
   1 |   1  |   1  |   a   |
   1 |   1  |   2  |   b   |
   1 |   2  |   1  |   c   |
   1 |   2  |   2  |   d   |
   2 |   1  |   1  |   e   |
   2 |   1  |   2  |   f   |
   2 |   2  |   1  |   g   |
   2 |   2  |   2  |   h   |
---------------------------|

Here is some code to create that example table :

CREATE TABLE Example(
Key1 int,
Key2 int,
Key3 int,
Data varchar(1));

INSERT INTO Example(Key1,Key2,Key3, Data)
VALUES (1,1,1,'a'),
(1, 1,2,'b'),
(1, 2,1,'c'),
(1, 2,2,'d'),
(2, 1,1,'e'),
(2, 1,2,'f'),
(2, 2,1,'g'),
(2, 2,2,'h');

I want to randomly select one whole row for each distinct pair of Key1 and Key2 values (one for the 1 / 1 pair, one for the 1 / 2 pair etc.) One possible result would be :

Key1 | Key2 | Key3 | Data  |
-----|------|------|-------|
   1 |   1  |   1  |   a   |
   1 |   2  |   1  |   c   |
   2 |   1  |   2  |   f   |
   2 |   2  |   1  |   g   |
---------------------------|

I can do this doing multiple queries, by first selecting all the Key1 and Key2 distinct pairs and then using those pairs to run an other query like

SELECT stuff FROM table WHERE Key1 = value1 AND Key2 = value2 ORDER BY RAND() LIMIT 1`

But this gross approach needs to ask as many queries as existing pairs of Key1/Key2 and is taking forever since my table is huge.

I've read things about using subqueries, partition, group by, but I struggle to implement them.
I'm new to SQL and only need to use it for a specific project and I don't really have the time to learn it properly, so I would be very thankful if you guys could give me a hand.

Thanks
JC

MariaDB 10.3 supports window functions, so something like this would work:

select * from (
  select
     t.*, -- all columns
     row_number() -- assign sequential numbers
     over ( -- within a "window"
        partition by k1, k2 -- determined by a unique combination of k1, k2
        order by rand() -- while ordering rows randomly within the partition
     ) as rn -- set the column alias
  from test t
) tt
where rn = 1 -- select only the first row from each "window" (partition)
order by k1, k2, k3

dbfiddle


Minimal grants for readonly single-table access on PostgreSQL Following is a list of commands that seems to work to create new user (login) and grant readonly access for one specified table on PostgreSQL.

Let's assume that these commands are executed on login with sufficient privileges (i.e. postgres login in default install).

CREATE ROLE user_name NOSUPERUSER NOCREATEDB
NOCREATEROLE NOINHERIT LOGIN PASSWORD 'pwd' VALID UNTIL 'infinity';

Now I want to grant select on table tab_abc on database db_xyz, so here it goes (database db_xyz is selected as current via PgAdmin or something like that):

grant select on tab_abc to user_name;

The question is: is this sufficient or there should be more grants (database connect, usage maybe)?

These commands seem to work for me, but my default installation has default security settings. What additional grants should I add if server admin configured stronger security?

It seems that I don't need to grant connect nor usage - are that implicite while granting select? Is it always so?

The question is: is this sufficient or there should be more grants
  (database connect, usage maybe)?

Security may be hardened from the default mainly on these points:

The pg_hba.conf file. It filters connections before any database privilege is considered. The default is relatively open for local connections, but it might be restricted to an explicit list of databases, usernames, and network origins.
The permissions for public, the pseudo-role that anyone has. A user can implicitly connect only if the connect privilege is granted to PUBLIC. See Created user can access all databases in PostgreSQL without any grants. A database might have all privileges revoked from public. See REVOKE in the doc for the major PostgreSQL version you're using.
The existence of the public schema. It's created with the database by default for convenience but it's not mandatory. Instead of removing public permissions from the public schema, it may make sense for DBAs to just drop the public schema when new users should have no implicit permission at all.

In the case when these defaults have been removed, to read one table a new user should be granted:

GRANT CONNECT ON DATABASE dbname TO username;

At the database level:

GRANT USAGE ON SCHEMA schemaname TO username;
GRANT SELECT ON schemaname.tablename TO username;


copy data from Previous non null rows and if first row is null copy next non null value I need to copy data from rows with non-null rows and if first row is null copy the next non-null value.
it is not a big table. There is no ID column and date column.
The datatype of this column is varchar.
I am using SQL Server 2012
This is how the data is in the table:

Masked

5574-12XX-XXXX-1338

6674-13XX-XXXX-0855

7784-14XX-XXXX-5237

Null

Null

Null

9984-11XX-XXXX-6196

3384-11XX-XXXX-0615

Null

Null

This is expected result.

Masked

5574-12XX-XXXX-1338

6674-13XX-XXXX-0855

7784-14XX-XXXX-5237

7784-14XX-XXXX-5237

7784-14XX-XXXX-5237

7784-14XX-XXXX-5237

9984-11XX-XXXX-6196

3384-11XX-XXXX-0615

3384-11XX-XXXX-0615

3384-11XX-XXXX-0615

online solutions work with simple data. but I am struggling with this data. I am very desperate for the solution. Thanks in advance.

For that you need an ORDER and another column that helps get the last number
the cte #help1 get you the order with Row# needed for the following tables
ChangeIndicator is need t make a group , s that you can get the corect masked value
In #help2 the groupy are established, so that you can get the first vaule of the group, which has the value, you want fr that you need also the order built in #help1.

CREATE TABLE table1
    ([Masked] varchar(19))
;

INSERT INTO table1
    ([Masked])
VALUES
    ('5574-12XX-XXXX-1338'),
    ('6674-13XX-XXXX-0855'),
    ('7784-14XX-XXXX-5237'),
    (NULL),
    (NULL),
    (NULL),
    ('9984-11XX-XXXX-6196'),
    ('3384-11XX-XXXX-0615'),
    (NULL),
    (NULL)
;
GO

WITH #help1 as (
select *
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Row#
, case when [Masked] is null then 0 else 1 end as ChangeIndicator
from [dbo].[table1]
)
, #help2 as (
select *, Sum(ChangeIndicator) over (ORDER BY Row#) RowGroup from #help1
)
select
case when [Masked] is not null then [Masked]
else first_value([Masked]) over (partition by RowGroup ORDER BY Row#)
end UnitsBalanceFillDown
from #help2

GO

| UnitsBalanceFillDown |
| :------------------- |
| 5574-12XX-XXXX-1338  |
| 6674-13XX-XXXX-0855  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 9984-11XX-XXXX-6196  |
| 3384-11XX-XXXX-0615  |
| 3384-11XX-XXXX-0615  |
| 3384-11XX-XXXX-0615  |

db<>fiddle here


How to create a multiple mysql replication My question is about to create a mysql replication server.

Here i have multiple slaves and i want replicate all respective database into multiple master server .

Que is that how could i set multiple master in a single server.

details: i have 4 clients  ,and I want to setup a master-server machine where I want database replication from my all 4 clients(slave), but here it require 4 master-server. and i want to do do it ina single hardware machine.

The question is a bit confusing ("into"? master; "master-server"?; "client" vs "slave"?; etc); I'll answer some possibly related things.

You can have multiple MySQL 'instances' (master or slave) on a single server (piece of hardware).  They must have separate data trees and my.cnf.  The implementation could be:

Separate Ports (not all 3306)
Separate VMs
Separate Docker instances

Those choices may involve separate or the same copy of the MySQL binary.

If by "client" you mean "web server", then you need to figure out how the "users" will hit the same or different web servers in order to get to different mysql instances.

Once you have multiple instances running, they can connect Master and Slave in the standard way -- namely that you need to know th host and port.  Ditto for Master-Master.

Are you talking about 4 "customers"?  Or 4 Slaves?  Either way, I have mostly covered both.

Setting up "Master-Master" (alias "multi-Master" replication) on a single server does not make much sense.  It does not give you much performance, HA, or any other reason to have M-M.


Best table schema to store daily hits for many items? In my Sql Server 2005 database, I'm considering two ways of creating a table to track daily views on 100,000 items:

Option 1) Each day would insert a new row for each item. So the table columns would look like this:

| ItemID | Date | #ofHits |

That would mean the table grows by 100,000 rows per day.

Option 2) Each row would contain an entire month's hits per item. So the table columns would look like this:

| ItemID | Year | Month | Day01Hits | Day02Hits | Day03Hits | ... | Day31Hits |

That would mean the table grows by 100,000 rows per month.

Option 3) Perhaps you have a better idea altogether?

Keep in mind performance, such as when doing a sum of all hits between two dates (i.e. total hits for July 5th-Aug12th). Also, I'd want to keep at least 1 year of historical data.

On a related note, we're simply parsing the IIS logs nightly to get the hit data.

In summary:

Option 1 would probably be the easiest way to query and sum data across a date range. The downside is it would add 100,000 rows every day (or a million rows everyday if we had a million items).

Option 2 would create much less rows, but probably wouldn't be efficient to query and sum data across a data range.

Is there a better way to store daily hits for many items?

Update:
Unless someone has a better design, I'm considering this:
Use option 1, but create a new table every month (this will prevent the table from growing too large). So my table names would contain the year and month, like "Stats2012_01", "Stats2012_02", etc.
Then, to query across dates (such as total hits from July 5th-Aug12th) it'd be:

SELECT ItemID, SUM(Hits)
FROM (
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_06 WHERE Day>=5 GROUP BY ItemID
        UNION ALL
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_07 GROUP BY ItemID
        UNION ALL
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_08 WHERE Day<=12 GROUP BY ItemID
) s
GROUP BY ItemID

Update 2:
I just discovered partitioning, thanks to the suggested answer from MZDBA. I'll continue to look into partitioning, which may lead to combining Option 1 with partitioning.

If performance of the site is a concern, then I would suggest going a third route.

Each time a page is viewed, insert a record into a table which is like:

  ItemId | TimeStamp

Then, each night (or whenever your site is least utilised), have a process that counts the occurences of ItemId and builds a value for a table such as you have in Option 1.

Doing it this way will be much more efficient, as an insert is ridiculously cheap compared to trying to update on a table that has 36,500,000 rows and avoids issues with locking.

I'd also strongly suggest you hold this data not on your main database, so you can query it as you like without a performace hit; and consider splitting the tables into weekly or monthly reports to cut down on the amount of data that needs to be scrolled through.


Delete more than 50K records in SQL table We have 500k records in a SQL table. Below is table structure
UserID varchar
FirstName varchar
LastName varchar
Address varchar

We need to delete 50K records in this table. We tried below approach
Approach 1: This has 50K DELETE statements which takes around 40 minutes
DELETE FROM [dbo].[Table] WHERE UserID = 'tom345'
DELETE FROM [dbo].[Table] WHERE UserID = 'john45'

and so on until 50K DELETE statements
Approach 2: This contains all 50K user id in IN statement but it takes more that 40 minutes
DELETE FROM [dbo].[Table] WHERE UserID IN ( 'tom345', 'john45',  .... )

Is there any other approach which takes less time comparatively?

Create a temp table, put in your users and use that in your delete statement:

CREATE TABLE delete_users (user_id ... primary key);
INSERT INTO delete_users (user_id) values (...),(...)...;
DELETE FROM  [dbo].[Table] x
WHERE EXISTS (
    SELECT 1 FROM delete_users y WHERE x.user_id = y.user_id
);
DROP TABLE delete_users;


How to count number of distinct days from one table using two dates (for range) from another table for each row? So I have one datatable with three columns: userid, mindate, maxdate.
I have another datatable that contains all the login logs for each user.

I need to make a query so that I can sum all the distinct login days from one datatable between the mindate and maxdate from another datatable for each user.

The code I can write until:

SELECT a.*, b.logins FROM 'usertable' as a
LEFT JOIN (SELECT user_id, COUNT(DISTINCT(loginday)) from 'logintable`
GROUP BY user_id) as b
on a.userid = b.user_id;

I just need help adding a 'where' statement that can query from b like:
WHERE loginday between a.mindate and a.maxdate.

SELECT a.*, COUNT(DISTINCT b.loginday)
FROM usertable a
JOIN logintable b
    ON b.loginday BETWEEN a.mindate AND a.maxdate
GROUP BY a.userid /* , a.mindate, a.maxdate, ... */


How can I find a table in the whole instance? I am trying to create a report which selects data from a table called costomersHR. I cannot find it as we have hundreds of databases in the instance.

How can I search for a table in the whole instance?

To do it through code...

1) If you can assume that this table is in the dbo schema.

SELECT name
FROM   sys.databases
WHERE  CASE
         WHEN state_desc = 'ONLINE'
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[costomersHR]', 'U')
       END IS NOT NULL

2) Otherwise you could loop through all databases (and query sys.tables in each) with something like this code for a more reliable and flexible sp_MSforeachdb


Get the size of list of documents in mongo db I want to get the size of the list of documents in mongodb.

db.getCollection('eventDetails').find({"dateTime" : { $gte : new ISODate("2018-05-01T12:00:00Z") },"dateTime" : { $lte : new ISODate("2018-05-31T23:59:59Z") }})

I want to get the size of the documents returned by the above mentioned query.

I tried the following query

var cursor = db.getCollection('eventDetails').find({"dateTime" : { $gte : new ISODate("2018-05-01T12:00:00Z") },"dateTime" : { $lte : new ISODate("2018-05-31T23:59:59Z") }})
var size = 0;
cursor.forEach(
    function(doc){
        size += Object.bsonsize(doc)
    }
);
print(size)

Each time execute the query, i get different results.
Let me know how to get the size of the documents in mongodb

As per MongoDB jira blog documentation Real document size in collection and also MongoDB documentation here the Object.bsonsize(<document>) prints the BSON size of a <document> in bytes.

To find the size of all documents in a collection:

Object.bsonsize(db.collection.find( {dbname:"collectionname"}))

For Example

> use test
switched to db test
> db.createCollection("StackExchange");
{ "ok" : 1 }

> show collections
StackExchange
>

> db.StackExchange.insertOne({"Name" : "user3138864", "Password" : "abc123"})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5b740353194beb82d3752206")
}
> db.StackExchange.findOne()
{
        "_id" : ObjectId("5b740353194beb82d3752206"),
        "Name" : "user3138864",
        "Password" : "abc123"
}
> db.StackExchange.find().pretty()
{
        "_id" : ObjectId("5b740353194beb82d3752206"),
        "Name" : "user3138864",
        "Password" : "abc123"
}
> Object.bsonsize(db.StackExchange.find( {m040:"StackExchange"}))
45734

For further your ref here and here


Is a composite index also good for queries on the first field? Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?

Additionally, I created an index on A, but Postgres still uses the composite index for queries on only A. If the previous answer is positive, I guess it doesn't really matter, but why does it select the composite index by default, if the single A index is available?

It certainly is. We discussed that in great detail under this related question:

Working of indexes in PostgreSQL

Space is allocated in multiples of MAXALIGN, which is typically 8 bytes on a 64-bit OS or (much less common) 4 bytes on a 32-bit OS. If you are not sure, check pg_controldata. It also depends on data types of indexed columns (some require alignment padding) and actual content.
An index on, say, two integer columns (4 bytes each) typically ends up to be exactly as big as an index on just one, where another 4 bytes are lost to alignment padding.
Update for Postgres 13: The new index deduplication changes this. Repeated index values can be compressed. An index on (a,b) is often less duplicative, so it gains less from deduplication.
If a is a unique column, deduplication doesn't change much. (There can still be duplicative entries due to the MVCC model, only one visible to any transaction at the same time, so adding b can still have a minor effect.)
Apart from that, there is really no downside for the query planner to use an index on (a,b) - compared to an index on just (a). And it is generally preferable for multiple queries to use the same index. The chance for it (or parts of it) to reside in (fast) cache grows when shared.
If you already maintain an index on (a,b), then it doesn't make sense to create another index on just (a) - unless it is substantially smaller. The same is not true for (b,a) vs. (a). Follow the link in the first line for more on that.
Coming from the opposite direction, when you need an additional index like that on (a,b), then consider dropping an existing index on just (a) - if possible. Often not possible as that's the index of a PK or UNIQUE constraint. Since Postgres 11 you might get away with just appending b to the constraint definition with the INCLUDE clause instead. Details in the manual.
Or create the new index on (b,a) instead to cover queries on just b additionally. For only equality conditions the order of index expressions in btree indexes does not matter. It does, though, when involving range conditions. See:

Multicolumn index and performance

There are potential downsides to including additional columns in an index, even if that only uses space otherwise lost to alignment padding:

Whenever the additional column is updated, the index now needs an update, too, which might add cost to write operations and create more index bloat.
HOT updates (Heap Only Tuple) on the table are not possible while any index column is involved.

More on HOT updates:

Redundant data in update statements

How to measure object sizes:

Measure the size of a PostgreSQL table row


Does increased Data Dictionary size affects performance As I understand, Data Dictionary

Is a collection of tables, part of SYS schema (if not the whole SYS
schema)
It is stored as 'regular' tables, so whatever is applied to
database tables, also applies to data dictionary (e.g. indexes,
statistics)
Its statistics should be updated
It is always cached

Can the the size and the complexity of oracle data dictionary affect the database performance?

When I add a new object on my database a new (or multiple) entries is/are added on data dictionary. A scenario I can think of has to do with partitioning. So if I have a table which have a partition and subpartitions, I will have an entry for the table, an entry for each partition and an entry of each partition subpartitions in data dictionary. Those entries are spawned among different 'tables'. Imagine know having tables with thousands of partitions and hundreds of subpartition. Again this number of records may not be something that a dbms can handle, but how it will affect its performance as it grows?

Note: I have read a lot of discussion on if and how partitioning affects performance in general. This is out of scope for this question. Partitioning is just an example I gave, to show how data dictionary volume can grow.

So a good starting point for this is Martin Widlake's post on the size of the 'data dictionary'. The term can be a bit woolly as there's a bunch of stuff that Oracle stores (such as executable code, job history etc) which may or may not be relevant.

https://mwidlake.wordpress.com/2009/08/03/why-is-my-system-tablespace-so-big/

He points out an example where he has 13 Gb in a data dictionary segment "C_OBJ#_INTCOL#". That is technically a cluster rather than a regular table, but it is really just a special table where the data is arranged so that related items tend to be stored in the same block on disk. That object is storing histogram information for table columns which tells you that, for a specific table/column, you might have 60% of values as US, 25% as Canada and 15% Mexico, or that you have twice as much data for October 2017 as you did for October 2016.

Whether that's a good use of space will depend on your situation. Histograms are basically used to determine the best access path. For example, given a query that has both a date and zip code paths available, which is going to involve less work.

Ultimately, it isn't about the size of a data dictionary, but a more precise question of whether storing additional information in there can help avoid mistakes. Even then, the downside is often the impact of gathering the additional information and keeping it up-to-date rather than the space required to store it.

Additional :
A cluster tends to take up a bit more space than a regular table as trying to keep related data together needs to allow more space for the data to grow rather than moving it around when it doesn't fit.


SQL Server crash with Reason 0x00000006 after a while on linux Environment

OS

Ubuntu 16.04 x86_64 (also happening on Windows)

DB

SQL Server 2017 with FullText Search module (mssql-server and mssql-server-fts packages)

Problem

The issue that I'm facing is that, after a while (I didn't find a pattern yet, but it happens from times to times... last time the database was in IDLE with just a DBeaver connection), the server crashes with a fatal error as follows:

mssql_1      | This program has encountered a fatal error and cannot continue running.
mssql_1      | The following diagnostic information is available:
mssql_1      |
mssql_1      |        Reason: 0x00000006
mssql_1      |        Status: 0xc0000005
mssql_1      |       Message: Last Chance
mssql_1      |       Address: 0x6a446912
mssql_1      |    Parameters: 0x802cef270
   Stacktrace: 000000006a4e84f6 000000006a43fb1e 000000006a43f9ca
mssql_1      |                000000006a518473 000000006a446912
      Process: 9 - sqlservr
mssql_1      |        Thread: 175 (application thread 0x1288)
mssql_1      |   Instance Id: 7eb1810a-0fbe-42f1-bb07-a0f20f7f3dcf
mssql_1      |      Crash Id: bec85ffc-9907-4f7b-a7bd-bd17e0c90561
mssql_1      |   Build stamp: 0fa9143612e88b9b2379174bbdc492bbfae2bec2f0278445d22e11dd124604ab
mssql_1      |
mssql_1      | Capturing core dump and information...
mssql_1      | No journal files were found.
mssql_1      | No journal files were found.
mssql_1      | Attempting to capture a dump with paldumper
mssql_1      | WARNING: Capture attempt failure detected
mssql_1      | Attempting to capture a filtered dump with paldumper
mssql_1      | WARNING: Attempt to capture dump failed.  Reference /var/opt/mssql/log/core.sqlservr.9.temp/log/paldumper-debug.log for details
mssql_1      | Attempting to capture a dump with gdb
mssql_1      | WARNING: Unable to capture crash dump with GDB. You may need to
mssql_1      | allow ptrace debugging, enable the CAP_SYS_PTRACE capability, or
mssql_1      | run as root.

this error reasons table (am I looking at the correct table?) points that:

Reason: 0x00000006 - The handle is invalid.

Message: Last Chance

I have no clue what is going on... so... Does anyone knows what is the real reason of the error here?

Any suggestions on how can I get more debug/runtime info in order to trace what is going on?

Thanks in advance!

[Update 1] It also happens on Windows:

This is also happening on SQL Server 2017 on Windows.

Partial Solution:

Downgrading to SQL Server 2008 solves the issue.

PS.:

Posting this as an answer just because, depending on the project needs, this may be enough.

But a solution to the SQL Server 2017 version is still needed though...


How to create Select query function in the Postgresql? I want create select function in postgres and get all data from table. I many example see and try but not perfact execute function. What perfact syntax for select query function create in postgres?

My function code.

    CREATE OR REPLACE FUNCTION dataget()
  RETURNS SETOF tbuserlogin AS
$BODY$BEGIN

RETURN QUERY  SELECT * FROM  tbuserlogin;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION dataget()
  OWNER TO postgres;

This function execute and show the datatable record  but this display record not proper column wise but only one function column display.
Like this

Thanks.

Function Execute like this And get my Answer

select * from functionname();


How to increase agent history rendering timeout in SSMS? We keep only 30 days of SQL agent job history but due to the frequent execution of jobs, SSMS will experience the following timeout error when trying to view history for a given job:
Execution Timeout Expired.
The timeout period elapsed prior to completion of the operation or the server is not responding.
(.Net SqlClient Data Provider)

Is there a way to increase this timeout in SQL Management Studio 18.7.1?

No unfortunately it is not possible. As @Newman mentioned, you can query the msdb.dbo.sp_help_jobhistory procedure to get the data back or you can adjust your jobs timeframe cutoff or total number of rows cutoff (whichever is more suitable to your business needs): https://docs.microsoft.com/en-us/sql/ssms/agent/resize-the-job-history-log?view=sql-server-ver15


How to run EXPLAIN ANALYZE and get results of the query? I have written a custom PostgreSQL client and I want to add a function that would automatically capture query execution time.

The current executionTime value in Slonik results records the total query execution time, including the roundtrip and parsing. In order to capture the actual query execution time, I plan to prefix every query with EXPLAIN ANALYZE, parse the explanation and capture the results of the query. However, I cannot find a way to run EXPLAIN ANALYZE and capture the actual query result (in addition to explain output).

How to run EXPLAIN ANALYZE and get results of the query?

If you do:

set auto_explain.log_min_duration TO 0;
set auto_explain.log_level TO notice ;
set auto_explain.log_analyze to on;

then the client will receive the execution analysis.  You just need to make it capture that message and distinguish them from other messages that might come along on the same channel.  If you need an example, 'psql' does capture and display these messages (but doesn't interpret them).


ORA-12541: TNS:no listener using EXPDP I need to export an Oracle 11g database to an Oracle 10g instance.  I have the command, which I believe is correct:

expdp user/pass@192.168.10.8 full=Y VERSION=10.2 directory=m_dump
    dumpfile=DB10G.dmp logfile=expdpDB10G.log

When I run this I always get this error:

UDE-12541: operation generated ORACLE error 12541
ORA-12541: TNS:no listener

I can connect to the server with SQLDeveloper, the 'no listener' error only occurs with this command.

You're not supplying a correct connection string. If you're using ezconnect, you need to pass the connection string in the format: //host:port/service-name

The expdp command-line should be:

expdp user/pass@//host:port/service-name full=Y VERSION=10.2 directory=m_dump dumpfile=DB10G.dmp logfile=expdpDB10G.log

So, something like:

expdp user/pass@//192.168.10.8:1521:DB10G full=Y VERSION=10.2 directory=m_dump dumpfile=DB10G.dmp logfile=expdpDB10G.log

If your local machine is not configured to use ezconnect, you need to specify the TNS name for the database, like so:

expdp user/pass@DB10G full=Y VERSION=10.2 directory=m_dump dumpfile=DB10G.dmp logfile=expdpDB10G.log

... and ensure there's a relevant TNS entry in your tnsnames.ora file.


How can I set Timeout by View, User or Role? For SQL 2008 R2, data views.

Looking for a timeout control using Microsoft SQL Server Management Studio (SSMS) that is NOT at the Server Level, and/or is NOT dependent on query timeout as set by application initiating the query.

I have been unable to find timeout controls by View, User or Role using SSMS.

There are server level timeouts (remote query timeout http://technet.microsoft.com/en-us/library/ms189040.aspx ), but as I understand it would also impact the main applications use of the database, which lives on a different server (main application gets to define its own limits).

I found DBPROP_COMMANDTIMEOUT http://msdn.microsoft.com/en-us/library/windows/desktop/ms712980(v=vs.85).aspx but not seeing any way to control it by View. And this https://serverfault.com/questions/242300/set-command-timeout-from-sql-server-2005-rather-than-through-the-code says "Command timeouts are always set by the client"

Considerations: These are connections for reporting from the production database of a major application, where the archived datasets (midnight last night) are not sufficiently current. We have a requirement to allow some access; we have a responsibility to not let that access adversely impact the application.

I have control over what is in the Data View Query, and any SQL settings that do not impact the main application.
I have control over the User Permissions that can access  the data view
I do not have control over external report application(s)
Snapshot isolation is not an option (application considerations)
The hints NOLOCK & READPAST are not viable solutions in SQL 2008 R2 https://stackoverflow.com/questions/15770402/sql-2008-nolock-vs-readpast-considerations-for-reporting-accuracy

Given the constraints and in addition to query optimizations and tuning.

Three stage approach, to limit potential conflicts when reporting from production server –

Use Resource Governor to limit report logins to Max 20% Min 0
Use an Operating Level Agreement (OLA) to require applications reporting from production databases to a 5 second timeout (DBPROP_COMMANDTIMEOUT)
Use a trace to track SQL queries against production database and confirm OLA compliance (filter by login)

Rationale:

Resource Governor, does not have any contra-indications when limiting occasional report queries against OLTP with constant connect
OLA is not an iron clad guaranty, but if the timeout is used, it limits the potential exposure to the main application (OLTP)
Trace permits follow-up and validation of solution.

References

Managing SQL Server Workloads with Resource Governor
http://msdn.microsoft.com/en-us/library/bb933866(v=sql.105).aspx

White paper  “Using the Resource Governor” from the Microsoft Download Center
http://msdn.microsoft.com/en-us/library/ee151608%28v=sql.100%29.aspx


When to disable MySQL Query Cache I have a high volume MariaDB 10 server thousands of databases of with over a million tables.  Currently we are only hitting around 20% query cache hits. We are already employing Memcache and other layers of caching. Is there a rule of thumb for when to disable query cache for better performance?

Do you have 4 or more cores/processors machine? Current MySQL query cache implementation does not scale well and there is a mutex contention problem on systems with more thread (the query might spend more time waiting to check the query cache than it would need to be executed fresh if the relevant data are already in other buffers which allow parallel access).

Do you have cacheable queries? If most queries do not repeat (they contain some random or time-dependent constants etc. - imagine a query getting "active articles" with 1 second precision - such query would be cached and maybe returned for one second if it were fired multiple times, but next second a new version is queried with a different timestamp so it does not match and is executed anew - you get your QC full of these queries, each only effective for 1 second). Then there are those containing rand(), now() and similar functions which cannot be cached at all.

Transactions may collide with QC - when one query inside a transaction invalidates some table in the query cache, later selects to that table inside the same transaction cannot use query cache at all (because of the repeatable-read consistency - other transactions might fill the QC with data which would be stale from the viewpoint of this transaction).

Thousands of databases and millions of tables sounds like some kind of hosting - you probably have no way to fix the queries or guarantee they will be cacheable at all? It seems only small percent is and giving it more memory will only slow down invalidations, not help with hit ratio.


SQL Server User Can't See All Logins I have a SQL Server 2016 instance and I've just noticed that some user logins aren't listed for some users. For example, if I execute this query:

select count(*) FROM sys.database_principals a where type = 'U'

I receive a count of 1348. But if I execute this query:

execute as user = 'some_user';
select count(*) FROM sys.database_principals a where type = 'U';
revert;

I receive a count of 92, meaning that there are 1256 users that can't be seen by the above user. If I grant the user db_accessadmin database role membership, then they're able to see all 1348 users returned from that above query. However I do not want to grant db_accessadmin to all users as that gives much more access than I want to grant.

I've compared the properties of one of the 92 users that does show up to the properties of the users that don't show up however I haven't noticed any property that looks to allow listing the user.

How may I go about allowing all users to be listed in the query to database_principals?

The minimum permission in database to make a user "see" all the users is VIEW DEFINITION.

This permission does not permit the user to alter anything, but it opens to user every definition of database objects: tables, procedures, etc.


One-to-"Zero or One" Relation I have the following requirements:

Every transaction has one of the following types; debit, credit, deposit, or withdraw.
Debit or credit transactions must have a linked invoice record and no bank account record.
Deposit or withdraw transactions must have a linked bank account record and no invoice record.

Currently my basic design is like this:

My current solutions are:

Have atwo nullable foreign keys to the invoice and bank account tables in the transaction table. However, I believe a nullable foreign key column isn't a good design.
Have a foreign key in the invoice and bank account tables that link to the transaction table. However it's seems harder to query and it models a one-to-many relation (not one-to-optional-one relation I want).

Which approach that I outlined is a better solution? Or is there another way to meet my constraints that is even better?

Let me suggest a third alternative: You can use triggers to check your business rules and fail the insert/update in a similar fashion a foreign key constraint would.

For your scenario I setup something like this:

CREATE TRIGGER Transaction_BEFORE_INSERT BEFORE INSERT ON Transaction_Table FOR EACH ROW
BEGIN

-- deposit, withdraw must have a bank account and no invoice
if NEW.`Type` in ('deposit','withdraw') then

   if not(select count(*) from Account_Table where id = NEW.id)
      or (select count(*) from Invoice_Table where id = NEW.id)
   then
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Error message';
   end if;
-- credit, debit must have an invoice and no bank account
elseif NEW.`Type` in ('credit','debit') then
   if not(select count(*) from Invoice_Table where id = NEW.id)
      or (select count(*) from Account_Table where id = NEW.id)
   then
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Another error message';
   end if;
end if;
END

I've used SQLSTATE 45000 which is a generic state for user-defined exceptions. You may customize that and also set an appropiate error message.

You would also need to create a BEFORE UPDATE trigger that does a similar check.

There will be a performance penalty for abusing triggers this way. Each insert/update will run 2 selects on the other table. You should optimize the selects to be as fast as possible. On high transaction databases it could be a bad solution. So if you choose taking this route please test the impact before running it on production.


Patching after Oracle 12c install? I am not able to find concrete information about patching after install. So I have downloaded Oracle 12cR2 software from Oracle and I have successfully installed it. But now I am wondering if I need to apply the latest CPU (database and ojvm update)?

edit: So as my question state then should I apply the latest CPU (database and ojvm update) as a the first order of business after fresh install?

Critical Patch Updates:

  Critical Patch Updates are collections of security fixes for Oracle products. They are available to customers with valid support contracts. They are released on the Tuesday closest to the 17th day of January, April, July and October.

So it depends on your security patch policy.

But there are other types of patches: PSU(Patch Set Updates) and BP(Patch Bundle)

Here is the description from Oracle Database - Overview of Database Patch Delivery Methods - 12.1.0.2 and older (Doc ID 1962125.1) from https://support.oracle.com

  Types of Proactive Patch (SPU / PSU / Bundle Patches)

  (...)

  Security Patch Update (SPU)

  a cumulative collection of security fixes released as part of Oracle's Critical Patch Update (CPU) program
  (...)

  Patch Set Update (PSU):

  a cumulative collection of fixes for proven high impact bugs encountered in the field
  includes the security fixes that are released as part of the CPU program
  guaranteed not to contain any changes to the optimizer or fixes which change application behaviour
  (...)

  Bundle Patch (BP)

  a cumulative collection of fixes to address bugs in a given feature, product, or configuration
  a superset of PSU
  (...)

So maybe you want to patch with last PSU or BP to get fixes for bugs and mybe sume enhancements

You can download these patches only if you have a valid CSI (Customer Support Identifier)


Is @@ROWCOUNT preserved by EXECUTE or not? The docs for @@ROWCOUNT say:

  EXECUTE statements preserve the previous @@ROWCOUNT.

I take this to mean that @@ROWCOUNT should have the same value after an EXECUTE statement that it had before.

However, considering the following:

CREATE TABLE #wat (id int);

INSERT #wat VALUES (1),(2),(3);

SELECT @@ROWCOUNT before;

EXECUTE('INSERT #wat VALUES (1),(2);');
SELECT @@ROWCOUNT after;

DROP TABLE #wat;

On SQL Server 2016 SP2 CU 8, before and after are different.

Do I misunderstand something here, or is this a documentation issue?

I propose a different test for you to understand that doc statement:

CREATE PROCEDURE TestRowCount
AS
BEGIN
    SELECT TOP (1) name FROM sys.databases;
    SELECT TOP (6) name FROM sys.databases;
END

After you create this procedure, run this:

EXECUTE TestRowCount;
SELECT @@ROWCOUNT AS Previous;

The Previous result is 6. With that result it's easier to understand that  EXECUTE statements preserve the previous @@ROWCOUNT means the previous statement that was called by the EXECUTE.

In your example the EXECUTE called an statement that affected 2 rows, so it returned 2 instead of 3 (as you expected).


SQL Server FCI online with incorrect file path I just encountered this strange issue with my SQL FCI yesterday.

Windows 2008 R2 and SQL 2012 - 2 nodes FCI

SQL server/ SQL agent resources are online and working as normal, but the system databases are now on a different drive.

They are now in drive X as opposed to drive K and drive K is empty. sp_helpfile and SQL startup parameter are showing the old drive path which is drive K. System db backup jobs failed because they were still pointing to the old path, which is expected. Now drive X has all the files that K used to have and K now is empty and has a new size.

I know SQL won't start if the file path is different because the master database files are not there. However, we never restarted SQL service and somehow the system database files were moved to the new drive without interrupting the SQL resources.

Has anyone encountered this behavior? My guess would be X is formerly K and the files didn't get moved, but how could SQL could still be on pointing to the old drive path K?Disk X is also under SQL server dependency.

Thanks.

Yes, it could happen if you add a new disk to the cluster. The new disk could use that same drive letter and force the another cluster disk to use the new letter (which happens to be the disk that holds the system databases). Since the dependency remains the same, changing the disk letter doesn't affect the SQL resources. As a result, SQL services would still be up but the startup parameters would still pointing to the old drive.


Why does Table Definitions Opened differ this much between cluster nodes? I have a Percona/Galera cluster and see this when i monitor the different cluster nodes. What does this really mean? And is this something erroneous that needs to be taken care of?

This is how one of the nodes looks like.

Thanks in advance.

All other nodes in the cluster looks like this

You are writing to the 'one' node?  That is, you are treating it as the only Master?  Galera does "Row Based Replication", so it probably does not need to open the table definition on the 'Slaves'?

Do you have the same values for SHOW VARIABLES LIKE 'table%'; on all nodes?


Database design, many rows or many columns? I am aware that this has been discussed before, but I've read conflicting information about this particular topic, so I'm hoping for a bit more decisive opinions from you.

I have multiple tables, which stand in a 1:1 relationship to a main table linked by a foreign key.

The largest of these tables, the parameter table, has about 1000 columns by now, each of the columns holding a single numerical parameter.

The table in question has a size of about 16 MB with 7000 rows right now.

After creating a normalized structure (ID, Key, Value) and inserting all the data into this new table, it turned into 3 million rows (after filtering out the columns that hadn't been used in the table with the many columns) and a size of 107 MB.

Now I'm wondering, if the effort is really worth the gain. Sure, it's a lot more scalable (I've had to add columns manually way too often lately to make sure it continued to work), but like this I have up to 1000 rows to sort through and I'm just not sure any more.

Some input would be appreciated. Thank you very much.

Edit: The majority of the operations will be 'SELECT', followed by UPDATE/INSERT ... ON DUPLICATE KEY UPDATE respectively (the later is for the relational approach)

MySQL has limits, such as 1017 columns in an InnoDB table (as of 5.6.9).

If you are not filtering or sorting on most of the columns, put them in a JSON string and put that string in a TEXT (or maybe MEDIUMTEXT) column.

A key-value store can lead to lots of JOINs.  The limit is 61.

I'm confused -- Do you have 1000 sets of 7000 parameters?  Or what?  If it is a single set of parameters, then a single key-value table makes some sense.


History of database restore completion times? I'm trying to estimate the restore time for a few 1TB+ databases on SQL 2012. Is it possible to look at completed restores from the last year and list the completion time ? It's an odd remote server on the other side of the world, all I know full restores used to run every beginning of a new month, for some reason, but can't figure out how to access the history.

This information is not tracked in MSDB, so there's no way to find it there (unless the restore operation is in its own step in a job, in which case you could get it from msdb.dbo.sysjobhistory).

If you can't rely on job history, and it was recent enough, you can approximate it from the default trace, with the assumption that the system does something to the database as soon as the restore is completed (which happens in all of my tests, though there may be scenarios where this doesn't happen, such as restoring with norecovery or restoring a read-only database - both scenarios I didn't test):

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT *, rn = ROW_NUMBER() OVER
  (PARTITION BY DatabaseName ORDER BY StartTime)
INTO #blat
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE DatabaseName IN (
  N'db1', N'db2' -- , ...
)
ORDER BY StartTime DESC;

SELECT b.DatabaseName, b.TextData,
  ApproximateRestoreTime = DATEDIFF(MILLISECOND, b.StartTime, b2.StartTime)
FROM #blat AS b
LEFT OUTER JOIN #blat AS b2
ON b.DatabaseName = b2.DatabaseName
AND b2.rn = b.rn + 1
WHERE b.EventClass = 115 AND b.EventSubClass = 2
ORDER BY b.StartTime DESC;

GO
DROP TABLE #blat;

(I tried the queries in this Stack Overflow answer, but they weren't quite as efficient - you're more than welcome to try those, too, to see if they better suit your needs.)

One scenario I did test where this strategy does not work is if all of the auto-stats options are disabled - since the activity that seems to happen immediately after restore is some auto-stats operations on system tables like syscolpars and sysschobjs. This doesn't happen - even for system tables - when auto-stats are disabled.

If you have timestamps from subsequent system events immediately following the restore, you will have a rough estimation of how long the restore took, but it won't necessarily be very precise. You don't get this type of more precise information, of course, which is what gets output to the messages tab if you run a restore interactively (and it also gets written to the error log, as long as trace flag 3226 is not enabled):

  RESTORE DATABASE successfully processed 291 pages in 0.021 seconds (108.258 MB/sec).

But you can still pull this info from the error log (again, as long as it happened recently enough that it hasn't been aged away). If you don't have trace flag 3226 enabled, you should see all of the messages like above in the current error log using the following query:

EXEC sp_readerrorlog 0, 1, N'Restore', N'MB/sec';

Now, you need to expand your search set once you've identified the timeframe you're after, since the message(s) above fail to include the database name. So you can trace down three sets of message from the log: when the database was marked as restoring, when the restore was complete, and the message that includes the pages and time (note that the latter might not line up with the delta between the first two if you are trying to get sub-second granularity).

CREATE TABLE #x(LogDate DATETIME, p SYSNAME, [Text] NVARCHAR(MAX));
INSERT #x EXEC sp_readerrorlog 0, 1, N'Restore', N'MB/sec';
-- repeat for 1, 2, 3, 4, 5, 6 if you want to capture more history:
--INSERT #x EXEC sp_readerrorlog 1, 1, N'Restore', N'MB/sec';
--INSERT #x EXEC sp_readerrorlog 2, 1, N'Restore', N'MB/sec';
--...

CREATE TABLE #y(LogDate DATETIME, p SYSNAME, [Text] NVARCHAR(MAX));
INSERT #y EXEC sp_readerrorlog 0, 1, N'is marked RESTORING';
-- again repeat for 1, 2, 3, 4, 5, 6 like above

CREATE TABLE #z(LogDate DATETIME, p SYSNAME, [Text] NVARCHAR(MAX));
INSERT #z EXEC sp_readerrorlog 0, 1, N'Restore is complete';
-- one more time, you may need to repeat if you want more history

SELECT LogDate, [Text]
FROM
(
  SELECT LogDate, [Text] FROM #x
  UNION ALL SELECT LogDate, [Text] FROM #y
  UNION ALL SELECT LogDate, [Text] FROM #z
) AS xyz
ORDER BY LogDate;

GO
DROP TABLE #x, #y, #z;

If you're only after a single database, or a database name pattern, you could add a second filter to the second and third queries, e.g.:

INSERT #y EXEC sp_readerrorlog 0, 1, N'is marked RESTORING', N'database_name';

If trace flag 3226 is enabled, you will only get the first two messages, and not the third.

If you want to troubleshoot slow restore times, first of all make sure Instant File Initialization is enabled (see here and here), and you can also actively monitor the process (in addition to the STATS option, which doesn't account for undo/redo, VLF work, or MSDB/history maintenance) by enabling trace flags 3604/3605, 3004, and 3014. See this article for more details.


JOIN on Microsoft Access derived table not working Can you please help me why my below SQL is not working in Access 2010?

SELECT
  Table2.Product,
  Table2.Color,
  SEQMAX,
  Table1.Attribute
FROM
(
  Table2

  LEFT JOIN
  (
    SELECT
      Table1.Product,
      MAX (Table1.Seq) as SEQMAX
    FROM Table1
    GROUP BY Table1.Product
  ) AS MAXTABLE
  ON  Table2.Product = MAXTABLE.Product
)
LEFT JOIN  Table1
  ON MAXTABLE.SEQMAX = Table1.Seq
  AND Table2.Product = Table1.Product ;

It is fine until I add the last line AND Table2.Product = Table1.Product both pieces of the AND worked fine separately, however I can't figure out why they don't work when combined.

I'm getting an error: JOIN expression not supported.

Perhaps rewrite the statement so Microsoft Access can more easily understand the requirements.

Something like:

SELECT
  T1.Product,
  T1.Color,
  T1.SEQMAX,
  Table1.Attribute
FROM (
    SELECT Table2.Product
        , Table2.Color
        , SEQMAX
    FROM Table2
        LEFT JOIN
        (
            SELECT
              Table1.Product,
              MAX (Table1.Seq) as SEQMAX
            FROM Table1
            GROUP BY Table1.Product
        ) AS MAXTABLE ON  Table2.Product = MAXTABLE.Product
    ) AS T1
    LEFT JOIN Table1
      ON T1.SEQMAX = Table1.Seq
        AND T1.Product = Table1.Product;


Is it efficient to use plpgsql functions only to validate identifiers and values? Will my queries be safe from SQL injections if I use plpgsql functions only to validate identifiers and values, instead of executing queries.

Here are the example functions:

CREATE OR REPLACE FUNCTION validate_identifier(identi TEXT)
  RETURNS TEXT AS
$func$
BEGIN
  RETURN quote_ident(identi);
END;
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION validate_value(val TEXT)
  RETURNS TEXT AS
$func$
BEGIN
  RETURN quote_literal(val);
END;
$func$ LANGUAGE plpgsql;

then i.e. simply use query:

SELECT * FROM some_table WHERE validate_identifier('user_input_identifier') = validate_value('user_input_value');

Is this query safe from SQLi, when user_input_identifier and user_input_value are replaced by actual user input, which might be an SQLi attempt?

Is this query safe from SQLi, when user_input_identifier and user_input_value are replaced by actual user input, which might be an SQLi attempt?

The whole sql injection vulnerability is about sanitized input, depending on how you're getting the inputs to the sanitizing function it may not even be safe to use them. In this case, I'll assume you're calling the SELECT statement from a client-library. In such a case, it's

no safer than if you just called the functions directly.
less efficient
still vulnerable to sql injection

The important thing to be safe is that you're binding the values to the input of the function with placeholders.

If your code looks like this,

k = sprintf("
  SELECT *
  FROM some_table
  WHERE validate_identifier(%s) = validate_value(%s)
");

You're not safe. If it looks like this,

k = sprintf("
  SELECT *
  FROM some_table
  WHERE quote_ident(%s) = quote_value(%s)
");

You're not safe. If it looks like this,

k = sprintf("
  SELECT *
  FROM some_table
  WHERE \"%s\" = '%s'
");

You're not safe. And, for all the same reasons. If it looks like this,

k = "
  SELECT *
  FROM some_table
  WHERE col = ?
";
k.exec($foo);

You're safe. If you don't know col at compile time, for instance if you want to safely do ? = ?, the library will either prefetch the escaped version by running something like this,

k = "SELECT quote_ident(?);"

Or it'll simply use a client-side version (which usually binds to libpq) thus saving a trip to the server.

So in answer to your question

  Is it efficient to use plpgsql functions only to validate identifiers and values?

It's certainly less efficient, and depending on how you're providing those functions their values, potentially not safe either.


Select rows, where 3 columns have the same values I have a structure like this:

 col0 col1 col2 col3
 1    1    1    1
 1    1    1    2
 1    1    1    3
 1    2    1    1
 1    2    3    1
 2    3    4    5
 2    3    4    9

I now want to select all rows, that match other rows on the first three columns, so the result of this query using the sample data would be:

 col0 col1 col2 col3
 1    1    1    1
 1    1    1    2
 1    1    1    3
 2    3    4    5
 2    3    4    9

How can I achieve this, I'm kinda stuck at the early beginning...

Thanks in advance!
Alex

Update: Clearified the question. Thanks Erwin.

SELECT
  col0
  ,col1
  ,col2
  ,col3
FROM
  foo
WHERE
  col0 = col1
  and col0 = col2;


Retrieve user password in Oracle How to obtain the password of a specified user in Oracle? What query to use to obtain that?

You cannot; Oracle does not store user passwords, only their hash values, which you can retrieve by select password from dba_users where username = 'WHATEVER'.


Remote connection to DB instance from fresh formatted system? I have 1 win7prof  fresh system. Now I want to connect to a DB instance remotely via sqlcmd/any other way. My req is  to connect and execute select query? is there any method/software needed to be installed in fresh win7prof system to access the instance.? kindly let me know what to do for access and use manipulation operations ?

I checked this out and the latest available standalone for SQL Server Management Studio is a 2008 version here.

Thanks to @AaronBertand, the process of obtaining the SQL Server Management Studio 2014 involves clicking the download button for the full SQL Server Express 2014 and then you will be offered a choice of only downloading the Studio (see here - with thanks again to Aaron Bertrand). Detailled instructions available here.

If you just want a command line client, then check out this post.


Trick question about FD's? X  Y  Z
x1 y1 z1
x1 y1 z1
x1 y1 z2
x2 y1 z1

The questions  are :

a. List all the non-trivial original functional dependencies that this relation instance preserves

b. Change a single value to eliminate all FDs that the above instance preserves.

To confirm that there is no typo from my part (could be from the professor):

I see several non trivial dependencies (9 if I count right!)

How to find them: Since, the relation has few attributes, we can examine each possible dependency separately, taking the respective projection. (The possible dependencies with just 3 attributes are not very few. They are 64 but see below for some ways to make that number smaller.)

Example: X -> Y. Does Y depend on X? Yes.

X  Y
x1 y1
x2 y1

Example: Y -> X. Does X depend on Y? No.

Y  X
y1 x1
y1 x2

Example: XY -> Z. Does Z depend on XY? No.

X  Y  Z
x1 y1 z1
x1 y1 z2
x2 y1 z1

Also we don't have to examine them all (64 possible dependencies).

First, we need to examine only the (37) non-trivial ones. The XY -> X for example is trivial and of course holds, We don't have to examine the (27) trivial dependencies at all.

Second, we can deduce several ones, in both directions:

Once we find that X -> Y holds, we can deduce that XZ -> Y holds, too.
Once we find that XY -> Z does not hold, we can deduce that X -> Z and Y -> Z don't either.
We can also use Armstrong's axions to make further deductions.

I won't go on further details but I think the exercise is good - and tricky.

If you are observant enough, you only need to find a single dependency and then you can prove the rest (8) that hold, using these deductions.

And only find that 2 more dependencies do not hold and then you could prove that the above 9 are the only ones and no other holds.


PostgreSQL 9.5.2 DELETE taking forever I have a seemingly simple delete:

DELETE FROM table WHERE added_at < '2017-08-14'

on a table with ~20k rows (w ~10k affected rows), but it has been running for 2000+ seconds.

SELECT * FROM table WHERE added_at < '2017-08-14'

takes a few ms.

I have a few other tables with FOREIGN KEYs pointing to this id and I had some previous records pointing to it, but they have already been deleted.

I have tried to:

check if the table is locked using the answer in  https://stackoverflow.com/a/26596931/2171758
terminate all backends
VACUUM

Add a index to dependents tables (for eficient deletes)

If you have

create table "table"(
  id integer primary key,
  added_at date
);

create table other(
  other_id integer primary key,
  table_id integer,
  foreign key (table_id) references "table" (id)
);

and you delete rows from "table" postgres must search the other table for each row that you deletes in the "table". If you add an index in the other table postgres can search in the index.

CREATE INDEX ON other (table_id);

Now you can eficiently delete rows in "table".


How do you protect against malicious PREPROCESSOR attacks in Oracle External Tables? I'm a new DBA and I recently found out about the option of External Tables in Oracle using the PREPROCESSOR feature ( http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf )
Unfortunately, this feature, which seems quite useful in our lounge seems very dangerous, as someone with access to the OS (or remotely....) could exploit it to cause the database to get compromised, or even worst - the whole OS.

I have restricted the access to this feature to the minimum, and revoked any additional privileges which might allow outside access to the os (extproc, java, etc)

However, there are still times when we must use this feature, and this is where I ask you guys 2 main questions:

How do you protect against malicious attacks using this wicked feature ?
Assuming something has failed in the security mechanisms, what sort of ways are there to detect that someone used this feature in an evil way? What sort of queries (or content of them) could be seen ?

Thanks (:

Oracle has had the capability of executing external code from SQL for a long time - EXTPROCs, data cartridges, and so on. You say

  someone with access to the OS (or remotely....) could exploit it to
  cause the database to get compromised

But what does this even mean? Someone with access to the OS as the oracle user can access your DBFs directly (they're just files on the disk), can attach directly to the SGA, can make a backup and copy it off, can snoop the network traffic (as root). In the case of a malicious developer, the can do whatever they want in PL/SQL and wrap it. I don't see how you are introducing a new vulnerability by using this feature. If it makes your job or your user's jobs easier, go for it.


transaction across multiple databases - what is the overhead? I run the following update, which is one single transaction across 2 databases.

Please note I have not committed the transaction.

--================================================================
--RUN THE UPDATE
--================================================================

BEGIN TRANSACTION T1_radhe

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT @@TRANCOUNT

BEGIN TRY

UPDATE DEStock.DBO.ItemStock
SET     QtyOnOrder = 0 ,
        DueDate = NULL
FROM DEStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
        ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating DEStock.DBO.ItemStock '

UPDATE USStock.DBO.ItemStock
SET     QtyOnOrder = 0 ,
        DueDate = NULL
FROM USStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
        ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating USStock.DBO.ItemStock '

--COMMIT TRANSACTION T1

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    WHILE @@TRANCOUNT > 0
         ROLLBACK TRANSACTION

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH

I get the following result from the update above:

  (196 row(s) affected) 196 updating DEStock.DBO.ItemStock

  (196 row(s) affected) 196 updating USStock.DBO.ItemStock

But when I look at the transaction logs of the databases in question I get the same view on their logs:

The spid in each database is the same, 6434, however the transaction_id seems to be different in each database.

How is it so?

Also, when running the following script AFTER I did the ROLLBACK on the updtes above I was still getting the same results as before the ROLLBACK.

I had to add the option recompile so that I could see the transaction was not there anymore.

use DEStock
go
select db_name(),@@trancount
go
Select SPID, [Master DBID], [Master XDESID], Operation, Context, [Transaction ID]
FROM sys.fn_dblog(NULL,NULL) where  [Transaction Name] = 'T1_radhe'
option (recompile)

What is the overhead of having transactions across multiple databases? I work with this quite often because I have many markets; each market has a set of databases.

Is there a way to make these transactions run quicker? I was thinking about DISTRIBUTED TRANSACTIONS.

The table used on the joint comes from here:
how to insert a list of varchar values, into a single column table?

This link explains about the function fn_dblog:
SQL Server fn_dblog() Function Details and Example

the spid in each database is the same, 6434, however the transaction_id seems to be different in each database.
how is it so?

What you have is wrong (may be you are looking at an older transaction which has the same name name).
From my test, I can see that the transaction IDs match (see below)

Using sp_WhoIsActive with @get_transaction_info = 1, I get below transaction detail
test_HV: 15 (1 kB),tempdb: 0 (0 kB),AdventureWorks_2005: 0 (0 kB)

You can easily check if the database is part of cross database transaction using
-- check if database is involved in Cross database transactions
if exists (
        select *
        from fn_dblog(null, null)
        where Operation = 'LOP_PREP_XACT'
            and [Master DBID] <> 0
        )
    print 'Based on the active part of the transaction log read, there is evidence that this database has participated in cross-database transactions.'
else
    print 'Based on the active part of the transaction log read, there is no evidence of this database having participated in cross-database transactions.'

---- check for Distributed Transaction Coordinator involvement (below)
if exists (
        select *
        from fn_dblog(null, null)
        where Operation = 'LOP_PREP_XACT'
            and [Master DBID] = 0
        )
    print 'Based on the active part of the transaction log read, there is evidence that this database has participated in distributed transactions.'
else
    print 'Based on the active part of the transaction log read, there is no evidence of this database having participated in distributed transactions.'

what is the overhead of having transactions across multiple databases?

A transaction itself does not have any cost associated (or to be more specific, it is very very negligible) To quote from my answer :

Transactions are needed to take the database from one consistent state into another consistent state. Transactions have no cost as there is no alternative to transactions.

Any modification to the database will generate log records, use memory and CPU along with tempdb space (depends on if you are doing a big sort and then updating or deleting records then spills to tempdb will be seen or if you are using snapshot isolation level, etc).

is there a way to make these transactions run quicker?

Yes, do your updates in batches or chunks. First select and update only those batches and make sure you have proper indexes on the tables involved, so that sql server will just seek the rows and update them.

I was thinking about DISTRIBUTED TRANSACTIONS.

I would avoid distributed transactions as they are not supported in database mirroring or AlwaysON (SQL Server 2016 has limited support) and if you are lucky - you will get an Orphan DTC Transaction SPID.
As a side note,

If you are using sys.fn_dblog for educational purpose or on non-PROD environments, you should be good. I would avoid using it in PROD environments!
You are using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ. This means that you will have phantom rows (which can lead to unexpected results) - so just making sure you are aware of what you are using.

Unlike a read committed scan, a repeatable read scan retains locks on every row it touches until the end of the transaction.  Even rows that do not qualify for the query result remain locked.  These locks ensure that the rows touched by the query cannot be updated or deleted by a concurrent session until the current transaction completes (whether it is committed or rolled back).  These locks do not protect rows that have not yet been scanned from updates or deletes and do not prevent the insertion of new rows amid the rows that are already locked.


Are there any third party tools that can read and/or analyze SQL Profiler trace (.trc) files I have a .trc file from a trace that a DBA did on one of my databases. I don't have the SQL profiler tool installed on my PC so I can't view the contents and analyze the trace log. How do I read this file without SQL profiler installed on my PC?

I would probably import the trace to a table, for example:

USE MyDB
GO
SELECT * INTO MyTraceTable FROM ::fn_trace_gettable('C:\Path\To\My\Trace\MyTrace.trc',
DEFAULT)

If you don't have permissions to create a table, consider using a temporary table or installing SQL Server Express locally and importing the trace there.

Kin's answer (entered concurrently with mine) might be a better option, but I haven't tried ClearTrace yet.


Performance of a linked server between two instances of SQL Server on the same virtual machine I would like to set up log shipping between SQL Server 2014 (main) and SQL Server 2016 (secondary). The thing is I want to set the secondary DB in STANDBY. Unfortunalely this setup is not supported by MS, as the secondary is a higher version (2016) than primary (2014). To overcome that issue I have a plan to install  a second instance of SQL Server on secondary in version 2014. Then in order to be able to access it I will have to create a linked server between those two instances  on the same machine.

And now the main question. Provided that both instances reside on the same VM, should I expect a significant performance drop during queries between them (large joins)? Is there a way to overcome this?

edit:

Thank you for your feedback. A bit of clarification from my side:

Secondary (2016) will be used solely for reporting purposes. There is no need to support a fallback scenario. The database is quite large so daily restores to secondary are becoming a bit cumbersome.

Having the secondary in a STANDBY state is a must, as I want the database to be accessible for reporting. As far as I'm concerned it is not possible.

https://www.brentozar.com/archive/2015/01/reporting-log-shipping-secondary-standby-mode/

should I expect a significant performance drop during queries between
  them (large joins)? Is there a way to overcome this?

This answer addresses directly your question about using a linked server to run queries through.  And the answer is YES, you can expect a performance drop, especially with large joins.

Usually when SQL processes a query, it can use statistics, indexes and other things to define an optimal execution plan, using later joins and where statements to eliminate required rows before they are even read (or at least making a good estimate for how much memory is needed).  But the linked server is a black box for those things.  With queries of even minimal complexity, you will quickly see a drop in performance.  This is due to SQL bringing the entire table contents over the wire into a worktable and then performing the join operation(s).

So, a few thoughts for you:

You can do log shipping of Database A on Server A to Database B on Server A (the same server, but the database is a different name).  You will have to roll your own, but it's just RESTORE LOG WITH STANDBY.  You won't be able to separate cpu/memory for each instance, but you would keep reporting transactions from interfering with your OLTP transactions.  If these are going to live on the same host (just as different instances) then this is no great loss.

What joins are you doing through the linked server?  If all the data you need is available in the reporting database, then you could use OPENQUERY() to make sure that the linked server handles all of the query.  Otherwise, you could use OPENQUERY to pull minimal data from the remote server to the local (temp table) for later joining/work.


Is there a product to auto-build indexes based on slow queries? I'm looking to either find, purchase, or write a script to identify slow queries, identify potential missing indexes, and when needed create the appropriate index within a reasonable amount of time of identifying the potential index. Anyone know of a product or capability that does it?

Thanks

No, there is no product that does what you describe automatically. At least not for MySQL.

There are tools that identify slow queries, and even run EXPLAIN automatically, for example pt-query-digest. But deciding on the right indexes is up to you. There are too many exception cases where you might choose to create a different index, or avoid creating the recommended index, regardless of the advice of the tool.

You might like to read my presentation, How to Design Indexes, Really. It's not that hard to learn to do it.


'Initorcl' file missing I recently installed Oracle 12c on my system on windows10. On sqlplus " /as sysdba"
I get connected to an idle instance.
So I executed startup. Now I get error

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
'C:\ORACLE12C\12C\PRODUCT\12.2.0\DBHOME_1\DATABASE\INITORCL.ORA'

I checked the path, the Initorcl file does not exist at the path.

What is the next step to get this configured?

You need to create new parameter file,search for file alert_orcl.log ,open that file copy all parameters(only lines with "=" signs) after the line "System parameters with non-default values:" to a new file orcl.ora in any location e.g C:\orcl.ora

Next start the database

  startup pfile='C:\orcl.ora'; -- if successful go to next step
  create spfile from memory;    --create spfile
  create pfile='C:\oracle12c\initORCL.ora' from memory --backup pfile

Always use rman to backup spfile and controlfiles.

Note:-You'll find alert_orcl.log in ORACLE_BASE\diag\rdbms\orcl\orcl\trace(start from diag folder in oracle base directory)


How to check the program/applicationname that triggered function? There are two applications that used the same function (trigger) in postgresql. I have to ignore notification by applicatonname. How do I identify the current program/application that called the function?

You can use select current_setting('application_name'); but I've seen instances of client program (not written by me) not giving application name after a connection reset. You should also see comment from @a_horse_with_no_name above.