texts:
Background

I'm designing a table which will serve as a repository of payment information to be used for research purposes. This table will have around 4.5 million records initially and will grow at a rate of around 400k annually. The PaymentReference column would be the only unique column in the table on an on going basis meaning after the initial historical load of data which would contain nulls.

Table example:

    CREATE TABLE [dbo].[Bil_ReturnsRepository](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [PolicyNumber] [nvarchar](32) NOT NULL,
    [PaymentReference] [nvarchar](32) NULL,
    [AccountingDate] [datetime] NOT NULL,
    [ReturnDate] [datetime] NOT NULL,
    [PaymentAmount] [decimal](19, 2) NOT NULL,
    [RegionCode] [nvarchar](5) NULL,
    [PolicyStateCode] [nvarchar] (3) NULL,
    [CompanyCode] [nvarchar](4) NULL,
    [LineOfBusiness] [nvarchar](4) NULL,
    [ReturnReasonCode] [nvarchar](3) NULL,
    [PaymentType] [nvarchar](20) NULL,
    [PaymentPlan] [nvarchar](20) NULL,
    [EntryUserId] [nvarchar](20) NULL,
    [PolicyTermId] [bigint] NULL,
    [PaymentDate] [datetime] NULL,
    [DateOfFirstReturn] [datetime] NULL,
    [BankId] [nvarchar](9) NULL,
    [EquityDateOfPolicy] [datetime] NULL,
    [OrginalStartDateOfPolicy] [datetime] NULL,
    [PaymentSource] [nvarchar](20) NULL,
    [DBTransactionTimeStamp] DATETIME NOT NULL DEFAULT(GETDATE()))

Access to Data

Business users will enter search criteria on a front end UI screen. The available options to use as search criteria will be every column located on the table except ID and DBTransactionTimeStamp. Every column of the table will have an associated text box that the user will have the ability to enter the necessary value for that column to use for the search. The only field that is not optional for the search is the ReturnDate field. For example, a search could be performed with just ReturnDate and PolicyNumber. ReturnDate, PaymentReference. ReturnDate, PolicyNumber, PaymentReference, AccountingDate. ReturnDate only. etc....

A stored procedure will be created to actually perform the read of the table and will use the parameters passed by the front end UI when querying the table.

Question

Since this is a new design I'm not currently experiencing any performance issues but I would like to be proactive and prevent any of those possible issues from occurring. I would imagine that with so many records on the table and the ability to search on all fields there would be table space scans that would occur. That would result in a slow response time for the output to be returned to the front end UI.  So with that being said would it be beneficial to add any indexes and or keys to this table ? If so what Keys ? What indexes ?


texts: horizontal partitioning or multiple tables?

texts:
I'm developing an application and need to propose a database schema.

We plan for a MySQL database with millions of records which should be distributed over multiple machines for performance and availability reasons.

The schema contains an element Category and we will have from 10 to 100 different categories.  The number of categories will vary during the system's lifetime.  Each query will be relevant for a single category only, and must be ACID-compliant.  This means several queries for different categories can (and should!) run in parallel.

I see the following options:

Define a single table and partition it horizontally.
Use separate tables for each category.

Since I don't have experience in administering database servers and we only have a small amount of categories, I'm wondering which of the two options is easier to administer and what are their other advantages and disadvantages.


texts: Understanding Galera Status wsrep_local_recv_queue_avg

texts:
Does wsrep_local_recv_queue_avg really represent the average size of the wsrep_local_recv_queue?

For the MariaDB version below I have problems to understand the status value of it, because the wsrep_local_recv_queue is 0 all the time, but wsrep_local_recv_queue_avg is sometimes very high (above 500), even though the node has been restarted 600 seconds ago and 580 of those 600 seconds the queue size is 0 and only 10-15 seconds after starting the mysql node the receive queue can be as high as 10000, but is being saved away fastly.

The wsrep_local_recv_queue_avg value is even also only decreasing in fractions when checking the value regularly.

So please enlighten me, what this value actually represents if not the average receive queue size for the node's uptime.

~ $ dpkg -l | grep mariadb
ii  libmariadbclient18                5.5.45+maria-1~wheezy         amd64        MariaDB database client library
ii  mariadb-client-5.5                5.5.45+maria-1~wheezy         amd64        MariaDB database client binaries
ii  mariadb-client-core-5.5           5.5.45+maria-1~wheezy         amd64        MariaDB database core client binaries
ii  mariadb-common                    5.5.45+maria-1~wheezy         all          MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
ii  mariadb-galera-server             5.5.45+maria-1~wheezy         all          MariaDB database server with Galera cluster (metapackage depending on the latest version)
ii  mariadb-galera-server-5.5         5.5.45+maria-1~wheezy         amd64        MariaDB database server with Galera cluster binaries


texts: Why is my PostgreSQL ORDER BY case-insensitive?

texts:
I have Postgres 9.4.4 running on Debian and I get the following ORDER BY behavior:

veure_test=# show LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8
(1 row)

veure_test=# SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') ORDER BY 1;
 regexp_split_to_table
-----------------------
 a
 A
 b
 c
 Capacitor
 CD
 d
 D
(8 rows)

And uname -a:

Linux ---- 3.2.0-4-amd64 #1 SMP Debian 3.2.65-1 x86_64 GNU/Linux

However, on my iMac, with Postgres 9.3.4, I get the following:

veure_test=# show LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8
(1 row)

veure_test=# SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') ORDER BY 1;
 regexp_split_to_table
-----------------------
 A
 CD
 Capacitor
 D
 a
 b
 c
 d
(8 rows)

And the uname -a:

Darwin ---- 14.4.0 Darwin Kernel Version 14.4.0: Thu May 28 11:35:04 PDT 2015; root:xnu-2782.30.5~1/RELEASE_X86_64 x86_64

I'm mystified by why the Debian version appears to be case-insensitive and the OS X version is not. What am I missing, or what other information do I need to provide?

Update: On my Mac, the pg_collation table shows I have an en_US.UTF-8 collation, but on Debian, I have an en_US.utf8 collation. Thus, on my Mac:

veure_test=# with foo as (
SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') as bar
   )
SELECT bar FROM foo
ORDER BY bar collate "en_US.UTF-8";
    bar
-----------
 A
 CD
 Capacitor
 D
 a
 b
 c
 d
(8 rows)

And on Debian:

veure_test=# with foo as (
SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') as bar
   )
SELECT bar FROM foo
ORDER BY bar collate "en_US.utf8";
    bar
-----------
 a
 A
 b
 c
 Capacitor
 CD
 d
 D
(8 rows)

So en_US.UTF-8 and en_US.utf8 have different sort orders?


texts:
I have a table in a MySQL database that stores survey results.

CREATE TABLE survey_responses (
  response_id INTEGER UNSIGNED AUTO_INCREMENT,
  survey_id INTEGER UNSIGNED NOT NULL,
  question_id INTEGER UNSIGNED NOT NULL,
=>response WELL_IT_DEPENDS_REALLY,
  FOREIGN KEY (survey_id) REFERENCES surveys (survey_id),
  FOREIGN KEY (question_id) REFERENCES questions (question_id)
) ENGINE=InnoDB;

If the question is free form, a BLOB may be best for the response. If the question is "select the best answer", an INT pointing to that answer's record would be best. I'm sure I could store this in a document database and determine the type during runtime. But I would hate to maintain two databases just for this one column.

I've thought about having response_blob, response_int, response_char, and having them default to NULL. Then I would read the appropriate column at runtime. Is that acceptable or just a silly idea?

Is there a standard way this situation is handled?


texts:
I'm trying to achieve a simple architecture:

An organization has many users
A user belongs to many organizations
A user can connect
A user can join and leave an organization
A user can read-only tables granted to organizations

