Alter table add column like another column syntax I was looking into MySQL specification, but couldn't find any solution to my problem. I want to clone specific columns in several tables like this:

ALTER TABLE `table` ADD COLUMN `new_column` LIKE `column` AFTER `column`;

Of course this gives me syntax error: right syntax to use near 'LIKE `column` AFTER `column`', what i was expecting. Is there any possible solution to this or i have to specify directly a new column? I know that "cloning" table structure using LIKE operator is possible. Is it also possible in this case?

No. This is not possible using command.

All you can do is write a procedure to grab the table & column-name. Get the column definition from information_schema. Create alter-table-modify-column syntaxes & exec... Something like following:

call modify_table(target_table, source_table, source_column);


How do you show SQL executing on an Oracle database? How can you show the SQL that is currently executing on an oracle db?

Extra information that would be useful would include user, session id etc.

Most of the info is available in v$session ... and the SQL Text can be grabbed from v$sql or v$sqltext_with_newlines...

Here is a query that I often use that formats In-flight SQL, longest running at top.

-- In Flight SQL
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,
       MACHINE,
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT
  FROM V$SESSION SES,
       V$SQLtext_with_newlines SQL
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
   and Ses.AUDSID <> userenv('SESSIONID')
 order by runt desc, 1,sql.piece;


Transfer row from 'parent' to 'child' without creating duplicate and without knowing all 'parent' columns I want to set up a database with different schemas that correspond to different data-entry systems. There needs to be a “master” (administration) schema with a “parties” table, and multiple other schemas with their own “parties” tables that inherit fields from the master parties table

Here’s a simplified example of the structure:

'PARENT'

CREATE TABLE master.parties
(
  party_key serial NOT NULL,
  name text,
  date_of_birth date,
  phone text,
  CONSTRAINT "PK_master_parties" PRIMARY KEY (party_key)
)

'CHILD'

CREATE TABLE corporate.parties
(
  corp_key integer,
  corp_role text
) INHERITS (master.parties)

I would like to be able to bring down a row that was added to the master.parties table and insert it into any of the parties tables in the other schemas (ie. corporate), without creating a duplicate row in the master table.

