Is content of the pg_autovacuum saved when pg_dumpall is used to backup the database? From the docs:
pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not     save these objects.) This currently includes information about database users and groups, tablespaces, and properties such as access permissions that apply to databases as a whole.
Since pg_dumpall reads tables from all databases you will most likely have to connect as a database superuser in order to produce a complete dump.

I would assume yes, but I am not completely sure.

pg_dumpall is simply a wrapper around pg_dump. It notes farther down in the documentation for the -n --schema flag that it only dumps non-system schemas.

  Dump only schemas matching schema; this selects both the schema itself, and all its contained objects. When this option is not specified, all non-system schemas in the target database will be dumped.

The system level things that pg_dumpall dumps instead of pg_dump are listed at the top of the pg_dumpall documentation, as you quoted above, which doesn't include a mention of system catalog tables.

So it shouldn't actually dump out the contents of pg_catalog.pg_autovacuum.

Which is actually a good thing. The system catalog tables are an intimate part of each PostgreSQL database, and automatically generated for you when you do a CREATE DATABASE. Their entries generally only make sense within the context of the database they were created in.

Additionally, pg_catalog.pg_autovacuum was last seen in the 8.1 to 8.3 releases of PostgreSQL, all of which have been unsupported since at least February 2013, so hopefully this is part of a migration path to a newer supported release.


Why $group is very slow in aggregation and how can faster it in MongoDB My query is taking ~3 seconds to execute with the $group stage, How can I faster this?

My query is:
db.getCollection('employee_data').aggregate([
  { $match: //Filter
  },
  {
   $group:
   {
     _id: {
       employee_id:"$employee_id",
       activity_date:"$activity_date"
     },
     trips: { $sum: 1 }
   }
  }
])
Data size : 4M records

As per MongoDB Documentation here Groups documents by some specified expression and outputs to the next stage a document for each distinct grouping. The output documents contain an _id field which contains the distinct group by key. The output documents can also contain computed fields that hold the values of some accumulator expression grouped by the $group’s _id field. $group does not order its output documents.

The $group stage has the following prototype form:

{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... } }

The _id field is mandatory; however, you can specify an _id value of null, or any other constant value, to calculate accumulated values for all the input documents as a whole.

$group Operator and Memory

The $group stage has a limit of 100 megabytes of RAM. By default, if the stage exceeds this limit, $group will produce an error. However, to allow for the handling of large datasets, set the allowDiskUse option to true to enable $group operations to write to temporary files.

As per blog of @VladMihalcea here the  MongoDB aggregation framework is extremely useful and its performances can’t go unnoticed.that didn’t require any extra optimization, aiming to demonstrate the out-of-the-box performance of MongoDB.

For further your ref here


SQL query using CTE not working after database migration We have recently migrated an application, and in the process we have migrated from SQL Server 2005 to SQL Server 2008 R2.

I have noticed that some SQL queries using CTE do not work anymore since.

I have tried simplifying the queries, but to no avail.
Even this following simple query (which gets the top parent in a hierarchy) ends up with the error message hereunder :

Code:

DECLARE @Companies TABLE (
      AccountId UNIQUEIDENTIFIER
      ,NAME VARCHAR(100)
      ,ParentAccountId UNIQUEIDENTIFIER
      )

INSERT INTO @Companies
SELECT
      AccountId
      ,NAME
      ,ParentAccountId
FROM AccountBase;

WITH CTE
AS (
      SELECT *
            ,0 [Level]
      FROM @Companies

      UNION ALL

      SELECT
            CTE.accountid
            ,CTE.NAME
            ,cmp.ParentAccountId
            ,LEVEL + 1
      FROM CTE
            INNER JOIN @Companies AS cmp ON CTE.ParentAccountId = cmp.AccountId
      WHERE
            cmp.ParentAccountId IS NOT NULL
      )
SELECT
      c.AccountId
      ,c.NAME
      ,c.ParentAccountId as TopParentAccountId
FROM (
      SELECT *
            ,MAX([Level]) OVER (PARTITION BY NAME) [MaxLevel]
      FROM CTE
      ) c
WHERE
      MaxLevel = LEVEL

Error message (msg 530):

  The statement terminated. The maximum recursion 100 has been exhausted
  before statement completion.

What could be possible reasons ?

Thanks in advance.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

The obvious reason for this error is that the query needed to do recursion of more than 100 levels. So you can try increasing the default of 100 to higher (say 1000) or to no limit with OPTION (MAXRECURSION 0).

Another possibility is to have "bad data" (i.e. cycles in the id - parent_id adjacency list relationship) and not well written recursive CTE that falls into an infinite loop.

For the specific query, a cycle on the id - parent ids, say (AccountId, ParentAccountId) with  values (1,2), (2,3), (3,1) will cause an infinite recursion loop.

I suggest you check the data in the table for cycles in the id - parent_id relationship and correct the query to avoid infinite recursion in any case this happens again.


Restore database according to editions I wanted to know that, can I restore backup of SQL Server 2016 Standard edition  database into SQL Server 2016 Express edition database.

Or vice-versa

Sometimes:

When restoring into Express Edition, the database size can't be larger than the size supported by Express (which varies by version)
When restoring into Express or Standard Edition from Enterprise or Developer Edition, if your database uses Enterprise-level features, the recovery portion of restore won't succeed (the Enterprise features vary starting with 2016 SP1, which put more features in Standard)
When restoring into any server, some features need to be enabled on the database server itself. Examples include replication, full text search, CLR.


OPTIMIZE FOR UNKNOWN in a Stored Procedure All the examples I've seen online imply that OPTIMIZE FOR UNKNOWN is applied on a specific query.

Can OPTIMIZE FOR UNKNOWN be applied to a stored procedure as a whole (and not just to a specific query)? If yes, what would be the syntax?

Not exactly, no.

OPTIMIZE FOR is a statement-level directive and as such, it can only be applied to statement-level queries - there's no way to apply an OPTIMIZE FOR directive directly to an entire stored procedure.

However, there is a directive available for stored procedures which may produce the results you are looking for.  First, let's consider what OPTIMIZE FOR actually does.