First, securing permissions, as recommended in many places (https://ankane.org/postgres-users, https://marcyes.com/2016/0922-messing-with-postgresql-users-and-permissions/)

REVOKE ALL ON DATABASE stamped_development FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

Then, creating an organizations role with read permissions:

CREATE ROLE organizations;
GRANT USAGE ON SCHEMA public TO organizations;
GRANT SELECT ON ALL TABLES in SCHEMA public TO organizations;
ALTER DEFAULT PRIVILEGES FOR ROLE organizations IN SCHEMA public
  GRANT SELECT ON TABLES TO organizations;

Then, creating a users role with connect permissions:

CREATE ROLE users WITH LOGIN;
GRANT CONNECT ON DATABASE stamped_development TO users;

Create an organization and a user:

CREATE ROLE org_123 IN ROLE organizations;
CREATE ROLE user_123 WITH ENCRYPTED PASSWORD 'secret' IN ROLE users;

Joining and leaving organizations as a user:

GRANT org_123 TO user_123;
REVOKE org_123 FROM user_123;

\du+
                                               List of roles
   Role name   |                         Attributes                         |    Member of    | Description
---------------+------------------------------------------------------------+-----------------+-------------
 org_123       | Cannot login                                               | {organizations} |
 organizations | Cannot login                                               | {}              |
 user_123      | Cannot login                                               | {users,org_123} |
 users         |                                                            | {}              |

Problems:

user_123 doesn't seem to inherit LOGIN from users
Even when granting LOGIN to user_123 it seems like user_123 can't SELECT tables in the public schema

Any thoughts on what's missing?

Edited to reflect fixes suggested in the answers


texts: Does this plan work as a Failover strategy-SQL Server 2016 Standard Edition

texts:
We have two SQL Server 2016 Standard edition (standard edition due to budget issues) configured one as primary sql server and another one as fail-over server.
I have implemented Transactional replication from primary to secondary server.

To do some maintenance and patching in primary,we have decided to do failover to the other server.But since SQL Server is standard edition,we have no other option but to break replication.I know that Replication is not a Failover solution.
Anyways after patching/maintenance is done we will switch the application back to primary server.But to bring the records which went to failovr server i am thinking to implement Log shipping.So i will restore a latest backup from fail-over to primary and then implement log shipping to synch with the data.

I don't get huge maintenance window in between,that is why i have to do this way.
Do you think this plan will work ?


texts:
we want to setup the mysql ibdata files in such a way that after every 100M , new data is added to another file specified in innodb_data_file_path

i.e. innodb_data_file_path=ibdata1:100M;ibdata2:100M:ibdata3:100M etc

and mysql uses it first uses ibdata1, when its filled uses ibdata2 etc.

Is it possible? if so how to add configuration?

EDIT:
We have a huge database of many gigs and we ran out of space recently. We  could not upgrade the disk, so we are thinking of having the new data automatically go to new files and have flexibility of moving those files to newly added physical disks.

we do not have innodb_file_per_table ON, but we have many dynamically created tables per second. We think that all that diskIO and open files might create some corruption.


texts: PostgreSQL How to get friend requests in table by seeing if there is a duplicate of the reverse of a column?

texts:
I've a table,

CREATE TABLE friends (
    friend1 serial references users(id),
    friend2 serial references users(id),
    unique (friend1, friend2)
);

Friends are in no particular order, but I need to have a query that returns friends where it is a one-way friendship. For example, if the data is:

friend1 | friend2
-----------------
1       | 2
2       | 1
3       | 2

the query for friend id=2 should return:

friend1 | friend2
-----------------
3       | 2

because there is not another row where friend1=2 and friend2=3.


texts:
I am trying to use SSIS to take a backup of Replication scripts. I am using Script task and using ReplicationServer class under Microsoft.SqlServer.Replication.

The problem is, the package is failing at ReplicationServer.Script(ScriptOptions.Creation) statement with exception "You do not have sufficient permission to run this command."

Does anyone know what kind of permissions are required?


texts:
For my web application with a postgresql backend I'm now looking into schema's. As I switched from MySQL to Postgresql(because of (amongst others) the proper geospatial extension) I was not aware of the concept of schemas.

In my application every customer now has his own database with tables and off course content. The user management and authentication is done with an 'oversight' database. This makes it possible for one user to have access to several DB's (several customers, ideally for administration purposes).
So: customer1, customer2,....., oversight

The structure of DB1 is not necessarily the same as DB2. In fact it even can be a complete different 'application'.

For now there is absolutely no interaction between the two databases; but in the future I want to collect statistics (read-only) from the several databases.

Does it have more advantages to use schema's instead of separate databases with properly setup roles?
Or is it better to use the different-database-setups.


texts: Building an bridge table from existing one with the use of the SELECT INTO

texts:
I have one big source table in which attributes are mixed with measures together. I want to make a bridge table from them to allow analysis of the data.

Problem (in my case N is actually 22, id_column reference same table)

|id | id_column1 | id_column2 | id_columnN |  stuff |
+---+------------+------------+------------+--------|
| 1 |     2017   |    2024    |    1452    |   red  |
| 2 |     4125   |    NULL    |    5234    |   blue |
| 3 |     NULL   |    5234    |    7234    |   pink |

I want to build only one bridge table from the above, but I don't want to extract all columns in that manner, my goal is to have someting like:

|idT1| idT2 |
+----+------+
| 1  | 2017 |
| 1  | 2024 |
| 1  | 1452 |
| 2  | 4125 |
| 2  | NULL |
| 2  | 5234 |

and so on. I want to build such bridge table only from the data that I have in the problem above, and then reference idT1 and idT2 with adequate tables.

My ideas of:
- using pivot table did fail since I need to map all of the id values.
- i was thinking about using some cursor to map all of the result into one variable bounded with the id value, but also it did not work for me.

Thanks in advance for your time and help!


texts: Need help to design model with multiple rights

texts:
For a website, I have multiple elements that need to be moderated by many different users.
Until now, I have always used Role-Based Access Control (RBAC) model but I want a more flexible model. I would like to be able to assign some user with some specific rights on particular ressources and after be able to remove/add rights as I want.

For example an element would be a blog with articles. There is the author of the articles who has full rights on his article but I would like to be able to add another user for that article with update right on it. And that for all elements in my website.

Here is what I have:

Elements are 'page', 'article', 'anotherElement'. 'Ownership' is the table where each user has what he can do. 'Right' is the table with specific right (read, write, ...). 'Category' is not important, only to subclass my elements.

Is here a better way to do this? The problem will be the size of table 'ownership' after a certain time.


texts: How can I fix SQL Server 2008 orphaned columns RE: msg 3853

texts:
I administer a SQL Server, but do not administer the actual application that runs on it. It is SQL 2008 r2 (10.50.1600.1) Recently when the company that develops and manages the application was deploying some updates they came to me with reports of corruption in the database and some output from DBCC CHECKCATALOG
To wit:

Msg 211, Level 23, State 192, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should
be discarded.

Followed by a series of:

Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=1732409441) of row (object_id=1732409441,column_id=1)  in
sys.columns does not have a matching row (object_id=1732409441) in sys.objects.

and one:

Msg 3853, Level 16, State 1, Line 1
Attribute (parent_object_id=1732409441) of row (object_id=323388817) in
sys.objects does not have a matching row (object_id=1732409441) in sys.objects.

I verified their results and figured out that these messages appear to be orphaned columns and an orphaned index from an old version of a table that has since changed in structure. The index doesn't appear in sys.indexes. The new table is in the database, and everything from an operations standpoint appears to be working fine (data is flowing, nothing seems out of place). But the vendor cannot complete their update due to this corruption.

We have done some digging on this and what we have found suggests that in SQL 2008 r2 and later you can no longer make direct edits to the sys.objects and sys.columns tables which I think would fix this issue as it appears to be just some orphaned columns and an orphaned index.

So my questions are two:

How can I clean this mess up? Is there a way to clear out the orphaned index and these orphaned columns or will I need to rebuild this database and migrate the data?
Any clue on how this would happen? (less important) The vendor recently did an update that made the structural changes to this table that are reflected in the orphaned columns versus the existant columns, but did not report any error to us at the time. I would like to put them on the hook for the cost of the fixes if they can be shown to be at fault.


texts: Incremental backup between now and backup taken one week ago

texts:
We do a full backup of our SQL Server 2012 database every night.

A new developer needs an incremental (or differential) backup of a backup we took one week ago. The reason is that for them, importing the data takes several days, as their destination is not SQL server and has to go through several transformations.

There have been 7 full backups since the last backup they imported.

How do I get them the incremental backup that they need? Do I use differential or log shipping and how do I account for that fact that the data they have is one week old with several full backups in between?


texts: Does union all have an enforced ordering

texts:
I know that a union does a de-duplication so can jumble the results, but I can't find a definitive answer for union all

Example query

select id from
(
 select id from table where id = 1
 union all
 select id from table where id = 2
) as t
limit 1

If the table has 1, 2 in it, will 1 always be returned for the query or can 2 appear?

Engine is currently MyIASM but I'd also appreciate an answer for InnoDB.


texts: Combining multiple queries(select, update) (mysql)

texts:
I am making a project to challange myself doing complex script(despite my skills are not that high). All the things were good untill now.
I faced an issue(lack of knowedge) for doing the following:

Table users:

users http://cloud.screenpresso.com/5rExb/2016-02-15_18h09_05.png

Table rounds:

rounds http://cloud.screenpresso.com/Gmxsg/2016-02-15_18h09_52.png

Table bets:

bets http://cloud.screenpresso.com/bWVZe/2016-02-15_18h10_26.png

The thing is that i want to update everybody's(on the people who put bet on the winning round) balance in 1 query(I can do it by fetching everyone's bet insert it in to PHP, and run new update query) but i think this will not be the right thing to do.
Users have 3 ranges to bet as you can see in the bets table.:

0-0
1-7
7-14

Can you please assist me with explanation/answer what is the best practice to do this?


texts: How to design the database schema for this activity planner

texts:
Hi I am new to database design and would like help designing the schema for activity planner for patients with depression. (this is a little project I am working on to build my skills).

What I am thinking right now is to have a table for user information, table for what the user plans to do the next day (the first coloumn), and another table for the patient to fill out at the end of the day (the rows, but with additional time input such as "Early morning" or "Night"

But with this current schema, when the patient inputs their activities at the end of the day, the app will need to make 6 calls (bc of the 6 different times periods such as afternoon, evening, etc)

Any help would he appreciated!

-- ************************************** "user"

CREATE TABLE "user"
(
 "id"        uuid NOT NULL,
 "email"     varchar(50) NOT NULL,
 "lastName"  varchar(50) NOT NULL,
 "firstName" varchar(50) NOT NULL,
 CONSTRAINT "PK_user" PRIMARY KEY ( "id" ),
);

-- ************************************** "Plan"
CREATE TABLE "Plan"
(
 "id"             uuid NOT NULL,
 "date"           date NOT NULL,
 "earlyMorning"   text NOT NULL,
 "lateMorning"    text NOT NULL,
 "earlyAfternoon" text NOT NULL,
 "evening"        text NOT NULL,
 "night"          text NOT NULL,
 CONSTRAINT "PK_Plan" PRIMARY KEY ( "id", "date" )
);

-- ************************************** "Actual"

CREATE TABLE "Actual"
(
 "id"          uuid NOT NULL,
 "date"        date NOT NULL,
 "time"        text NOT NULL,
 "actuallyDid" text NOT NULL,
 "mood"        smallint NOT NULL,
 "triggers"    text NOT NULL,
 CONSTRAINT "PK_Actual" PRIMARY KEY ( "id", "date" )
);

(Picture from webmd)


texts: Stats Maintenance after Restoring to Newer SQL Server

texts:
What's the ideal (or necessary) sequence of call when restoring a SQL Server 2012 backup onto a SQL Server 2014 instance and updating the compatibility level?

-- Update Statistics for all tables
EXEC sp_MSforEachtable 'Update Statistics ? with FULLSCAN';

-- Update page and row counts in catalogviews
DBCC UPDATEUSAGE('MY_DATABASE') ;

-- Change DB Compatibility level
EXEC sp_dbcmptlevel 'MY_DATABASE', '120';

I'm not sure if the db compatibility level effects the stats or simple the engine on the server. If the db compatibility level influences stats than it should be done before update stats. Also unclear if UPDATEUSAGE is still recommend and if it should be before update stats as well.


texts: Occasional/intermittent, slow (10+-second) UPDATE queries on PostgreSQL table with GIN index

texts:
The Setup

I am running PostgreSQL 9.4.15 on an SSD-based, quad-core Virtual Private Server (VPS) with Debian Linux (8). The relevant table has approximately 2-million records.

Records are frequently being inserted and even more frequently (constantly -- every few seconds at least) updated. As far as I can tell, I have all appropriate indexes in place for these operations to execute snappily, however, and the vast majority of the time they do execute instantly (in milliseconds).

The Problem

Every hour or so, however, one of the UPDATE queries takes an excessive amount of time -- like 10 seconds or more. And when this happens, it's usually like a "batch" of queries that get "blocked", all terminating at roughly the same time. It's as if one of the queries, or some other background operation (e.g., a vacuum) is blocking them all.

Schema

The table, items, has many columns, but I think the following are the only ones possibly relevant to the problem:

id INTEGER NOT NULL (primary key)
search_vector TSVECTOR
last_checkup_at TIMESTAMP WITHOUT TIME ZONE

And these are the relevant indexes:

items_pkey PRIMARY KEY, btree (id)
items_search_vector_idx gin (search_vector)
items_last_checkup_at_idx btree (last_checkup_at)

Likely Culprits

Finally, after rigging together a little script to dump the contents of pg_stat_activity (the list of all active Postgres connections/queries) whenever a "connection leak" warning is emitted to my log-file, I've narrowed down the possible culprit queries/columns (assuming the problem isn't external, like with a misbehaving VPS). These are, roughly, the kinds of queries that seem to appear again and again:

UPDATE items SET last_checkup_at = $1 WHERE items.id = 123245
UPDATE items SET search_vector = [..] WHERE items.id = 78901

Those are slightly paraphrased, but I truly doubt anything relevant is missing. Occasionally other queries (on other tables) appear as well, but those usually look like they were just "unlucky" to get caught in the mix.

Now, even though the first query (setting last_checkup_at) tends to appear most of the time, the query that sets search_vector seems to appear every time. (And in addition, there are probably many more instances of the first query being issued in general, making it more likely to just be there on happenstance.)

(I think I'm winnowing in on a solution here, but even if I have it in the bag I wanted to document the incident here for others... I've been mystified by this problem for months, before getting a chance to deep-dive.)


texts: How to setup SQL active/active cluster to achieve Blue / Green instance switching?

texts:
I am wondering if anyone has ever used a multi-instance cluster (nee 'Active/Active') to achieve blue/green (or A/B) deployment scenarios, and what the best way of configuring it is (using SQL 2012 / Windows 2008 R2)?

To be specific, the scenario I want to achieve is to be able to switch between which cluster instance is being connected to by clients without either the clients or the SQL instances knowing (I stress I'm not talking about node failover here). I'm envisaging that the best way to achieve this is something like:

Setup 2 node cluster, each of which has InstanceA and InstanceB instances
Configure both InstanceA and InstanceB to listen as if they were the default instance on their cluster address (given each instance on a cluster has it's own unique IP)
Use DNS to switch which virtual address clients actually connect to.

This should hopefully enable me to do the following:

Deploy database to instance A, and have clients connect to it via DNS
alias as if default instance
Deploy new version of database to
instance B
Vet new version of database (connecting explicitly to cluster\InstanceB)
Redirect DNS alias to point to instance B's cluster name
Clients now connect to InstanceB without realising anything's changed
Both instances can still failover to the other node in a true outage

Joining the dots, it seems like this should be possible:

http://blogs.msdn.com/b/dbrowne/archive/2012/05/21/how-to-add-a-hostname-alias-for-a-sql-server-instance.aspx
https://stackoverflow.com/questions/9313358/connecting-to-a-multiple-instance-sql-server-without-specifying-instance

... but I've never seen a full example. Has anyone done it? Will what's proposed above work? What have I missed?


texts:
Good Morning,

I have recently setup SQL replication and changed some data in one of the tables on the primary table. This data however has not been replicated to the subscription server.

On the subscription server synchronization is in progress all the time.

This is my first attempt at setting up replication so I apologize in advance.

Any questions or more info needed just ask.


texts: When do I need to backup the Service Master Key?

texts:
I'm reading some documentation and whitepaper about Transparent Data Encryption. Some documentation mention to backup the Service Master Key as well (For clarification I'm not talking about the Database Master Key). I just don't exactly understand why this is necessary, because I was able to backup/restore a Database with TDE Encryption from Server A (backup) to Server B (restore) without using any Service Master Key.

In what scenario do I need to restore the Service Master Key?


texts: Query doesn't benefit from the index (much). How to speed it up?

texts:
So I created these indexes
CREATE INDEX start_time_index
    ON public.entries USING btree
    (start_time ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX end_time_index
    ON public.entries USING btree
    (end_time ASC NULLS LAST)
    TABLESPACE pg_default;

On a table, where start_time and end_time are timestamps with time zone.
Then I ran a query
SELECT * from entries e
WHERE e.start_time >= '2020-01-01'
     AND e.end_time <= '2021-01-01'

However it doesn't seem to benefit from end_time_index.  Why is it so?
Also, without the indices the query takes roughly 3 seconds, with them it takes 2.8 seconds, which is very little improvement (returns about 80k nodes). Can I improve it any further?

Edit:
the structure of the Entries table is naturally more complicated. It basically captures timesheet for people (user works on an item (that's scoped under some project) from time time_start until time_end.
CREATE TABLE public.entries
(
    id integer NOT NULL DEFAULT nextval('entries_id_seq'::regclass),
    user_id integer NOT NULL,
    category_id text COLLATE pg_catalog."default",
    story_id integer,
    predefined_task_id text COLLATE pg_catalog."default",
    text text COLLATE pg_catalog."default" NOT NULL,
    project_id integer,
    start_time timestamp with time zone NOT NULL,
    end_time timestamp with time zone NOT NULL,
    all_day boolean NOT NULL,
    deleted boolean NOT NULL,
    CONSTRAINT entries_pkey PRIMARY KEY (id),
    CONSTRAINT entries_category_id_fkey FOREIGN KEY (category_id)
        REFERENCES public.categories (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT entries_project_id_fkey FOREIGN KEY (project_id)
        REFERENCES public.projects (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT entries_story_id_fkey FOREIGN KEY (story_id)
        REFERENCES public.stories (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT entries_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public.users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

And here's the EXPLAIN ANALYZE without indexes

And after adding the two indexes

My end goal is to generate a table (html table) with rows being user/project and cols being dates) so we know which day who spent how much total time on which project.
Edit 2: here's the query I'm running (where the indices don't seem to help)


texts:
I have a table

id  name
1   Alpha
2   Alpha
3   Beta
4   Charlie
5   Charlie

I want to assign a temporary id at the run time in the SQL statement, So the desired result should be like

myid  name
1   Alpha
2   Beta
3   Charlie

So basically the logic should be like reading the value of name field and see if it is changed then increment the value for custom field. How we can achieve this?


texts: Need sample DELETE Query where SQL Server by default takes PAGELOCK instead of ROWLOCK

texts:
I need a sample DELETE Query where SQL Server by default takes an X lock at Page Level and not at Row Level.
BACKGROUND:
In our purging SP, we are using DELETE Query with ROWLOCK hint and I want to proof to my manager that if we remove that ROWLOCK Hint, SQL Server might go to acquire locks at PAGE Level. We are suspecting that LOCK ESCALATION is happening because of the ROWLOCK hint which we have explicitly mentioned in the DELETE Query.


texts: Static Data Masking does not appear in SSMS 18 preview 6

texts: I installed SSMS version 18 preview 6 and am using it with SQL Server 2017. I want to test the Static Data Masking feature, but the option does not appear.


texts: postgres partition data record assignment

texts: It is possible in PostgreSQL 11 to display the partition table for each row in the table? Well, it's clear that the assignment of partitions is always known by the declarative definition but is there any way to get this information programmatically?


texts: Does SQL Server 2008 R2 always fail with a severity level 20 error with certain large text manipulation?

texts:
When we run this on our SQL Server 2008 R2 instances, it causes a severity 20 error every time. It does not have that issue on our SQL Server 2016 instances.
Query:
DECLARE @v varchar(MAX) = REPLICATE(CONVERT(varchar(max),'a'),524289);
SELECT @v = @v FROM(SELECT 1 AS a) AS b;

Result:
Location:    tmpilb.cpp:3256
Expression:  fNoReaderWriterConflict
SPID:        90
Process ID:  1576
Location:    tmpilb.cpp:3306
Expression:  fNoReaderWriterConflict
SPID:        90
Process ID:  1576
Msg 3624, Level 20, State 1, Line 4
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

SELECT @@VERSION:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
    Aug 19 2014 12:21:34
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

SQLDump0002.txt (from non-production server):
Computer type is Intel(R) Xeon(R) CPU           E5430  @ 2.66GHz.
Bios Version is DELL   - 1
Phoenix ROM BIOS PLUS Version 1.10 2.5.0
8 X64 level 8664, 2 Mhz processor (s).
Windows NT 6.1 Build 7601 CSD Service Pack 1.

Memory
MemoryLoad = 87%
Total Physical = 32762 MB
Available Physical = 4058 MB
Total Page File = 65524 MB
Available Page File = 27332 MB
Total Virtual = 8388607 MB
Available Virtual = 8355194 MB
**Dump thread - spid = 0, EC = 0x00000003EF7E5F00

Is this a bug in this version of SQL Server 2008 R2?
Updates:

This does produce a set of crash dump related files. The layperson-readable sections of them basically repeat the error result above.
We recognize SQL Server 2008 R2 is old and out of support. We will be upgrading.
We have already worked around this issue by setting the large text variable outside of a SELECT statement.
We were curious if this happened in any other environments and we wanted this publicly documented in case it happens to someone else.


texts: RESTORE VERIFYONLY locks file in Azure Blob Storage

texts:
I'm using SQL Server 2012 SP2 to backup my transaction logs directly into Azure Blob Storage using the BACKUP TO URL statement.

I am then attemtpting to verify my transaction log as follows:

RESTORE VERIFYONLY FROM  URL = 'https://mystore.blob.core.windows.net/logfile.trn'
WITH CREDENTIAL = 'azurecreds'

The RESTORE VERIFYONLY operation puts a lease on the file in Azure which I can see using Azure Management Studio's blob browser (The last 2 I created without running RESTORE VERIFYONLY).

I can manually break the lease using Azure Management Studio but am I doing something wrong with RESTORE VERIFYONLY that is leaving an active lease on the file?


texts: I need to send email from SQL Server SP. Until now I used sp_send_cdosysmail. Is it better to use sp_send_dbmail? And the main question - which mail server is used by sp_send_dbmail - is it configured somewhere?


texts: After Restoring Log Shipping to Secondary Server, First Stored Procedure Execution is Slow

texts:
We've set up log shipping to a secondary SQL server on Standby/ Read-Only to offload all SSRS report generation.
This works fine within the restrictions imposed by:

Kicking out the user during the transaction log restore (we got around this by setting up multiple instances and restoring the most recent transaction logs using a round-robin schedule)
The data being out of date by, at most, the time-frame indicated by the scheduled transaction log backup/ restore job.

Unfortunately, the first time any/ all stored procedure are run, after the transaction log was restored, it takes a much longer time to complete than normal. All subsequent executions of that same stored procedure complete within the expected time. If we then execute another stored procedure, the first time it is slow and all subsequent executions complete in the expected time.

For reference, the difference in execution is ~00:02 normally compared to ~01:00 on the first run.

I assume this has something to do with either the server execution statistics or the stored procedure parameter sniffing/ stored execution plan.
Is there any way to get around this issue? Or is this inherent to the transaction log restore?

If it was just the very first execution of any stored procedure we could get around this easily by executing any stored procedure upon restore, but it appears to affect the first time all stored procedures are executed.

I tried running count( * ) on the 11 tables the stored procedure I'm using for testing touches. The first run took 00:32, and subsequent count(*) took 00:00. Unfortunately, this did not have any impact on the first run of the stored procedure.

I don't see any results on either my primary or secondary servers for is_temporary stats, either before or after execution of a stored procedure.

I'm currently on SQL Server 2012Query Exection Plan:The query execution plan at first glance appears significantly different, however, upon saving the execution plan and opening the .sqlplan file generated they are exactly the same. The difference appears to be coming from the different versions of SSMS I am using, 2014 on the primary server and 2018 on the secondary. When viewing the execution plan on the secondary it shows underneath every node's % and time cost ### of ### (##%) - neither those numbers, nor the actual execution plan change upon further executions.I also included client statistics and they show almost exactly the same, the only difference being the primary server executes with 1.4 seconds of Wait time on server replies and the secondary takes 81.3 seconds.I do see a large number of PAGEIOLATCH_SH locks from the first execution, as you predicted:

diff after first exec vs diff after second exec
waiting_tasks_count    10903    918
wait_time_ms          411129  12768

One of the odd things about this situation, is, except for the round-robin multiple instances part of the setup we already have our production SSRS server reading from a standby/ read-only database that is fed by periodic transaction logs and do not experience these slow downs on the first execution of a stored procedure. Our users are kicked off every time the transaction log is restored, though, which is the problem the above setup is supposed to resolve.


texts:
I have an SQL statement that is taking about 40 seconds to run and the only thing I see is an high hash match and sort. I have added indexs to the temp table but still taking an long time to complete.

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

SQL Server Execution Times:
   CPU time = 46735 ms,  elapsed time = 9004 ms.

SELECT g.CustomerId, g.LogDate
INTO #TempGuidelineLog
FROM vwGuidelineLog g --nolock
where g.LogDate >= '2017-10-01' and g.LogDate < dateadd(day, 1, '2018-09-30')

CREATE NONCLUSTERED INDEX ix_temp1Customer ON #TempGuidelineLog (CustomerId)
INCLUDE ([LogDate])

select
g.*,
a.StateId,
a.CountryId
into #Temp
from #TempGuidelineLog g
JOIN [vwCustomerAddress] a ON a.CustomerId = g.CustomerId

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CountryId)
INCLUDE ([CustomerId])

SELECT States=(SELECT Total=COUNT(c.CustomerID), c.StateId, st.Name
,[Distinct] =  COUNT(DISTINCT c.CustomerId)
FROM #Temp c
JOIN [State] st ON st.StateId = c.StateId
where c.CountryId = 1 and st.StateId NOT IN (65,66)
GROUP BY c.StateId, st.Name
ORDER BY 1 DESC
FOR XML PATH('State'),type)

,Dates = (SELECT StartDate = CONVERT(VARCHAR(10), '2017-10-01' , 101), EndDate = CONVERT(VARCHAR(10), '2018-09-30', 101)
FOR XML PATH('Date'),type)

,Countries=(SELECT Total=COUNT(c.CustomerID), co.CountryID, co.Name, ISOCode=co.TwoLetterISOCode
,[Distinct] =  COUNT(DISTINCT c.CustomerId)
FROM #Temp c
JOIN Country co ON co.CountryID = c.CountryId
GROUP BY co.CountryID, co.Name,co.TwoLetterISOCode
ORDER BY 1 DESC
FOR XML PATH('Country'),type)
FOR XML PATH('Report')


texts: What does "-" mean before a column in where

texts:
I was seeing an Oracle view sys.dba_audit_trail, and I came across the following where clause:

where   aud.action#     = act.action    (+)
  and - aud.logoff$dead = spm.privilege (+)
  and   aud.logoff$dead = aom.option#   (+)
  and - aud.priv$used   = spx.privilege (+);

I would like to know what - means.

And if possible, know if I can do it with ANSI SQL.


texts:
I have the next table structure:

 CREATE TABLE `sat_msgPos` (
  `idOwnApp` mediumint(11) NOT NULL DEFAULT '0',
  `idMsg` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `idVeh` mediumint(11) NOT NULL DEFAULT '0',
  `idProviderComm` mediumint(11) DEFAULT NULL,
  `txMsg` varchar(255) DEFAULT NULL,
  `tmMsg` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `amLat` double DEFAULT NULL,
  `amLong` double DEFAULT NULL,
  `tmUpdate` datetime DEFAULT NULL,
  `idMsgBlobParent` int(11) DEFAULT NULL,
  `txDescPos` varchar(255) DEFAULT NULL,
  `idMsgVpr` int(11) DEFAULT NULL,
  `amSpeedCurr` float DEFAULT NULL,
  `idSts` char(1) DEFAULT NULL,
  `idTrigger` tinyint(3) unsigned DEFAULT NULL,
  `amDistTot` mediumint(9) DEFAULT NULL,
  `amEngMinsTot` float DEFAULT NULL,
  `inException` tinyint(2) DEFAULT '0',
  PRIMARY KEY (`idMsg`,`idVeh`,`tmMsg`),
  KEY `X1` (`idMsgBlobParent`),
  KEY `X2` (`idMsgVpr`),
  KEY `X3` (`tmMsg`),
  KEY `X4` (`idVeh`,`tmMsg`,`idTrigger`)
) ENGINE=InnoDB AUTO_INCREMENT=2102445247 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(tmMsg)
(PARTITION a9 VALUES LESS THAN ('2017-02-01') ENGINE = InnoDB,
 PARTITION a10 VALUES LESS THAN ('2017-03-01') ENGINE = InnoDB,
 PARTITION a11 VALUES LESS THAN ('2017-04-01') ENGINE = InnoDB,
 PARTITION a12 VALUES LESS THAN ('2017-05-01') ENGINE = InnoDB,
 PARTITION a13 VALUES LESS THAN ('2017-06-01') ENGINE = InnoDB,
 PARTITION a14 VALUES LESS THAN ('2017-07-01') ENGINE = InnoDB,
 PARTITION b1 VALUES LESS THAN ('2017-08-01') ENGINE = InnoDB,
 PARTITION b2 VALUES LESS THAN ('2017-09-01') ENGINE = InnoDB,
 PARTITION b3 VALUES LESS THAN ('2017-10-01') ENGINE = InnoDB,
 PARTITION b4 VALUES LESS THAN ('2017-11-01') ENGINE = InnoDB,
 PARTITION b5 VALUES LESS THAN ('2017-12-01') ENGINE = InnoDB,
 PARTITION b6 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
 PARTITION b7 VALUES LESS THAN ('2018-02-01') ENGINE = InnoDB,
 PARTITION b8 VALUES LESS THAN ('2018-03-01') ENGINE = InnoDB,
 PARTITION b9 VALUES LESS THAN ('2018-04-01') ENGINE = InnoDB,
 PARTITION b10 VALUES LESS THAN ('2018-05-01') ENGINE = InnoDB,
 PARTITION b11 VALUES LESS THAN ('2018-06-01') ENGINE = InnoDB,
 PARTITION b12 VALUES LESS THAN ('2018-07-01') ENGINE = InnoDB,
 PARTITION b13 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

I try to reorganize the b13 partition with following command

ALTER TABLE sat_msgPos REORGANIZE PARTITION b13 INTO(PARTITION c1 VALUES LESS THAN ('2018-08-01'), PARTITION c2 VALUES LESS THAN ('2018-09-01'));

I am receiving following error:

"ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
"

How can i split b13 into 2 new partitons without lose the data?

Thanks a lot!


texts: Can I launch a stored procedure and immediately return without waiting for it to finish?

texts:
We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day.

I have a second stored procedure that should be run after the first stored procedure runs since it is based on the numbers obtained from this first stored procedure, however it takes longer to run and is for a separate process, so I don't want to make the user wait while this 2nd stored procedure gets ran.

Is there a way to have one stored procedure start a second stored procedure, and return immediately without waiting for results?

I'm using SQL Server 2005.


texts: If else with 5 possible cases PostgreSQL

texts:
I have bunch of records in Database and i have been searching for query on NULL json object and i got that right,but now its difficult for me to continue it on 5 possible cases

For example: am showing data on basis of 5 cases and 3 attributes

config->'external_http_port' , config->'external_host' , config->'snapshots'->>'jpg'

cases are

1: config->'external_http_port' can be null

2: config->'external_host' can be null

3: config->'snapshots'->>'jpg' can be null

4: All three can be null

5: two of three can be null.

for just checking all three nulls i am doing query as

select * from cameras where (config->'external_http_port') is null and (config->'external_host') is null and (config->'snapshots'->>'jpg') is null

any help will be appreciated

Using PostgreSQL 9.3


texts:
I am new to SQL, I have a million rows in a table to delete. I wrote code to commit the transaction (delete the rows) every time the count reaches 50k and reset the counter again. My question is why I put a commit statement for the counter 50,000 not 10,000 or 1,000?

Is it OK to commit for every 1,000 deletes? What will happen and is it likely to affect performance?


texts:
I've been given a task to test where an SQL Server 2012 can achieve better performances - we have two options:

Use the SQL Server as an On-Premise machine (Standalone server that we have)
Use the SQL Server as a cloud-based server.

What is the best way to check which option gets better performance for our needs? (CPU + I/O performances), is there any recommended benchmark tool I can use? I thought about writing some SQL queries of various types and run them against both options, but I'm not sure which queries I should use.

Are there any recommended  type of queries that can give some good glance about the performance?


texts: Does SQL Server Management Studio 2017 Contain a New Explain Feature

texts:
TL;DR

I happened upon a feature in SQL Server Management Studio v17.9 which I was not aware of. It looks a bit like the EXPLAIN found in Oracle, PostgreSQL and MySQL. Where is this feature documented?

How to Turn The Feature On

I was running the following query with the options Include Actual Execution Plan (Ctrl + M) and Include Live Query Statistics turned on:

    SELECT sdes.session_id
          ,sdes.[status]
          ,sdes.login_name
          ,sdes.[host_name]
          ,sder.blocking_session_id
          ,sdb.name
          ,sdes.cpu_time
          ,sdes.logical_reads --optionally: + sdes.reads + sdes.writes
          ,sdes.last_request_start_time
          ,sdes.program_name
          ,sdes.session_id
          ,sder.request_id
          ,dest.[text]
    FROM   sys.dm_exec_sessions             AS sdes
           LEFT JOIN sys.dm_exec_connections     AS sdec
                ON  sdes.session_id = sdec.session_id
           JOIN sys.databases               AS sdb
                ON  sdes.database_id = sdb.database_id
           LEFT JOIN sys.dm_exec_requests  AS sder
                ON  sdes.session_id = sder.session_id
           CROSS APPLY sys.dm_exec_sql_text(sdec.most_recent_sql_handle) AS dest
    WHERE 1=1
    AND sdb.name = ''

Outcome

This produced four results tabs:

Results
Messages
Live Query Statistics
Execution Plan

Special Results Tab

When I switched to the Results tab, I noticed a result set just below the actual results which looks like this:

Observations

If I turn on either the Include Actual Execution Plan (Ctrl + M) option or the Include Live Query Statistics option, then the SQL Server Explain isn't displayed. The "SQL Server Explain" is only displayed if both options are turned on.

Questions

Can this feature be turned on without having to activate both Include Actual Execution Plan (Ctrl + M) option and the Include Live Query Statistics?
Where is this neat little feature documented?

Prerequisites

The following components and/or software packages are involved in the scenario:

Server

Windows Server 2016
SQL Server 2016 SP1

Client

Windows 10 Version 1807 (Build 17134)
SQL Server Management Studio v17.9

Microsoft SQL Server Management Studio    14.0.17285.0
Microsoft Analysis Services Client Tools  14.0.1016.283
Microsoft Data Access Components (MDAC)   10.0.17134.1
Microsoft MSXML                           3.0 6.0
Microsoft Internet Explorer               9.11.17134.0
Microsoft .NET Framework                  4.0.30319.42000
Operating System                          6.3.17134

SQL Server Management Studio v17.9

Include Actual Execution Plan (Ctrl + M) turned ON
Include Live Query Statistics turned ON


texts: Understanding IAM Pages: extent intervals

texts:
I'm reading Itzik's book "Querying Microsoft SQL Server 2012" as well as reading/watching different educational material on the internet. My intention is to have a useful understanding of how the database internals work.

I have a small doubt that I couldn't resolve about the IAM pages. Since I'm in a very early stage of my understanding I need maybe extra help from those who have a better picture of it, so excuse me if my doubts seem ridiculous.

In chapter 15 "Implementing Indexes and Statistics" appears an image - shown below - as an example of an IAM page:

You can see by the red arrow what seems to be 16 pages related to the same extent. How is that possible? Is it an author/editor's mistake? Or what is more likely: is there something I'm not understanding correctly?

The other question I have is related to the pages intervals. Why aren't they contiguous? Take for example the last extent, it will cover pages with id 336 to 22642, or the preceding one, 296 to 328.


texts: Load test with databases. Best practices , tools

texts:
I need to design a way to stress a clon of my production databases.
At the moment, I'm using mysql percona xtradb with tree nodes running in google cloud.

I'm using haproxy as load database balancer.

All backend microservices in my company are configured to connect through haproxy to the node1 for writes and to the nodes 2 and 3 for select.

I want to make my database three times bigger.

I have cloned my production database using backups in other google instances now I'm going to populate this database inserting more records.

I want to stress my new database x3 bigger, using in my load test the top queries with the poor performance that I have found using my tuning tool (Percona monitoring and management PMM)

I have thinking in three options:

Use mysqlslap
Use Apache JMeter using jdbc module
Design my own stress tool using python with flask to expose a API with my custom loadTest and then I can use JMeter or soapUi to stress with different multithread profiles my API and it runs the queries.

I would like to have feedback on how to approach this or to ask if anybody knows other tools to stress database in a multithread environment.

Thanks.


texts: Getting a proxy account to backup to a share using Ola Hallengrens backup script

texts:
I have a SQL Server 2012 Standard install where the Engine and Agent are running under the default NT Service accounts

Engine - NT Service\MSSQLSERVER

Agent  - NT Service\SQLSERVERAGENT

I am trying to get Ola Hallengrens backup scripts to backup to a Network Share but this is proving a bit problematic.
Normally I would have the Engine and Agent running under domain accounts and grant those access to the network share and it would just work however this was setup before my time and am unable to change these.

As the Agent and Engine are not running under domain accounts I am attempting to use a Proxy to get this working.

I have a share \\ComputerName\Folder and a service account Domain\account.svc. I am able to browse and create files and folders using the Domain\account.svc account so the access seems to be working ok. However when running this through the Agent it is not working.

What I have done.

Created a Credential using Domain\account.svc
Created a Operating System (CmdExec) Proxy and linked it to the Credential
In the Backup System Databases job step set the "Run as" to the Proxy account

The error I am getting in the log file is

  Msg 50000, Level 16, State 1, Server [SERVER NAME], Procedure DatabaseBackup, Line 457
  The directory \\ComputerName\Folder does not exist.

  Msg 50000, Level 16, State 1, Server [SERVER NAME], Procedure DatabaseBackup, Line 740
  The documentation is available at https://ola.hallengren.com/sql-server-backup.html.

This being the same error you normally get when the Agent Domain account doesnt have access to a share.

Question - Can someone shed some light on why this would not be working.

Thanks.

Edit 1:
The Domain\account.svc has Full Control on the share, is a local admin on the machine as well as a sysadmin within SQL.

Edit 2:
Installed a test instance of SQL on my desktop and am able to replicate the issue. When I then change the Engine and Agent service accounts to run under Domain\account.svc the backup completes successfully to the share.

Edit 3:
Not an ideal solution but for now I am backing up locally and have a robocopy job set to mirror the directory to the share.


texts: MySQL Query Optimization : Indexing and Pagination

texts:
I'm adding pagination to my 'latest news' php script and running in to an issue.

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

I have indexes on approved, time columns.

The problem is when I do an EXPLAIN on the query, it shows it is using the 'approved' index - but it still has to scan all 1000 rows to get the 10 new entries I want to display.

Anyway to optimze the query so that doesn't happen?


texts: Is using a relational DB for storing votes and (accessed resources) overkill?

texts:
I am writing a forum component for a site and would like to keep track of what topics/questions a user accesses so that other topics/questions can be recommended based on said users previous choices.

I'd imagine one individual data/row is not very important, where it'd absolutely necessitate ACID properties on every single entry; not to mention when the user-base grows large(on the order of millions, or tens of millions), this data set will surely explode. Is this then a case for using say, MongoDB? What is the advisable general approach for something like this or votes?


texts:
I have a table in my SQL Server database with data like this:

ID  ParentID
--- ---------
1   NULL
2   1
3   1
5   1
5   4
5   6
8   6
7   9
9   10

I need a select statement that will return all the rows that have some relation between them (no matter parent or child). For example for id = 1 I need to get all the rows except last two. The same result must be for id = 2, 3, 4, 5, 6 ,8. And  for id = 7 or 9 the statement must return only the last two rows. I tried to solve this issue with recursive CTE but I failed.


texts: Postgresql 12 Truncate partition with foreign key

texts:
I am building a datawarehouse using Postgresql (12) and think I should be using partitions on the most populated tables, for performance and maintanability reasons.

I have something along the lines of:

CREATE TABLE sale (id varchar,
                    vers varchar,
                    CONSTRAINT sale_pkey PRIMARY KEY (id, vers)
                    )
                   PARTITION BY LIST (vers);

CREATE TABLE sale_line (id varchar,
                    sale_id varchar,
                    vers varchar,
                    CONSTRAINT sale_line_pkey PRIMARY KEY (id, vers),
                    CONSTRAINT sale_fk FOREIGN KEY (sale_id, vers) REFERENCES sale(id, vers)
                    )
                   PARTITION BY LIST (vers);

CREATE TABLE sale_v1 PARTITION OF sale FOR VALUES IN ('v1');
CREATE TABLE sale_line_v1 PARTITION OF sale_line FOR VALUES IN ('v1');
CREATE TABLE sale_v2 PARTITION OF sale FOR VALUES IN ('v2');
CREATE TABLE sale_line_v2 PARTITION OF sale_line FOR VALUES IN ('v2');

Now, I want to get rid of data in version v2, and I try this:

truncate sale_line_v2, sale_v2;

and get this error:

SQL Error [0A000]: ERROR: cannot truncate a table referenced in a foreign key constraint
  Détail : Table "sale_line" references "sale_v2".
  Indice : Truncate table "sale_line" at the same time, or use TRUNCATE ... CASCADE.

Notice that using 'truncate ... cascade' is not an option. As stated in the documentation (and experimented the hard way), it will also truncate my version v1:

  Note in particular that when the table to be truncated is a partition,
  siblings partitions are left untouched, but cascading occurs to all
  referencing tables and all their partitions with no distinction.

I ahve seen similar questions on this site (truncate table with foreign keys, Truncate tables with dependent foreign key constraints), but no answer working for me. The best approach would be disabling the constraints, but this does NOT work:

BEGIN;
ALTER TABLE sale_line disable TRIGGER ALL;
TRUNCATE sale_v2, sale_line_v2;
ALTER TABLE sale_line enable TRIGGER ALL;
COMMIT;

results again in:

SQL Error [0A000]: ERROR: cannot truncate a table referenced in a foreign key constraint
  Détail : Table "sale_line" references "sale_v2".
  Indice : Truncate table "sale_line" at the same time, or use TRUNCATE ... CASCADE.

I also tried disabling contraints on the partitions I want to truncate in addition to the main table, or even on all partitions and the main table, up to the above, but the result is always the same...

BEGIN;
ALTER TABLE sale_line disable TRIGGER ALL;
ALTER TABLE sale disable TRIGGER ALL;
ALTER TABLE sale_line_v1 disable TRIGGER ALL;
ALTER TABLE sale_line_v2 disable TRIGGER ALL;
ALTER TABLE sale_v1 disable TRIGGER ALL;
ALTER TABLE sale_v2 disable TRIGGER ALL;
TRUNCATE sale_v2, sale_line_v2;
ALTER TABLE sale_line enable TRIGGER ALL;
ALTER TABLE sale enable TRIGGER ALL;
ALTER TABLE sale_line_v1 enable TRIGGER ALL;
ALTER TABLE sale_line_v2 enable TRIGGER ALL;
ALTER TABLE sale_v1 enable TRIGGER ALL;
ALTER TABLE sale_v2 enable TRIGGER ALL;
COMMIT;

SQL Error [0A000]: ERROR: cannot truncate a table referenced in a foreign key constraint
  Détail : Table "sale_line" references "sale_v2".
  Indice : Truncate table "sale_line" at the same time, or use TRUNCATE ... CASCADE.

So contraints regarding partitions must have some sort of special status... but this defeats the purpose of being able to truncate partitions...

Any hint ?


texts:
I'm working on a user authorization database. Haven't much work with ACLs before. Trying to keep things somewhat simple. I guess my question would be. Am I missing anything?

Purpose:

The database is to allow access to applications and any granular authorization with in those applications.

Theory of Operation

Users belong to Groups.
Applications Belong to Groups.
Authorization is based on Group/Role.

You would have to fill in the sysAplicationList, sysApplicationAclList, and sysAclRoles tables. Create your groups, assign the applications to the groups. Then filling in the sysGroupApplicationAclList At this point the role would be assigned. Then sysGroupApplicationRoleItems would list the ACL item for that group, application, role

Diagram

Tables

sysAplicationList - Holds the list of applications installed on the system.
sysApplicationAclList - Repository of possible to ACLs for the application.
sysAclRoles - Roles in the system.

sysGroupDetailList - Name of groups.
sysApplicationGroupMemebership - Applications in a group.
sysGroupApplicationAclList -
sysGroupApplicationRoleItems - ACL items per AclList.

sysUsersDetailList - User information.
sysUserGroupsMemebership - User assigned groups.


texts: query processor ran out of internal resources - extremely long sql query

texts:
I have a SQL Server 2012  database with around 2000 tables (daily log files) with around 20 million records each.
Output of select @@Version

  Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Standard Edition (64-bit) on Windows NT 6.2  (Build 9200: )

Now, in order to generate a yearly or 3-years report, I need to run query on each table and "Union All" the results. The queries are generated dynamically and are usually small in size. However after doing "Union all: with 1000 tables, it become a single huge query.

While processing such queries, I am getting an error as highlighted below.

The application is already built, tested and in live production and hence I cannot use temp tables or hash tables to store intermediate results.

Now, how do I get rid off this error message?

  The query processor ran out of internal resources and could not
  produce a query plan. This is a rare event and only expected for
  extremely complex queries or queries that reference a very large
  number of tables or partitions.


texts: How to query Wikipedia's database to retrieve the main topic and the surface forms of a given title?

texts:
First, Wikipedia's database scheme diagram is available here. I have used one of their dumps locally to work with it.

Now, I need a single MySQL query to check both page and redirect tables with a given input 'Dog_breeder' or 'Dog_breeding' for example. I want to get the list of both types of pages linked to this input.

For example, Dog_breeding is a main topic and the below pages will redirect to it:

Dog_Breeder
Dog_breeder
Dog_breeders
Stud_dog

In the database, this is stored in this fashion:

|---------------------------|       |---------------------------|
| table: page               |       | table: redirect           |
|---------------------------|       |---------------------------|
| page_id   | page_title    |       | rd_from   | rd_title      |
|---------------------------|       |---------------------------|
| 476072    | Dog_breeding  |       | 796236    | Dog_breeding  |
| 796236    | Dog_breeder   |       | 16274701  | Dog_breeding  |
| 16274701  | Dog_breeders  |       | 37353772  | Dog_breeding  |
| 37353772  | Stud_dog      |       | 40738352  | Dog_breeding  |
| 40738352  | Dog_Breeder   |       |---------------------------|
| 11606599  | Dog_breeders  |
| 22534134  | Dog_breeders  |
|---------------------------|

So all pages are in the page table and then redirections are stored in the redirect table (rd_from is the page that will be redirected to the main rd_title page)

I have the query to retrieve all redirect entries of a main topic:

SELECT
    page_id,
    page_title,
    rd_from,
    rd_title
FROM
    redirect
    JOIN page ON page_id = rd_from
WHERE rd_title = 'Dog_breeding'

But two problems:

it is not giving me the main page itself ('Dog_breeding')
if I put WHERE rd_title = 'Dog_breeder' it finds nothing...

I would like to get something like the below regardless of the input being Dog_breeding or Dog_breeder:

page_id     | page_title    | rd_from   | rd_title
-------------------------------------------------------
476072      | Dog_breeding  |           |
796236      | Dog_breeder   | 796236    | Dog_breeding
16274701    | Dog_breeders  | 16274701  | Dog_breeding
37353772    | Stud_dog      | 37353772  | Dog_breeding
40738352    | Dog_Breeder   | 40738352  | Dog_breeding

I guess I could do it with PHP and several back and forth with the Database (check if it's a main topic, if not grab the main topic, then query like I did above) but I feel like there is a one-query solution?


texts: StartTime and EndTime for the Attention Extended Event

texts:
The documentation says that the "Attention" extended event class has StartTime and EndTime as part of it.

But I can't seem to figure out how to get at it or record it...

Here is what I am using to create the event session:

CREATE EVENT SESSION [TimeoutMonitor] ON SERVER
    ADD EVENT sqlserver.attention(
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,
          sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.server_principal_name,
          sqlserver.sql_text,sqlserver.username))
    -- Save to our file.  Max size of 500 megabites and 3 rollover files (so it can take up 1.5 GB total)
    ADD TARGET package0.event_file(SET filename=N'd:\TimeoutMonitoring\TimeoutMonitor.xel', max_file_size=500, max_rollover_files=3)

But if I try to add sqlserver.start_time (or any variant like StartTime) I get an error.

And if I try to query as if it is just automatically recorded, I also get an error.

Is there a way to get start time and end time for Attention Events?


texts: how to find who used SA for login in SQL Server?

texts: In an organization if a community used one SA account for a database. So, Is there a way in SQL server to find that person who used SA password and logged in as a user ?


texts: Are there opensource hierarchical database management systems

texts: I was looking for a hierarchical database management system and the only one i came across was IBM's ims.Are there opensource systems that one can use?.


texts: REORGANIZE seems to work as well as REBUILD all the time

texts:
On this MSDN Page it says if you should reorganize or rebuild based on the amount of fragmentation:

 5% to 30%   ->   ALTER INDEX REORGANIZE
 Over 30%    ->   ALTER INDEX REBUILD WITH (ONLINE = ON)*

However, we have noticed that even with really high fragmentation (over 95%) on large and small tables, REORGANIZE works fine.  (The fragmentation goes down to less that 1%).

Why does the MSDN page say this?  Is it not supposed to work like it is for me?

Or is there a drawback I am missing?  (A hidden problem if I don't rebuild?)


texts: How can I execute a PL/SQL procedure for Oracle via JDBC?

texts:
I have a Java webapp with an Oracle DB, and I try to execute this statement at the end of a data migration script:

EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'MY_SCHEMA', tabname => 'MY_TABLE', estimate_percent => dbms_stats.auto_sample_size);

It works in sql*plus, but not via JDBC:

"Error: ORA-00900: invalid SQL statement"

What am I doing wrong?


texts: Why do queries on two tables with the same structure have different execution times

texts:
I have partitioned a large table in a PostgreSQL database into monthly child tables with the same structure and similar numbers of rows. Recently I have noticed that the newer tables take much longer (up to 4 times as long) to run the same query, but I'm unsure why. They currently both have no indices and no discernable differences. I have run VACUUM ANALYZE on both tables, with no change in execution time.

The explain plans for each table running a simple query are as follows

c4billing=# EXPLAIN ANALYSE select distinct "LinkedAccountId" from c4102017;

QUERY PLAN

 HashAggregate  (cost=948388.67..948388.77 rows=33 width=13) (actual time=436086.651..436086.656 rows=35 loops=1)
   ->  Seq Scan on c4102017  (cost=0.00..943718.29 rows=9340764 width=13) (actual time=187.460..432554.323 rows=8344050 loops=1)
 Total runtime: 436086.702 ms
(3 rows)

c4billing=# EXPLAIN ANALYSE select distinct "LinkedAccountId" from c4012017;
QUERY PLAN
 HashAggregate  (cost=829581.45..829581.55 rows=32 width=13) (actual time=257116.246..257116.253 rows=35 loops=1)
   ->  Seq Scan on c4012017  (cost=0.00..825845.67 rows=7471557 width=13) (actual time=13.498..254204.195 rows=7470147 loops=1)
 Total runtime: 257116.284 ms
(3 rows)

I'd be very grateful for any insight or assistance!


texts: How to make a table name an attribute in PostgreSQL?

texts:
I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data.

I would like to Union them all into a single table but without losing that very critical information that is the date of the data. I guess that I should somehow use information_schema.tables and make use of PL/pgsql which I don't know and therefore should learn, but to be honest this seems to be a big effort for a one-shot operation. So, my question would be:
Is there an easy way to achieve this or what would be the best solution?


texts:
I have a partitioned table on the basis of the Primary key having 1.5 Billion rows in it. But when I check the .ibd files only 80 ibd show in GB's whereas 120ibd shows 1MB file. I would like to know how does the distribution work when I limit Partition with PARTITIONS 200. My structure is as below. Given that id & checksum both are unique all the time.

CREATE TABLE `mapping` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `sha_checksum` varchar(50) NOT NULL DEFAULT '',
  `path_id` varchar(90) DEFAULT NULL,
  `file_id` varchar(35) DEFAULT NULL,
  `server_id` CHAR(6) DEFAULT NULL,
  PRIMARY KEY (`id`,`sha_checksum`)
) ENGINE=InnoDB
/*!50100 PARTITION BY KEY (id,sha_checksum)
PARTITIONS 200 */

Please help me to understand where I am wrong.


texts: MongoDb cleanupOrphaned() running for days

texts:
My cleanup orphans on a collection is running for 3 days now. Log shows a few million records being deleted once in a while. However, most of the time nothing is deleted.

  I SHARDING [conn3919] rangeDeleter deleted 0 documents for < my collection name >

Not sure why this is so slow. Is this expected? We do have over 100 shards. When we tried to run cleanup on all shards in parallel, we noticed that all cleanups were stuck for a day (no cleanup logs). So we had to run it sequentially. As an alternate to cleanupOrphaned script, we tried adding a new replica set (after shutting down secondary nodes) thinking that it will sync from primary without orphans. Sync is always very fast comparatively. However, after sync the new replica set also had orphans! So that didn't work.

Any advice on a quicker way to cleanup orphans is highly appreciated. We are using Mongo v3.4

Also, is it possible to have orphans when balancer is off? We had our balancer off always but still ended up having orphans.


texts: Does MySQL 5.5 support shared database with separate schema?

texts:
I am developing a SaaS based product using MySQL 5.5 as the backend database. I want to keep shared database for all the tenants, but separate a database schema of each tenant. There would be a single database for all tenant but different schema for each tenant.

I am not sure whether MySQL does support it.

Your answer would help me a lot.

Thanks in advance!


texts: How to Grant A User Under A Sql Domain Account To Insert and Update Records

texts: I have a sql domain account in sql server like DOMAIN\GroupName. There are many user accounts under that account like DOMAIN\user.name. I want to give insert and update permission only to a user under that domain account. How can i do it for only one user?


texts:
We have SQL Server 2008 R2 cluster active/active/active (a, b, c) and three instances run on this environment.

We did a test on one of the instances, and failover test.

Manual failover from node c to b.  After 3 minutes, the application cannot connect to SQL Server.
Back to node c we are OK and application is happy
Manual failover from node c to a.  After 3 minutes, the application cannot connect to SQL Server.
Back to node c we are OK and application is happy

Please give me some helps.


texts: Converting 'False'(string) to False(boolean) During LOAD DATA LOCAL INFILE

texts:
In MySQL is there anyway of loading "False" into a boolean column.

While loading data in table having boolean column and where data is in "True" or "False", it gives warning of:

"Incorrect integer value: 'False' for column 'c1' at row 1".

Table structure is as follow

Create Table: CREATE TABLE `n1` (
  `c1` boolean ,
  `c2` int
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I am using load data statement to load from file "file1" which is comma separated and enclosed in double quotes. Following is the data sample

"False","1"
"True","2"

The load data local infile statements is as below

load data local infile 'file1'
into table n1
  FIELDS TERMINATED BY ','
  enclosed by '\"' LINES
  TERMINATED BY '\r\n';


texts: Changing the grid listener port of an Oracle Database Appliance X8-2M

texts:
I need to add an extra Port: 1522
Listener Parameter File   /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/xxxx/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxx)(PORT=1521)))
Services Summary...
Service "xxxx" has 1 instance(s).

/u01/app/19.0.0.0/grid/network/admin/listener.ora->
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))))            # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                #

Every time I edit the listener.ora The listener fails to start:
I tried:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1522))
    )
  )
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