This solution I found on another post (https://stackoverflow.com/questions/29509162/postgres-table-inheritance-move-from-parent-to-child-and-vice-versa) was very close to what I am looking for:

WITH deleted AS (
  DELETE FROM ONLY master.parties
  WHERE party_key = 1
  returning *
)
INSERT INTO corporate.parties (name, date_of_birth, phone, corp_key, corp_role)
SELECT name, date_of_birth, phone, 2, 'President'
from deleted;

However, it seems to me that there should be a better way of adding a row from the 'parent' table to the 'child' table without having to delete the existing row in the 'parent' table.

This solution also poses another, more serious problem: if a change is made to the parties table in the master schema (eg. new column added), I don’t want to have to update all the data-entry systems corresponding to the sub-schemas to include that new column in every INSERT statement. If I were to miss an INSERT statement in code, or if it wasn't possible to immediately update one of the data-entry systems, I’d risk losing the values in that new column of the master.parties table when I try to bring down a row into another schema’s party table (as it is not included in the existing INSERT statement).

Basically what I’m after is to have the ability to add a row to a 'child' table from the 'parent' table, without necessarily knowing all of the columns in the 'parent' table and without creating a duplicate row in the 'parent' table. Is this possible? Should I be reconsidering my DB design?

Any help or suggestions are much appreciated!

No one (or rather very few) uses "table inheritance", like that. That's a half-baked PostgreSQL feature which is used for some internal underpinnings exposed to the user in a fashion likely to confuse them. I'd highly suggest avoiding it. For more information see,

Using table inheritance instead of mapping tables

In your case though you can use a single-table hierarchy or self-referencing hierarchy,

CREATE SCHEMA master;
CREATE TABLE master.entities
(
  entity_id       int  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  parent_id       int  REFERENCES master.entities,
  entity_name     text,
  entity_data      jsonb
);

INSERT INTO master.entities VALUES
  ( 1, null, 'Master', null),
  ( 2, 1   , 'Corp'  , '{"date_of_birth":"2018-07-05"}');

  I would like to be able to bring down a row that was added to the master.parties table and insert it into any of the parties tables in the other schemas (ie. corporate), without creating a duplicate row in the master table.

I'm not 100% sure what you mean here. Essentially the relations are not meta-data, it's data. So you have to have a duplicate row to have one entity under two parents. If you want to minimize duplication of the JSONB though you could outsource that to another table -- though I would probably not bother with it unless you have a desired answer as to how two parties will manage the data-stored in the jsonb.

Querying the above structure is slightly more complex, but you'll get the hang of it, you have to use a recursive CTE. You can find lots of examples of this under hierarchy,

WITH RECURSIVE t(id,parents,name, data) AS (
  SELECT entity_id, ARRAY[]::int[], entity_name, entity_data
  FROM master.entities
  WHERE parent_id IS NULL
  UNION ALL
    SELECT e.entity_id, parents||parent_id, e.entity_name, e.entity_data
    FROM t
    INNER JOIN master.entities AS e
      ON e.parent_id = t.id
)
TABLE t;

 id | parents |  name  |              data
----+---------+--------+---------------------------------
  1 | {}      | Master |
  2 | {1}     | Corp   | {"date_of_birth": "2018-07-05"}
(2 rows)

Note this schema also permits N-level relationships (you can create an almost infinite amount of children under children -- no additional tables required)


Do you need something like this?

WITH cte AS ( SELECT dates.date date, series.generate_series transect
              FROM ( SELECT DISTINCT date
                     FROM datatable ) dates,
                   ( SELECT *
                     FROM generate_series(1, 5) ) series
            )
SELECT cte.date, COALESCE(datatable.value, 0) AS value, cte.transect
FROM cte
LEFT JOIN datatable ON cte.date = datatable.date
                   AND cte.transect = datatable.transect;


The reason for this behaviour is that rows where LD is NULL cannot be found in the index. Therefore Oracle has to scan the full table. If the table is created with LD as a NOT NULL column then the optimizer uses this information and does an INDEX FAST FULL SCAN. If you add a "CHECK(LD is not null)" constraint to the table that has not NOT NULL defined for the column LD then the optimizer does not use the information provided by the constraint and makes a full table scan again, even if you gave him a hint. Jonathan Lewis wrote about this behaviour.

The following scripts demonstrate this behaviour for Oracle 11.2.0.3.0

*create_table.sql* inserts data into the table and creates index and statistics

set autotrace off
drop table objects
/
create table objects(
  object_id number,
  owner varchar2(30),
  object_name varchar2(128),
  object_type varchar2(19)
)
/

insert into  objects(
  object_id,
  owner,
  object_name,
  object_type
)
select
  object_id,
  owner,
  object_name,
  object_type
from dba_objects
/

create index idx_object_id on objects(object_id);

exec dbms_stats.gather_table_stats(user,'objects', cascade=>true)

Now run the following script:

spool output
set feedback off
set linesize 300
set trimout on
set trimspool on

@create_table
set autotrace traceonly explain

prompt
prompt 1. plan for query with no constraints:
select distinct object_id
from objects;
rem ---------------------------------------------------

@create_table
alter table objects add constraint nn_object_id check(object_id is not null) validate;
set autotrace traceonly explain

prompt
prompt 2. plan for query with check constraint
select distinct object_id
from objects;
rem ---------------------------------------------------

@create_table
alter table objects modify object_id not null;
set autotrace traceonly explain

prompt
prompt 3.plan for query with NOT NULL column
select distinct object_id
from objects;
rem ---------------------------------------------------

@create_table
create bitmap index bidx_object_type on objects(object_type)
/
set autotrace traceonly explain

prompt
prompt 4.plan for query with bitmap index
select distinct object_type
from objects;
rem ---------------------------------------------------

spool off

This gives the following output

1. plan for query with no constraints:

Execution Plan
----------------------------------------------------------
Plan hash value: 4077265613

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         | 59063 |   288K|   139   (3)| 00:00:02 |
|   1 |  HASH UNIQUE       |         | 59063 |   288K|   139   (3)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| OBJECTS | 59063 |   288K|   136   (0)| 00:00:02 |
------------------------------------------------------------------------------

2. plan for query with check constraint

Execution Plan
----------------------------------------------------------
Plan hash value: 4077265613

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         | 59063 |   288K|   139   (3)| 00:00:02 |
|   1 |  HASH UNIQUE       |         | 59063 |   288K|   139   (3)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| OBJECTS | 59063 |   288K|   136   (0)| 00:00:02 |
------------------------------------------------------------------------------

3.plan for query with NOT NULL column

Execution Plan
----------------------------------------------------------
Plan hash value: 4172758181

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               | 59063 |   288K|    40   (8)| 00:00:01 |
|   1 |  HASH UNIQUE          |               | 59063 |   288K|    40   (8)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 59063 |   288K|    37   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

4.plan for query with bitmap index

Execution Plan
----------------------------------------------------------
Plan hash value: 2970019208

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |    43 |   387 |     6  (34)| 00:00:01 |
|   1 |  HASH UNIQUE                 |                  |    43 |   387 |     6  (34)| 00:00:01 |
|   2 |   BITMAP INDEX FAST FULL SCAN| BIDX_OBJECT_TYPE | 59063 |   519K|     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Summary

If there is a normal B*-tree index on the column NULL values are possible in the column, then the optimizer cannot rely only on the information of the index to do the 'select distinc' and makes a TABLE ACCESS FULL.

If there is a normal B*-tree index  and a NOT-NULL check constraint on the column the optimizer also does not rely on the information of the index and makes a TABLE ACCESS FULL.

If there is a normal B*-tree index  and the column is defined NOT NULL then the optiomizer relies on the information of the index and does a INDEX FAS FULL SCAN.

If there is a bitmap index on the column then the optimzer knows that all information is in the index and does a BITMAP INDEX FAST FULL SCAN


Powershell Get-Counter not returning values for SQL counters I am attempting to use PowerShell to get some SQL counters but it doesn't seem to return the SQL specific counters anymore. I am not sure what could have changed on my server that would have caused this.

The script is pretty straight-forward, I am not sure what I am missing:

## Define some variables
$serverName = "ISOMORPH" # Server we are collecting from

# Define our list of counters
$counters = @(
    "\Memory\Available MBytes",
    "\Memory\Pages/sec",
    "\PhysicalDisk(_Total)\Avg. Disk sec/Read",
    "\PhysicalDisk(_Total)\Avg. Disk sec/Write",
    "\PhysicalDisk(_Total)\Current Disk Queue Length",
    "\PhysicalDisk(*)\Avg. Disk sec/Read",
    "\PhysicalDisk(*)\Avg. Disk sec/Write",
    "\PhysicalDisk(*)\Current Disk Queue Length",
    "\Process(sqlservr)\% Privileged Time",
    "\Process(sqlservr)\% Processor Time",
    "\Processor(*)\% Privileged Time",
    "\Processor(*)\% Processor Time",
    "\SQLServer:Buffer Manager\Buffer cache hit ratio",
    "\SQLServer:Buffer Manager\Buffer cache hit ratio base",
    "\SQLServer:Buffer Manager\Lazy writes/sec",
    "\SQLServer:Buffer Manager\Page life expectancy",
    "\SQLServer:Memory Manager\Memory Grants Pending" ,
    "\SQLServer:SQL Statistics\Batch Requests/sec",
    "\System\Context Switches/sec",
    "\System\Processor Queue Length"
)

## Get our performance counter data
Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 1

The script will return all of the counters, but all of the SQL counters show 0 values. I am not getting any errors, it just shows all of the SQL counters as having a value of 0.

If I manually open Performance Monitor I can see these counters and they have values. I am running this on a Windows 7 laptop with SQL 2014 Developer Edition installed.

EDIT: Further testing leads me to believe this is an OS specific problem. Someone else tested this script with Windows 8.1 running SQL 2012 and it returns 0 for the SQL specific counters in that case as well.

The reason your code works when you run it as Administrator is that when you start the powershell console it's not elevated.

There are several options to elevate the script. One nice example from Ben Armstrong lets the script check if it's elevated and if it's not it's started in elevated mode and executes your code: http://blogs.msdn.com/b/virtual_pc_guy/archive/2010/09/23/a-self-elevating-powershell-script.aspx


Estimating the size (of the records) and the overhead in Postgres Consider the following table in Postgres 9.4:

CREATE TABLE t
(
  a1 bigserial,
  a2 bigint NOT NULL,
  a3 bigint NOT NULL,
  a4 integer,
  a5 timestamp with time zone NOT NULL,
  a6 timestamp with time zone NOT NULL DEFAULT now(),
  a7 bigint NOT NULL,
  a8 bigint NOT NULL,
  a9 real,
  a10 integer,

  CONSTRAINT kkkey PRIMARY KEY (a1)
)

What are the estimated costs to save this table?

A record costs:

size(bigserial)
+ size(bigint)
+ size(bigint)
+ size(integer)
+ size(timestamp)
+ size(timestamp)
+ size(bigint)
+ size(bigint)
+ size(real)
+ size(integer)
= 8 + 8 + 8 + 4 + 8 + 8 + 8 + 8 + 4 + 4 = 68 bytes

Database Page Layout of Postgres gives rather detailed information how the records land on the secondary storage, but I am not sure how to get all the numbers together.

The Linux reports

blockdev --getbsz /dev/sda1
1024

Questions:

(1) Are there any helper functions to assess the storage costs per Row (so one does not need to do those complicated computations by hand)

(2) How to put the numbers together, i.e., estimate the overhead costs for each row?

(3) How to estimate the costs for the primary key index?

Functions that give the size of columns, tables, and indexes are documented in the manual: http://www.postgresql.org/docs/9.4/static/functions-admin.html

There is no function to calculate the size of an entire record (while there is a function to know the storage cost of an individual data value (pg_column_size)), since records are in general of variable length (sometimes they are compressed), so I think you have two possibilities, either perform a catalog query to sum up all the sizes of the columns of a table, or simply get the size of a populated table and divide by the numbers of records, so to have an average size for record.


Connection to SQL Server requires TCP communication on port 50235? We have an SQL Server 2008 R2 with a database instance running on port 1433. 1433 is open for TCP and 1434 is open for UDP.

When connecting through Microsoft SQL Management Studio, our firewall detects TCP traffic at port 50235. Why? The SQL Server does not mention this port as far as I can tell.

Are you connecting to a named instance?  In which case, the initial connection is to the SQL Server Browser service (UDP 1434) and then that dynamically allocates a port for named instances.  This port is allocated on instance startup.

See this TechNet Resource on how the SQL Server Browser Service allocates a port for named instances.

There is a way to explicitly define a static port that a named instance will be listening on, but it appears that you are seeing a dynamically allocated port.


Is there a restriction which prevents you from creating a user and a role with the same name? I have a (SS2005) database with a user named MY_APPLICATION_USER. I'm trying to create a role in that database named MY_APPLICATION_USER.

When I try to do that using this script:

/****** Object:  Role [MY_APPLICATION_USER] ******/
CREATE ROLE [MY_APPLICATION_USER] AUTHORIZATION [dbo]
GO

I get the error message:

Msg 15023, Level 16, State 1, Line 2
User, group, or role 'MY_APPLICATION_USER' already exists in the current database.

Does SQL Server have a restriction where it is not allowed to have a user and a role with the same name? I googled various ways of asking that question and did not find anything which addresses this, but it's certainly possible that I missed something.

No, you cannot have a User and a Role with the same name.  These objects are all stored and referenced in sys.database_principals, requiring they are unique.

sys.database_principals MSDN Reference

Quoted from the name parameter:

  name : Name of principal, unique within the database.


Simple query is slow on 4M-rows table I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to execute:

SELECT post_id
FROM pageviews
WHERE user_id = 981
GROUP BY post_id

Here is the execution plan:

mysql> EXPLAIN SELECT post_id FROM visits WHERE user_id = 981 GROUP BY post_id;
+----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows  | Extra                                        |
+----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | visits | ref  | user_id       | user_id | 5       | const | 54696 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+

I'm not sure how to look for the cause of the slowness: maybe the table is not well configured, the mysql server not well tuned, other queries locking stuff, ... Or maybe just 4M rows is a good size to start partitioning.

Production database is on Amazon RDS

CREATE TABLE `pageviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `post_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `post_id` (`post_id`),
  KEY `user_id` (`user_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `FK_444839EAA76ED395` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `visits_ibfk_2` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4587432 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In addition to @tombom's suggestions, creating an index on (user_id, post_id) instead of (or in addition, but the less indexes the better) separate indexes on user_id and post_id will simplify the query, probably getting rid of the filesort and temporary table, plus giving you the benefits of a covering index.

This will probably lower the query execution significantly if you have a large enough buffer pool and the query is relatively frequent.

If after doing that, the query is still slow, you will need to do (pre)caching in order to speed up the query execution.


What is the maximum number of columns allowed on a table in MySQL MEMORY storage engine? The official MySQL documentation the the MEMORY storage engine covers maximum table size (in MB) but the maximum number of columns that can be defined on a table is not specified.

This question is specifically about the MEMORY storage engine - not InnoDB or MySQL in general.

From This MySQL link

  There is a hard limit of 4096 columns per table, but the effective
  maximum may be less for a given table. The exact limit depends on
  several interacting factors.

  Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

  ...

These are engine-independent MySQL limits. As you say the MEMORY engine specific manual pages do not seem to specify any additional restrictions for size or count.


Can a database file be brought back online if the file is recovered from a disk backup? TL:DR

Deleted .ndf files
Database wouldn't load
Set deleted files offline
Brought database online
Restored files from disk backup
How can I bring them online/remove the database reference?

During a (manual) disk cleanup process while the specific database was offline a developer in our shop deleted some .ndf files from the data directory because they were believed to be no longer in use. Said database was then unable to start due to the missing files. These files were not on the primary filegroup and were part of a partition scheme based on monthly data. There are no applications (outside SQL Server) which need to access the deleted files and the bulk of them were empty.

The files were set to offline to enable the database to be brought back online. The files have since been recovered from a disk backup - not a SQL Server backup. The files themselves have not been modified in 3+ months and I would like to remove them as a matter of cleanup. The partition scheme they were part of has been removed.

A full restore is not feasible as it is a 1TB database and disk space will not allow it. Is there SQL script I can run in SSMS to either bring the files back online to remove them properly or remove the references outright? I tried to do an alter of the database/file with recovery but SQL let me know they must be offline (sys.master_files tells me they are).

I'm 99% sure that setting a file to offline is a one-way operation. The only route to bring them back online is to restore the data from a full backup and then apply log backups.

There may be an obscure hack route to get this working but I'd be inclined to rebuild the database. Create a new database, transfer the schema you want to keep, copy the data across.


Reading from Postgres SQL in a Jupyter Notebook I am trying to read data directly from Postgres to my python environment with the below:

import psycopg2 as pg
import pandas.io.sql as psql
conn = pg.connect(database="abcd",user="postgres", password="xxxx")
df = pd.read_sql('SELECT * "xyz"', conn)
df.head()

But I get the below error when reading:

  DatabaseError: Execution failed on sql 'SELECT * "xyz"': syntax error at or near ""xyz""
  LINE 1: SELECT * "trans"

You didn't specify important keyword "FROM":

df = pd.read_sql('SELECT * "xyz"', conn)

so the right one is:

df = pd.read_sql('SELECT * FROM "xyz"', conn)


How to create a non-sa SQL Server login / access without control server permission I have SQL Server, and I need to give access to other users to run basic queries (just select on 1 database).

I have created a login for a user, and have amended their permissions, but once I deny access to "Control Server" that login can no longer connect.

I am totally stuck as have no experience here, but must complete this task. Please can someone help with what the next step is?

p.s. it can't be hire someone that knows what they are doing :-)

Cheers

You should not need to DENY CONTROL to a login.  Simply do not GRANT the right to that user.

Regarding the side effects of DENY CONTROL see the post here:
http://sqlity.net/en/2102/deny-control-side-effects/

Comment from that link: "The only option you have is to pay careful attention to not grant any permission to the principal that you did not indeed intend to include. You can consider using techniques like auditing to help you with this, but you cannot enforce it."

So REVOKE your DENY and just make sure that only the needed rights are granted.


Which is the better schema design for a voting system? Imagine a q/a site or forum similar to stackoverflow. I'm trying to determine which of these two methods would be the better design for a voting system:
(note: I would also like to keep record of all a users vote decisions; and you can assume the site experiences a relatively high amount of traffic)

create a junction table named VOTE referencing USERS, and POSTS. The problem I see with this is that when a view displays a topic listing all the posts, this seems very inefficient when we want to display each post's "score", since we'd have to count N votes for M number of posts; if the forum is very large this will be a major hindrance.
create the above junction table, but also keep a score on the posts table and simply use this to alleviate the above performance issue. The problem with this is that it describes something that is already described in another table which isn't a good idea either, if im correct?
Use some nosql solution for this part of the application; however this introduces another moving part into the equation and complicates things by having two db technologies working with eachother.

(my solution doesn't have to be one of these three, if you have a fourth or even fifth it is more than welcome, thank you)

(oh, and if it matters in any way, ill be using postgres)

Approach 1 is the cleanest with no redundancy. However I can understand your worries about scanning through a huge table just to count a post votes.

So approach 2 which features some "caching" seams fair.
The downside is that you add some redundancy but this is not even the main issue. You also lose consistency. It's possible to have a post with 500 votes while the votes column in the post table only says 300. This is a potential risk.

So it's the choice between a clean and elegant approach and increased performance that though comes along with redundancy and loss of consistency.

In my opinion it'd be OK to go with the 2nd option though it's somewhat crappy.
I think though that the performance difference will not be that huge as one could expect.
For example every time you're displaying a Question and it's answers you are doing a SELECTION on ALL Answers ever given to all questions.
Best way would be to put the 1st and 2nd option to a test on a massive amount of test data but for the time being I'd go with the first option. It is the best and cleanest and performance optimization can be done better elsewhere.

Note you save yourself the time to update 2 places with every vote.


Select to get a value from a id combined with a max value I have a table like this:

idAction | numberLine | action
   1     |     1      |   1
   1     |     2      |   2
   1     |     3      |   7
   1     |     4      |   6
   2     |     1      |   2
   1     |     2      |   5
   1     |     3      |   3

I need to select for each idAction where the numberLine is MAX the action for that row. So in this example I should get two rows:

idAction | numberLine | action
   1     |     4      |   6
   2     |     3      |   3

I have tried with Select idAction, MAX(numberLine), action from table Group by idAction. But it says to put action in the group by, but if I do that it gets me all the rows.

greatest-n-per-group problems are typically solved with a window function:

select idAction, numberLine, action
from (
  select idAction, numberLine, action,
         row_number() over (partition by idaction order by numberline desc) as rn
  from the_table
) t
where rn = 1;

If you can have more then one maximum value for numberline the above will only return one of them. If you want to see all of them, use dense_rank() instead of row_number()


How to find offset/index base on id with order? How to find offset/index base on id with order?

SELECT p.*
FROM product p
WHERE p.id = $1
ORDER BY p.id desc NULLS LAST

id:1 return 0

id |
1  |
2  |
3  |

If you mean the number of rows with a smaller id value:

SELECT count(*) FROM product WHERE id < $1;


Updating one table from another table where non-primary-key field is similar string I have a database in Access that I am trying to fix (actually, I'm working on a copy of the original).

There are two tables, one with data input errors, and a second that is a copy of those errors, but fixed, if that makes sense.  However the second table only contains 3 out of 14-ish fields, and the primary key is not among them.

I would like to update the first table with the information from the second, searching for records to update based on similarity between the Title field in both.  The second has had some typos fixed throughout as well...

I know some SQL but not much.

Without the primary key, fixing the data with an update statement is likely to get very messy.  You're most likely better off getting a fresh export of the original data with the PK included, run the process/es that you did to clean up the data, then run an update statement.  And then put constraints on the table to prevent the errors from occurring again.

The update statement should be fairly simple though.  I don't know the full syntax for Access, but here's the general SQL statement I'd use on SQL Server:

update TableA
set FieldA = source.FieldA
from TableA destination
inner join TableB source
on destination.PK_ID = source.PK_ID


Is there a way to have a system generated report of list of DBA in SQL Server? I'm an auditor, and I want to ask if there is a list of system generated report of DBA users in SQL Server? Per my audit client, there is no way to generate it.

However, I'm thinking that this is a data also in which you can just generate to the SQL Server databases. I just want to validate that the list of DBAs is restricted to DBA of our company for legitimate business need and security purpose.

This gives the list of users/logins/groups that have sysdba which sounds like your "Database Administrator" role.  This is not all logins, just the ones with the most powerful role.

USE master
GO

SELECT  p.name AS [loginname] ,
        CASE
        WHEN p.is_disabled = 1
        THEN 'LOCKED'
        ELSE 'OPEN'
        END as 'Account Status'
FROM    sys.server_principals p
        JOIN sys.syslogins s ON p.sid = s.sid
WHERE   p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
        -- Logins that are not process logins
        AND p.name NOT LIKE '##%'
        -- Logins that are sysadmins
        AND s.sysadmin = 1
GO

Or you may prefer this query that shows who has the privileged roles on the database.  As a comment indicates if you want to focus on what a login can do you would include the securityadmin role as users with this role can elevate their privileges.  See here for the documentation.  If there is poor access control you never know what you will find...

select * from sys.syslogins
go
select * from sys.sql_logins
go
sp_helprolemember 'db_securityadmin'
go
sp_helprolemember 'db_owner'
go
sp_helprolemember 'db_accessadmin'
go
sp_helpsrvrolemember 'sysadmin'
go
sp_helpsrvrolemember 'serveradmin'
go
sp_helpsrvrolemember 'securityadmin'
go


Run Unix command using PL/SQL Is it possible to run a Unix command using a query in Oracle?

I want to run simple commands (like df -h) using a query.

Is this at all possible or am I wasting my time? I don't want to use a different language like Java or C to call a procedure, it needs to purely PL/SQL.

You can use the DBMS_Scheduler

DBMS_SCHEDULER.create_program
(
program_name => 'testScript',
program_type => 'EXECUTABLE',
program_action => '/home/someone/scripts/script.sh',
number_of_arguments => 0,
enabled => TRUE,
comments => 'Just a Test'
);
end;
/

and a little hosekeeping:

exec DBMS_SCHEDULER.drop_program(PROGRAM_NAME => 'testScript');


Mysql Order By affecting a Query with column alias, group by, and having Description:

I have a query with column alias, group by and having. It's getting altered when I add an order by.

This strange behavior is only happening in 5.6.16 and 5.6.21 (which I was using in development, over Windows)

It was not happening on 5.5.41 and 5.5.43 (which I had in production, over Linux)
So I downgraded my local MySQL to 5.5.* and the query worked as expected.

How to repeat:

I'm not sure on how to repeat, but I will do my best in order to show this strange behavior

My query:

SELECT
a.*
, if(u.status = 1 AND a.acre_aprobado = 0, 5, u.status) tipo
FROM acreditado a
JOIN users u ON (`a`.`acre_id` = `u`.`id`)
WHERE `acre_test` = 0
GROUP BY `a`.`acre_id`
HAVING tipo = '1'
#ORDER BY `acre_aprobado` ASC

The result in both 5.5.* and 5.6.*: (without the order by)

============================================================
acre_id | obac_id | ... | tipo
27      | 23      | ... | 1
37      | 22      | ... | 1
44      | 22      | ... | 1
46      | 22      | ... | 1
============================================================

Now the result when I add order by:

5.5.*

============================================================
acre_id | obac_id | ... | tipo
27      | 23      | ... | 1
37      | 22      | ... | 1
44      | 22      | ... | 1
46      | 22      | ... | 1
============================================================

5.6.*

============================================================
acre_id | obac_id | ... | tipo
25      | 24      | ... | 0
32      | 24      | ... | 0
44      | 22      | ... | 1
46      | 22      | ... | 1
============================================================

As you can see, in 5.5.* everything works as expected, but in 5.6.* order by is modifying the result, and it's even making "having" don't filter

I created an SQL fiddle

http://sqlfiddle.com/#!9/c928c/8

First run the query as is, and then uncomment "order by" and run it.

I read something about MySQL Extensions to GROUP BY from the post
Why does MySQL allow HAVING to use SELECT aliases? that might have some to do with this issue, but I wonder why the different behavior between 5.5.* and 5.6.*

The HAVING tipo = '1' should in no way allow output to include tipo=0, regardless of GROUP BY issues, etc.

File a bug report at http://bugs.mysql.com .


service broker with mirroring After we tried configure a database with service broker into an always on environment and failed (the service broker is not working properly) we are now considering to try mirroring for high availability.

The client demands it'll use automatic failover which i know needs a witness server. i have configured mirroring many times before but never with service broker.
Has anyone tried and it worked? I tried to find an official documentation for it but didn't find anything.
What are the steps to configure it on the mirrored database? will it cause performance issues to use service broker with mirroring which is also synchronized?

There is nothing special about configuring Service Broker to work with mirroring.  Configure Service Broker, then enable mirroring.

Having said that, you'll need to test failover to ensure any clients accessing the Service Broker infrastructure, such as queues, are able to failover gracefully from primary to secondary.

I'd recommend building a test-network using Virtual Machines including a primary, a secondary, and a witness VM.  Restore a copy of the target database into the primary, and configure mirroring to see how it works.


convert sql query to laravel I have an sql qyery.

SELECT rt.id, rt.type, rt.total_no_of_rooms
     , SUM(r.no_of_rooms) as "reserved rooms"
     , rt.total_no_of_rooms - SUM(r.no_of_rooms) as "rooms available"
FROM Roomtypes rt
LEFT OUTER JOIN Reservations r
    ON r.roomtype_id = rt.id
    AND '2018-04-11' >= r.check_in
    AND '2018-04-12' <= r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

can anyone tell e how to convert it to laravel

This query is generated from midnightcowboycoder.com. Don't forget to replace backtick with a single quote.

DB::select(`rt.id`,`rt.type`,`rt.total_no_of_rooms`)
        ->addSelect(DB::raw(`SUM(r.no_of_rooms) as reserved rooms`))
        ->selectSub(`rt.total_no_of_rooms`, `rooms available`)
        ->selectSub(`-`, `rooms available`)
        ->selectSub(`SUM`, `rooms available`)
        ->from(`Roomtypes as rt`)
        ->join(`Reservations as r`, function($join) {
            $join->on(`r.roomtype_id`, `=`, `rt.id`)
                ->on(`2018-04-11`, `>=`, `r.check_in`)
                ->on(`2018-04-12`, `<=`, `r.check_out`);
            })
        ->groupBy(`rt.id`)
        ->groupBy(`rt.type`)
        ->groupBy(`rt.total_no_of_rooms`)
        ->get();


Why does the concatenation order change with a User-Defined Table Type? I've encountered what seems to me to be a strange ordering issue when using CONCAT with a User Defined Table Type.

I've put the SQL below that allows me to reproduce this:

I have the following user type:

CREATE TYPE [dbo].[StringList] AS TABLE
(
    [Value] VARCHAR(8000) NOT NULL
)

And the following query to set everything up:

DECLARE @ColumnNames AS TABLE([ColumnName] VARCHAR(MAX))
DECLARE @ValueTable AS [dbo].[StringList] --VariableLineA
--DECLARE @ValueTable AS TABLE([Value] VARCHAR(MAX)) --VariableLineB

INSERT INTO @ColumnNames([ColumnName])
VALUES
('Forename'),
('Surname')

INSERT INTO @ValueTable([Value])
VALUES
('Ellis'), ('Ali')

And this is the query I'm running:

SELECT CONCAT(STRING_AGG(CONCAT([ColumnName], ' - ',  [sc].[Value]), ', '), ', ') -- QueryLineA
        --CONCAT(STRING_AGG([ColumnName] + ' - ' + [sc].[Value], ', '), ', ') --QueryLineB
FROM        @ValueTable AS [sc]
INNER JOIN  @ColumnNames ON 1=1

If the VariableLineB line is uncommented, and the VariableLineA line is commented out, the following string is returned for both query lines if run individually or together:

Forename - Ellis, Forename - Ali, Surname - Ellis, Surname - Ali,

If the VariableLineA line is uncommented and the VariableLineB line is commented out, the following string is returned for QueryLineB, and from both queries if run together, but not QueryLineA on its own:

Forename - Ellis, Forename - Ali, Surname - Ellis, Surname - Ali,

When QueryLineA is run on its own, the following string is returned:

Forename - Ellis, Surname - Ellis, Forename - Ali, Surname - Ali,

Why has the order changed?  What am I seeing here, and is there any way to prevent this from happening?

In terms of usage, the values of @ValueTable would be getting passed into a function, and it's important that the original order (whatever that may be) remains the same.

The order changes because the order of the tables involved in the cross join has changed. In one plan @ValueTable is the outer (driving) table, in the other @ColumnNames is the outer table.

for each row in @ColumnNames
    for each row in @ValueTable
    concatenate the result
    //produces Forename - Ellis, Forename - Ali, Surname - Ellis, Surname - Ali,

for each row in @ValueTable
    for each row in @ColumnNames
    concatenate the result
    //produces "Forename - Ellis, Surname - Ellis, Forename - Ali, Surname - Ali,"

Order is never guaranteed unless specified in the query, so if consistent order is important you should add a stable order by clause defining order for both tables inside the STRING_AGG definition.

SELECT CONCAT(STRING_AGG(CONCAT([ColumnName], ' - ',  [sc].[Value]), ', ')
                        WITHIN GROUP (ORDER BY [ColumnName],  [sc].[Value])  , ', ') -- QueryLineA
FROM        @ValueTable AS [sc]
INNER JOIN  @ColumnNames ON 1=1


Calculate Average between two dates of different rows I have the below table

BIDID AppID AppStatus   Time
23390   16  In Review   2017-07-03
23390   16  Approved    2017-09-03
23390   16  In Review   2017-10-11
23390   16  Approved    2017-12-11
23390   16  Approved    2017-14-11

I want to calculate the time difference according to the following logic

First find the In Review status and then get the next approved status and then calculate the time difference. Then get the next In Review status and then next Approved and calculate the time difference. Then get an average.

So in this case what I am looking for is

Say

2017-07-03  - 2017-09-03  - 2 days
    2017-10-11  - 2017-12-11   -2 days

Last Approved is ignored because there was no matching In review before it

Then get total average (2 + 2) / 2 = 2

Can someone please tell me how I can achieve this.

Thanks

WITH ct AS
(
SELECT
    CASE WHEN AppStatus = 'Approved'
              AND LAG(AppStatus)
                  OVER (PARTITION BY BIDID, AppID ORDER BY [Time]) = 'In Review'
         THEN
             DATEDIFF (day, LAG([Time]) OVER (PARTITION BY BIDID, AppId
                                             ORDER BY [Time]), [Time])
         ELSE
             0
    END days
FROM
    t
)
SELECT
    SUM(days) / COUNT(*) Average
FROM
    ct
WHERE
    days <> 0;

The CTE part calculates DATEDIFF with the previous row, every time it finds an 'Approved' AppStatus after an 'In review'AppStatus.

BIDID | AppID | AppStatus | Time                | days
----: | ----: | :-------- | :------------------ | ---:
23390 |    16 | In Review | 07/03/2017 00:00:00 |    0
23390 |    16 | Approved  | 09/03/2017 00:00:00 |    2
23390 |    16 | In Review | 10/11/2017 00:00:00 |    0
23390 |    16 | Approved  | 12/11/2017 00:00:00 |    2
23390 |    16 | Approved  | 14/11/2017 00:00:00 |    0

The simply sums the calculated days and divide by the rows that has calculated days.

| Average |
| ------: |
|       2 |

db<>fiddle here

UPDATE

Due you are on 2008 and as per comments you pointed out there is an IDENTITY column, you can simulate LAG/LEAD function using an APPLY join with the next ID.

WITH ct AS
(
SELECT
    CASE WHEN t1.AppStatus = 'In Review' AND t2.AppStatus = 'Approved'
         THEN DATEDIFF(day, t1.[Time], t2.[Time])
         ELSE 0
    END as Days
FROM
    t t1
CROSS APPLY(SELECT TOP 1 *
            FROM t
            WHERE id > t1.id
            ORDER BY id) t2
-- WHERE id < 100
)
SELECT
    SUM(days) / COUNT(*) Average
FROM
    ct
WHERE
    days <> 0;

db<>fiddle here


Database Design / ER Diagram Please take a look at this ER Diagram that I started.
I just don't know if I've set it up correctly (I have not made any relationships yet). I just want to know if I have the appropriate "Tables" or do I need to delete or add new ones...

Read the directions/requirements in the "Grey Box."

A Google of "open source subscription software rent a bike" gives a number of interesting results, including this one. You can take and adapt ideas from some of these programmes - the beauty of Open Source.

Just a couple of thoughts about the ERD. Why include colour - that can readily change - and is it really relevant? I mean, how many people care about the colour of a hire bike? Here in Dublin, they are the same colour and have a uniform appearance - I think that this "branding" contributed in some way to the success of the scheme. You also have size - again, here in Dublin, a similar scheme uses 1 size (adjustable).

All of your bicycle attributes (top left of ERD) should be in 1 table with PK Bicycle_ID - model, make, size and colour follow logically from the Bicycle_ID.

Also, one thing that struck me was this GPS stuff - are you sure that there aren't EU Data Protection issues with this? Has it been looked at by a lawyer competent in EU DP law? Finally, another thing about GPS - maybe you should have a GPS coordinate for the Terminals? People regularly now navigate using phones with GPS - they'll be able to find the nearest terminal more easily this way.


Organizing and assigning privileges to multiple objects in MySQL I'm new to MySQL, moving from MS SQL Server, and I'm wondering if there's a way to logically group tables, views, sp's, etc in the same manner as you would with schemas in SQL Server....seeing as how the concept of a schema is entirely different in MySQL.  In SQL Server I was able to use both schemas and roles to easily manage permissions.  I've found that I can create roles in MySQL and suppose I could write a script that grants permissions on a gazillion objects that way.  Just wondering if there is a cleaner, easily maintained approach.  Am I missing something?

MySQL does not support multiple schema in Database, you must create separate database and grant permissions to roles as per your requirements.


Find ClientHost using ClientIP - SQL Server 2014 Recently we started getting alerts as below :

EventID 18456 - Login failed for user 'abc'. Reason: Could not find a login matching the name provided. [CLIENT: xx.xx.xx.xxx]
EventID 17828 -
The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: xx.xx.xx.xxx]

Below are the steps I did so far to find the Hostname using the Client IP in above log (xx.xx.xx.xxx) :

nststat -a xx.xx.xx.xxx

This does not give me any information about Client Host name and says HostName not found .

ping -a xx.xx.xx.xxx

  This gives me Reply message for IP mentioned .

nslookup xx.xx.xx.xxx

  Domain cant find IP.

What else should I do to find out what is wrong with failed login and whose login it is and from which machine ?

Try the following (taken from https://stackoverflow.com/questions/15873060/how-to-get-machine-name-from-ip-address-in-sql-server-2008)

declare @IP as varchar(15)
declare @cmd as varchar(1000)
set @IP='xxxxx'
SET @cmd = 'ping -a -n 1 ' + @IP
Create Table #Output (Output varchar(150) default(''))
INSERT INTO #Output
EXEC xp_cmdshell @cmd
Begin try
Select top 1 Replace(LEFT([Output],CHARINDEX('[', [Output])-2),'Pinging ','') as HostName from #Output where Output like 'Pinging%'
End Try
Begin catch
Select 'Host name for:' + @IP +' could not be find'
End catch


UPDATE/INSERT into Dynamic Columns I am trying to update columns based on the value of the inserted column of the table 'my_table'. I want to be able to record the timestamp of the values changed.
I am using the triggers but I can't find out how to choose the column dynamically

CREATE TRIGGER `record_time` AFTER UPDATE ON `my_table`
 FOR EACH ROW INSERT INTO `record_changes` (dynamic_column) values (CUR_DATE())

here you see that I want to choose the column dynamically(dynamic_column) based on the value of the column in the table 'my_table'.

table structure for 'record_changes' is something like this,
a,b,c,d.... upto 9 columns. The value being updated in 'my_table' is one of these 9 values.
So I want to select the column to update based on the value of the 'my_table'.
Can someone help?

Unfortunately, Dynamic SQL is not permitted in Triggers.
Notwithstanding, what you are asking for is still possible, but you will have to apply some elbow grease.

I wrote an answer to the post Disable trigger for just one table. What I did was create a user variable @TRIGGER_DISABLED as a flag to control if the code within the trigger was to be executed.

In your particular case, you will need 9 flags. You will also need a BEFORE UPDATE trigger:

DELIMITER $$
CREATE TRIGGER `setup_record_time` BEFORE UPDATE ON `my_table`
FOR EACH ROW
BEGIN
  SET @rec_col1 = IF(NEW.col1=OLD.col1,0,1);
  SET @rec_col2 = IF(NEW.col2=OLD.col2,0,1);
  SET @rec_col3 = IF(NEW.col3=OLD.col3,0,1);
  SET @rec_col4 = IF(NEW.col4=OLD.col4,0,1);
  SET @rec_col5 = IF(NEW.col5=OLD.col5,0,1);
  SET @rec_col6 = IF(NEW.col6=OLD.col6,0,1);
  SET @rec_col7 = IF(NEW.col7=OLD.col7,0,1);
  SET @rec_col8 = IF(NEW.col8=OLD.col8,0,1);
  SET @rec_col9 = IF(NEW.col9=OLD.col9,0,1);
END $$
DELIMITER $$

The AFTER UPDATE trigger looks like this:

DELIMITER $$
CREATE TRIGGER `record_time` AFTER UPDATE ON `my_table`
FOR EACH ROW
BEGIN
  IF @rec_col1 = 1 THEN INSERT INTO `record_changes` (col1) values (CUR_DATE()); END IF;
  IF @rec_col2 = 1 THEN INSERT INTO `record_changes` (col2) values (CUR_DATE()); END IF;
  IF @rec_col3 = 1 THEN INSERT INTO `record_changes` (col3) values (CUR_DATE()); END IF;
  IF @rec_col4 = 1 THEN INSERT INTO `record_changes` (col4) values (CUR_DATE()); END IF;
  IF @rec_col5 = 1 THEN INSERT INTO `record_changes` (col5) values (CUR_DATE()); END IF;
  IF @rec_col6 = 1 THEN INSERT INTO `record_changes` (col6) values (CUR_DATE()); END IF;
  IF @rec_col7 = 1 THEN INSERT INTO `record_changes` (col7) values (CUR_DATE()); END IF;
  IF @rec_col8 = 1 THEN INSERT INTO `record_changes` (col8) values (CUR_DATE()); END IF;
  IF @rec_col9 = 1 THEN INSERT INTO `record_changes` (col9) values (CUR_DATE()); END IF;
END $$
DELIMITER $$

Just to be on the safe side, you should initialize these 9 user variables with 0.

SET @rec_col1 = 0;
SET @rec_col2 = 0;
SET @rec_col3 = 0;
SET @rec_col4 = 0;
SET @rec_col5 = 0;
SET @rec_col6 = 0;
SET @rec_col7 = 0;
SET @rec_col8 = 0;
SET @rec_col9 = 0;
UPDATE mytable ... ;

That way, your session dictates the trigger's behavior.

Give it a Try !!!


Alter of stored procedure and execution performance I would like to understand how an alter of a stored procedure without modifications can impact performance. I had a situation where a production environment had its database schema updated and a particular stored procedure (that referenced a table that had schema changes but required no modifications) that normally ran in minutes went to hours.   The procedure does consolidation work and has to query a table with about 800 million records and this table was modified as part of the schema update (table swap).  We found that when we made a copy of the procedure and executed it the normal execution time returned but the original procedure performance did not improve.  The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why.    I'm not clear what resolved our issues but I'm more concerned how to keep it from happening again.  The procedure didn't have an explicit recompile statement so I'm not sure it was implicitly recompiled and the cache plan replaced.

Again, I am just trying to understand the mechanics of how altering the procedure resolved the issue so I can plan accordingly for any schema changes.

I appreciate your input!

Had a situation where a production environment had DB schema updated
The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why.

Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query.
From BOL - Recompiling Execution Plans :


Use index intersection feature in MongoDB 2.6 as compound shard key I have three indexes on name, age, salary.

I can use them as compound index on MongoDB 2.6 (name and age together), but can I use the same indexes as compound shard key?

I also have a constraint that this will be a unique shard key.

Will it work?

In order to deploy a shard key in your case (name, age) you need to pre-create an index on (name, age).

The shard key can be unique, but I don't see how a unique constraint fits here, for example isn't it quite likely to have two same records? (name:Mary age:26?).

You cannot enforce a unique constraint directly to the database without a compound index.

Index intersection is to combine two index on the run-time. For example, you have an index on name and one on age and a query {name='somename' and age='someage'}. Prior to 2.6, you needed a compound index on (name, age). On 2.6, the optimizer can use these individual indexes to execute the above query.


Is it possible for total participation constraints to be enforced by an attribute? For a school Food Delivery application I am developing, I've been told to use the most accurate ER model that captures as many constraints as possible. In particular, I have decided to enforce a given constraint

"Each order’s food items must be from a single restaurant"

using an aggregation from Orders to Restaurant-FoodItem relationship. However, as the application layer requires to get a restaurant_id associated to an Order, what I am currently doing to get restaurant_id is to join all the way from Orders to Restaurants, following the edges on the ER model.

There was another alternative suggested by my teammate to just put restaurant_id as an attribute in Orders to enforce the constraint as well as allow easier more efficient retrieval of restaurant_id. May I know whether the suggested alternative allows us to enforce the earlier constraint?

This would be one way to enforce the constraint.

-- User USR exists.
--
user {USR}
  PK {USR}

-- Restaurant RST exists.
--
restaurant {RST}
        PK {RST}

-- Fare (food, drink) FAR exists.
--
fare {FAR}
  PK {FAR}

-- Restaurant RST serves fare FAR.
--
menu {RST, FAR}
  PK {RST, FAR}

FK1 {RST} REFERENCES restaurant {RST}
FK2 {FAR} REFERENCES fare       {FAR}

-- User USR placed (his/her) order number ORD#,
-- from restaurant RST.
--
order {USR, ORD#, RST}
   PK {USR, ORD#}
   SK {USR, ORD#, RST}

FK1 {USR} REFERENCES user       {USR}
FK2 {RST} REFERENCES restaurant {RST}

-- User USR ordered QTY of fare FAR
-- from restaurant RST in (his/her) order number ORD#,
-- as item number ITM# of that order.
--
order_item {USR, ORD#, RST, ITM#, FAR, QTY}
        PK {USR, ORD#, RST, ITM#}
        AK {USR, ORD#, RST, FAR}

  FK1 {USR, ORD#, RST} REFERENCES
order {USR, ORD#, RST}

 FK2 {RST, FAR} REFERENCES
menu {RST, FAR}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Using suffix # to save on screen space.
Works for SQL Server and Oracle, for others use _NO.
For example, rename ORD# to ORD_NO.


null check and string concatenation Can I do better than this query?

 select func_returns_str('key1') ||
        nvl2( func_returns_str('key2'),
              ',' || func_returns_str('key2'),
              null)
   from dual;

I would like to avoid calling func_returns_str('key2') twice.
func_returns_str returns a varchar.

Elaborating further:
 func_returns_str could be a generic function like substr() in Oracle which you can't change the definition.

You can create a wrapper for the nvl2 call:

create or replace function add_comma(s varchar) return varchar as
begin
  return case when s is null then null else ','||s end;
end;
/

with t as (select 'key2' as val from dual union all select null from dual)
select val, nvl2( substr(val, 3, 2), ','||substr(val, 3, 2), null) as orig,
       add_comma(substr(val, 3, 2)) as wrapped
from t;

VAL  ORIG WRAPPED
---- ---- -------
key2 ,y2  ,y2

alternatively you can use a with clause (also know as a CTE ):

with t as (select substr('key2', 3, 2) as s from dual)
select nvl2(s, ','||s, null) from t;


Does a record with NULL data does not eat any storage on the harddrive? This is not an expert question about database, so I'm not sure if this question can be posted here or not. So if this question is out of place, feel free to close it.

It's a long development story, but long story short, the managers want to create a generic table to store various assets data. They want the table to be able to hold various type of data, eg. they want the table to be able to hold a car's number of tires, car's color, car's year model, etc, but they also want the table to be able to hold a house's address, floor size, etc.

So they want to create this generic table for record all the various assets' details with like 20 VARCHAR(45) columns, 20 DOUBLE columns, and 20 BOOLEAN columns. I have been against this decision because I'm afraid that the unused column on the row will used up space in harddrive. But they said that it would not used up space in harddrive if the record is NULL. So if for 60 columns, I only use 3 column in a row of data, then the 57 other columns will not take space because they are NULL. Is this true? Which one is true? Thanks.

You haven't mentioned your RDBMS system. You should always do this when asking any question.

Secondly, this would be easy to test. I'm sure that NULLs will add storage - maybe your question is about "How much storage exactly?"?

Why not add 10,000 records of your structure with full data, then with NULL data and compare the size on disk. I'm fairly sure that the exact number will vary by RDBMS. There will be some overhead associated with fields even if empty.

There are many reasons why managers decide to do things - one of them is a lack of understanding of the technology. One solution might be to have another table "hanging" off your main table - only those records for which extra data is available will be in your subsidiary table?

To answer your last comment

"i have also looking at another solution, like having a table with a
few columns, with each row is storing one property key with its value on
the next column. But this ended up pushes the development back too far
because of the complexity in data design and communication."

This sounds like you are considering an EAV-type design - NOT a good idea - see here and links from that post.


Efficiently searching geolocations - LAT/LNG and DECIMALs I have a database with > 7.5 million rows (and growing), each with an image record that has a specific LAT/LNG geolocation representing where the photo was taken, stored as DECIMAL values -

 mysql> describe image_meta;
 +---------------------+------------------+------+-----+-------------------+----------------+
 | Field               | Type             | Null | Key | Default           | Extra          |
 +---------------------+------------------+------+-----+-------------------+----------------+
 | id                  | int(11) unsigned | NO   | PRI | NULL              | auto_increment |
 | media_id            | varchar(255)     | YES  | UNI | NULL              |                |
 | user_id             | int(11) unsigned | YES  | MUL | NULL              |                |
 | create_time_unix    | int(11) unsigned | YES  | MUL | NULL              |                |
 | create_time_zulu    | datetime         | YES  |     | NULL              |                |
 | latitude            | decimal(12,10)   | YES  | MUL | NULL              |                |
 | longitude           | decimal(13,10)   | YES  | MUL | NULL              |                |

I want to search within a rectangular area, defined by LAT/LNG, and the queries are very slow.

I am using this query:

SELECT user_id FROM image_meta WHERE (latitude BETWEEN 40.779769 AND 40.792399 AND longitude BETWEEN -73.988457 AND -73.963308);

with the result:

123569 rows in set (8 min 28.99 sec)

What's the best way to go about making this go faster? Should I search a broader area with fewer significant figures, then use PHP to narrow the results by the more precise LAT/LNG boundaries?

I'm a novice SQL guy (always had DBAs to help me in the past) but I'm doing this project solo. Thanks in advance...

"Standard" MySQL indexes use B-Trees, which are horrible for searching ranges in 2 or more dimensions (they can only use the first column). You want to use R-Trees, available, like in many other RDBMS, when using the GIS/spatial extensions for MySQL.

They require specific datatypes. POINT is what you want- you will have to convert the latitude and the longitude to this format. Then create a spatial index. Then using the function Contains() or MBRContains().

Please note that this feature is typicaly criticised in MySQL for the lack of features (for example, it wasn't available for InnoDB before 5.7, no distance function, no projection support, ...). Ironically, the lack of those features made it a very fast implementation -when it worked. And it should be enough for what you want to do.

If you cannot use spatial indexing in MySQL, an index on (latitude, longitude) may be a bit faster in 5.6 with ICP, or you can alternatively try to use some indexing tricks. If that does not work for you, and it is still too slow, you will need an external indexing solution or a complete change of database server.


How do you query supertypes to get subtype data? I'm a bit of a DBA newbie here (coming from a programming background having to fill the role of a DBA), so please excuse by blantant and somewhat basic ignorance.

Using a simple relationship between a supertype VEHICLE and its relative subtypes that are actual vehicles, like cars, boats or planes etc.  If you query the Vehicle table to get some results, say, show me all vehicles that have a colour = RED, then how does the database know how to query the CAR table, seeing as that is the only table in this example that has a column to do with colour.

I supsect I must have some kind of vehicle type lookup type table, which should also point to the table relevant for that subtype, but I'm not sure if I'm over complicating things or if this is even good practice.  Any help by experienced, wiser heads would be much appreciated.

VEHICLE (supertype)

╔════════╦══════════╗
║ VEH_ID ║  FOO_COL ║
╠════════╬══════════╣
║     28 ║ foo      ║
║     52 ║ column   ║
║     18 ║ data     ║
╚════════╩══════════╝

CAR (subtype)

╔════════╦══════════╗
║ CAR_ID ║  COLOUR  ║
╠════════╬══════════╣
║    52  ║ red      ║
╚════════╩══════════╝

BOAT (subtype)

╔════════╦══════════╗
║ BOAT_ID║  SPEED   ║
╠════════╬══════════╣
║    18  ║ VERY FAST║
╚════════╩══════════╝

Unless you use dynamic SQL to build the query (which adds no value anyway) then SQL is a "fixed column" language. That is, you'd have to send all columns you need. This example is bad in that you have all columns but you get the idea.

SELECT
   V.*,
   C.*,
   B.*
FROM
   Vehicle V
   LEFT JOIN Car C  ON V.VEH_ID = C.CAR_ID
   LEFT JOIN Boat B ON V.VEH_ID = B.BOAT_ID

Another option is to hide the VEHICLE-CAR join behind a view "CARS" and VEHICLE-BOAT join behind another view "BOATS". However, you can't UNION them as the have different columns and types

Basically, you don't query supertypes/subtypes like this because each subtype gives a different resultset.


Percona MySQL 5.6 databases ignoring key length at constrait creation We have a 3 servers running Percona MySQL 5.6 in a Master/Master configuration that are all reporting errors on key length, either too long or not specified. The structure of the databases is all generated from a mysqldump on existing standalone MySQL 5.6 databases that do not report these errors.

Key too long error:

2015-04-13 11:00:29 26692 [ERROR] Slave SQL: Error 'Specified key was too long; max key length is 767 bytes' on query. Default database: 'dataBaseName'. Query: 'alter table UserGroup add constraint UK_f6x7vpmw83jptft2toy0wsfk6 unique (groupName)', Error_code: 1071

Table structure:

CREATE TABLE `UserGroup` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `groupName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `groupType` int(11) NOT NULL,
 `clientAccountSettings_id` bigint(20) DEFAULT NULL,
 `timestamp4support` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `groupName` (`groupName`(191)),
 KEY `FK8A5BE1545E3F3DC` (`clientAccountSettings_id`),
 CONSTRAINT `FK8A5BE1545E3F3DC` FOREIGN KEY (`clientAccountSettings_id`) REFERENCES `ClientAccountSettings` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Although the column used in the unique constraint reported in the error is 255 chars long using utf8mb4 (1020 bytes) we are only trying to use 191 chars (764 bytes) which works on the standalone system but not on the Percona system.

Unspecified key length error:

2015-04-13 10:56:25 26692 [ERROR] Slave SQL: Error 'BLOB/TEXT column 'name' used in key specification without a key length' on query. Default database: 'dataBaseName'. Query: 'alter table ISP add constraint UK_tfe1hfqbgydnfe2siovm1i80k unique (name)', Error_code: 1170

Table structure:

CREATE TABLE `ISP` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `abuseEmail` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `timestamp4support` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=465 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Again, as you can see we are trying to specify the index length (working in standalone MySQL) but it is getting ignored.

In order to try and get around the key too long error, I have turned on innodb_large_prefix but restarting the first instance caused MySQL to remove all data and resync all 2.7TB again, this is due to all the replication errors, of which there are quite a few, all the same as above. I still have to apply this to the other 2 servers.

Is there anything that could change the behaviour so it utilises the specified key length? At the moment this is only a test service so I can make changes but with the way things stand it will take days to get the changes across the cluster.

Does anybody know why the service ignores the key length parameter? Is this a bug?

By default, an index key for a single-column index can be up to 767
  bytes

–MySQL Reference Manual

The size of your index is:

191 (characters) * 4 (utf8mb4 max size per character) + 4 (length) + 1 (NULL) = 769

The calculation may not be 100% exact, I am not fully sure about the length requirements, but I will assume it is not a bug.

The reason why it may be working initially could be because you may be using utf8, not utf8mb4 on the original system (which is 3 bytes in size per character instead of 4) or you had enabled the variable innodb_large_prefix.

In any case, I would recommend you to fix it enabling that very same variable, which will allow you prefixes of up to 3072 bytes (it will be the default in newer versions).

Edit: ALTER TABLEs on a production PXC are usually done in RSU mode.


Recover Instance after Log File disk error This is mainly theoretical, but I would like to have a documented list of options if this happens in the future.

Today we had a critical disk error on the SAN, meaning the disk holding the Transaction Log files for one of our production instances crashed and initially looked like it was dead. Obviously, down go the instance, databases, and therefore the applications running on it.

Our data center guys were busy working on the what, why and how's of the disk failure, meanwhile I was quickly coming up with a list of options for database recovery.

Ok, so the Data Center guys recovered the disk. It was a VPLEX error, rather than a physical hardware fault.

But what I found meanwhile was that I didn't have a great deal of options. The instance would not start, given that all log files for both Sys and User databases were out of reach. Would the instance have restarted if the Sys database log files were on a separate disk that was 'Up'

I could access the .mdf files, so I had the option of copying them over to another server, then attaching them with new log files on another volume. Either that or restoring the databases to another server\instance using our fairly resilient backups. Either option meant work for the App guys, because all the applications and associated services would need to re pointed.

I had another option of trashing the instance on the server and reinstalling it with the same instance name, then restoring all the databases from full ad log backups. Theoretically, this meant no work for the App team but had a serious time overhead for me (the only DBA).

Am I missing any options here? I only started this job recently, and its fair to say documentation here is limited. I've been busy this past few months putting together inventories of our SQL Estate, looking at patching/upgrade gaps etc, and getting involved in several projects. I think its fair to say that a documented disaster recovery plan for just this type of scenario is now at the top of our hierarchy's agenda.

Any help appreciated.

Disaster Recovery Plan depends on
1.Acceptable Downtime
2.Acceptable Dataloss
Any Disaster recovery  plan you devise will revolve around the above two points only

Either option meant work for the App guys, because all the applications and associated services would need to re pointed
Theoretically, this meant no work for the App team but had a serious time overhead for me (the only DBA).

your Disaster Recovery option doesn't depend on the amount of work involved and who has what share in that..
You will have to sit together with Business Guys and get Clarity on what is the downtime and dataloss they can afford .Based on this you have to devise plans(using High availability options) and test it(Mock Drills) from time to time.


Incremental backup in postgresql in windows I am developing an application with postgresql as backend. So my requirement is to take the incremental backup either hourly or daily basis.
I have gone through many related articles (like https://coderwall.com/p/jxebnw/incremental-backups-for-postgresql) but i found that postgresql database does not support incremental backup independently. It means that the postgresql needs third party tools like pg_rman in order to get incremental backup. Also the pg_rman tools only works on UNIX not windows.

Is it really so? I mean its hard to believe that the database like postgresql do not support incremental backup.

So my question is do postgresql support incremental backup independently? If yes then what is the procedure for achieving it.

Yes, it is right that the incremental backups are not possible in postgresql.

I don't know if there is an alternative for pg_rman in windows but you can achieve the incremental backup in postgres with the continuous archiving. Which can be later restored with PITR (point in time recovery)

Details on:
https://www.postgresql.org/docs/current/static/continuous-archiving.html


Is it possible to configure PostgreSQL to automatically close idle connections? Clients connect to our PostgreSQL 8.3 database but leave the connections opened. Is it possible to configure PostgreSQL to close those connections after a certain amount of inactivity?

Finally, I have a solution.
I'll use the tool PgBouncer with its parameter server_idle_timeout.

Another approach would consist in using some cron-like tool running a query periodically to find idle connections.

Once those idle connections are found, a simple call to pg_terminate_backend will close them.

See an in depth description of this approach in the anwser of the following question:

How to close idle connections in PostgreSQL automatically?


Why is my pg_restore command not working? Yesterday, I created backups of my various PostgreSQL databases using the following command:

pg_dump -Fc -d junk -h localhost -p 5434 -U postgres -W > z:\pg_dump\96_junk.dump

I created an individual backup for each database.

Today, I'm trying to restore them to an upgraded database server using the following command:

pg_restore -h localhost -p 5434 -U postgres -W -C -v z:\pg_dump\96_junk.dump

When I run pg_restore using the above it seems that the database was restored. The console screen displays a series of SQL statements and other messages I would expect to see for this database and, at the end, "PostreSQL database dump complete" is the output shown.

When I check the database, the database is not present. What am I doing wrong?

Employed versions info

Backups created with version 9.6.0
Attempting to restore using version 9.6.1

Try specifying the name of the DB to start in, using the -d or --dbname argument. This worked for me when I had the same problem. Note that it doesn't have to be the target DB:

pg_restore -h localhost -p 5434 -U postgres -d postgres -W -C -v z:\pg_dump\96_junk.dump


Can TRUNCATE TABLE be overloaded on SQL Server? I know it may look like a weird question...

I have a 3rd party db and services, and I want to monitor it by DML triggers on INSERT, UPDATE and DELETE of each table.

But alas, I have a problem when TRUNCATE TABLE is called, since it's not activating the DELETE triggers.

I'm looking for a way to "overload" the TRUNCATE TABLE to be something like:

DELETE FROM X; --remove all rows
TRUNCATE TABLE X; --truncate the empty table

Or maybe activate some DDL trigger before truncate, so I can check which rows are gonna be truncated.

I'll be happy if there are any other proposals too.

Is something like this possible?

EDIT
I don't want to disable the truncate I just want to monitor the data.

It is 3rd party DB, and it been used by services that I can't have control on, so I can't prevent the truncate.

I'm just trying to monitor changes and currently can't track the deletion of records caused by truncate command.

Thanks!

I'm looking for a way to "overload" the TRUNCATE TABLE to be something
  like:

  DELETE FROM X; --remove all rows
  TRUNCATE TABLE X; --truncate the
  empty table

When you delete from a table, you are removing rows. You can specify what rows to delete with a WHERE clause. When you TRUNCATE a table, it is not logging what rows are deleted and it does perform minimal logging. TRUNCATEis a great way to clear a table without causing the Transaction Logs to balloon.

  Or maybe activate some DDL trigger before truncate, so I can check
  which rows are gonna be truncated.

You do not need a trigger for this. A TRUNCATE will remove all rows in the table, you know all rows are going to be removed in the process. No survivors.

If you would like to capture the event to know who truncated what or when, you can do this with SQL Audit, as shown by Mohd Sufian on SQL Ship.

I don't believe you can fire trigger from a Truncate, here is the DDL List on MSDN of what DDL events can be fired from a trigger.

You can also use extended events to capture Truncate events as Jonathan Kehayias shows on MSDN.

If you want to prevent users from running Truncate, you can create a view or another object with SCHEMABINDING. This prevents DDL statements from altering the table in question, which includes Truncate.

One last note about Truncate is that if you have an identity column, it will reseed the value. I have seen people use the RESEED function and Truncate together. They will capture the last identity used, store it into a variable table, truncate the table removing all records with minimal logging and resetting the identity column, then reseed the last incremental. Usually if you truncate though, you don't care about the reseed. (Not always true, but usually.)

I truncate primarily on staging tables or when I need to reset an instance for testing. I rarely truncate tables otherwise because I care about the referential data integrity and which rows are removed from the tables. When I care about neither, I truncate.

I would highly recommend to read more on both Delete and Truncate, they will achieve similar results but are two very different things.

Atul Gaikwad on MSSQLTips wrote a nice quick article on some of those differences.

Now let's look at this question:

  Can I count the rows that will be affected by the truncate from a
  third party application?

Ideally, the truncate event is scheduled or it can be planned for. Even more ideally, you will be able to alter the code or add something in so it can log information for you. From your post, I'm going to assume you can't plan, schedule, or alter the code.

So how do you track what rows will be affected by truncate if you had to, with absolute certainty?

I personally would create two tables, a trigger, and a procedure.

The trigger would be based on Insert or delete tasks and it would insert into a table the number of rows affected, the table name, and the datetime. So if your process inserted 6 rows, the trigger would insert the value 6 indicating 6 new records flowed in. If you deleted 10 rows, it would insert a value of -10 indicating 10 records are now gone.

I would want to create an extra column for notes or events. I would want the audit or extended events to log to this table the day / time the truncate occurred. They won't be able to tell you how many rows, but it can tell you when and who. That's what's really important at this moment.

The second table would be an aggregate of the first table as I assume you may have many transactions in a day. This would tell you the net total of records at the end of the day and also tell you when truncates occurred. This means you would know how many records were in the table just prior to truncation, meeting your requirements.

The procedure would do the aggregation between tables and it would also perform cleanup work on the first table.


What is the difference between using First..Last and 1..count in Oracle? What is the difference between using First..Last and 1..count in Oracle?

FOR i IN l_collection.FIRST..l_collection.LAST LOOP

or

FOR i IN 1..l_collection.COUNT LOOP

first and last returns the first index and last index. count returns the number of elements in the collection. These do not necessarily give a correct way of referencing the elements in the collection, as you can have sparse collections, where index values are not contiguous (see below example).

declare
  type t_numbers is table of number index by pls_integer;
  l_numbers t_numbers;
begin
  l_numbers(1) := 5;
  l_numbers(4) := 6;
  for i in l_numbers.first..l_numbers.last loop
    dbms_output.put_line(i);
  end loop;
end;
/

1
2
3
4

declare
  type t_numbers is table of number index by pls_integer;
  l_numbers t_numbers;
begin
  l_numbers(1) := 5;
  l_numbers(4) := 6;
  for i in 1..l_numbers.count loop
    dbms_output.put_line(i);
  end loop;
end;
/

1
2

I hope you see why both versions fail. These work properly only as long as you have dense collections (where index values are contiguous). With sparse collections, both method fail, and you should use a WHILE loop:

declare
  type t_numbers is table of number index by pls_integer;
  l_index number;
  l_numbers t_numbers;
begin
  l_numbers(1) := 5;
  l_numbers(4) := 6;
  l_index := l_numbers.first;
  while (l_index is not null)
  loop
    dbms_output.put_line(l_index);
    l_index := l_numbers.next(l_index);
  end loop;
end;
/

1
4


Personal finance database design I'm making a personal finance management application in PHP and MySQL, for learning myself some more PHP and MySQL.

This system is currently using 4 tables:

users, this table holds all the user accounts with the following columns:

ID
First name
Middle name
Last name
email
password

users_balance, this table holds all the user's balance's. The user's balance is getting updated everytime the user gets a new income or expense, after a certain date or time ofcourse. This table has the following columns:

ID
user_id
balance

user_incomes, this table holds all the incomes for the specific user. This table has the following columns:

ID
user_id
title
description
date
time

user_expenses, this table holds all the expenses for the specific user. This table has the following columns:

ID
user_id
title
description
date
time

The user can view it's current balance, but what if the user wants to check it's balance from a specific date?

Do I need to subtract and sum up all the expenses and incomes back to that specific date or do i have to create another column which keeps track of every new user balance when a new transaction is created?

If you look at incomes and expenses tables, you see they are the same, they differ only in a sign of the change of user balance. You can easily create only one table  of "transactions" to keep both and either add a column with a type [Income, Expense] or decide it only by a sing on a value column (I suppose you want that one as you talk about summing all expenses and incomes, but it is not defined in your question).

Then you can do simple

select sum(value) as balance from transactions where user_id = X and datetime <= Y;

As you can see, I use datetime as one column - I suggest you do do that as otherwise all conditions for checking ranges get quite cumbersome:

where date < xxx or (date = xxx and time <= yyy)
-- and now add once more for the upper limit

This is good enough if you need that only sometimes and have a good index with user_id and datetime and preferably a value too. But if know that you will check that often or that there will be really lots of transactions for each user, it makes sense adding one more column where you will store the value of user balance after applying that transaction. That way getting the user balance for a given datetime is like

select balance_value
from transactions
where user_id = X and datetime <= Y
order by datetime desc
limit 1;

with again using index on (user_id, datetime) for a really fast retrieval.

This can have a secondary usage too - it lets you simply verify the users_balance records and check that there is no transaction missing. That can come heplful in a case of a bug or problem with data storage.


Any MYSQL Client with fully dark theme? I would like to find a MYSQL Client program which can be config as FULLY
DARK THEME. Many of them can not be dark theme in displaying result in grid.

DBForge Studio has exactly what you are looking for.

Once installed, go to Tools -> Skin -> Visual Studio 2013 Dark. Everything will be dark (The database tree view, the query windows, the table results, etc).


DB2: Cannot use RUNSTATS/REORG in trigger definitions? CREATE TRIGGER MY_TRIG AFTER INSERT ON MY_TBL FOR EACH ROW
    BEGIN
        IF SOMECONDITION THEN
            DELETE FROM MY_TBL WHERE PK IN (SELECT PK FROM MY_TBL WHERE SOMEOTHERCONDITION FETCH FIRST 50 ROWS ONLY);
            RUNSTATS ON TABLE MY_TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL;
            REORG TABLE MY_TBL;
            RUNSTATS ON TABLE MY_TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL;
        END IF;
    END#

Executing this through the control centre of DB2 9.4.7 (with # as terminating char) this gives me error SQL0104N:

An unexpected token "RUNSTATS" was found following "T 50 ROWS ONLY;
            ". Expected tokens may include: "COMMENT

I do not understand why. Am I not allowed to use commands such as RUNSTATS/REORG inside trigger definitions? Executing the RUNSTATS command separately works just fine.

------Edit------
A bit more about what I'm trying to achieve here:
The table in question contains BLOBs and many of them. In order to limit the disk space used by this table I'd like to set a limit on how big the table may get. So I use the information from the xxx_OBJECT_P_SIZE columns in SYSIBMADM.ADMINTABINFO to determine the current size of the table and if it's beyond some limit I simply delete the oldest 50 records of the table. The more records I delete on such an occassion, the less frequently reorg/runstats would have to be executed. So the statements are not being executed for each row inserted - only the condition SOMECONDITION would be evaluated every time.

Is there a better way of achieving this than by using a trigger?
------Edit------

You can only use SQL statements in a trigger. RUNSTATS and REORG are not SQL statements; they are DB2 CLP commands, and only the command line processor understands them. You can try using the system stored procedure ADMIN_CMD() to invoke those utilities, however, 1) it still may not work because of the nature of the utilities and 2) even it does work it would not be a good idea.

There are other problems with your trigger. You cannot use FETCH FIRST in a delete statement, for example. And what is the point of updating statistics just before a REORG?


Select @@identity in Merge Replication I'm going to implement database replication (merge publication, bi-direction) between two servers running SQL Server 2008 Standard edition. The setup is alright but it comes to a problem when synchronizing data.

The main task of my program is to let user register and after the INSERT statement is executed on my member table (with uid primary key and auto-incremented), it queries SELECT @@identity; for the max. primary key. The program then uses this key for insertion into another table.

All these work fine without replication.

But after replication, when doing SELECT @@identity; it returns back an incorrect primary key. (Suppose now member contains 100 members, after inserting a new record, it should have returned 101. But instead it returns 2 or 3 which is wrong and not what I expected.)

So my question is how to get a correct value using SELECT @@identity;.

I know the possible solution is SCOPE_IDENTITY(), but I want to know how to fix this without changing the source code.

Thanks in advance.

@@identity is not a good solution and will return incorrect results since .  SCOPE_IDENTITY() is session based and will return the last inserted record for the session.  There isn't a way to correct this without a code change.


Postgres join where foreign table has ALL records I have this people and tags table, like this,

CREATE TABLE people
AS
  SELECT *
  FROM ( VALUES
    (1,'Joe'),
    (2,'Jane')
  ) AS t(id,name);

CREATE TABLE tags
AS
  SELECT * FROM ( VALUES
    (1, 1, 'np'),
    (2, 1, 'yw'),
    (3, 2, 'np')
  ) AS t(id, people_id, tag);

If I want to find all people that contain both the np and yw tags in the tags table using a join, how would I do this efficiently in Postgres 9.6?

In this scenario, I should only get Joe's record from the people table.

Here are a couple of alternate approaches which don't involve using array_agg.

Use the INTERSECT operator against the the sets of people_id returned for each tag:

WITH both_tags AS (
    SELECT people_id FROM tags WHERE tag = 'np'
    INTERSECT
    SELECT people_id FROM tags WHERE tag = 'yw')
SELECT *
  FROM people
  WHERE id IN (SELECT people_id FROM both_tags);

Or you could use a COUNT(DISTINCT tag) = 2 to find people with both tags. (Note that the DISTINCT was added to handle the case that a person may have the same tag twice. If that is impossible, it's safe to remove.)

WITH both_tags AS (
    SELECT people_id
      FROM tags
      WHERE tag IN ('np', 'yw')
      GROUP BY people_id
      HAVING COUNT(DISTINCT tag) = 2)
SELECT *
  FROM people
  WHERE id IN (SELECT people_id FROM both_tags);

This second approach would be easier to extend to accept an arbitrary number of tags, though the first approach would not be impossible.


Optimisation Newbie: How much of a 'sin' is redundancy? I've been developing for a while, but never really had to deal with DB/scaling issues before. That's suddenly changed and I've found myself in the deep end.

I have 2 SQL tables, as such:

VOTES
vote_id (PK)
question_id (FK)
user_id (FK)
option_id (FK) <The option the user voted for>

The 'questions' table looks like this:

QUESTIONS
question_id (PK)
option_1 (FK --> Options)
option_2 (FK --> Options)

The problem I have is that I very often have to retrieve the sum of all option_1 votes (or option_2 votes) for a question. This is currently done by selecting count where question_id = [@question.id] and option_id = [@question.option_1.id]"

I'm guessing it'd be a heckuva lot quicker to just add option_1_votes and option_2_votes columns to the "questions" table and increment them each time a vote is added. But ultimately, that's redundant data.

So, as someone who's pretty clueless re: database design princpiples, what's the rule of thumb here? Would a top DBA just add in the columns, or try some other solution?

Cheers...

Generally, I don't add redundant columns unless I really need too.

Running a COUNT over a set of data is quite efficient in any RDBMS.

Consider this is a read over indexed (hopefully) cached data to get the count will beat the the 2nd write in to maintain the denormlaised column. This write requires more resources/locking/longer transaction etc which impact reads more

If performance becomes an issue over time, then you can pre-calculate the COUNT more efficiently using an indexed (aka materialised) view


Table Partitioning - database file increased after partitioning I have a table that is 100 GB in size. I forgot the exact blog which I read when partitioning tables but when I ran the partition query, my primary table file increased while being able to partition it into a new drive.
The steps I took were:

Create file group

ALTER DATABASE MY_DB
ADD FILEGROUP MY_FILEGROUP
GO

Create secondary file for the filegroup

ALTER DATABASE MY_DB
ADD FILE
(
NAME = my_partition,
FILENAME = 'D:\my_partition.ndf',
size = 3072 KB
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
TO FILEGROUP MY_FILEGROUP

Create partition function

CREATE PARTITION FUNCTION myPartitionFunc (date)
AS RANGE RIGHT FOR VALUES ('20200101')

Create partition scheme

CREATE PARTITION SCHEME myPartitionScheme
AS PARTITION myPartitionFunc
TO (PRIMARY, my_partition)

Partition the table

ALTER TABLE my_schema.my_table DROP CONSTRAINT PK_MY_TABLE
ALTER TABLE my_schema.my_table ADD CONSTRAINT PK_MY_TABLE PRIMARY KEY CLUSTERED
(
DATE ASC,
ID ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON myPartitionSceme(DATE)

After step 5, I was able to partition my database to my two separate drives. My only problem was my primary database file increased after partition.
IE
My initial file is C:\my_initial_file.mdf is 100 GB and will be partitioned to D:\my_partition.ndf
Once partitioned, my D:\my_partition.ndf is 10 GB and my C:\my_initial_file.mdf is 94 GB which still consumed an excess of 4 GB.
My reason for partitioning is because my drive C is getting full and would like to keep the server running until we migrate to elsewhere.

In this instance rather than messing with the complexity of partitioning I'd either:

Just move the entire DB over to the drive with more space.
Detach the DB, move the files over and reattach.
See https://docs.microsoft.com/en-us/sql/relational-databases/databases/detach-a-database and the other related sections.
Or backup, drop original, and resore with the files on the other drive.
Create a new datafile as you have done, but instead of partitioning tables, just move whole tables and indexes over to the new file group like so:

    CREATE CLUSTERED INDEX PK_MY_TABLE
    ON my_schema.my_table(DATE ASC, ID ASC)
    WITH (DROP_EXISTING = ON, ONLINE = ON)
    ON MY_FILEGROUP

In either case I would undo the partitioning first as it would not be needed, and for paranoia's sake take regular manual backups during the process.
With regard to partitioning, note that all your examples above have done is move the data (the clustered index) for rows one side of your partitioning function. The data for non-clustered indexes will still all be in the original filegroup unless you partition those also. This may explain not as much data moving over as you expected.
Note that is also applies to moving tables between filegroups without partitioning: the non-clustered indexes need to be moved explicitly also, or they will stay where they are. In fact having the indexes in another filegroup to the table will work just fine so you don't have to move them, or you could move then instead of the main table data (the clustered index or heap) and leave that where it is.
Freeing Space
Once all this is done, which ever way you do it (partitioning or moving while tables/indexes to a new filegroup), the file(s) making up your primary filegroup will not automatically shrink to release space to the OS, you will need to do this as an extra step afterwards.


ORA-01031: insufficient privileges why do i get  ORA-01031: insufficient privileges ?

SQL> grant create view to rojib;

Error :

grant create view to rojib
*
ERROR at line 1:
ORA-01031: insufficient privileges

In order to grant the CREATE VIEW privilege, your user needs the CREATE VIEW privilege WITH ADMIN OPTION, or the GRANT ANY PRIVILEGE system privilege. Otherwise you have insufficient privileges.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#i2094944

  To grant a system privilege, one of the following conditions must be
  met:

  You must have been granted the GRANT ANY PRIVILEGE system privilege.
  In this case, if you grant the system privilege to a role, then a user
  to whom the role has been granted does not have the privilege unless
  the role is enabled in user's session.
  You must have been granted the system privilege with the ADMIN OPTION.
  In this case, if you grant the system privilege to a role, then a user
  to whom the role has been granted has the privilege regardless whether
  the role is enabled in the user's session.


How exactly does sp_msforeachdb work behind the scenes? I need to troubleshoot an issue I am having, and I need some help understanding how sp_msforeachdb works in order to overcome my issue.

What happens is every time I run sp_msforeachdb, I get an error Msg 102, Level 15, State 1, Incorrect syntax near '61'

An example of my code is as follows:

EXEC SP_msforeachdb 'SELECT ''?'' AS Database
                     FROM ?.sys.objects
                     WHERE name like ''%aetna%''

However, it does not matter what query I have as a parameter to sp_msforeachdb. Every time I get the same error. I do have a database that starts with '61s1d', so that makes me think it has an issue with the DB name, but I honestly do not know what is going behind the scenes on sp_msforeachdb.

Things to note.

It is the only database that starts with a number
I can try to use code like "If database is like '%61%' don't do ......", but still the same error.
I cannot test changing the database name--too many things connected to it.
If I create a test db that starts with '51', then I also get the error for that database

How can I overcome this?

First of all don't use sp_msforeachdb it has several known issues.  You are better off using Aaron Bertrand's version here and here.

However it uses a cursor, dynamic SQL and a replace.  You can actually look at the code by using sp_helptext.

EXEC sp_helptext sp_msforeachdb

If you use this code it will fix some of your issues.

EXEC SP_msforeachdb 'SELECT ''?'' AS Database
                 FROM [?].sys.objects
                 WHERE name like ''%aetna%''

The brackets will the specific problem you mentioned.  You will however run into issues if you have a database with [ or ] in it.


AWE using 24GB of RAM SQL Server 2012 Because my memory seemed to be rapidly disappearing (according to the Task Manager graph), but I couldn't see any programs using it, I downloaded RAMMap to get a better picture of what's going on. Turns out all of my memory was being chewed up by AWE.

When I try to research this, however, everyone seems to say that AWE isn't enabled for SQL Server 2012 64 bit systems, since it isn't needed; the expanded memory that AWE would enable is directly accessible. But the system still will happily chew up memory in AWE until it reaches its limit.

How can I disable this and free the 24GB ram?

I would recommend you to read this article:
https://blogs.msdn.microsoft.com/psssql/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set/

It covers all possible questions about AWE and answers to them
Also you might find the post from Paul Randal helpful:
A SQL Server DBA myth a day: (5/30) AWE must be enabled on 64-bit servers


Oracle 10g slow performance on first query We have an application which we recently upgraded and is having bad performance issues. Any ideas would be much appreciated.

The main issue is windows taking ages (minutes) to open because the initial query to fill their list boxes is taking so long. The second time a user opens the window its fine.

We would expect this to be application cacheing but we don't think so. We see similar problems using Crystal Reports and using command line SQL queries.

We have increased the RAM available to the database.

Another part of our business has a slightly different version of the same application and database and doesn't get this problem.

The time to complete a query can be 30 minutes for the first time in a session and 30 seconds for subsequent executions.

The usual reason for such issues is that the first execution of the query requires physical reads to fetch the required blocks from disk, and the second and subsequent executions find that they are already cached.

You can test this by running the queries from SQL*Plus (and timing them) prior to first opening these windows of which you speak, to see if that affects performance.

If you can come back with comparison of the timings when running the queries through SQL*Plus and then through the application it would help further analysis.