When you use the OPTIMIZE FOR hint ( and that's what it is at the end of the day, a query plan hint, not entirely different than going out of your way to suggest a HASH JOIN ), you're dictating what the optimizer should do and that can come with a lot of baggage you might not be interested in carrying.  This is not to say "don't do it," but merely as a caution to be sure you know the implications of going down this path.  In the specific case of OPTIMIZE FOR UNKNOWN, you're telling the optimizer to build a query plan for the statement that will be suitable for any parameter that is passed.  When included in a stored procedure, the stored procedure's query plan retains the generalized plan of the statement.  Once complied and executed, the plan is stored in the plan cache and will be reused for subsequent executions, even if the optimizer might have been able to produce a better plan for a different parameter set, through removing the optimizer's ability to regenerate the plan.

Sometimes, the stored procedure is called very frequently and this technique can improve performance by letting the optimizer know it doesn't have to worry about generating plans for different parameter sets, allowing it to skip recompiling and use the "good enough for whatever" plan to produce results reasonably quickly.  More often than not, it is used to avoid the more nefarious effects of parameter sniffing.

CREATE PROCEDURE dbo.SomeProc
    @Name                       sysname
AS BEGIN
    SELECT  TOP 10 *
    FROM    sys.objects
    WHERE   name = @Name
    OPTION ( OPTIMIZE FOR UNKNOWN );
END;
GO

If the need to tune a procedure falls into the realm of eliminating parameter sniffing, another technique which can be applied - on the entire stored procedure level, if desired - is the RECOMPILE option.  This option is basically the absolute opposite approach from OPTIMIZE FOR, which instead of caching a general version of the query plan which can be reused later, tells to optimizer to go ahead and generate a new query plan every single time the procedure is executed, allowing the optimizer to produce an optimal plan for each of those executions.   Generating query plans is CPU intensive and can be quite expensive ( you usually -want- the plan in the plan cache ), but if the stored procedure isn't executed particularly often and / or involves dynamic SQL, using the RECOMPILE option can be a legitimate tactic.  This can be helpful in specific cases where CPU is cheaper than I/O as well, making the processor work harder to alleviate a little disk contention.

CREATE PROCEDURE dbo.SomeProc
    @Name                       sysname
WITH RECOMPILE
AS BEGIN
    SELECT  TOP 10 *
    FROM    sys.objects
    WHERE   name = @Name;
END;
GO

You can use OPTIMIZE FOR ( @variable_name = UNKNOWN ) at the statement level or the old-school method of re-declaring parameters as local variables and then using those throughout the procedure ( SET @p_Name = @Name;, WHERE name = @p_Name; ), both which direct the optimizer to use statistic densities instead of statistic histograms, but often it's better to just let the optimizer be and turn your attention to where the issue frequently is:  The plan cache itself.  Clearing the plan cache of a specific stored procedure that is giving you grief will often alleviate any immediate problems and may even buy you time to properly analyze and address why your stored procedure is suffering from desperate performance, without resorting to OPTION-related measures.

SELECT  ProcName = OBJECT_NAME( st.objectid ),
        PlanHandle = cp.plan_handle
FROM    sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text( cp.plan_handle ) st
WHERE   cp.objtype = 'Proc'
    AND st.objectid = OBJECT_ID( 'dbo.SomeProc', 'P' );
GO

DBCC FREEPROCCACHE ( <PlanHandle from above results> );
GO


SQL Server 2016 expiration date I have a question about the installation of SQL Server 2016.

We Installed a trial version tha was made on 2017/09/19

According to the script that I execute in the database, the expiration date was 2018/03/18 but we can still use it without problems.

The Sharepoint site that uses this DB does not give any errors or warnings legends that it is expired or that some features are no longer available. I can also enter from MSQLServer Management Studio and execute scripts, create new DB, etc.

How can exactly know the expiration date?

SELECT
@@SERVERNAME as SERVERNAME,
CREATE_DATE as INSTALALTIONDATE,
SERVERPROPERTY('EDITION') as Version,
DATEADD(DD, 180, CREATE_DATE) AS "EXPIRY DATE"
FROM SYS.SERVER_PRINCIPALS
WHERE SID = 0X010100000000000512000000

SERVERNAME      INSTALLATIONDATE        Version                                 EXPIRY DATE
SAHARCVSPS02    2017-09-19 13:15:20.967 Enterprise Evaluation Edition (64-bit)  2018-03-18 13:15:20.967

SQL Server doesn't stop the service when it hits the expiration. Instead, it is checked when the service starts. If you restart the service, or reboot, or your system crashes, when it tries to come back up, it won't start.

So I'd take your backups, clean SQL Server off the machine entirely (since other components like SSMS might still give you expiration issues) and fix this - before you have to restart the service or reboot and you're not prepared.

David points out that upgrading from eval to full license is supported, but I avoid that because I’ve had more than one experience where that definitely did not go smoothly. Hopefully this isn’t production, where eval should never have been used in the first place, but in any case I think it is cleaner and safer to nuke and pace than try an in-place upgrade. Those have a variety of potential pitfalls even when an expired eval edition isn’t part of the mix.

I have a post about fully removing evaluation edition, but please don't start this process until you've validated your backups are all good and you've scripted logins, jobs, linked servers etc.

Fun with software : uninstalling SQL Server 2008 R2 Evaluation Edition

The actual date isn't important now, IMHO. If you have sour milk in the fridge, it's sour, and you should throw it away, whether it expired on Easter or on Christmas. Also note that some components may expire sooner, e.g. if you install SSMS along with one instance of eval edition, then install another instance of eval edition later, SSMS will expire with the first one, not the second.


Is the foreign key automatically indexed in MySQL? Do we need to create an index for a foreign key while creating the table in InnoDB of MySQL?

Foreign keys are not automatically indexed in MySQL*.  If you should index, the answer is always: "It depends."
If you are going to primarily search based on that column, it's more than likely it should form the first part of that table's primary key and there would be no need for an additional index.
If the values of that column are not particularly selective, adding an index won't improve performance as a table scan will still be required.
So as always, test and validate.
*This depends on where you are expecting the foreign key to be created, on the table with the FK constraint or the table referenced by that constraint.  Most RDBMs follow the relational model, you can only create a FK reference to a primary or candidate key in another table (in practical terms an index would already exist).  MySQL does not have this restriction and will automatically create an index on the referenced column (or use an existing index if the column(s) referenced comprise(s) the start of that index).


Database convert character_set_server latin1 to utf8 I am writing about a problem on the character encoding that i have on my site with Magento. In practice, the special characters do not take utf8 and gives me the classic non-coded characters: Ã.

These are the codes in my database:

character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8

My question is ... the problem of encoding is caused by character_set_server latin1 string? How do I convert my database character_set_server latin1 to utf8? It can be a very simple Query?

Thanks so much

character_set_server is not relevant.  The setting on the database is just a default for when you CREATE TABLE without specifying a DEFAULT CHARACTER SET.

You need

The bytes in your client to be encoded utf8.
The connection specifies utf8.  If you are using mysqli, use mysqli_set_charset('utf8').
The column in the table must be CHARACTER SET utf8.  Look at SHOW CREATE TABLE; if it is not explicit on the column then look at the default for the table.
If you are doing html output, then it needs <meta charset=UTF-8>.


Best practice around sys.dm_db_missing_index_group_stats in SQL Server I have a fairly simple query using sys.dm_db_missing_index_group_stats that identifies missing indexes in my SQL Server database. This is pretty commonly used. My question is how do I identify which of these indexes are important and should be created and should I just create them all or are there any other secrets? Anything I specifically should look for in the avg_user_impact or avg_total_user_cost columns?

Kimberly Tripp did a few very good video's on Index Analysis that covered your question extremely well as part of the MCM Readiness videos.

Indexing Strategies Demo
Indexing Strategies
Index Analysis

Among other things she said DO NOT just create all of them.  Review each one and compare it to your existing indexes, review it's cost etc.  I highly recommend them and best of all they are free!


How do I get Postgres DO $$ to work from the bash prompt? Postgres 9.6.9 on Linux RHEL 6.10

If I surround my DO command in double-quotes and the roles in single quotes, then bash interprets the $$, and that obviously makes the command fail.  So I switched the double- and single-quotes, but now it treats the NOT IN string literals as column names, which also throws an error.

So, as mentioned in the title, what's the magic sauce for getting a DO $$ command to work from the command line?

$ export PGHOST=10.x.y.z
$ export PGUSER=postgres
$ psql -c 'do $$
>          declare rolename text;
>          begin
>            for rolename in select rolname
>                            from pg_roles
>                            where rolname not in ("postgres",
>                                                  "pg_signal_backend",
>                                                  "TAP")
>            loop
>            execute "DROP ROLE " || rolename;
>            end loop;
>          end $$
>          ;'
ERROR:  column "postgres" does not exist
LINE 3:                            where rolname not in ("postgres",...
                                                         ^
QUERY:  select rolname
                           from pg_roles
                           where rolname not in ("postgres", "pg_signal_backend", "TAP")
CONTEXT:  PL/pgSQL function inline_code_block line 4 at FOR over SELECT rows

Thanks

Using \ to escape the dollar $

echo \$PATH
$PATH

Reference to psql doc, in order to execute multi commands, they also provide 3 ways to work around as below

1) Repeats -c option

psql -c 'SELECT now()' -c 'SELECT * FROM foo;'

2) Combines echo and psql

echo 'SELECT now() ; SELECT * FROM foo;' | psql

3) Uses psql and EOF

psql <<EOF
\x
SELECT now();
SELECT * FROM foo;
EOF

Besides, considering changing rolename text to rolename RECORD to avoid ERROR:  missing FROM-clause entry for table (record type)

  Record variables are similar to row-type variables, but they have no
  predefined structure. They take on the actual row structure of the row
  they are assigned during a SELECT or FOR command

In terms of your case, please careful with DROP ROLE due to dependencies (refer: dropping role)

  If DROP ROLE is attempted while dependent objects still remain, it
  will issue messages identifying which objects need to be reassigned or
  dropped.

Last but not least, here's my example

#### Using psql and EOF
psql << EOF
DO \$$
DECLARE
  v_role record;
BEGIN
  for v_role in select rolname from pg_roles where rolname not in ('postgres', 'pg_signal_backend', 'TAP')
  loop
    raise notice '%', v_role.rolname;
    execute 'DROP ROLE ' || v_role.rolname;
  end loop;
END
\$$;
EOF

#### Using echo and psql
echo "
DO \$$
DECLARE
  v_role record;
BEGIN
  for v_role in select rolname from pg_roles where rolname not in ('postgres', 'pg_signal_backend', 'TAP')
  loop
    raise notice '%', v_role.rolname;
    execute 'DROP ROLE ' || v_role.rolname;
  end loop;
END
\$$;
" | psql


ON CONFLICT on two columns where one can be null I'm trying to use ON CONFLICT on two columns where one can be null. Unfortunatelly with partial index I don't seem to be able to do it.

create table tbl( col1 int, col2 int, col3 boolean);

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NOT NULL; -- NOT NULL

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NULL;     -- NULL

INSERT INTO tbl(col1, col2)
values(1, 3)
ON CONFLICT(col1, col2) DO UPDATE SET col3 = true;

  ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

Because PostgreSQL can not infer it from the values, you need the index_predicate

  index_predicate
  Used to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred. Follows CREATE INDEX format. SELECT privilege on any column appearing within index_predicate is required.

Sample Data

CREATE TABLE tbl( col1 int, col2 int, col3 boolean);

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NOT NULL; -- NOT NULL

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NULL;     -- NULL

INSERT INTO tbl(col1, col2)
VALUES (1, 3, false); -- set value to false

Test

INSERT INTO tbl(col1, col2)
VALUES (1, 3)
ON CONFLICT(col1, col2)
WHERE col2 IS NOT NULL  -- predicate here
DO UPDATE SET col3 = true;

TABLE tbl;

 col1 | col2 | col3
------+------+------
    1 |    3 | t
(1 row)


Indexes on XtraDB tables I have old db with myisam tables, using mariadb and want try move to xtradb for row-level locking. Each table have 27 columns, primary index and other 11 indexes. Columns with indexes have many selects with where, group and order. I make some test and I'm little confused.

Table with about 6M rows - disk space (data + keys):
myisam - 915MiB
innodb (xtradb) - 2011MiB

On innodb(xtradb) I try large select with many where, group and order on indexed columns and it takes 63s, but if I delete all other indexes - only primary stay, then it takes 21s.

It looks like if I don't use transaction, foreign keys, joins, etc...on this tables, that primary key is fine and all others is useless. But I think this is very strange :-/

I don't have hardware for better test, are you guys think it's good idea to convert all tables to inno(xtra) and drop all indexes except primary in production environment?

You should index as per usage. If you drop all indexes for the benefit of a small % of your workload you will hit pain elsewhere. Run pt-duplicate-key-checker 1st and remove the redundant keys. Then review your queries with pt-query-digest and weed out the poorly performing queries by reindexing them or rewriting them. You could paste the output of EXPLAIN with your table structure and status for your problematic query for further help tuning on that.


microsoft webmatrix deleted my wamp databases? I've been using WAMP server for a while and I have several databases on the mysql server.

today I installed microsoft webmatrix and when I opened one of my sites on wamp I got a mysql error saying the connection has failed, and when I try to open the phpmyadmin it says access denied.

#1045 - Access denied for user 'root'@'localhost' (using password: NO)

I guess it somehow changed the password for my user, so how can I reconnect to wamp's phpmyadmin and restore my old databases?

Currently I can connect to the server with the password ' ' (space) but my databases are gone.

EDIT: ok I changed the password back to '' (null) using an sql command

SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

is it possible to restore the old dbs?

Here is the only thing I can advise for now:

Open a DOS Window and run the following

C:\> dir mysql.user /s

If you see two mysql.user files, then you may have two DB setups.

You would just have to point datadir in my.cnf back to the old setup.

Please find out and let me know ...


Truncation error creating clustered index on schema-bound view I was just creating a schema-bound view that I wanted to put an index on (to try out some computed column variations).

I created the view WITH SCHEMABINDING, then I had to create a clustered index before I could create my other non-clustered ones.

The underlying table has an INT primary key column, so I was going to make the view's clustered index based on that.  So I ran

Create UNIQUE CLUSTERED INDEX [cix_viewEvents_EventID] ON [dbo].[viewEvents] (EventID)

Much to my surprise, after 4-5 minutes I got a

Msg 8152, string or binary data would be truncated

error.

The only column in the index is an int; what could be getting truncated?

I couldn't find anything relevant on google.

EDIT:

The original table is like this,