srvctl start listener
PRCR-1079 : Failed to start resource ora.LISTENER.lsnr
CRS-5016: Process "/u01/app/19.0.0.0/grid/bin/lsnrctl" spawned by agent "ORAAGENT" for action "start" failed: details at "(:CLSN00010:)" in "/u01/app/grid/diag/crs/xxxx/crs/trace/crsd_oraagent_grid.trc"
Listener failed to start. See the error message(s) above...
2021-02-17 15:32:19.462 :CLSDYNAM:2613040896: [ora.LISTENER.lsnr]{1:35727:65428} [start] (:CLSN00010:)Utils:execCmd scls_process_join() uret 1
2021-02-17 15:32:19.462 :CLSDYNAM:2613040896: [ora.LISTENER.lsnr]{1:35727:65428} [start] clsnUtils::error Exception type=2 string=
CRS-5016: Process "/u01/app/19.0.0.0/grid/bin/lsnrctl" spawned by agent "ORAAGENT" for action "start" failed: details at "(:CLSN00010:)" in "/u01/app/grid/diag/crs/etabonxrdbs0010/crs/trace/crsd_oraagent_grid.trc"
2021-02-17 15:32:19.463 :    AGFW:2610939648: [     INFO] {1:35727:65428} Agent sending reply for: RESOURCE_START[ora.LISTENER.lsnr etabonxrdbs0010 1] ID 4098:1355034
2021-02-17 15:32:19.463 :CLSDYNAM:2613040896: [ora.LISTENER.lsnr]{1:35727:65428} [start] LsnrAgent::Lsnrctl::start lsnrctl start complete lsnrname:LISTENER output:
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2021 15:32:19
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
...
2021-02-17 18:19:34.200 :CLSDYNAM:2606737152: [ora.LISTENER.lsnr]{1:35727:1260} [check] clsnUtils::error Exception type=2 string=
CRS-5020: Not all endpoints are registered for listener LISTENER
I tried: srvctl modify listener -p "TCP:1521/TCP:1522";

srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: grid
Home: <CRS home>
End points: TCP:1521, 1522
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:


texts: Efficiently query MAX over multiple ranges

texts:
When performing a MIN() or MAX() over a single range covered by an appropriately sorted index, SQL Server does a TOP() and so returns the value after fetching just one row.  When the search criteria include more than one range, SQL Server instead grabs all the indexed values from both of the ranges and does a stream aggregate, which is far slower than performing a TOP() for each sub-value.

For example, assume a large number of orders per customer in a table like:

CREATE TABLE orders
(
  customer_id int,
  quantity int
)

Running this query:

SELECT MAX(quantity)
FROM orders
WHERE customer_id IN (1,2)

will result in a query that takes several times as long as if only one customer ID were specified.

What is the most efficient way to perform a query like the above?  Relatedly, if separate results were needed (i.e. GROUP BY customer_id), what would the best method be?

SQL Fiddle: http://sqlfiddle.com/#!3/ef0c6/1


texts: Java and SQL error: Unknown column 'newID' in 'where clause'

texts:
I have a table called colleagues and it has 6 columns: userid, firstname, lastname, title, squad, and email. The code I have typed for updating the first name is

String ID = textID.getText();
String first = textFirstName.getText();
String last = textLastName.getText();
String title = textTitle.getText();
String squad = textSquad.getText();
String email = textEmail.getText();

int newID = Integer.parseInt(ID);
int newSquad = Integer.parseInt(squad);

try {
    if(first != null) {
        String sql = "Update colleagues " + "set firstname = first " + "where userid = newID";
        myStmt.executeUpdate(sql);
    }
    DoConnect();
    JOptionPane.showMessageDialog(NewJFrame.this, "Updated");
} catch (SQLException err) {
    System.out.println(err.getMessage());
}

The DoConnect(); after the if statement is just to update a jtable when I run the program.


texts: MySQL - Get actual precision and scale of number

texts:
I would like to query the integer part and fractional part of all decimal(8,4) values in a column. By that I mean, I want to know the actual given values, and not the max allowed by the data type, which are integer = 4, fractional = 4.

For example, 33.99 would return (2,2) and 1.375 would return (1,3). I have started trying to parse the numbers as strings using char_length() like this:

SELECT max(char_length(SUBSTRING_INDEX(col,'.',1))), max(char_length(SUBSTRING_INDEX(col,'.',-1)));

A similar, more intuitive, and uglier way in Oracle is

SELECT max(length(regexp_substr(col,'^.*[.]',1))), max(length(regexp_substr(col,'[.].*$',1)))

But is there a better way that exploits MySQL's knowledge that these are actually numbers?

This page implies that the true lengths of numbers - e.g. without leading 0s - is known and retrievable, so I was hoping there was a function that would just do this. Does this have something to do with the assertion that "MySQL returns all data as strings and expects you to convert it yourself" or is that specific to that Python module?