CREATE TABLE [dbo].[Events](
[EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[EventTitle] [varchar](max) NOT NULL,
[EventContent] [varchar](max) NULL,
[EventDate] [datetime] NOT NULL,
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

while the view I created looked like this

CREATE VIEW dbo.viewEvents
WITH SCHEMABINDING
AS
SELECT EventID, EventTitle,
EventContent, EventDate, HASHBYTES('SHA1', EventContent) as ContentHash, HASHBYTES('SHA1',
EventTitle) as TitleHash
from dbo.Events;

I was hoping to speed up comparisons on the title and content by having hashes to compare against instead of big blobs of text.

Rather than add the computed columns straight to the table, I thought I'd try the schema bound view first, but I ran into the "you need a clustered index on the view before you can create non-clustered indexes on it".  So I tried to create a clustered index, as noted above.

We're talking an int in both places, but creating the clustered index on the view  bombs with a truncation error, which doesn't seem to make sense.

The underlying table has an INT primary key column, so I was going to
  make the view's clustered index based on that.

Here's what Microsoft says about Create Indexed Views:

  Creating a unique clustered index on a view improves query performance
  because the view is stored in the database in the same way a table
  with a clustered index is stored.

Since a clustered index is composed by data rows themselves your assumption that "the only column in the index is an int" is incorrect for, even though only one column was listed on the DDL, the clustered index view shall contain all columns under the hood. If it was a Nonclustered index, then only the listed columns would compose the index.

With the DDL you provided I was able to run some lab tests and the following chart from SSMS shall ilustrate my point:

As you can see, the view has pretty much the same size of the table. And before the index was created the view wouldn't even show on the chart. If it was formed only by the int column as you thought, it would have a much smaller size.

That info along with the contribution of SMor about the limitation of HASHBYTES Arguments for SQL Server 2008:

  For SQL Server 2014 (12.x) and earlier, allowed input values are
  limited to 8000 bytes.

In my lab I generated some data that was greater than 8000 bytes, but since I'm running a SQL Server 2019 instance, the DDL executed without any error. You can verify if you have any values that would exceed that limit with the following query:

SELECT COUNT(1)
FROM [dbo].[Events]
WHERE DATALENGTH([EventTitle]) > 8000
    OR DATALENGTH([EventContent]) > 8000;


Insert bulk Parent and Children rows using SQL Query We receive bulk data from our customers in a spread sheet. I loaded them in a temporary table as of now. I tried to normalize the data and create a parent table and each parent to have 4 or 5 child rows. Is there a way to insert all the parents and their children using queries? I am using MSSQL

Once you have normalised you need 2 queries, one to load parents then one to load children (you can only insert into 1 table at a time using sql)

It sounds like you do this ofter, so why not create a file with the sql commands that you use, and each time you do this task open the file up in your databases sql editor window and run (may-be first modifying) these commands. If it always the same sort of task, you could write a script that connects to the database and runs the sql statments you need - then it is a one click or command task.


SQL Server 2008 R2 - VLDB Backup Strategies I have a 200GB database running on SQL Server 2008 R2 Enterprise Edition.  I just recently partitioned it in order to archive 11 months of history, so it was pushing 400GB prior to archiving.

Our company has 2 data centers in order to implment DR solutions.  Most of the databases are at DC1 where there is tape backup infrastructure, where DC2 doesn't have tape backup capability.

This VLDB of 200GB resides at the DC2 location with no tape backup infrastructure.  Our server team stopped doing tape backups about a year ago when the database .bak file exceeded 90GB and became too large to snap back to DC1 to backup to tape.  I keep 2 disk copies of the .bak file, but still am concerned if a recovery is needed that requires going back more than 2 days.

I am looking for solutions.  I thought I could setup a log shipping solution from DC2 to DC1, then each night stop the log shipping and run a backup of the secondary standby, but am concerned it could through the ability to start log shipping into a tiz.  I have been doing a lot of research and see that it could cause a problem, but if a standby can be connected to and read from, you'd think a backup could be done.

The database grows by 30GB a month with a daily average of 1-2GB a day, so the traffic to log ship from DC2 to DC1 should be manageable.  It is a vendor supplied database, so we try very hard not to modify it to support our environment, although, I did just partition it with the vendor's approval.

Anyone have any ideas of how best to save .bak files to tape from a location without tape infrastructure?

Log shipping to the primary site then doing backups isn't going to work as you can't backup a database which is waiting for t-log backups.

With SQL 2008 your only option would be to backup to local disk, have the storage array (I'm assuming that you have a storage array in both sites that can replicate data between the sites) replicate the backup LUN to the array at DC1.  Then during the day once the backup has been fully copied over to DC1 snapshot the copy of the LUN at DC1, mount it to a server at DC1 and backup the database to tape.

Another option would be to upgrade to SQL Server 2012 and take advantage of AlwaysOn Availability Groups which would let you replicate the data to a server in DC1 then you can take your backups from the server in DC1.

A third option would be to buy more storage at DC2 and keep more than two backups around.

A fourth option would be to move the application to DC1 and run it from there.

A fifth option would be to buy a tape backup unit for DC2.


Conversion failed when converting date and/or time from character string I have a problem with this query:

SELECT @dateforaftersixmonth=U_PYMON FROM [@CCS_PYRUNPERIODS]
    WHERE @STRTEMP_DATE between U_BEGDA and U_ENDDA

@STRTEMP_DATE is a datetime variable and @dateforaftersixmonth is a string. U_BEGDA and U_ENDDA are datetime fields and U_PYMON is varchar.

But when I execute this query I get:

  Conversion failed when converting date and/or time from character string.

And when I execute this query separately I can't find any error and I got the answer. Here @STRTEMP_DATE comes is 'Mar  1 2013 12:00AM'.

@STRTEMP_DATE is a datetime variable and @dateforaftersixmonth is a
  string. U_BEGDA and U_ENDDA are datetime fields and U_PYMON is
  varchar.

The error indicates that there is an implicit conversion from string to date/time. As you stated the variables and fields here, there is no implicit conversion. Since I trust the error message more than I trust you, I must conclude that you did not stated the variable and column types correctly and at least one of the following is true:

@STRTEMP_DATE is a string and U_BEGDA  or U_ENDDA is a date/time column
@STRTEMP_DATE is a date/time variable and U_ENDDA is a string column
@dateforaftersixmonth is a string and U_PYMON is a date/time columns
@dateforaftersixmonth is a date/time and U_PYMON is a string column

One of the above must be true for the conversion to occur and for the erorr message to appear. My money is on the @dateforaftersixmonth being a datetime and U_PYMON being a string (varchar or nvarchar). This also explains the aparent randomness of the error: it occurs only when you scan a range that contains values that cannot be converted.

Depending on which of the above cases happens, the solution is to appropiately modify the parameter/variable or the column to be of type datetime when storing dates/times.


Columnstore Aggregate Pushdown doesn´t work Recently I started investigating columnstore indexes for some warehouse-behaving part of our system.

What I can´t achieve yet is to make aggregate pushdown work. I've checked data types and all requirements, but still it doesn't work.
Just to be sure, I just ran query from this question and I see that execution plans for all data-types are exactly the same.

The version of SQL Server is SP1-CU5.

Do you know what else could I check?

update: I think I got it, I have Standard edition and this is one of standard edition limitations.

The answer is actually quite simple, I should've read more about limitations. The problem is that I have Standard edition and this is one of standard edition limitations.


Sort Order has been change due to DB server movement We are using SQL server 2012 (service pack 3) and we have recently moved from one server to another.
After that we have encountered issue SQL sort order problem. We have many report where the order by clause not present. After the movement, the sort order has been changed and the results in the report became different than the actual.

I know the solution by putting Order by clause in ALL stored procedure and View. Since the number of files are huge, it is there anything we can do in the SQL configuration to resolve the issue?

If you don't have an ORDER BY clause, the order of the result set is never guaranteed.

Connor Cunninghan No Seatbelt Expecting Order Without ORDER BY. In case it gets archived again
Aaron Bertrand showing another exmaple of this

Also, you mentioned "report". If you are using SSRS or any reporting suite really, they have sort functions within them as well which means you wouldn't have to change any of your procedures or views.


Using Workbench to export. Views no longer work I'm using Workbench 5.2 from my production DB machine (Ubuntu Linux).  I've been trying to export.  First try.. I had the entire schema selected including all tables and views in the right hand window.

When I imported this data into my test machine all the views came in as tables.  Obviously not what I want because they don't return any data now.... and it's now a table.

But this is where I'm confused.  I can see how to export just the tables.  But how do I get the views exported and into the test machine?

There's probably an error occurring somewhere in the process, because that would cause exactly what you're seeing.

Here's why:

When you define a view, the views or tables referenced by that view must already exist:

mysql> CREATE VIEW broken_view AS SELECT some_column FROM nonexistent_table;
ERROR 1146 (42S02): Table 'test.nonexistent_table' doesn't exist

For this reason, mysqldump generates a dump file that includes statements defining all of the views as equivalent(ish) tables, then goes back one by one and drops the tables and creates the views.  This avoids the problem that would occur if any view referenced another view, or referenced a table that hadn't been restored yet (tables are restored in alphabetical order).

The error could be while generating the dump file -- if any of your existing views are invalid -- for example, they reference tables or columns that don't exist, or reference DEFINER users that don't exist or don't have proper permissions, or if you don't have sufficient privileges -- their definitions can't be extracted, because SHOW CREATE VIEW only works on valid views.

If, by way of illustration, you create a view against a table, then drop the table, you'll find that you can no longer SHOW CREATE VIEW against that view.

If your views reference tables in a different schema that aren't on the destination server, you'd have a similar problem, because their definitions won't be accepted.

Being someone with a strong distaste for almost all graphical tools, I can't stand Workbench, but I believe it uses mysqldump under the hood, so this is likely to be the nature of the problem.

The export file should be human-readable.  If, at the end of the file, there are view definitions, then a problem is occurring when you're restoring the file.  If not, then you've got a problem preventing the export from finishing, and Workbench may not be showing you the error... but an incomplete export (or import) would be exactly the explanation for views being defined as tables on the destination server.


can I do a restore controlfile from autobackup out FAST RECOVERY AREA? I know that I can issue the command restore controlfile from "location_here". But there is some config that can I do for issue the command restore controlfile from autobackup even whether the autobackup of the controlfile is not in fast recovery area?

Why not?

From Database Backup and Recovery Reference 12c:

  The default location for the autobackup on disk is the fast recovery
  area (if configured) or a platform-specific location (if not
  configured). RMAN automatically backs up the current control file
  using the default format of %F. You can change the location and file
  name format with the CONFIGURE CONTROLFILE AUTOBACKUP FORMAT and SET
  CONTROLFILE AUTOBACKUP FORMAT commands.


Postgis reporting a point is 1216m away from itself using ST_Distance I have a table containing a geometry column in 4236 projection. I take the ST_X and ST_Y values from one row, and create a point from them. I then transform the point and the original geometry column value into 3857 projection to get a 2d distance between then using ST_DISTANCE. I expect the distance to be ~0. The answer I actually get it 1216.something metres.
Here is example code which creates two points, which should be identical, and takes the distance between them, giving the same ~1216m answer
select ST_Distance(
               ST_Transform(
                       ST_SetSRID(
                               ST_MakePoint(5.6100, 58.8900), 4236),
                       3857),
               ST_Transform(
                       ST_SetSRID(
                               ST_MakePoint(5.6100, 58.8900), 4326),
                       3857));

I'm presumably making some stupid mistake here (I am by no means a GIS expect), but what is it that I'm doing wrong?

The two points use different ellipsoids.
EPSG:4326 is WGS 84.
EPSG:4236 is Hu Tzu Shan 1950.


How to return values from "upsert" query in case when UPDATE has been performed? WITH upsert AS (UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.*)
INSERT INTO tbl (a)
SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert )
RETURNING *

This "upsert" statement works however I would like to retrieve either UPDATE or INSERTED values. When upsert performs INSERT (row is not yet in db) then inserted values are returned correctly returned from query.

However when UPDATE is performed then no values are returned (but row IS updated). I've tried adding RETURNING upsert.* but it yelds error missing FROM-clause entry for table \"upsert\".

You need to use a second CTE for the INSERT:

WITH upsert AS (
  UPDATE tbl SET a = 2
  WHERE a = 1
  RETURNING tbl.*
), inserted AS (
  INSERT INTO tbl (a)
  SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert )
  RETURNING *
)
select *
from upsert
union all
select *
from inserted


Is a Secondary Index in MySQL the Same Thing As a Non-Clustered Index in SQL Server? I am assuming that a secondary index in MySQL is the same thing as a non-clustered index in SQL Server.

Is this true?

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

  In InnoDB, the records in nonclustered indexes (also called secondary
  indexes) contain the primary key columns for the row that are not in
  the secondary index. InnoDB uses this primary key value to search for
  the row in the clustered index. If the primary key is long, the
  secondary indexes use more space, so it is advantageous to have a
  short primary key.

Could have just searched for this.


From a design perspective, what is the difference between an inventory system and an asset tracking system? From what I can see, the database design is similar.

One is the consumables that are resident in a warehouse (inventory).  The others are items, capital, that are assigned to people or places, but are not consumable (assets).

For database design, does that sound like the right track to start down?

LedgerSMB, which is my main project, does both.  I don't think we will merge these and the explanation why may give you an answer.

The difference you have to understand is that if you are trying to do both, you are probably trying to do accounting on both.  The accounting issues are different and require that different things are tracked.  There is room to centralize some information, but others can't be handled as well.

Let's look at the flow items through both systems.

You buy 10 widgets for inventory for $10 each.  What you have to track depends on how you value your inventory.  If this is FIFO or LIFO, you are going to have to track the financial aspect of the transaction ($100 debit in inventory, $100 credit in payables), and for average cost, you will have to track also the average cost per item.  As an item gets sold then you have to record revenue and receivables but you also move inventory value to your cost of goods sold account.  Modelling this process really defines a usable inventory system.

An asset control system is different.  Suppose you buy 100 desks at $50 each.  You then use these for a minimum of five years and depreciate using some method (many methods are acceptable) over that time.  This example will use straight-line.  To do this properly you also have to track date in service, estimated salvage value (in this case $0 if we intend to donate on disposal), etc.  Then each year we will have a transaction which partially moves that $5000 into expenses ($1000 per year using straight-line).  After that the asset will be fully depreciated but still in service and we will have no further transactions for the part until disposal.  When we dispose even if for $0 on a fully depreciated item, we will still have another financial transaction accounting for this.

I know in LedgerSMB, our asset management side is more complex than our inventory management side.

So your design has a lot less to do with the nature of what is being stored and a lot more to do with the business processes you have to support, the accounting requirements, and so forth.  These mean you have to track different information and expect different flows of stuff through your system.

Hope this helps.  One thing I would highly recommend is that you do one of two things, however.  Either hire a consultant (me! no conflict of interest there ;-) ) or pick up a book on accounting.  If you want to support accounting workflows you want to know the basics enough you can have an intelligent conversation with the folks from finance.  Domain knowledge here really is important because this is an area that looks really weird to the uninitiated.  You don't have to know everything but you should be able to flag issues to talk about with accountants.  I got into this through a book I picked up for $2 at a garage sale.  It's a vast field though and you want to know what human problems you are solving and what business processes are accepted.