texts: What's the exact meaning of `innodb_max_dirty_pages_pct`, and how does it relate to the redo log?

texts:
I'm puzzled by the innodb_max_dirty_pages_pct parameter, and its relation to the redo log.

The definition itself is simple:

  InnoDB tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value.

However, what confuses me is the relationship with the redo log.

Does "flush data" mean:

copy dirty pages from the log buffer to the redo log, or
from the redo log to the buffer pool file?

I can't imagine how case 1 can happen. Even in systems with innodb_flush_log_at_trx_commit = 0, logs are flushed one per second, so how can 90% of the buffer pool be changed in one second?

If the case is 2 instead (and this interpretation would be, I guess, (checkpoint age > total log size * pct), such high number of dirty pages don't fit in the redo log.

What am I misunderstanding?


texts: Understanding the recreation of index in Table partitioning

texts:
I need your help. I have[AdventureWorks2014] database and I wanted to perform partitioning on [Production].[TransactionHistoryArchive] table based on [TransactionDate] column. [Production].[TransactionHistoryArchive] table has primary key clustered PK_TransactionHistoryArchive_TransactionID. I preferred using  a wizard and at the end of wizard I a script below:

USE [AdventureWorks2014]
GO

BEGIN TRANSACTION

CREATE PARTITION FUNCTION [myFUNCTION](datetime) AS RANGE LEFT FOR VALUES (N'2011-12-31T23:59:59.997', N'2012-12-31T23:59:59.997', N'2013-12-31T23:59:59.997')
CREATE PARTITION SCHEME [mySCHEMA] AS PARTITION [myFUNCTION] TO ([DRIVE E], [DRIVE F], [DRIVE G], [DRIVE H])

ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]
ALTER TABLE [Production].[TransactionHistoryArchive] ADD  CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED ([TransactionID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

CREATE CLUSTERED INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive] ([TransactionDate])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [mySCHEMA]([TransactionDate])

DROP INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive]

COMMIT TRANSACTION

Please help me to clearly understand the index recreation and correct me if I am mistaken.

MY THEORY: For performing table partitioning, we need to assign clustered index to the [TransactionDate] column. In order to do it we need to alter primary key constraint [PK_TransactionHistoryArchive_TransactionID] from PRIMARY KEY CLUSTERED to PRIMARY KEY NONCLUSTERED. SQL does it with the help of the following query:

ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]
ALTER TABLE [Production].[TransactionHistoryArchive] ADD  CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED ([TransactionID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Now SQL can create clustered index for [TransactionDate] column:

CREATE CLUSTERED INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive] ([TransactionDate])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [mySCHEMA]([TransactionDate])

In the last part SQL drops the created index (did not understand this part. How come SQL creates and then immediately drops that clustered index?):

DROP INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive]


texts: Is there a way to run this query in all databases ? ( SQL Server 2008 R2 )

texts:
How can I use this query for each database?

I can easily use sp_msforeachdb but it breaks the results for every database. So I tried to insert the results in a temp table, but it's giving me syntax error.

It is a query to find a string inside the result set of a column. I have REDGATE SQL SEARCH but it only searches for objects. I need to find, inside every table, every column of every database a certain string.

This is the query:

DECLARE @SearchStr nvarchar(100) = 'SEARCH_STRING_HERE'
DECLARE @Results TABLE (TABELA nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @TABELA nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @TABELA = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                         QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@TABELA IS NOT NULL)

    BEGIN
        SET @TABELA =
        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE
                    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @TABELA
        )

        IF @TABELA IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @TABELA + ''', LEFT(' + @TABELA + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @TABELA + ' LIKE ' + @SearchStr2
            )
        END
    END

END

SELECT  distinct db_name(db_id()), * FROM @Results

EDIT1:

Just to show you, this is what I get with sp_msforeachdb ( I know it's a bad Idea to use this, if someone knows a better way to do this, I would be grateful):

I need something like a simple query result.

This is the query with sp_msforeachdb

exec sp_msforeachdb 'use [?]  ;
DECLARE @SearchStr nvarchar(100) = ''abortion''
DECLARE @Results TABLE (TABELA nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @TABELA nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''''
SET @SearchStr2 = QUOTENAME(''%'' + @SearchStr + ''%'','''''''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @TABELA = ''''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = ''BASE TABLE''
            AND    QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                         QUOTENAME(TABLE_NAME)
                         ), ''IsMSShipped''
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@TABELA IS NOT NULL)

    BEGIN
        SET @TABELA =
        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE
                    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'', ''int'', ''decimal'')
                AND    QUOTENAME(COLUMN_NAME) > @TABELA
        )

        IF @TABELA IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                ''SELECT '''''' + @TableName + ''.'' + @TABELA + '''''', LEFT('' + @TABELA + '', 3630)
                FROM '' + @TableName + '' (NOLOCK) '' +
                '' WHERE '' + @TABELA + '' LIKE '' + @SearchStr2
            )
        END
    END

END

SELECT  distinct db_name(db_id()), * FROM @Results'


texts: Update statement is giving an invalid column name error, even though the column exists

texts:
I have a temporary table created at the beginning of this stored procedure. It is created successfully and can be selected from and inserted to. Here is the create statement

CREATE TABLE #tmpImportData
( GuideFirstName VARCHAR(MAX),
  GuideLastName VARCHAR(MAX),
  email VARCHAR(MAX),
  group_id_text VARCHAR(MAX),
  CandidateName VARCHAR(MAX),
  grade_text VARCHAR(5),
  dateofbirth DATE
)

My problem is trying to update a column after I alter the temporary table. I get the error:

  Msg 207, Level 16, State 1
  invalid column name

Code:

declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData
ADD group_id INT
ALTER TABLE #tmpImportData
ADD guide_id INT
ALTER TABLE #tmpImportData
ADD password_plain_text VARCHAR(500)
ALTER TABLE #tmpImportData
ADD guide_email VARCHAR(500)
ALTER TABLE #tmpImportData
ADD class_id INT'

exec sp_executesql @Sql1

UPDATE #tmpImportData
SET group_id = CAST(group_id_text AS INT)

UPDATE #tmpImportData
SET group_id = 0 WHERE group_id IS NULL

Solution: NOT THE SOLUTION ANYMORE, IT DOESN'T WORK ANYMORE

declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData ADD group_id INT
ALTER TABLE #tmpImportData ADD guide_id INT
ALTER TABLE #tmpImportData ADD password_plain_text VARCHAR(500)
ALTER TABLE #tmpImportData ADD guide_email VARCHAR(500)
ALTER TABLE #tmpImportData ADD class_id INT'

exec sp_executesql @Sql1
WAITFOR DELAY '000:00:05'

UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT)
UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL

What I thought would be better but still throws the invalid column name error Msg 207, Level 16, State 1, Line 2
Invalid column name 'group_id'.:

  declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData ADD group_id INT, guide_id INT, password_plain_text VARCHAR(500), guide_email VARCHAR(500), class_id INT; UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT);
UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL; '

exec sp_executesql @Sql1
WAITFOR DELAY '000:00:05'

--UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT)
--UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL

SELECT * FROM #tmpImportData


texts: Why does an AS cube rebuild use so much TempDB?

texts:
I have a 50GB SQL 2008 database (only 36 of that is data) with a 24GB Analysis Server cube that rebuilds nightly.

The cube rebuilds have been using more and more of the TempDB on the SQL server each night. Last night TempDB data files grew to about 100GB before using up all space on the TempDB drive.

I can add more space, but I'd feel better if I understood more about what is going on here:

How/why do cube rebuilds use TempDB space on the SQL server?
Is there any way to reduce the amount of TempDB that it uses, or force it to "release" tempdb between steps, or something?
Is there any way for me to estimate the amount of space it might continue to use as my data continues to grow? (based on the size of certain key tables in my database, or something?)

Edit: Only significant difference in this particular database that I can think of is that they use some very large, very complicated nested views for their final data set. If these complex views are the ones being used to build the cube, could that explain the high tempDB use?


texts:
Databases use the file system to store the data.
As far as I know it is not possible to delete a record from a random access file.
So does that mean that when we do a DELETE FROM TABLE the size of the table i.e. the file that stores the table never decreases?
So databases essentially keep growing and never reduce in size?


texts: How are page splits determined internally in MySQL?

texts:
Going through this answer for a stack-overflow question, I found a fact that

  In The physical structure of InnoDB index pages I describe the Last Insert Position, Page Direction, and Number of Inserts in Page Direction fields of each index page. This is how the tracking for ascending vs. descending order is done (as left vs. right, though). With each insert, the last inserted record is compared to the currently inserted one, and if the insert is in the same "direction", the counter is incremented. This counter is then checked to determine the page split behavior; whether to split in half or create a new, empty page

It is understood that page split up is based on Number of Inserts in Page Direction

But , I wonder whether there will be threshold for "Number of Inserts in Page Direction" to initiate page split up?  Any particular logic behind setting "Page Direction" flag?

I am curious in getting these concepts with a working example with asap details!!! All efforts welcome.


texts: Oracle Stored Procedures / schema maintenace but still keep service online, how?

texts:
I got a service running on a classic 3 tier model: 2 web servers and 2 application servers connecting to a single Oracle database instance.  Most of the business logic are written as stored procedures in the Oracle DB.  The problem I am facing is that the service has to be taken down to update the stored proc each time bugs need to be fixed or new features have to be introduced.

I'm just wondering if there is any way to keep the service running while we are updating the stored proc.  Approaches involving getting new hardware / software are also welcome.


texts: Compare the hash password value in mysql.user

texts:
Say I have a password "Foobar1234". In MySQL database (5.6), the hash value is

*59C0D3B7983F06CF32F555AD04BAC66BCF952597

I want to try this outside of MySQL database so the SQL query isn't logged. Is the hash function known? I am using the default settings.

Here is the doc: https://dev.mysql.com/doc/refman/5.6/en/password-hashing.html


texts:
Can I use truncate command with a where clause?  I need to remove specific rows from several tables.

How can I delete specific data from the entire database?

SELECT DimEmployee.[FirstName], DimEmployee.[LastName], [SalesOrderNumber], [ShipDateKey]
    FROM DimEmployee
    JOIN [FactResellerSales]
        ON DimEmployee.[EmployeeKey] = [FactResellerSales].[ProductKey]
    WHERE DimEmployee.[FirstName] like 'kevin%' <--have to truncate this specific name from entire DB

Is there any other method to remove a specific data from entire DB?

In my database there are 172 tables. I wanted to delete a specific name and its corresponding columns from the entire database. The name is spread across entire database, hence I want to remove it in a single shot instead of going to each table and deleting it individually.


texts: Moving all Heap Tables to another Filegroup in SQL Server

texts:
In SQL Server 2017, I want to move ALL Heaps in a DB to a secondary filegroup. What's the easiest way to do this?

This question is only for Nonclustered Indexes.

https://stackoverflow.com/questions/4237238/moving-all-non-clustered-indexes-to-another-filegroup-in-sql-server

In the process of optimizing an older legacy database.

I want to write a script to move all heap tables with a cursor loop similar to the Stack Overflow link above.


texts:
I have a really large MySQL table counting more than 12 billion rows at the moment (table scheme: InnoDB). From time to time the problems get more and more. The biggest problem is that altering the table takes a really long time (more than 48h) and every minute downtime is one minute too much. So whenever I need to add a new index or add a new column, the problem with the altering is there.

Are 12 billion rows too much for MySQL to handle? What would you suggest to do?


texts:
Is it possible to update the data directly inside the backup file (*.bak) in SQL Server 2014?

The scenario is that I want to automate the process of providing the "smaller" database to some endusers/developers (just deleting some rows/updating values in column) to be restored by them furthermore. Can I achieve this goal by running any command to directly update the *.bak ... or I have to prepare additional (copy of) database, then restore it from this *.bak, then provide my "cleaning" (running update command) and then prepare different *.bak file again?


texts: Where is the database SERVICE NAME defined and what is the proper method for changing it?

texts:
I'm researching the connection methods for an Oracle 12c database and I understand the difference between an SID and Service Name, but I cannot figure out where the service name is defined or how to change it.

Presumably it was named the same as the SID I typed in during installation, but can it be changed post-installation?  Is there a configuration file where that is done?

Thanks in advance!


texts:
Postgresql 9.6, if it matters.
We have an audit finding which says that the "postgres" role must not be used at all.
After creating a new SUPERUSER role, is it possible to disable role "postgres" (or do some jobs like autovacuum/autoanalyze require it)?  If they are the only ones which need it, I could disable autovacuum/autoanalyze and rely on manual vacuums and analyze.


texts: Avoid Vacuum (to prevent wraparound) on read-only databases

texts:
We use a Postgresql 10 database server to import nearly 1 million user files every day.
To keep the databases easier to handle (backup, maintenance) we create a new database every week.
The database from the previous weeks are vacuumed, backuped and no further write operations are performed on them.
In total we have 17 databases with > 7 million imported  files (500GB) in each.
That are 120 million files which leads to 240 million transactions in total.
(To import one file we need two transactions.)

Some days ago, we detected that Postgresql starts the autovacuum process with the ‘prevent wraparound’ option on the oldest databases.
Postgresql starts only those vacuums and not the occasional vacuums (clean-up and maintenance tasks) to analyze the current week's database.
Due to the additional IO load, we have performance problems with select statements on the current database.

Is there a way to prevent this behavior on the old databases?
We tried a vacuum freeze to reset the relevant counters but they start growing immediately when our import is running.

We fetch these counters with the following query:

SELECT
       oid::regclass::text AS table, --relfrozenxid,
       age(relfrozenxid) AS xid_age,
       mxid_age(relminmxid) AS mxid_age,
       least(
           (SELECT setting::int
            FROM    pg_settings
            WHERE   name = 'autovacuum_freeze_max_age') - age(relfrozenxid),
           (SELECT setting::int
            FROM    pg_settings
            WHERE   name = 'autovacuum_multixact_freeze_max_age') - mxid_age(relminmxid)
        ) AS tx_before_wraparound_vacuum,
pg_size_pretty(pg_total_relation_size(oid)) AS size,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum
FROM    pg_class
WHERE   relfrozenxid != 0
AND oid > 16384
ORDER BY tx_before_wraparound_vacuum;

Edit

After further investigation, we have read about visibility maps in the 10.x documentation.
A freeze run does not touch the XID but set a flag in this map for indication.
So we hope that any old database, where the wraparound or freeze vacuum was running, is never touched again by any autovacuum in the future.