Merge queries into one The following 2 queries are working as expected and they show the count.
Is it possible to merge them into single query?
I need sum_hits columns to be part of the rest of the column shown in the second query.

SELECT date_time, SUM(diac.requests) AS sum_hits
FROM ox_data_summary_ad_hourly diac
JOIN ox_zones z ON (z.zoneid = diac.zone_id)
JOIN ox_affiliates p ON (p.affiliateid = z.affiliateid AND p.agencyid = '3')
WHERE diac.date_time BETWEEN '2012-07-01 00:00:00' AND '2012-07-01 23:59:59'
AND p.agencyid = '3'
AND z.zone_type in ('application')
AND z.delivery in (8)
GROUP BY date_time

SELECT s.date_time AS date_time,
SUM(s.requests) AS sum_requests,
SUM(s.impressions) AS sum_views,
SUM(s.clicks) AS sum_clicks,
SUM(s.conversions) AS sum_conversions,
SUM(if(d.network_type like '%_user' and d.is_network=1,impressions,0)) AS sum_effimp,
SUM(s.total_revenue) AS sum_revenue,
SUM(s.total_revenue) AS sum_spend,
SUM(s.total_basket_value) AS sum_bv,
SUM(s.total_num_items) AS sum_num_items,
SUM(s.total_pubrevenue) AS sum_pubrevenue
FROM ox_data_summary_ad_hourly AS s
INNER JOIN ox_banners AS d ON (d.bannerid=s.ad_id)
INNER JOIN ox_campaigns AS m ON (m.campaignid=d.campaignid)
INNER JOIN ox_clients AS a ON (a.clientid=m.clientid)
LEFT JOIN ox_zones AS z ON (z.zoneid=s.zone_id)
LEFT JOIN ox_affiliates AS p ON (p.affiliateid=z.affiliateid)
WHERE a.agencyid = 3
AND z.zone_type = 'application'
AND z.delivery = 8
AND s.date_time>='2012-07-01 00:00:00' AND s.date_time<='2012-07-01 23:59:59'
GROUP BY date_time

SELECT B.*,A.sum_hits
FROM
(
    SELECT date_time, SUM(diac.requests) AS sum_hits
    FROM ox_data_summary_ad_hourly diac
    JOIN ox_zones z ON (z.zoneid = diac.zone_id)
    JOIN ox_affiliates p ON (p.affiliateid = z.affiliateid AND p.agencyid = '3')
    WHERE diac.date_time BETWEEN '2012-07-01 00:00:00' AND '2012-07-01 23:59:59'
    AND p.agencyid = '3'
    AND z.zone_type in ('application')
    AND z.delivery in (8)
    GROUP BY date_time
) A INNER JOIN
(
    SELECT s.date_time AS date_time,
    SUM(s.requests) AS sum_requests,
    SUM(s.impressions) AS sum_views,
    SUM(s.clicks) AS sum_clicks,
    SUM(s.conversions) AS sum_conversions,
    SUM(if(d.network_type like '%_user' and d.is_network=1,impressions,0)) AS sum_effimp,
    SUM(s.total_revenue) AS sum_revenue,
    SUM(s.total_revenue) AS sum_spend,
    SUM(s.total_basket_value) AS sum_bv,
    SUM(s.total_num_items) AS sum_num_items,
    SUM(s.total_pubrevenue) AS sum_pubrevenue
    FROM ox_data_summary_ad_hourly AS s
    INNER JOIN ox_banners AS d ON (d.bannerid=s.ad_id)
    INNER JOIN ox_campaigns AS m ON (m.campaignid=d.campaignid)
    INNER JOIN ox_clients AS a ON (a.clientid=m.clientid)
    LEFT JOIN ox_zones AS z ON (z.zoneid=s.zone_id)
    LEFT JOIN ox_affiliates AS p ON (p.affiliateid=z.affiliateid)
    WHERE a.agencyid = 3
    AND z.zone_type = 'application'
    AND z.delivery = 8
    AND s.date_time>='2012-07-01 00:00:00' AND s.date_time<='2012-07-01 23:59:59'
    GROUP BY date_time
) B USING (date_time);


Null in Varchar datatype I am using a SQL Server database. If NULL value is inserted to a VARCHAR datatype column, what will be the memory size used for the column?

Each variable-width column in a row will require a single bit to maintain the nullability status (this is so SQL Server can check this bit, rather than skipping down to the row and parsing the contents of that column). Of course you can't add a single bit to a row, you need to add a byte for this data, which means that if you have a single variable-width column, there is a byte of overhead, and that byte can be used to store the nullable status of up to 8 columns (I haven't tested if there are row configurations where multiple but < 8 variable-width columns may still use different nullable bytes).

Note: there is other overhead for variable-width columns, such as length, but I believe in general that that overhead is the same whether the column is nullable or not.


PostgreSQL: What is the best way to cast from from text to point[]? I'm looking for a way to cast from a text value:

'(180,90),(-180,-90)'

into a point[] value:

{"(180,90)","(-180,-90)"}

What is the best way to do this?

One possibility that I can think of is to manipulate the string to be of this form:

'{"(180, 90)", "(-180,-90)"}'

Which I can then cast to point[] like so:

select '{"(180, 90)", "(-180,-90)"}'::point[];

Surely there's a better way? The solution I currently have is this:

select concat('{', replace(replace(<text>, '(', '"('), ')', ')"'), '}')::point[]

Not any better than what you already got, but:

select string_to_array(replace('(180,90),(-180,-90)','),(',');('), ';')::point[];

If the separator token in the array would have been something else (say ";"), it would have been sufficient with:

select string_to_array('(180,90);(-180,-90)', ';')::point[];

EDIT:

If I get it right you have a foreign table like:

create table mytbl (p point[]);
insert into mytbl (p)
values ( '{"(180, 90)", "(-180,-90)"}'::point[] );

which you access through a dblink. Would the following work ( I cant use DBLink at the moment )?

select string_to_array(array_to_string(p,';'),';') from mytbl;


PARALLEL_MIN_TIME_THRESHOLD parameter when AUTO or MANUAL mode From How Parallel Execution Works:

The following is a summary of parallel statement processing when parallel degree policy is set to automatic.
A SQL statement is issued.
The statement is parsed and the optimizer determines the execution plan.
The threshold limit specified by the PARALLEL_MIN_TIME_THRESHOLD initialization parameter is checked.

If the execution time is less than the threshold limit, the SQL statement is run serially.

If the execution time is greater than the threshold limit, the statement is run in parallel based on the DOP that the optimizer calculates.

Suppose the parallel_degree_policy is MANUAL and the SQL statement contains a parallel hint or a defined parallel DDL value. Will the Oracle optimizer still check PARALLEL_MIN_TIME_THRESHOLD?
Also, is the estimated execution or the actual execute time used in the calculations around the threshold limit?

The following quote is from your link, emphasis mine:

  PARALLEL_MIN_TIME_THRESHOLD is the second initialization parameter that controls automatic DOP. It specifies the minimum execution time a statement should have before the statement is considered for automatic DOP. By default, this is 10 seconds. The optimizer first calculates a serial execution plan for the SQL statement; if the estimated execution elapsed time is greater than PARALLEL_MIN_TIME_THRESHOLD (10 seconds), the statement becomes a candidate for automatic DOP.

Another good resource is Using Default Parameter Settings, emphasis mine:

  PARALLEL_MIN_TIME_THRESHOLD: The execution time, as estimated by the optimizer, above which a statement is considered for automatic parallel query and automatic derivation of DOP.

To answer your questions directly:

  Suppose the parallel_degree_policy is MANUAL and the SQL statement contains a parallel hint or a defined parallel DDL value. Will the Oracle optimizer still check PARALLEL_MIN_TIME_THRESHOLD?

No, PARALLEL_MIN_TIME_THRESHOLD will not be used. If you specify the degree of parallelism why would the query optimizer calculate a cost for the serial plan? What would it do with that information? You've already specified the DOP. This only makes sense in the context of automatic DOP.

  Also, is the estimated execution or the actual execute time used in the calculations around the threshold limit?

This has to be the estimated time. Does it make sense for Oracle to possibly execute most of the query in serial and then switch to a parallel plan? As far as I understand it that will not happen, DOP is chosen at the beginning of execution for a data flow operation.


Evaluating most frequent values of columns in a single query I have a table describing my application users with some details such as name, surname, birth date, nationality, email, etc...

I would like to known for each property, and for each user category, the most frequent value and the percentage of occurence.

For instance :

create table test ( userId int identity(1,1),
                    categoryId int,
                    name varchar(50),
                    surname varchar(50))

insert into test(categoryId, name, surname)
values  (1, 'John', 'Locke'),
        (1, 'John', 'Millar'),
        (1, 'James', 'Mill'),
        (1, 'John Stuart', 'Mill'),
        (2, 'Thomas', 'Bayes'),
        (2, 'Laurent', 'Schwartz'),
        (2, 'Herrmann Amandus', 'Schwartz'),
        (2, 'Thomas', 'Simpson'),
        (2, 'Leonhard', 'Euler')

Result should be:

+------------+-------+--------+---------+----------+------------+
| categoryId | total |  name  | namePct | surname  | surnamePct |
+------------+-------+--------+---------+----------+------------+
|          1 |     4 | John   |    0.50 | Mill     |       0.50 |
|          2 |     5 | Thomas |    0.40 | Schwartz |       0.40 |
+------------+-------+--------+---------+----------+------------+

For this simple example, I can figure how I can achieve that, with a query like:

select  t.categoryId,
        t.total,
        n.name,
        1. * n.total / t.total as namePct,
        sn.surname,
        1. * sn.total / t.total as surnamePct
from (
    select categoryId, count(*) as total
    from test
    group by categoryId
    ) t
join (
        select categoryId, name, total
        from (
            select categoryId, name, total, row_number() over(partition by categoryId order by total desc) as rn
            from (
                select categoryId, name, count(*) as total
                from test
                group by categoryId, name
                ) t
            ) t
        where rn = 1
        ) n on t.categoryId = n.categoryId
join (
        select categoryId, surname, total
        from (
            select categoryId, surname, total, row_number() over(partition by categoryId order by total desc) as rn
            from (
                select categoryId, surname, count(*) as total
                from test
                group by categoryId, surname
                ) t
            ) t
        where rn = 1
        ) sn on t.categoryId = sn.categoryId

But, in my real use case my table has millions of rows, hundreds of categories and a dozen of attributes.

Is there a way to make the query simpler and more efficient (ie. without a bunch of sub select for each attribute) ?

I'm currently using SQL Server 2008, but answers using more recent versions are welcome.

You can use the windowed version of function COUNT(), split by each category with PARTITION BY to get counts and totals without a subquery (note the lack of GROUP BY):

SELECT
    T.categoryId,

    T.name,
    NameOccurencies = COUNT(T.name) OVER (PARTITION BY T.categoryId, T.name),
    NameTotals = COUNT(T.name) OVER (PARTITION BY T.categoryId),

    T.surname,
    SurnameOccurencies = COUNT(T.surname) OVER (PARTITION BY T.categoryId, T.surname),
    SurnameTotals = COUNT(T.surname) OVER (PARTITION BY T.categoryId)
FROM
    #test AS T

Result:

categoryId  name                NameOccurencies NameTotals  surname             SurnameOccurencies  SurnameTotals
1           John                2               4           Locke               1                   4
1           John Stuart         1               4           Mill                2                   4
1           James               1               4           Mill                2                   4
1           John                2               4           Millar              1                   4
2           Thomas              2               5           Bayes               1                   5
2           Leonhard            1               5           Euler               1                   5
2           Herrmann Amandus    1               5           Schwartz            2                   5
2           Laurent             1               5           Schwartz            2                   5
2           Thomas              2               5           Simpson             1                   5

Then you can use this result to get each percentage simply dividing the occurencies by each total. You can also calculate the best (most frequent) name and surname using a ROW_NUMBER() on this step:

;WITH Totals AS
(
    SELECT
        T.categoryId,

        T.name,
        NameOccurencies = COUNT(T.name) OVER (PARTITION BY T.categoryId, T.name),
        NameTotals = COUNT(T.name) OVER (PARTITION BY T.categoryId),

        T.surname,
        SurnameOccurencies = COUNT(T.surname) OVER (PARTITION BY T.categoryId, T.surname),
        SurnameTotals = COUNT(T.surname) OVER (PARTITION BY T.categoryId)
    FROM
        #test AS T
)
SELECT
    T.categoryId,

    T.name,
    NamePercentage = T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0),
    NameMostFrequentRanking = ROW_NUMBER() OVER (
        PARTITION BY
            T.categoryId
        ORDER BY
            T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0) DESC), -- NamePercentage

    T.surname,
    SurnamePercentage = T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0),
    SurnameMostFrequentRanking = ROW_NUMBER() OVER (
        PARTITION BY
            T.categoryId
        ORDER BY
            T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0) DESC) -- SurnamePercentage
FROM
    Totals AS T

Result:

categoryId  name                NamePercentage  NameMostFrequentRanking surname     SurnamePercentage   SurnameMostFrequentRanking
1           John Stuart         0.250000000000  3                       Mill        0.500000000000      1
1           James               0.250000000000  4                       Mill        0.500000000000      2
1           John                0.500000000000  1                       Millar      0.250000000000      3
1           John                0.500000000000  2                       Locke       0.250000000000      4
2           Herrmann Amandus    0.200000000000  4                       Schwartz    0.400000000000      1
2           Laurent             0.200000000000  5                       Schwartz    0.400000000000      2
2           Thomas              0.400000000000  1                       Simpson     0.200000000000      3
2           Thomas              0.400000000000  2                       Bayes       0.200000000000      4
2           Leonhard            0.200000000000  3                       Euler       0.200000000000      5

Finally, for each available category...

SELECT
    T.categoryId,
    TotalRecords = COUNT(1)
FROM
    #test AS T
GROUP BY
    T.categoryId

we can get the most frequent name and surname with their percentages with a few joins:

;WITH Totals AS
(
    SELECT
        T.categoryId,

        T.name,
        NameOccurencies = COUNT(T.name) OVER (PARTITION BY T.categoryId, T.name),
        NameTotals = COUNT(T.name) OVER (PARTITION BY T.categoryId),

        T.surname,
        SurnameOccurencies = COUNT(T.surname) OVER (PARTITION BY T.categoryId, T.surname),
        SurnameTotals = COUNT(T.surname) OVER (PARTITION BY T.categoryId)
    FROM
        #test AS T
),
MostFrequentRanking AS
(
    SELECT
        T.categoryId,

        T.name,
        NamePercentage = T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0),
        NameMostFrequentRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.categoryId
            ORDER BY
                T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0) DESC),

        T.surname,
        SurnamePercentage = T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0),
        SurnameMostFrequentRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.categoryId
            ORDER BY
                T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0) DESC)
    FROM
        Totals AS T
),
AvailableCategories AS
(
    SELECT
        T.categoryId,
        TotalRecords = COUNT(1)
    FROM
        #test AS T
    GROUP BY
        T.categoryId
)
SELECT
    A.categoryId,
    A.TotalRecords,
    MN.name,
    NamePercentage = CONVERT(DECIMAL(3, 2), MN.NamePercentage),
    MS.surname,
    SurnamePercentage = CONVERT(DECIMAL(3, 2), MS.SurnamePercentage)
FROM
    AvailableCategories AS A
    LEFT JOIN MostFrequentRanking AS MN ON
        A.categoryId = MN.categoryId AND
        MN.NameMostFrequentRanking = 1
    LEFT JOIN MostFrequentRanking AS MS ON
        A.categoryId = MS.categoryId AND
        MS.SurnameMostFrequentRanking = 1

Result:

categoryId  TotalRecords    name    NamePercentage  surname     SurnamePercentage
1           4               John    0.50            Mill        0.50
2           5               Thomas  0.40            Schwartz    0.40

It might be a little big, but you can edit this query with as many new columns you want without adding new SELECT, just repeating the same logic with each new column you want to display and an additional join at the end.

If you have millions of records and query takes long, you might want split each CTE into a temporary table with SELECT ... INTO + CREATE INDEX by categoryId to speed up the process (if you are willing to spend a few resources by creating these tables)


Attribute-Attribute Dependency So as part of our college project, I'm working on CENSUS Database. I stumbled upon a problem wherein if an individual is below the age of 18, his/her marital status should only allow 'NA' and anyone above 18 should be allowed to choose from any of the following values ('Married', 'Widowed', 'Single', etc). The information on CONSTRAINTS doesn't seem to be helping. Not finding any way to implement this kind of constraint mechanism, please suggest.

MySQL does not allow to define those kind of constraints, the only ones that are allowed are those related to indexes, such as UNIQUE or FOREIGN KEYs, or invalid data types. CHECK is not supported.

There are several options, depending on what you want to achieve.

First, there is obviously the option of doing application-only checks.
The recommended way on server side is to revoke INSERT and UPDATE access to the table from the user editing the information and enforce the usage of a couple of stored procedures to check the data before actually inserting it, so that the user has to CALL insert_my_table(colums, of, my, table).
You can also do some "hacks" like using a BEFORE INSERT/UPDATE TRIGGER that will force the transaction to fail if it detects a "wrong" value, as it was suggested on the previous link. This is easier to implement but a bit more ugly.


Altering failed job email I'm looking for a simple solution: if you have a job with multiple steps and one step fails but the job is set to continue, is there a way for the SQL Agent email to say which step(s) in the process failed?

Something more than "One or more of the job steps in the  job was not successful."

I'm sure I can add another step that queries the job history table and sends another email but I'm trying to combine the default email with a bit more information.

I've always done this by making a step's next action on failure be a specific step that sends an e-mail about that step only, then that step's on complete action is to continue to {failure step + 1}. Yes it requires some manual interaction with the jobs up front, but I can't think of any way to change the defaults without some level of manual tinkering.


sqlplus SP2-0341 (line overflow during variable substitution) I want to export an Oracle database to a file and i'm using SQLDeveloper on Windows for that (the resulting SQL text file is about 35MB). This results in very long lines.

I hit a snag when importing the file using sqlplus on linux:

SP2-0341: line overflow during variable substitution (>3000 characters at line 1)

This occurs on multiple lines.

I see no options on how to limit the length of the lines when exporting. I've tried to set line size and then export, to no avail. I've also tried to edit the file but the vim tools i used focus on explicit line length and not the semantics of SQL (they'll break strings). Also my regex-fo can only go as far as surrounding the VALUES keyword with newlines, but even then the lines are still much longer than 3000. Also, 35MB is a lot to edit by hand.

I'm limited to those tools and RDBMS but i'm open to suggestions.

SQL*Plus is not a data loading tool. Use the proper tools.

SQL Developer can create CSV files.
Instead of SQL*Plus, use SQL*Loader to load the data.

SQL*Plus Limits


Why does "SELECT *" take longer than "SELECT "? I'm having a problem with a query running on a little PHP program.
When I use * to select all the columns, it takes way more to return the result than when I use just one column.

The query that is running is this:

mysql> SELECT cupom_fiscal_id FROM cupom_fiscal WHERE send_date IS NULL AND travado IS
FALSE AND status = 0 AND ong_id = 1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-
05-05' LIMIT 1;

In this case it returned:

1 row in set (0.00 sec)

But when I replace the column with '*':

mysql> SELECT * FROM cupom_fiscal WHERE send_date IS NULL AND travado IS FALSE AND
status = 0 AND ong_id = 1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05'
LIMIT 1;

1 row in set (1.37 sec)

The funny thing is the result of the query below:

mysql> SELECT * FROM cupom_fiscal WHERE cupom_fiscal_id = (SELECT cupom_fiscal_id FROM
cupom_fiscal WHERE send_date IS NULL AND travado IS FALSE AND status = 0 AND ong_id =
1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05' LIMIT 1);

1 row in set (0.00 sec)

What I'm doing wrong?

PS: Sorry for the bad english.

EDIT 1: Posting the 'EXPLAIN' and the 'PROFILING' requested by: @ValerieParham-Thompson

mysql> EXPLAIN SELECT * FROM cupom_fiscal WHERE send_date IS NULL AND travado IS FALSE
AND status = 0 AND ong_id = 1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05'
LIMIT 1;
+----+-------------+--------------+-------------+--------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------------------------------+
| id | select_type | table        | type        | possible_keys                                                                  | key                | key_len | ref  | rows | Extra                                            |
+----+-------------+--------------+-------------+--------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE      | cupom_fiscal | index_merge | fkey_id_ong,status,ong_id,data_emissao,send_date,ong_id_2,ong_id_3,send_date_2 | status,fkey_id_ong | 4,4     | NULL | 3712 | Using intersect(status,fkey_id_ong); Using where |
+----+-------------+--------------+-------------+--------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------------------------------+
1 row in set (0.00 sec)

Profiling result:

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000121 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000030 |
| init                 | 0.000054 |
| System lock          | 0.000018 |
| optimizing           | 0.000027 |
| statistics           | 0.000282 |
| preparing            | 0.000041 |
| executing            | 0.000014 |
| Sending data         | 0.627346 |
| end                  | 0.000027 |
| query end            | 0.000018 |
| closing tables       | 0.000021 |
| freeing items        | 0.000081 |
| cleaning up          | 0.000012 |
+----------------------+----------+
15 rows in set, 1 warning (0.04 sec)

EDIT 2: I think I got it!
I just created an index with the the same fields and in the same order that they appear in the WHERE.

mysql> ALTER TABLE cupom_fiscal ADD INDEX `process` (`send_date`, `travado`, `status`, `ong_id`, `data_emissao`);

I really don't know if that makes any sense, but it solved my problem. The same query that took more than 1 second to return the results now takes < 7ms.

Your query:

SELECT *
FROM cupom_fiscal
WHERE send_date IS NULL
  AND travado IS FALSE
  AND status = 0
  AND ong_id = 1618
  AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05'
LIMIT 1 ;

involves only one table and 5 of its columns in the WHERE clause. Four conditions are equality (=) and the 5th is a range condition (BETWEEN .. AND).

(parenthesis)

The query is not very efficient as it uses the index_merge algorithm, i.e. it uses more than one index (actually two: status and fkey_id_ong) and then "merges" the results using the index_merge/intersect algorithm (Read: Using intersect(status,fkey_id_ong);.) After that operation, it has to also check the actual rows of the table (because, I guess, these two indexes do not include all the 5 columns of the WHERE clause.) This also has a negative effect on efficiency.

The best index for this query would be a 5-column index where the 5th column in data_emissao (the column with the range condition.) The order of the first 4 columns in the index would not matter.

It may matter for other queries but not for this one. So, choose an order that would help other queries as well (if for example you have many queries that use ong_id, use that as the 1st column of the index.


Pulling data on SSL expiration from SQL server I am trying to figure this out and seems to be out of thoughts and need your inputs to help me figure out on this.

We have implemented SSL across our 200+ servers, SQL2012 & 2014.

These cert are valid for 9 months . These are from vendor symantec.

Now the problem is tracking when they get expired. Keeping a manual track of these many servers are tedious.

Is there a way we can pull this up from SQL to get the information and create an SSRS report to check when they are going to expire?

Is the data present in the expiry_date column of sys.certificates in each DB or in master.sys.certificates, to be pulled out and reported upon?


MySQL error in where clause : Unknown column When I try to execute the query below, I receive the error message:

  Unknown column 'have_discount' in 'where clause'

My query is:

SELECT
    DISTINCT(MP.item_id), item_title, item_user_id, item_price, item_discount_price, url_slug,
    thumbnail_img, thumbnail_ext, thumbnail_width, thumbnail_height, is_free_item, default_img,
    default_img_ext, default_img_width, default_img_height, item_category_url_str,

    IF( ( DATE( NOW() ) BETWEEN item_discount_fromdate AND item_discount_todate), 1, 0 ) AS have_discount,

    item_category_id, NOW() as date_current, date_activated, MP.total_downloads,
    MP.item_sold, MP.total_views, MP.total_comments, MP.total_likes AS likes,
    MP.total_dislikes, MP.total_rating, MP.rating_count, MP.total_users_rated,
    (MP.total_rating/MP.rating_count) as rating, MP.default_img_ext , MPL.item_title,
    MPL.lang_id, MPL.item_description, MPL.item_highlight_text, MP.user_section_id, is_downloadable_item

FROM mp_item AS MP, mp_item_lang AS MPL

WHERE have_discount = 1
AND item_status = 'Ok'
AND MP.item_id = MPL.item_id
AND item_user_id = 3
AND MPL.lang_id = 2
ORDER BY date_activated DESC
LIMIT 0,12

I am trying to list items with a discount. How can I do this?

Now, if you rewrite the query in a clear manner, you can see where's the troublesome column. From what I know of general SQL you can't use the alias of the column in the WHERE clause (but you can use it in the ORDER BY clause).

So you'd have to rewrite the query something like:

Select *
from (
    -- Your nice query without the have_discount filter
) as Aux
WHERE Aux.have_discount = 1
ORDER BY date_activated DESC
LIMIT 0,12


Mysql search matching multiple values in multiple columns I need to speed up a search.
Currently we let our users input a name: John Doe

Then in Java we split that string to create multiple values (or "terms") ["John", "Doe"] and loop through the terms provided doing a select in the database looking for that term in a number of relevant columns:

// For each term in the string[]
SELECT *
  FROM Person p
 WHERE (p.idNumber = #{term}
          OR LOWER(p.firstName) LIKE LOWER(CONCAT('%', #{term}, '%'))
          OR LOWER(p.lastName) LIKE LOWER(CONCAT('%', #{term}, '%'))
          OR LOWER(p.companyName) LIKE LOWER(CONCAT('%', #{term}, '%'))
          OR p.phone = #{term});

at the end of the loop we use Java to do an intersection of the matched rows so that all matches are unique.

persons = (List<Person>)CollectionUtils.intersection(persons, tmpPersons);

I'm planning on changing this to a dynamic SQL statement to match all terms provided in one sql statement and removing the CollectionUtils.intersection but before I go through that process is there another way to do this that produce a faster search?

We're using Mysql 5.5 and an InnoDB table.

You're correct: FULLTEXT search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options:

Update to MySQL 5.6 and use a FULLTEXT index
Change the contract of your function to only allow prefix searches; that is, 'term%'. It will fulfill many use cases while saving your DB.
Convert to a MyISAM table, or create a spare MyISAM table that you can join on specifically for searches.

Since you're building the query in Java, I'd also suggest removing the CONCAT('%' stuff in favor of just creating a second variable that has the %s already applied. That saves the DB from the extra work of parsing it and lets it utilize indexes better. Speaking of indexes, be sure you have indexes that span the columns where possible.

Regarding the Duplicate removal, you may be better off performing a GROUP BY p.idNumber to save the extra effort and data transfer in Java.


How to increase column size in Redshift database tables? In oracle, I can:

Alter table table_name
modify column_name datatype;

Is this possible in a redshift database?

In AWS Redshift is now possible to alter ONLY VARCHAR column but under these conditions:

You can’t alter a column with compression encodings BYTEDICT, RUNLENGTH, TEXT255, or TEXT32K.
You can't decrease the size less than maximum size of existing data.
You can't alter columns with default values.
You can't alter columns with UNIQUE, PRIMARY KEY, or FOREIGN KEY.
You can't alter columns inside a multi-statement block (BEGIN...END).

See the documentation.


pg_waldump: command not found According PostgreSQL documentation I can use pg_waldump for display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster.

I have clean installation of PostgreSQL-11 server on Ubuntu 18.04, but I have not this command:

  pg_waldump: command not found

Below list of present pg utilites:

pg_archivecleanup    pg_conftool          pg_dumpall           pg_recvlogical       pg_virtualenv
pg_basebackup        pg_createcluster     pg_isready           pg_renamecluster
pg_buildext          pg_ctlcluster        pg_lsclusters        pg_restore
pg_config            pg_dropcluster       pg_receivewal        pg_updatedicts
pg_config.libpq-dev  pg_dump              pg_receivexlog       pg_upgradecluster

What I need to do for use this command on my server

It is not in the default PATH, so you'll have to invoke it with its absolute path:

/usr/lib/postgresql/11/bin/pg_waldump <WAL file name>


Stored procedure encryption and master service master key, SQL Server 2008 Is the Service Master Key (SMK) used to encrypt stored procedures in SQL Server 2008?

I know it is used for data, but what about database objects? If the Service Master Key is not used, then I would expect that if one were to do a database recovery from one server to another (without restoring the SMK) that the stored procedures would continue to work.

Is this true?

It is not used. Details about the encryption algorithm used to encrypt database objects you can find here: http://sqlity.net/en/1617/decrypting-encrypted-database-objects/


Multiple mariadb instances - how to access to the not default instances via command line? I have a TYPO3 and a Moodle installation on one server with Debian stretch.

Moodle needs some database configurations (MariaDB 10.1.38) which are different from the ones TYPO3 needs.

I made a new configuration /etc/mysql/conf.d/mymoodle.cnf (with port 3307 and datadir /var/lib/mysql-moodle), installed the default DBs in the new datadir with

mkdir /var/lib/mysql-moodle
chown mysql:mysql /var/lib/mysql-moodle
mysql_install_db --datadir=/var/lib/mysql-moodle

and started the instance with

service mariadb@moodle start

Obviously this works:

# ps aux | grep mysql
mysql    14135  0.1  1.8 678184 74032 ?        Ssl  Jun25   1:20 /usr/sbin/mysqld --defaults-file=/etc/mysql/conf.d/mymoodle.cnf
mysql    14323  4.8  6.1 720344 241200 ?       Ssl  Jun25  55:03 /usr/sbin/mysqld
root     25475  0.0  0.0  12712   880 pts/0    S+   14:06   0:00 grep mysql

# netstat -tulpen | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      107        76817214   14323/mysqld
tcp        0      0 127.0.0.1:3307          0.0.0.0:*               LISTEN      107        76816770   14135/mysqld

That's looks good, I thought, but I didn't find an access via command line. Calling the client with

mysql --defaults-file=/etc/mysql/conf.d/mymoodle.cnf --port 3307

leads me to the default instance:

SHOW VARIABLES LIKE '%datadir%';

gives me /var/lib/mysql instead of /var/lib/mysql-moodle.

What is the mistake?

As the manual says:

  If hostname is not specified or hostname is localhost, then Unix sockets are used. Unused connection parameters (such as port) will be ignored.

To override the default behaviour, use either the --protocol option to use TCP

mysql --defaults-file=/etc/mysql/conf.d/mymoodle.cnf --port 3307 --protocol tcp

or the --socket option to direct it to the second instance's socket (I assume you have specified a different socket in /etc/mysql/conf.d/mymoodle.cnf


Is there a way to show the creation statement for an index in PostgreSQL I need to recreate an index in PostgreSQL which has suffered index bloat. Since I need the index to be usable while it's being created, I can't use REINDEX. I am going to recreate the index with a new name and then drop the old one. Is there any way to see the SQL statement that was used to create an index so I can just copy that?

There is actually, just query the pg_indexes system catalog view as follows:

SELECT indexdef FROM pg_indexes WHERE indexname = '...'

and you should get back the SQL statement used to define it.


Understanding index fillfactor I'm trying to understand the index FILLFACTOR parameter on a simple example. I have a table that is generally updated only once in ten days, but the update is quite heavy (requires 5-6 hours).

Now, I tend to drop the index on a column of the table, update the data and then create the index again with FILLFACTOR = 100.

Is that a good practice? I'm not sure about that because I don't understannd the FILLFACTOR concept in general. By default it's 90% and they say that this is needed to perform updates and insertions.

But I tried to create an index with FILLFACTOR = 100 and then update some row and add new one and it executed fine. But I have autovacuum turned off. Running ANALYZE, however required only 3secs.

So, how does the free space left by fillfactor work?

Free space within the table can be used to speed up certain kinds of updates on tables, do inserts faster by avoiding the need to extend the table, and make index growth more efficient.

If your table only changes infrequently and you don't mind reindexing it (or dropping & re-creating the index) each time, that's totally fine. A fillfactor of 100 will save a little bit of I/O when scanning the index, and the spare space is no use to you unless you do inserts/updates on the indexed table.


Stored procedure variable storage takes long time to execute I have a stored procedure which take an endless time for execution:

BEGIN
set @my_user_id=null;

select  user_id into @my_user_id
    from  office_user
    where  username like my_username
      and  `password` like MD5(my_password);

select  user_id,`name`,username,mobile_no,email,designation,role_id,
       signImage,colorCode,category,role_new.permission,report
    from  office_user
    INNER JOIN  role_new on office_user.role_id=role_new.roleId
    where  user_id=@my_user_id;

select  id,`name`,parent_id
    from  businesslevels
    where  parent_id=1
      AND  is_active =1
      AND  `name`!='Main'
      and  FIND_IN_SET(id,
(          ( SELECT  GROUP_CONCAT(topLevelId)
                    from  businesslevels
                    where  FIND_IN_SET(id,
(                         ( SELECT  GROUP_CONCAT(distinct bLevelid)
                                    from  forms
                                    where  FIND_IN_SET(Fname,
(                                        ( SELECT  GROUP_CONCAT(formName)
                                                    from  userforms
                                                    where  userType='office'
                                                      and  emp_id IN (@my_user_id))))))
                          )
    ORDER BY  `name`;

END

and when I am using this i.e. No variable usage, direct as a repeat sub query

This procedure execute quickly:

BEGIN
select  user_id,`name`,username,mobile_no,email,designation,role_id,
       signImage,colorCode,category,role_new.permission,report
    from  office_user
    INNER JOIN  role_new on office_user.role_id=role_new.roleId
    where  where username like my_username
      and  `password` like MD5(my_password);

select  id,`name`,last_name,emp_code,reporting_to,shift_start,
       shift_end,model,version,track_interval,conveyance,reporting_to
    from  field_user
    where  FIND_IN_SET(reporting_to,concat(@my_user_id,GetUserFamilyTree
(          ( SELECT  user_id
                            from  office_user
                            where  username like my_username
                              and  `password` like MD5(my_password))));

END

I don't know why this happening. I don't want to write subquery again. As there are more queries in procedure that need user_id

Any suggestion will be appreciated

Don't use FIND_IN_SET ( SELECT GROUP_CONCAT ... ).  Instead JOIN the two tables together.


Distributing a single table across multiple disks of different performance? I have a table that increases by several million rows per day. It holds over a year's worth of data.

The table is regularly SELECTed from, but the older the data is the less frequently it is accessed. The current data (i.e. data added in the last hour) is very frequently queried, but as the records get older they are less likely to be queried.

The database is currently on a traditional (magnetic) hard disk. I also have a much smaller solid state disk that is very fast, but not large enough to hold more than few days' worth of data.

I’d like to somehow spread the table across the two disks. I’d like new data to be INSERTed into the small, high performance disk, and then invisibly migrated to the slower but larger disk after a few days (when it’s less likely to be needed).

I know I could split the data into two tables (let's call then TABLE_SLOW and TABLE_FAST). I could insert into TABLE_FAST and then run an archiving process to transfer the oldest records into TABLE_SLOW every night, and then create a view based on a union of TABLE_SLOW and TABLE_FAST to use for queries – but I’m looking for a solution that wouldn’t require any changes to the application, only the database.

The data includes a field called ‘loadedTime’ so there’s no problem working out how old the data is.

How to put data on a different disk: (using tablespaces)

CREATE TABLESPACE fastspace LOCATION '/srv/fast_disk/postgresql/data';

https://www.postgresql.org/docs/10/static/manage-ag-tablespaces.html

.

How to make two tables behave like one: (SQL inheritance)

CREATE TABLE TABLE_FAST () inherits TABLE_SLOW TABLESPACE fastspace;

You will also need to add constraints and indices as apropriate to this new table.

https://www.postgresql.org/docs/10/static/tutorial-inheritance.html

Now selects from TABLE_SLOW will also see the data in TABLE_FAST

you may also benefit from implementing constraint exclusion (also described on that page), but the index on Loaded_Time (which I am assuming you have) should give a good speed increase itself

.

How to move data data: (CTE delete returning)

eg: 3 days old

with deleted as (
     delete from only TABLE_FAST
     where Loaded_Time < 'now()' - '3 days'::interval
     returning * )
insert into slow_table select * from deleted;


Is this database fine for calculation? I am doing an application called as Invoice Application.For that I have my database is like this.

Now my problem comes when I am taking the tax part in my database.As I have made tax as an option where user can add one or two different taxes in an invoice form.Also I have taken total in invoice_items table to do calculations.So is this database is fine for doing all the calculation or I have to take another extra table for doing calculation and all?Any help regarding to make the database design simpler will be more appreciable.

Update

Here is the link for another question little bit similar.So can this question be solved like this?

Sales/excise/VAT tax rules can be very complicated.  There can be different rules about the applicability of different taxes based on things like:

What kind of item is it?
How many of the items are being purchased?
Who is purchasing the item?

Also, some taxes are calculated on the base amount and others are compounded (applied to other taxes) as well.  These are some of the kinds of rules that apply in the jurisdiction where I live.  You might have others as well.

I would suggest not limiting yourself to two applicable taxes per item.  Instead, I would create an intersection table (many-to-many) that indicates which taxes are applied to each item.  This could be driven by a similar intersection table between PRODUCT and TAX to indicate which taxes are usually applicable to each product, if that is an important distinction in your case.

While adding another table may not seem like a simplification, it will actually make your code simpler and easier to maintain because you are normalizing your applicable tax data.  The design you have illustrated isn't even in 1NF, which is almost always a recipe for trouble.

Compounding Taxes

To handle the situation where some taxes compound and some don't, I've used a calculation_order attribute, which is just a number that indicates when to apply the tax.  Taxes with the same calculation_order value get applied to the same base.  Taxes with a higher calculation_order get applied to the net amount of the product and all taxes with lower calculation_order values.


Oracle auditing: Is it possible to log when a permission is being used? Does Oracle offer a way to log instances where a given permission is evaluated?
Suppose I GRANT SELECT ANY DICTIONARY or something similar to a role, I am looking for a way to log every instance where the internal permissions engine of the database evaluates that exact permission for that role. Think of the audit.log on Linux systems that logs applications of SELinux policies.
I am also open to any workarounds achieving something similar.

Yes, that is entirely possible. The use of any privilege or grant can be audited by instance, by session, by success, or by failure. You can audit all uses, or just uses by specific users. See here for a basic guide to getting started: https://oracle-base.com/articles/8i/auditing, and here: https://oracle-base.com/articles/12c/auditing-enhancements-12cr1
For example:

CREATE AUDIT POLICY test_audit_policy
  PRIVILEGES CREATE TABLE, CREATE SEQUENCE
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TEST'''
  EVALUATE PER SESSION
  CONTAINER = CURRENT;

AUDIT POLICY test_audit_policy;

See here for a blog I wrote a few weeks ago about basic audits to use in 12c and up, called "Auditing By The Numbers"


Enforce Schema-bound views For reporting purposes, I need to be able to query dependencies between views and underlying tables at column level (via sys.sql_expression_dependencies).

One way to get SQL Server to store referenced columns is to use schemabound views. As some of my users will create views too, I would like to enforce those. As far as I've seen it isn't possible to allow only views with schemabinding to be created within a SQL Server database though (although this seems a legit requirement to me).

Are there other ways to force SQL Server to keep track of which columns are being referenced by which views? Or is there a hidden way to enforce schemabound views in SQL Server?

Very easy to do with a DDL trigger on your database:

CREATE TRIGGER RequireSchemaBinding ON DATABASE FOR CREATE_VIEW, ALTER_VIEW
AS
DECLARE @object nvarchar(max)
DECLARE @schema nvarchar(max)
SET @object = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)')
SET @schema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)')
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(QUOTENAME(@schema) + '.' + QUOTENAME(@object)), 'IsSchemaBound'), 0) = 0
BEGIN
    RAISERROR('Not today, punk.', 16, 1)
    ROLLBACK TRANSACTION
END


PostgreSQL 11 trigger can't select a value in a table to use it in a second table Here's a sample of log :

gid alti  numlog
1   79.55 1
2   79.65 2

Here's a sample of us :

gid numlog numus sommet base alti_sommet alti_base
1   1      1000  0      0.25 (79.55-0)   (79.55-0.25)
2   1      1001  0.25   0.39 (79.55-0.25)(79.55-0.39)
3   1      1002  0.39   0.59 (79.55-0.39) ...
4   2      1000  0      0.15 (79.65-0)
5   2      1001  0.15   0.40 (79.65-0.15)

With a trigger on us, I would like to update alti_sommet and alti_base with the result of the math between ().

I create this function just for alti_base for now :

create or replace function activite.calcul_alti_us()
returns trigger as
$body$
 BEGIN
  IF (TG_OP IN ('insert')) THEN
  NEW.alti_base = l.alti-NEW.base from log l where NEW.numlog = l.numlog ;
 return NEW ;
  END IF ;
 return NULL ;
END ;
$body$
language 'plpgsql' ;

and the trigger :

CREATE TRIGGER trg_calcul_alti_us
AFTER insert
ON us
FOR EACH ROW
EXECUTE PROCEDURE activite.calcul_alti_us();

The result : alti_surface = 0 everywhere.
I tried :

NEW.alti_base = (select l.alti-NEW.base from activite.log l where NEW.numlog =l.numlog) ;

I tried with a trigger BEFORE as well.
Both failed.
Thanks for help.

There are many errors:

It must be a BEFORE trigger to modify the row about to be inserted.
There is no column base in table log.
TG_OP is always in upper case, so you must compare with 'INSERT'.


problem with optimizing SQL query I'm noob when it's come to DBMS so i wrote a query on couple of relations and i want to know what's the best way to do it because i don't feel good about my query.

Here are the relations:

CREATE TABLE "Product" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "name" text,
  "unit_price" float8,
  "quantity" integer,
  "produce_per_day_ratio" integer,
  "sold_till_now" integer
);

CREATE TABLE "Order" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "personal_id" bigint,
  "product_id" bigint,
  "quantity" bigint,
  "company" text
);

CREATE TABLE "SalesManager" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "fName" text,
  "lName" text,
  "age" integer,
  "gender" bigint,
  "salary" bigint,
  "profit" float8
);

CREATE TABLE "Gender" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "name" text
);

ALTER TABLE "Order" ADD FOREIGN KEY ("personal_id") REFERENCES "SalesManager" ("id");

ALTER TABLE "Order" ADD FOREIGN KEY ("product_id") REFERENCES "Product" ("id");

ALTER TABLE "SalesManager" ADD FOREIGN KEY ("gender") REFERENCES "Gender" ("id");

So the question is "show the name of people who have sold the most products?" and by most sold i meant (quantity * unit_price)

EDIT:this is the updated query:

WITH orderProduct AS(
  SELECT
    O.personal_id as pId ,
    sum(O.quantity * P.unit_price) as sold_amount,
    RANK() OVER (ORDER BY sum(O.quantity * P.unit_price) DESC) as rank
  FROM "Order" AS O
  INNER JOIN "Product" AS P ON O.product_id = P.id
  GROUP BY O.personal_id
)
SELECT concat("fName" , ' ' , "lName") as name , orderProduct.sold_amount
FROM "SalesManager"
INNER JOIN orderProduct ON id = pId
WHERE rank = 1;

So how bad is it ? :(

PS: i'm using postgres 12.2

One thing that can be improved is the choice of data types:

for fields like company or product name varchar(50) (or a bit more) is sufficient. Text is overkill. You are not storing big chunks of text like forum posts.
for age the realistic range would be 18-70. So smallint is more than enough (range: -32768 to +32767)
usage of bigint is not justified in your schema - consider the range, it's totally overblown for your purpose:

  bigint    8 bytes     large-range integer     -9223372036854775808 to
  +9223372036854775807

(source: PostgreSQL 9.5.21 Documentation - Chapter 8. Data Types)

for gender: an enumerated type would be a good candidate because you are really expecting no more than a few options: Male/Female/Other.
for unit_price you chose: float8, if I'm correct the usage range is: 1E-307 to 1E+308. That may be useful if you are trying to count the number of atoms in the universe but no item on Earth can be priced that much. It would be more reasonable to use NUMERIC and choose the precision you want, depending on how many digits you want to the right of the dot. For example retail items may need 2 decimals if you are pricing in cents, when buying in bulk from suppliers prices may be expressed with 4 decimals.
ditto for salary

Here you are doing arithmetic operations like SUM so the performance is impacted by the data type although you may not notice it. On bigger datasets the impact could be more noticeable.

So I think you should review the data types available on that particular DBMS and redesign your tables with an effort to select the most appropriate data type for each field.

You are the designer and maybe the end user too, so you should know more or less what kinds of values are expected and the data types should be chosen accordingly, with some room for growth if you want but stay reasonable.

Since your question is about optimizing, another point that has not been addressed but that is strongly related to performance is indexing.  If you join tables that have lots of records you will need indexes sooner or later, or retrieval times will become unacceptably slow. Good table design is important too.


SQL Services Wont Start After Removing TLS I need to remove TLS 1.0 from one of my SQL servers. When I do so and then reboot the server the SQL services dont start automatically. When I attempt to start them I get an error that it cant be done.
Once I renable TLS 1.0 the SQL services start again after a reboot.

Im running SQL 2012 Developer edition. Version is 11.0.2100.60

Im using IIS crypto to remove TLS.
I keep TLS1.1 and 1.2 on there

Has anyone got a suggestion of what I can do to have the SQL services start when I remove TLS 1.0

Thanks

You have to patch the sql server instance to a newer version to be able to use TLS 1.2

Take a look at TLS 1.2 support for Microsoft SQL Server

Either patch at least to SP2 CU10 (preferably a higher CU)
 or SP3 CU1.

When patching to SP3 CU1

You would still be 9 CU updates behind, the latest CU for SP3 at the time of writing is SP3 CU10.

So when going this route I would go for SP3 CU10 but preferably choose for SP4

  Note: TLS 1.2 support is also available in 2012 SP4.

When patching to SP4 (preferred)

In short, simply patching to SP4 would be the preferred option, you have to remember that there is a hotfix out for this Service Pack.

More questions and answers on disabling tls 1.0 and the sql server service not being able to start
here and here


How to access the text definition of sql indexes I am able to access the text definition of stored procedures, views, triggers and functions from sys.sql_modules. I want to get the text definition of indexes.

How can I get the text definition of indexes in SQL Server?

The objects that have definitions found in the sys.sql_modules Catalog View are the only ones that have full definitions, and indexes are not in this group. The sys.check_constraints and sys.default_constraints Catalog Views have partial definitions, but again, those aren't indexes. So no, you are not going to find the text definition of indexes.

What you can find, however, is the meta-data related to indexes that can be used to construct the full text definition. You would find that info in the following Catalog Views:

sys.indexes
sys.index_columns
sys.columns
sys.data_spaces
sys.key_constraints
sys.xml_indexes
sys.partition_schemes  (might be needed for partitioned indexes)

If you need to get this definition programmatically, then you can use SMO:

SQL Server Management Objects (SMO) Programming Guide
Automated Script-generation with Powershell and SMO

P.S. This answer was originally posted on this same question on Stack Overflow prior to the question getting copied over here.


Installing SSIS package outside of SSIDB catalog I'm trying to simplify deployment of SSIS package to a remote server. At the moment, I have

Local Visual Studio SSDT and SSIS package that has 4 connection managers - DEV environment
Remote server with SQL, SSMS, and SSDT installed - PROD environment

The easiest way to deploy SSIS package from local computer to the remote server is to

Create SSIDB catalog on the remote server
Perform Deploy from Visual Studio and point it to the catalog created on the remote server

In this case everything works fine.
In the meantime, some remote servers may not be accessible from Visual Studio directly, so I'm trying to precompile SSIS package on local computer, upload to the remote server and add it to SQL server job as a single file, like it's described in this article. The second screenshot in that article shows that I can choose Package Source = File System and specify a path to SSIS package file.
The issue
When I deploy SSIS package from Visual Studio to SSIDB catalog and create SQL server job using package in the catalog, it correctly shows 4 connection managers used by the package. When I create SQL server with manually uploaded SSIS package file, the tab Data Sources in SSMS shows empty list of connection managers. Why in the second scenario, connection managers were not deployed with the package?

If VS doesn't have access to the remote server, you can build SSIS package, upload it to the remote server and install using SSMS.

Build SSIS project to create IPSAC file in BIN folder
Copy IPSAC file to the remote server
Go to the remote server => SSMS => Integration Services Catalog => create new folder
Created folder will have 2 sub-folders, one of them is Projects
Click right mouse button on the Projects folder and choose option Deploy
In the popup window, choose to deploy project and point it to IPSAC file