SQL Server 2008 R2 error: 15023, user, group, or role already exists I have a test database that I'm having permissions issues with.
I can't access the reporting database, and the application's help documentation says to do the following:

Resolution:

1. Launch the SQL Server Management Studio and connect to the database server(s) hosting   the Vision and Reporting Server databases.
2. Expand the security folder.
3. Select logins and right click on the <username> user and choose properties.
4. Click the User Mapping tab
5.Make sure the following databases are selected in the Users mapped to this Login:

    ReportServer
    ReportServerTempDB
    Your Vision databases
    This maps the login/user to the respective databases.
6. As you select each database (including your Vision database), select the db_owner role in the Database role membership for: section. You must select this option for each database.

When I do this, I get the following error:

"Create failed for user '<servername>\<username>'.  User, group, or role '<servername>\<username>' already exists in the current database. (Microsoft SQL Server, Error: 15023)"

I have googled this error and tried the following command on each database:

 ALTER USER [<username>] WITH LOGIN = [<username>]

The message indicated that the commands completed successfully but I am still getting the above error when I try to map each database as instructed above.

What am I missing?

Per Kin's comment (thanks) I tried this:
- I right clicked the user and selected: Script Login As > Drop and Create To > New query window.
- I ran the resulting query and tried to map the user roles by selecting the other two databases and db_owner again but I am still getting the same error message as above.

Thoughts??

There are a number of things you can try, and the success of each will probably depend on what server role you have.

For starters, if it is just one or two users then honestly, the easiest way is to drop the database user from the restored database, and providing there is an existing server login already, remap the database user to the server login using SSMS. If the server login does not exist then just create it, map the user, and presto! Away we go.

Next option: If you are migrating a large number of users, use sp_help_revlogin. sp_help_revlogin is a Microsoft supplied stored procedure that will help migrate logins from one server to another, including passwords and SIDs. Here is a good article about it SP_HELP_REVLOGIN

go to the master database and delete the user


Group By hour over large dataset Using MS SQL 2008 I am selecting an averaged field from 2.5 million records. Each record represents one second. MyField is an hourly average of those 1 second records. Of course the server CPU hits 100% and the selection takes too long. I need to possibly save those averaged values so that SQL does not have to select all those records on each request. What can be done?

  SELECT DISTINCT
         CONVERT(VARCHAR, [timestamp], 1)+' '+ CAST(DATEPART(Hh,[timestamp]) as VARCHAR) AS TimeStampHour,
         MIN([timestamp]) as TimeStamp,
         AVG(MyField) As AvgField
    FROM MyData
   WHERE TimeStamp > '4/10/2011'
GROUP BY CONVERT(VARCHAR, [timestamp], 1)+' '+ CAST(DATEPART(Hh,[timestamp]) as VARCHAR)
ORDER BY TimeStamp

The part of the query is maxing out the CPU for long periods is the the functions in the GROUP BY clause and the fact that the grouping is always going to require an unindexed sort in this instance. While an index on the timestamp field will help the initial filter this operation has to be performed on every row that the filter matches. Speeding up this are using a more efficient route to do the same job as suggested by Alex will help, but you still have a huge inefficiency there because what-ever function combination you use the query planner is not going to be able to come up with something that will be helped by any index so it will have to run through every row first running the functions to compute the grouping values, only then can it order the data and compute the aggregates over the resulting groupings.

So the solution is to somehow make the process group by something it can use an index for, or otherwise remove the need to consider all the matching rows at once.

You could maintain an extra column for each row containing the time rounded to the hour, and index this column for use in such queries. This is denormalising your data so might feel "dirty" but it would work and would be cleaner than caching all the aggregates for future use (and updating that cache as the base data is altered). The extra column should be maintained by trigger or be a persisted computed column, rather than maintained by logic elsewhere, as this will guarantee all current and future places that might insert data or update the timestamp columns or existing rows result in consistent data in the new column. You can still get the MIN(timestamp) out. What the query will result in this way is still a walk down all the rows (this can not be avoided, obviously) but it can do it index order, outputting a row for each grouping as it gets to the next value in the index rather than having to remember the whole set of rows for an unindexed sort operation before the grouping/aggregation can be performed. It will use a lot less memory too, as it won't need to remember any rows from prior grouping values in order to process the one it is looking at now or the rest of them.

That method removes the need find somewhere in memory for the whole result set and do the unindexed sort for the group operation and removes the computation of the group values out of the big query (moving that job out to the individual INSERTs/UPDATEs that produce the data) and should allow such queries to run acceptably without needing to maintain a separate store of the aggregated results.

A method that doesn't denormalise your data, but does still require extra structure, is to use a "time table", in this case one containing one row per hour for all the time you are likely to consider. This table would not consume a significant amount of space in a DB or appreciable size - to cover a timespan of 100 years a table containing one row of two dates (the start and end of the hour, such as '2011-01-01@00:00:00.0000','2011-01-01@00:00:59.9997', the "9997" being the smallest number of milliseconds a DATETIME field won't round up to the next second) which are both part of the clustered primary key will take ~14Mbyte of space (8+8 bytes per row * 24 hours/day * 365.25 days/year * 100, plus a bit for the overhead of the clustered index's tree structure but that overhead won't be significant). With such a table you could do something like the following:

SELECT CONVERT(VARCHAR, [timestamp], 1)+' '+ CAST(DATEPART(Hh,[timestamp]) as VARCHAR) AS TimeStampHour
     , MIN([timestamp]) as TimeStamp
     , AVG(MyField) As AvgField
FROM TimeRangeByHours tt
INNER JOIN MyData md ON md.TimeStamp BETWEEN tt.StartTime AND tt.EndTime
WHERE tt.StartTime > '4/10/2011'
GROUP BY tt.StartTime
ORDER BY tt.StartTime

This means that the query planner can arrange for the index on MyData.TimeStamp to be used. The query planner should be bright enough to work out that it can walk down the tame table in step with the index on MyData.TimeStamp, again outputting one row per grouping and discarding each set or rows as it hits the next grouping value. No storing all the intermediate rows somewhere in RAM then performing an unindexed sort on them. Of course this method requires that you create the time table and make sure it spans far enough both backwards and forwards, but you can use the time table for queries against many date fields in different queries, where as the "extra column" option would require an extra computed column for each date field you needed to filter/group by this way, and the small size of the table (unless you need it to span 10,000 years of course, in which case it'll be around 1.4Gb) will mean it it likely to always be in RAM.

The time table method has an extra difference (that could be quite advantageous) compared to you current situation and the computed column solution: it can return rows for periods for which there is no data, simply by changing the INNER JOIN in the example query above to be a LEFT OUTER one.

Some people suggest not having a physical time table, but instead always returning it from a table returning function. This means the content of the time table is never stored on (or needs to be read from) disk and if the function is well written you never have to worry about how long the time table needs to span back and forth in time, but I doubt the CPU cost of producing an in-memory table for some rows every query is worth the small saving of hassle of creating (and maintaining, should its timespan need to extend beyond your initial version's limit) the physical time table.

A side note: you don't need that DISTINCT clause on your original query either. The grouping will ensure that these queries only return one row per period under consideration so the DISTINCT will do nothing other than spin the CPU a little more (unless the query planner notices that the distinct would be a no-op in which case it'll ignore it and use no extra CPU time).

I would consider abandoning the idea of implementing this sort of calculation using a relational database model. Especially if you have many data points for which you collect values every second.

If you have the money you could consider purchasing a dedicated process data historian like:

Honeywell Uniformance PHD
Osisoft PI
Aspentech IP21
etc.

These products can store huge amounts of insanely dense time series data (in proprietary formats) while simultaneously permitting swift processing of data extraction queries. The queries may specify many data points (also called tags), long time intervals (months/years), and may additionally do a wide variety of summary data calculation (including averages).

.. and on a general note: I always try to avoid using the DISTINCT keyword when writing SQL. It is hardly ever a good idea. In your case you should be able to drop DISTINCT and get the same results by adding MIN([timestamp]) to your GROUP BY clause.


How to Pivot in PostgreSQL Having the following data in a table:

ID     Category   Value
1234   Cat01      V001
1234   Cat02      V002
1234   Cat03      V003
1234   Cat03      V004
1234   Cat03      V005

I want to have the following output:

ID     Cat01  Cat02   Cat03
1234   V001   V002    V003
1234   V001   V002    V004
1234   V001   V002    V005

The output I want to achieve is a kind of pivot table where I have all the values vertically in a table and I want to have those values, horizontally, having the category as a column. But there are some categories that have multiples values, in that case, I need to repeat the values of all other categories and create a row per each repeated value

How can it be done in PostgreSQL?

This is a tricky one. crosstab() expects one (or no) value per category for each row_name.

We can work around this restriction like this:

SELECT id
     , COALESCE(cat01, max(cat01) OVER w)
     , COALESCE(cat02, max(cat02) OVER w)
     , COALESCE(cat03, max(cat03) OVER w)
FROM   crosstab(
   'SELECT id::text || row_number() OVER (PARTITION BY id, category ORDER BY value) * -1 AS ext_id
         , id, category, value
    FROM   tbl
    ORDER  BY ext_id, category, value'

   ,$$VALUES ('Cat01'::text), ('Cat02'), ('Cat03')$$
   ) AS ct (xid text, id int, cat01 text, cat02 text, cat03 text)
WINDOW w AS (PARTITION BY id);

Returns your desired result.

How?

Add an extended id: ext_id from the existing id and a row number for each value of the category for the same id. This way we ensure as many rows per id in as there are values for the most common category. We get a derived table like this to build our crosstab() on:

ext_id   | id   | category | value
---------+------+----------+-------
'1234-1' | 1234 | 'Cat01'  | 'V001'
'1234-1' | 1234 | 'Cat02'  | 'V002'
'1234-1' | 1234 | 'Cat03'  | 'V003'
'1234-2' | 1234 | 'Cat03'  | 'V004'
'1234-3' | 1234 | 'Cat03'  | 'V005'

Now we can feed it to crosstab() using the safe 2-parameter form for missing attributes. Read the basics first if you are not familiar with this:

PostgreSQL Crosstab Query

The original id is carried over as "extra column". See:

Pivot on Multiple Columns using Tablefunc

Your question leaves room for interpretation. My solution pairs the lowest values per category first and keeps filling the following rows until there are no values left. (We could combine multiple values per category any other way, it has not been defined.) If a category is short of values for a given id, the rest is filled in with NULL values.
In the final step I replace those NULL values with the maximum value of each category per id:

COALESCE(cat01, max(cat01) OVER (PARTITION BY id, category))

which is effectively the same as:

max(cat01) OVER (PARTITION BY id, category)

I am hoping to make it slightly faster if we only default to the window function if the value is NULL.

What you look at is a crosstab. Assuming your table is called "Fact_Table", write:

select * from crosstab('select id, category, value from Fact_Table')

Also see http://www.postgresql.org/docs/9.5/static/tablefunc.html if you look for other variants.


How to run specific version (8.4, 9.1) of postgresql pg_* command (e.g., pg_dump) I have Postgresql versions 8.4 and 9.1 installed.  For any given Postgresql command, how do I specify a specific version of the command to run?  (e.g., psql, pg_dump, pg_ctlcluster, pg_restore, ...)

My question is motivated by wanting to do a pg_dump in preparation for an upgrade from 8.4 to 9.1, and I want to know which pg_dump version I am running.

I'm running on Ubuntu 10.04 Natty.

You are on Ubuntu and obviously have Martin Pitt's pg_wrapper installed (judging from pg_ctlcluster) that is provided by the package postgresql-common and comes with the standard Debian packages. I use the same on Debian.

On a Linux system, run which in the shell to see which executable is actually picked:

postgres@db:~$ which pg_dump
/usr/bin/pg_dump
postgres@db:~$ ls -l /usr/bin/pg_dump
lrwxrwxrwx 1 root root 37  4. Jun 18:57 /usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper

pg_dump is actually a symlink to pg_wrapper, which dynamically picks the appropriate version of the client program for the db cluster you run pg_dump with. I quote the man page of pg_wrapper:

  This program is run only as a link to names which correspond to PostgreSQL programs in /usr/lib/postgresql/version/bin. It determines
  the configured cluster and
         database for the user and calls the appropriate version of the desired program to connect to that cluster and database, supplying any
  specified options to that command.

   The target cluster is selected by the following means, in descending order of precedence:
   1.  explicit specification with the --cluster option
   2.  explicit specification with the PGCLUSTER environment variable
   3.  matching entry in ~/.postgresqlrc (see postgresqlrc(5)), if that file exists
   4.  matching entry in /etc/postgresql-common/user_clusters (see user_clusters(5)), if that file exists
   5.  If only one local cluster exists, that one will be selected.
   6.  If several local clusters exist, the one listening on the default port 5432 will be selected.

   If none of these rules match, pg_wrapper aborts with an error.

IOW, the right version should be picked automagically - unless you screwed up your installation somehow. You can always add the option --cluster to be specific.

In Ubuntu 20.04:
export PGCLUSTER=9.6/main

pg_dump -V

pg_dump (PostgreSQL) 9.6.21


SQL Server dates - can I get date and hour? In SQL Server, can I do the equivalent of to_char(date_field, 'YYYY-MM-DD:HH24') in Oracle to get 2012-01-30:23?

In SQL Server <= 2012 you can say:

SELECT CONVERT(CHAR(10), date_field, 120)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(HOUR, date_field)), 2)
  FROM dbo.table ...;

Or

SELECT STUFF(CONVERT(CHAR(13), date_field, 120), 11, 1, ':')
  FROM dbo.table ...;

In SQL Server 2012 this is much easier; you can say:

SELECT FORMAT(date_field, 'yyyy-MM-dd:HH')
  FROM dbo.table ...;

Though FORMAT is considerably slower, so if you are doing this at scale, it isn't the one I would use (and in fact I would highly recommend you consider formatting your date output at the presentation layer, not in SQL Server).

SQL fiddle: http://sqlfiddle.com/#!6/72276/5

something like

DECLARE @D DATETIME
SET @D = GETDATE()
SELECT CONVERT(varchar(20), @D , 120) as date


Oracle returns an error ORA-01031: insufficient privileges Oracle returns an error "ORA-01031: insufficient privileges"
When I run sqlplus / as sysdba but sqlplus sys/oracle123 as sysdba doesn't, why?

Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Linux version: SUSE Linux Enterprise Server 11 (x86_64)

I know that Oracle uses OS authentication when running sqlplus / as sysdba, I also checked sqlnet.ora carefully and it contains SQLNET.AUTHENTICATION_SERVICES = (NTS).

I also changed the oracle user's group, oracle user absolutely belongs to dba group because when I run command id the following information returns:

oracle@linux-mage:~> id
uid=103(oracle) gid=106(dba) groups=105(oinstall),106(dba)

Another information is I can logged on through sqlplus sys/oracle123 as sysdba, but I can not shutdown the instance:

oracle@linux-mage:~> sqlplus sys/oracle123 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 5 16:29:57 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected.
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-27140: attach to post/wait facility failed
SQL> shutdown abort
ORA-01031: insufficient privileges
SQL>

i ran strace -o trace.out sqlplus "/ as sysdba" and get the following output message.
the strace message is too big. so i paste it into pastebin. please visit this link

http://pastebin.com/YpjVeG8u

ipcs -s and ipcs -ls information:
    oracle@linux-mage:~> ipcs -s

------ Semaphore Arrays --------
key        semid      owner      perms      nsems

oracle@linux-mage:~> ipcs -ls

------ Semaphore Limits --------
max number of arrays = 200
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767

Something must have gone wrong at the O/S level - check this page for some steps to track down the problem

It seems that once the shutdown immediate fails, the connection is dropped, although it doesn't show it. Exiting sqlplus, reconnecting and immediately issuing shutdown abort got things unstuck for me.

From https://community.oracle.com/thread/459659


Is there any difference between Rem and -- comments for SQL*Plus? On SQL*Plus prompt, both Rem and -- qualify as comment indicators:

Rem this is a comment
-- this is also a comment
create table emp (
id number primary key,
name cvarchar2(40));

Is there any difference at all between the two commenting techniques?

The difference is that -- and /* */ can be used in a PL/SQL block, while REM[ARK] cannot.  The following will work in SQL*Plus:

REM comment
-- comment
/* comment */
begin
   DBMS_OUTPUT.PUT_LINE('Test'); --comment
   DBMS_OUTPUT.PUT_LINE('Test'); /* comment */
end;
/

These will not:

begin
   DBMS_OUTPUT.PUT_LINE('Test'); REM comment
end;
/

begin
   REM comment
   DBMS_OUTPUT.PUT_LINE('Test');
end;
/

The 11.2 documentation on all comment types has more comment information.  The basics are...

  You can enter comments in a script in
  three ways:

  using the SQL*Plus REMARK command for single line comments.
  using the SQL comment delimiters /*... */ for single or multi-line
  comments.
  using ANSI/ISO (American National Standards Institute/International
  Standards Organization) comments - -
  for single line comments.

The documentation also includes notes on four places that comments should not be used, but these do not include any further differences.

No there should be no difference at all.


Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL? If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example:

$ mysql -u root -p --column-type-info
Enter password:

--- Copyright and help message snipped for brevity ---

mysql> select cast(1 as signed integer);
Field   1:  `cast(1 as signed integer)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG            <== LONGLONG i.e. 64 bit integer
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+---------------------------+
| cast(1 as signed integer) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

I would have expected the return type from that cast to be a LONG (32 bit integer).

If I select a column from a table that has an INT I see that it is indeed just a LONG:

mysql> describe contact;

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| contact_id | int(11) | NO   | PRI | NULL    | auto_increment |

      == remainder of table snipped ==

mysql> select contact_id from contact where contact_id = 20;
Field   1:  `contact_id`
Catalog:    `def`
Database:   `centreon`
Table:      `contact`
Org_table:  `contact`
Type:       LONG                     <== LONG i.e. 32 bit integer
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

+------------+
| contact_id |
+------------+
|         20 |
+------------+
1 row in set (0.00 sec)

mysql>

If I cast the same column to a signed integer, I again get a 64 bit integer returned:

mysql> select CAST(contact_id as signed integer) from contact where contact_id = 20;
Field   1:  `CAST(contact_id as signed integer)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+------------------------------------+
| CAST(contact_id as signed integer) |
+------------------------------------+
|                                 20 |
+------------------------------------+
1 row in set (0.00 sec)

There is a similarly reported issue here:

  http://bugs.mysql.com/bug.php?id=64084

But sadly the OP doesn't get a straight answer.

Is this a bug in the CAST() function or is this by design?

As you can see on https://www.w3schools.com/sql/func_mysql_cast.asp

SIGNED    Converts value to SIGNED (a signed 64-bit integer)
UNSIGNED  Converts value to UNSIGNED (an unsigned 64-bit integer)

And from the official MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html

MySQL supports arithmetic with both signed and unsigned 64-bit values. For numeric operators (such as + or -) where one of the operands is an unsigned integer, the result is unsigned by default (see Section 12.6.1, “Arithmetic Operators”). To override this, use the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

So looks like the CAST output will actually be a 64 bits integer when you use SIGNED or UNSIGNED datatypes.

Int store as 32 bit in MySQL.

MySQL supports arithmetic with both signed and unsigned 64-bit values. For numeric operators (such as + or -) where one of the operands is an unsigned integer, the result is unsigned by default. To override this, use the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

That's why is for int it is showing long int : 32 bit

and for signed int it is showing long long int OR BIG int : 64 int


Single sql query to get the transpose of a matrix I am facing a problem in writing a query for this question.Consider two random 3x3 (n=3) matrices A and B,having the following schema:

A(i,j,val) and B(i,j,val) where

i int:Row Index

j int:Coloumn Index

val int:Cell Value

Write a single query to get the matrix transpose(A) in the same format as A ie.,output tuples should be of format (i,j,val) where i is row, j is coloumn and val is cell value.

All you need to do is swap the indexes. Original:

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
)
select
  *
from
  A
order by
  1, 2
;

  I   J VAL
--- --- ---
  1   1   1
  1   2   2
  1   3   3
  2   1   4
  2   2   5
  2   3   6
  3   1   7
  3   2   8
  3   3   9

Transposed:

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
)
select
  j as i,
  i as j,
  val
from
  A
order by
  1, 2
;

  I   J VAL
--- --- ---
  1   1   1
  1   2   4
  1   3   7
  2   1   2
  2   2   5
  2   3   8
  3   1   3
  3   2   6
  3   3   9

transpose:

create view transpose as select j,i,val from a;

multiplication:

select a.i,b.j,sum(a.val*b.val)
from a,b
where a.j=b.i
group by a.i,b.j;


Query to compare the structure of two tables in MySQL To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version).

Can you think of a query that can compare two tables?

Here are some example tables that you can compare.

CREATE TABLE product_today
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_yesterday
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_2days_back
(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

The first two tables have identical structures. The last one is different. I just need to know whether two tables have different structures or not. I'm not interested in the how they differ.

TWO TABLES IN THE CURRENT DATABASE

If you want to know if two tables are different, run this

SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

TWO TABLES IN A SPECIFIC DATABASE

If you want to know if two tables are different in database mydb, run this

SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema='mydb'
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema='mydb'
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

TWO TABLES IN TWO DIFFERENT DATABASES

If you want to know if db1.tb1 and db2.tb2 are different, run this

SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

GIVE IT A TRY !!!

for all changes in table structure of two databases :

SELECT table_schema, table_name, column_name,ordinal_position,data_type,column_type FROM (
    SELECT
        table_schema, table_name, column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema IN ('database1', 'database2')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1 ) A;

Ref.: from RolandoMySQLDBA ans


Select multiple rows based on date interval from two fields I am still learning SQL and I would like to do something that I dont know if it is possible. I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date.
It would be something like this:

Original:

    Product|price|initial_date|final_date
    A|20.50|2014-08-10|2014-10-01
    B|50|2015-01-15|2015-02-20

Resulting view:

 Product|price|Date
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01

I would like to do it, because this dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.

Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.

By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.

Do you have a calendar table ?
I am building a kind of calendar table with object cteSeries.
Then adjust the Initial_Date and Final_Date to hold BOM (beginning of Month YYYY-MM-01) in cteSample , fields: Initial_Date_M,Final_Date_M

DECLARE @tSample TABLE
(
    Product VARCHAR(10)
    ,Price  DECIMAL(18,2)
    ,Initial_Date DATE
    ,Final_Date DATE)
INSERT INTO  @tSample (Product,price,initial_date,final_date)
VALUES  ('A',20.50,'2014-08-10','2014-10-01')
    ,('B',50,'2015-01-15','2015-02-20');

DECLARE
@dStart AS DATE  ='20140101'
,@dEnd AS DATE ='20160101'

-- building a calendar table  -
;WITH cteSeries
AS
(SELECT @dStart AS cteDate
    UNION ALL
 SELECT DATEADD(MONTH,1,cteDate)
 FROM cteSeries
 WHERE
    cteDate < @dEnd
 )

--SELECT    * FROM  cteSeries OPTION (MAXRECURSION 0)

, cteSample
AS
(SELECT Product ,Price ,Initial_Date ,Final_Date
        , DATEADD(MONTH , DATEDIFF(MONTH , 0,Initial_Date), 0) AS  Initial_Date_M
        , DATEADD(MONTH , DATEDIFF(MONTH , 0,Final_Date), 0) Final_Date_M
    FROM @tSample
)

SELECT
  S.Product
  ,S.Price
  ,cteDate AS Date
  --,*
FROM
   cteSample AS S
   INNER JOIN cteSeries AS C
   ON S.Initial_Date_M<=C.cteDate
   AND S.Final_Date_M >= C.cteDate

and the output :

 Product    Price   Date
 A  20.50   2014-08-01
 A  20.50   2014-09-01
 A  20.50   2014-10-01
 B  50.00   2015-01-01
 B  50.00   2015-02-01

Declare @i INT;
SELECT @i = 0;
CREATE table #temp(
numbers int not null);
while @i<10
BEGIN
insert into #temp values(@i)
SELECT @i =  @i+1;
END
SELECT Product, Price, DATEADD(MM,a.numbers,DATEADD(m, DATEDIFF(m, 0,initialDate), 0))
FROM #ProdDate, #temp a
where a.numbers <= DATEDIFF(mm,initialDate,finalDate)
drop table #temp;


What does MySQL mean by "Major Version", and is MySQL replication supported between 5.1 and 5.6? I am migrating from some MySQL 5.1 servers to MySQL 5.6 servers. The MySQL docs are a bit unclear if replication is supported between a 5.1 master and a 5.6 slave, and MySQL's documentation is unclear if this is supported.

In terms of version numbers, MySQL seems to follow the norms for semantic version numbers

5.1 has a "Major version" of 5, and a "release level" of 1.
5.6 has a "Major version" of 5, and a "release level" of 6.
MySQL 4, MySQL 5 and MySQL 6 would all be considered "Major versions".

This is documented at https://dev.mysql.com/doc/refman/5.6/en/which-version.html :

  The naming scheme in MySQL 5.6 uses release names that consist of
  three numbers and a suffix; for example, mysql-5.6.1-m1. The numbers
  within the release name are interpreted as follows:

  The first number (5) is the major version and describes the file format. All MySQL 5 releases have the same file format.
  The second number (6) is the release level. Taken together, the major version and release level constitute the release series number.

https://dev.mysql.com/doc/refman/5.6/en/replication-compatibility.html says replication is supported between one major version and the next highest major version. This wording is pretty ambiguous if replication is supported between 5.1 and 5.6.

  MySQL supports replication from one major version to the next higher
  major version. For example, you can replicate from a master running
  MySQL 4.1 to a slave running MySQL 5.0, from a master running MySQL
  5.0 to a slave running MySQL 5.1, and so on.

The updated 5.7 beta docs sort of simply that replication is only supported between release levels, although the manual uses the term "major version". What do they really mean here?

  MySQL supports replication from one major version to the next higher
  major version. For example, you can replicate from a master running
  MySQL 5.0 to a slave running MySQL 5.1, from a master running MySQL
  5.1 to a slave running MySQL 5.5, and so on.

Is MySQL replication supported between MySQL 5.1 and MySQL 5.6? I understand that MySQL 5.1 is EOL on most platforms, but it's still shipped with RHEL6.

I've confirmed this is a bug with MySQL/Oracle Support. They will update the public tickets when the internal fixes are ready.

To summarize: MySQL replication is only supported between one release level (the second digit) and the next; e.g. between MySQL 5.1 and 5.5; 5.5 and 5.6 but not between MySQL 5.1 and 5.6. The documentation is mistaken when it says "MySQL supports replication from one major version to the next higher major version."* They mean release level or release series number, not major version. That is, unless they change the terminology in the future.

Replication between 5.1 and 5.6 might work, but it's not supported and not well tested. It's best to for replication servers to stay within one release level of each other, and to use the latest version of your 5.x release.

The bug reports are:

"Section should be more clear about 5.5 and 5.6 versions". Now, the documentation is more clear:

  MySQL supports replication from one release series to the next higher
  release series. For example, you can replicate from a master running
  MySQL 5.5 to a slave running MySQL 5.6, from a master running MySQL
  5.6 to a slave running MySQL 5.7, and so on.

A second private bug which I'll add once the bug is made public.

Not strictly an answer, but perhaps a very long comment:

MySQL is very similar to Mac OS X versioning.  Mac OS X was first released in 2001.  MySQL 5.0 in 2005.  It would be just as unfair to compare OS X El Capitan to 10.0 as it would MySQL 5.7 to 5.0.

The History:

Version 6.0 of MySQL was canceled, and 7.x is the versioning scheme of MySQL Cluster, which makes it more confusing to pick a next number to use.

MySQL 5.6 contains more new lines of code than any release prior.  Making the de facto major version number the first and second digit combined.


How to perform a left join of list of dates with an existing table of records smartly I am using the following query to get a list of dates between two given dates:

SELECT selected_date FROM
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
WHERE selected_date BETWEEN '2016-03-08' AND '2016-03-11';

This gives me the following output:

+---------------+
| selected_date |
+---------------+
| 2016-03-08    |
| 2016-03-09    |
| 2016-03-10    |
| 2016-03-11    |
+---------------+

The following query gives me a list of all employees required to work for a particular date:

SELECT employee.id, employee.firstname
FROM Users employee
LEFT JOIN Table1 table1 ON table1.fId = employee.tId
WHERE table1.day = dayofweek('2016-03-10')

This yields:

+----+-----------+
| id | firstname |
+----+-----------+
|  1 | Mat       |
|  2 | Tom       |
+----+-----------+

The results are empty if no employee is required to work on the given date.

I have a thrid table which lists the performance of each employee for a particular day:

SELECT * FROM performance;

+----+------------+------------+------+
| id | employeeId |    date    | val  |
+----+------------+------------+------+
|  1 |          1 | 2016-03-08 | 100  |
|  2 |          2 | 2016-03-08 | 90   |
|  3 |          1 | 2016-03-09 | 80   |
|  4 |          2 | 2016-03-09 | 100  |
+----+------------+------------+------+

I want to join the above three results to yield:

+----+------------+-----------+------------+------+
| id | employeeId | firstname |    date    | val  |
+----+------------+-----------+------------+------+
|  1 |          1 | Mat       | 2016-03-08 | 100  |
|  2 |          2 | Tom       | 2016-03-08 | 90   |
|  3 |          1 | Mat       | 2016-03-09 | 80   |
|  4 |          2 | Tom       | 2016-03-09 | 100  |
|  5 |          1 | Mat       | 2016-03-10 | null |
|  6 |          2 | Tom       | 2016-03-10 | null |
+----+------------+-----------+------------+------+

Note : On 2016-03-10 Mat and Tom aren't required to work. Hence, their values should be null in the final result. The performance table will not have entries for Mat and Tom for 2016-03-10 in this case.

I am kind of lost on how to achieve this.

You must CROSS JOIN the Users table with your Calendar query.

It will give you this result with one of each date for each user:

selected_date | firstname
2016-03-08    | Mat
2016-03-09    | Mat
2016-03-10    | Mat
2016-03-11    | Mat
2016-03-08    | Tom
2016-03-09    | Tom
2016-03-10    | Tom
2016-03-11    | Tom

This can then be LEFT JOIN with the Performance table. (SQL Fiddle)

Query:

SELECT u.id, u.firstname, cal.selected_date, p.val
FROM Users u
CROSS JOIN (
    SELECT selected_date FROM (
        SELECT adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date
        from
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
    ) v
    WHERE selected_date BETWEEN '2016-03-08' AND '2016-03-11'
) cal
LEFT JOIN performance p
    ON u.id = p.employeeId AND cal.selected_date = p.date;

Output:

 id | firstname | selected_date |    val
  1 |       Mat |    2016-03-08 |    100
  2 |       Tom |    2016-03-08 |     90
  1 |       Mat |    2016-03-09 |     80
  2 |       Tom |    2016-03-09 |    100
  1 |       Mat |    2016-03-10 | (null)
  2 |       Tom |    2016-03-10 | (null)
  1 |       Mat |    2016-03-11 | (null)
  2 |       Tom |    2016-03-11 | (null)

Build a table with a million integers.  Then JOIN to that table for things like this.

In MariaDB (10.0.3 or later), it is much simpler:  JOIN seq_1_to_1234 -- a magical table that has all the ints from 1 to 1234, or whatever range you like.  Ref page and Ref.


Specific changes to glibc 2.28 that affect PostgreSQL I am preparing for upgrading a PostgreSQL server to Debian 10 (Buster) with the warning from Debian release notes about changes to gclibc affecting PostgreSQL in mind. The PostgreSQL wiki provides two specific strings that will be sorted differently under LC_COLLATE=en_US.UTF-8 with glibc versions prior to 2.28 (i.e. up to Debian 9) or from 2.28 (i.e. from Debian 10) respectively.

( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort # 11 1-1 under Debian 9
( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort # 1-1 11 under Debian 10

From this warning I have been expecting that similar problems occur with many string patterns. In order to better understand this I have created a table test with 1 million rows of random samples, i.e. each primary key is a string permutation of lower case letters, digits, hyphens and German umlauts. There are two additional rows that hold the two special strings 11 and 1-1.

When I dump this table with SELECT * FROM test ORDER BY key \g test.out from a database with encoding UTF8 and collation en_US.UTF-8 under Debian 9 and Debian 10 respectively what I observe is that the sort order only differs with respect to the two special strings. All the other (random) strings don't seem to be affected.

This makes me wonder what is the precise nature of changes to locale en_US (en_US.UTF-8) in glibc 2.28. Does it only concern the relative order between - and digits (and nothing else)? Where does the package source code reveal the exact difference (URL into GitHub, or similar)?

I'm not sure what the minor change were but even minor changes affect how the index stores data on disk, any difference would result in a collision and/or corruption when using a index that had the old sort method.  data is could be looked-up and/or stored in the wrong places..

This requires drop and rebuilding of the index
The source code to glibc can be found here

https://packages.debian.org/buster/glibc-source

Postgresql did add a new feature I think in 10 ICU
which allows the creation of custom Collation tables.
https://www.2ndquadrant.com/en/blog/icu-support-postgresql-10/

Which could be used to avoid these problems in the future


Error running DB2MOVE command I'm trying to execute the DB2MOVE command at a db2 prompt console in a windows 7 machine (I have db2 client installed, with proper catalogs and nodes created and tested.).

The steps I made was:

Open the console (cmd)
Run the db2cmd command (it is on path) and it opens a new console window
Run the db2 tool which puts me into the db2 console window
Connect to a existent database: connect to mydb user x using x
Try to run the db2move command

db2move MYDB COPY -sn MYSCHEMA -tf C:/Users/MYUSER/Desktop/exp.lst -co
  TARGET_DB TESTDB USER user USING pass SCHEMA_MAP ((MYSCHEMA, MYSCHEMA_TEST)) PARALLEL 2

ps.: there is no enter on the command line

The error I'm getting is:

  DB21034E  The command was processed as an SQL statement because it was not a
  valid Command Line Processor command.  During SQL processing it returned:
  SQL0104N  An unexpected token "db2move MYDB COPY -sn" was found following
  "BEGIN-OF-STATEMENT".  Expected tokens may include:  "".
  SQLSTATE=42601
  db2 =>

I'm strictly following the rules on the docs what am I missing?

In order to get the DB2 command window (DB2 CLP) in Windows, you can either Windows key + R and type db2cmd or Start > All Programs > IBM DB2 DB2COPY1 > DB2 Command Line Processor and then you can execute different db2 commands, like.

db2ilist    --> List instances
db2level    --> DB2 Version
db2licm -A  --> DB2 License

etc.

Regards,


List the database privileges using psql I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL using the psql command line tool?

I'm on Ubuntu 11.04 and my PostgreSQL version is 8.2.x.

postgres=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

The docs on Privileges give an explanation of how to interpret the output. For specific privileges on a table of the current database, use \z myTable.

This is my query composed of multiple answers on this question:

SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table,
    CASE
        WHEN COUNT(privilege_type) = 7 THEN 'ALL'
        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
    END AS grants
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee;

This results in something like this:

+------+--------------+----------------+
| user |    table     |     grants     |
+------+--------------+----------------+
| foo  | schema.table | ALL            |
| bar  | schema.table | SELECT, INSERT |
+------+--------------+----------------+


SQL Server not properly using memory? Ihave a database with two tables in it. One table has approximately 20 million rows and another has approximately 70 million rows.

When I run sp_spaceused on these two tables I find that the total size of the data is ~6.7GB and the total size of the index is ~2.6GB. Combined this is a total of ~9.3GB.

This is the only data on this server.

I am a SQL novice so I would assume that the amount of RAM these two tables will eat up would be somewhere close to the 9.3GB after the entire data set and indexes have been loaded into memory. But I am apparently woefully wrong as after running queries for a few minutes the memory taken up by the SQL Server process will hit 30+GB and continue to climb. I've even tested running the same query over and over again and the amount of memory being taken continues to climb.

The reason for the excessive amount of RAM is that the eventual size of the data will be 5-6 times this size and this will be used for data analytics.

My question is why is SQL Server using so much memory? If the entire table and index are loaded into memory what is it continuing to load into memory? Is there a way I can control this? Is there a different type of index I can be using besides nonclustered indexes if my goal is in-memory analytics? I have been reading into columnstore indexes but after testing on a smaller dataset I was actually able to achieve better performance by removing indexes altogether than I was with a columnstore index.

Overall, I'm confused as to what is going on with this server. Can anyone chime in with some guidance? At this time I'm trying to test out SQL Server 2014 but it's still in CPT2 so it isn't a solution until release.

I've also looked into column based storage technologies such as Amazon Redshift but their service is not HIPAA compliant.

On this question I found a few queries to run.  The cached size of my database right now is 32230.390625.  This is greater than 3 times the total size of the database.

db_buffer_pages = 4125490

db_buffer_MB = 32230

I also wanted to do a bit of testing and decided to completely wipe the database, restart the SQL service, and reload the data.  When I did that it was using ~20 GB of RAM.  I can't understand why.  That is over two times the size of the raw data and index.

As stated in the referenced answers, SQL Server will use as much memory as you let it use. Additionally, SQL Server uses memory for a variety of things that are isolated to the domain of "data storage." In addition to the things that mrdenny noted, it also caches the query plan of a query

In SQL Server, memory use is tracked by Memory Clerks. Take a look and see how many there are:

SELECT *
    FROM sys.dm_os_memory_clerks;

These can be roughly broken down into four categories:

General ("Default") - These are the ones that start with "MEMORYCLERK_" (buffer pool is tracked in here so that's where your "buffer pages" query is looking)
CACHESTORE - "CACHESTORE_" objects. This is where query plans are monitored and stored.
USERSTORE - "USERSTORE_" objects. Database metadata and permissions are monitored and stored.
OBJECTSTORE - "OBJECTSTORE_" objects. Internal operations are handled here.

How SQL Server coordinates requests for memory is through what's called a Memory Broker.

SELECT *
    FROM sys.dm_os_memory_brokers;

These brokers are in charge of managing the memory. The CACHE broker will be the first to release memory to system when pressure is encountered. What this means is that your query plans will be evicted from memory before your data pages are flushed from the buffer pool. (Most of the above taken from my notes from Bob Ward's memory presentation at PASS Summit 2013).

This is just a brief overview. There's more to it than just this, but the reality is that there are so many things going on in your system that it's not quite correct to assume that SQL Server isn't handling memory properly just because the size-on-disk value of your database isn't less-than-or-equal-to the value of buffer pages*8/1024.

Here's another query that lists buffered objects, just for fun.

        SELECT

            object_sysname = OBJECT_NAME(p.[object_id]),
            index_id = p.index_id,
            buffer_size_mb = COUNT(*)/128.,
            buffer_count = COUNT(*),
            compression_type = p.data_compression_desc,
            allocation_type_desc = a.type_desc,
            rows = p.[rows]
        FROM sys.allocation_units AS a
            INNER JOIN sys.dm_os_buffer_descriptors AS b
                ON a.allocation_unit_id = b.allocation_unit_id
            INNER JOIN sys.partitions AS p
                ON a.container_id = p.partition_id
        WHERE b.database_id = CONVERT(int,DB_ID())
            AND p.[object_id] > 100
        GROUP BY p.[object_id], p.index_id,
            p.data_compression_desc,
            a.type_desc, p.[rows]
        ORDER BY buffer_count DESC

(A modified version of Glenn Berry's DMV query).

To see what you're allowing SQL Server to use for the buffer pool (including all page allocations) and CLR allocations, you can run this query:

SELECT *
FROM sys.configurations
WHERE name = N'max server memory (MB)';

OR

sp_configure N'show advanced options',1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure N'max server memory (MB)';
GO

A config_value of this (2147483647) means that you're allowing SQL Server to use everything it can get. This may lead to resource starvation on the OS which will, in turn, start taking memory back and swapping to the page file - a nasty scenario. Glenn Berry (again) has a good recommendation for choosing an appropriate setting for max server memory (though it's for up to 2008 and I've not found good guidance for >= 2012).

Additionally, since you've noted that you're testing in SQL Server 2014, there is also the possibility of extending the buffer pool to disk (only if you have SSD storage attached).

As a newly minted DBA, you should understand that, as a matter of policy,  SQL server will consume all the memory in the system to the exclusion of all other programs. If you do not want that behavior, you need to set the MAXMEM setting for the instance. SQL server is not just an app, it has low level functionality that overlaps with things you would ordinarily expect the operating system to handle. That's not a problem, it's intended behavior.

If you are on 2012, look into "xVelocity memory optimized ColumnStore index". It's a way to get a taste of columnar performance without re-architecting your schema. You could also look into the SSAS Multidimensional and Tabular/PowerPivot/"xVelocity in-memory analytics engine" products,but those would be a significant effort. If you're really forward thinking read up Hekaton.

I'd also suggest you start reading Brent Ozar's Accidental DBA 6 month training plan.


SQL Server agent job question Scenario:

I am trying to understand a strange situation. I have a SQL Server 2012 having DB engine and SQL Server agent configured using same domain account. That domain account isn't added to SQL Server logins. Checked using xp_logininfo 'Domain account', 'all' and didn't get any output. I have a SQL server job which updates a table in a DB. That job is running fine. Till now I was under the impression that SQL server agent account has to be defined in SQL Server login in-order to run the job. However, in this case the job was running fine even though SQL Server agent login is not defined in SQL Server logins. So trying to understand how it is possible. Also, since the job updates a table don't know which user/login it uses to update the table. As a test, I changed the job owner to 'sa' and non 'sa' login in both the cases the job is working fine.

So my questions are:

Is it mandatory to have SQL Server agent account defined in SQL server logins to run the job? If yes, then how come my update job is running even though there is no login present for SQL Server agent account in SQL Server logins. If no, how it internally works? Like which account it uses to start the SQL job.
What user/login my update job is using in order to update the table?

1. Is it mandatory to have SQL Server agent account defined in SQL
server logins to run the job?
See this and this.
The SQL Server Agent runs as a Windows service named NT SERVICE\SQLSERVERAGENT. The NT SERVICE\SQLSERVERAGENT login is how the Windows process that is SQL Server Agent connects to the Database Engine to read the msdb database to find out what it should do; and then do it. Both of these logins are members of the sysadmin fixed server role, so they can do anything in the Database Engine. And they need to stay that way.
As for the account that you used to run the services, this is complicated and has changed in each version of SQL Server.The short answer is that the account you specify will be used when a process tries to reach outside of the current Windows environment. But within the computer, there is a mix of authorization granted to the domain user, the service, and the Windows group SQLServerMSSQLUser$computername$MSSQLSERVER.
There are permission in the operating system that an agent account gets by default. Details here.
As per books online:

The account that the SQL Server Agent service runs as must be a member
of the following SQL Server roles:
The account must be a member of the
sysadmin fixed server role.
To use multiserver job processing, the
account must be a member of the msdb database role TargetServersRole
on the master server.
In all Windows versions, permission to log on as a service (SeServiceLogonRight)

But it should not be a member of local administration group.

We recommend choosing a Windows user account that is not a member of
the Windows Administrators group. However, there are limitations for
using multiserver administration when the SQL Server Agent service
account is not a member of the local Administrators group

2. What user/login my update job is using in order to update the table?
Reference: https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly/
Job ownership is an important concept in SQL Server Agent. SQL Server Agent sets the security context for job execution based on the role of the user owning the job. By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account.
The exception to this rule is T-SQL job steps, which execute under the security context of the job owner. If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.

Is it mandatory to have SQL Server agent account defined in SQL server
  logins to run the job?

The service startup account defines the Microsoft Windows account in which SQL Server Agent runs and its network permissions. SQL Server Agent runs as a specified user account. You select an account for the SQL Server Agent service by using SQL Server Configuration Manager, where you can choose from the following options:

Built-in account. You can choose from a list of the following built-in Windows service accounts:
Local System account. The name of this account is NT AUTHORITY\System. It is a powerful account that has unrestricted access to all local system resources. It is a member of the Windows Administrators group on the local computer, and is therefore a member of the SQL Server sysadmin fixed server role.

This account.Lets you specify the Windows domain account in which the SQL Server Agent service runs. We recommend choosing a Windows user account that is not a member of the Windows Administrators group. However, there are limitations for using multiserver administration when the SQL Server Agent service account is not a member of the local Administrators group.

Note :- The Local System account option is provided for backward compatibility only. The Local System account has permissions that SQL Server Agent does not require. Avoid running SQL Server Agent as the Local System account. For improved security, use a Windows domain account with the permissions listed in the following section, "Windows Domain Account Permissions."

  What user/login my update job is using in order to update the table?

Connect the SQL Server instance using management studio
Right-click on the instance and select

“Reports”—“Standard Reports”—“Schema Changes History”

We get a report of schema changes for all databases from which we can get the user account which was used to delete/drop the database.

If you want to know more about the table info regarding deletion/updation then there is undocumented fn_dump_dblog  you can check out Here.

For your further info Here & Here


Oracle conditional statements I am making a report, and I am trying to do following:
If the value of the field is NULL, output empty string. Another words, I don't need the word "null" to be output to the user, just empty string. Is that possible to write such a SQL query? How?
I don't even know how to formulate a google search request for this case, so I am sorry for stupid question.

In Oracle, empty string '' when assigned to a varchar2 type is NULL for all intents and purposes. In my SQLdeveloper, on a 12c database, the following query results in a (null) for every column. If you display to user a blank (a space, ' ') it would surely be displayed by all tools as a blank.

SELECT NULL, '', CAST('' as varchar2(1)), CAST('' as char(1)), NVL(null,'') FROM dual;

Why Oracle violates the SQL92 standard

Use the NVL function for this.

  NVL(columnName,'')

will substitute an empty string for a null value in columnName.


SQL Filegroup degrading performance On our dev boxes, our database resides entirely in the PRIMARY filegroup, and everything works fine.

On one of our production servers, recently upgraded from 2005 to 2008, we noticed it was performing slower than it should.  On this machine, there are two filegroups - PRIMARY and INDEXES.    Both filegroups contain 1 file per logical volume, one logical volume per CPU. The filegroups are sharing logical volumes (both of them are spread out across all disks).

We isolated a few queries that were performing fast on the dev boxes and slow (up to 40x slower) on the production machine.  Turned out these queries were using the non-clustered indexes that resided in the INDEXES filegroup.   Tweaking some of the queries to only use clustered indexes that were in the PRIMARY filegroup dropped their times back to normal.

As a final confirmation, we redeployed the same database on the same machine to have everything in PRIMARY, and things went back to normal!

Here's the statistics output of one of the queries, run on the machine in question  (table names changed to protect the innocent):

FAST (everything in PRIMARY filegroup):

(3 row(s) affected)
Table '0'. Scan count 2, logical reads 14, ...
Table '1'. Scan count 0, logical reads 0, ...
Table '1'. Scan count 0, logical reads 0, ...
Table '2'. Scan count 2, logical reads 7, ...
Table '3'. Scan count 2, logical reads 1012, ...
Table '4'. Scan count 1, logical reads 3, ...

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 445 ms.

SLOW (indexes split into their own filegroup):

(3 row(s) affected)
Table '0'. Scan count 209, logical reads 428, ...
Table '1'. Scan count 0, logical reads 0,...
Table '2'. Scan count 1021, logical reads 9043,....
Table '3'. Scan count 209, logical reads 105754, ....
Table '4'. Scan count 0, logical reads 0, ....
Table '5'. Scan count 1, logical reads 695, ...
**Table '#46DA8CA9'. Scan count 205, logical reads 205, ...**
Table '6'. Scan count 6, logical reads 436, ...
Table '7'. Scan count 1, logical reads 12,....

 SQL Server Execution Times:
   CPU time = 17581 ms,  elapsed time = 17595 ms.

It's pretty clear that having a second file group is making SQL Server batty with choosing an execution plan.  Looking at the corresponding execution plans for the query in each configuration verifies this, they are quite different.

What's going on?  Any insights would be much appreciated.

Random thoughts

The statistics etc weren't disable on rebuild into new filegroup?
Maintenance tasks do not look at the filegroup?
Heavily fragmented disk?
Try MAXDOP 1
Exactly same fillfactor etc?

The IO looks way off so I wonder if stats or fragmentation is causing an issue

My best guess would be that as indexes are separated from the data and present on the same physical disks so the database engine will try to access indexes and data at the same time for the same physical disk slowing it down.

By having the indexes and data in the same filegroup the engine won't try to request both index and data at the same time on the same disk, as he knows it's on the same physical support.

The use of separate filegroup for indexes and data is often discouraged because it requires a heavy inspection of your requests and of your material to find a solution that will improve the request by this mean, ( if there is one ). But basically having data and index of this data on the same disks and in different filegroups causes performance issue.


What is a non-atomic yield in the context of SQL Server? There's a SQL Server performance counter for 'Non-atomic yield rate'.

The documentation simply states that this is

  Number of non-atomic yields per second.

I'm having trouble finding an explanation for what a non-atomic yield is.

What is a non-atomic yield in the context of SQL Server?

The following is what an Atomic block in SQL is:

  Every natively compiled stored procedure contains exactly one block
  of Transact-SQL statements. This is an ATOMIC block.

This is how Atomic blocks relate to transactions and error handling:

BEGIN ATOMIC

  If a transaction already exists on a session (because a batch executed
  a BEGIN TRANSACTION statement and the transaction remains active),
  then starting an atomic block will create a savepoint in the
  transaction. If the block exits without an exception, the savepoint
  that was created for the block commits, but the transaction will not
  commit until the transaction at the session level commits. If the
  block throws an exception, the effects of the block are rolled back
  but the transaction at the session level will proceed, unless the
  exception is transaction-dooming. For example a write conflict is
  transaction-dooming, but not a type casting error.

Atomic Blocks in Native Procedures


INSERT INTO #temp taking 1000 times longer than SELECT INTO #temp I am shredding an xml blob that is being passed to me and loading a temp table for further processing.  One of the things I noticed while working through the process is that performing an INSERT INTO is taking like 20 minutes while doing a SELECT INTO is taking seconds.  The DB statistics are up to date and we are running daily maintenance plans so not sure what is going on.  I know that SELECT INTO bypasses the logs which would make it faster, but I as not assuming that the INSERT INTO would be so much worse.

EDIT: Based on comments adding some more details

Why would the INSERT INTO be taking so much longer than the SELECT INTO for a temp table.

Query example below.  I am shredding XML and inserting into a temp Table I have both the INSERT INTO and SELECT INTO commented out.  The INSERT INTO for just 100 records is showing 382,000+ logical reads while the SELECT INTO is showing none.

;with cteScen AS (
SELECT    DENSE_RANK() OVER ( ORDER BY scenario_node ) AS scenario ,
        RANK() OVER ( PARTITION BY scenario_node ORDER BY scen_val ) AS level_order ,
        scen_val.value('text-value[1]',
                       'varchar(100)') AS 'scen_value' ,
        scen_val.value('(distribution/@id)[1]',
                       'int') AS 'dist'
FROM      @scenarioXML.nodes('/scenarios/scenario')
        AS scenario ( scenario_node )
        CROSS APPLY scenario.scenario_node.nodes('./values/value')
        AS x ( scen_val ))
--INSERT INTO #tmpTbl(Scenario, Level_Order, Scenario_Value, Distribution_ID)
Select top 100 *
--INTO #tmpTbl
From cteScen

Any thoughts?

There are a couple of connect items that might be relevant here.

An INSERT statement using XML.nodes() is very very very slow in SQL2008 SP1
Poor xml performance with table variable
Bad performance when inserting data from element-centric documents
INSERT from .nodes with element-based XML has poor performance on SP2 with x64

I have not been able to reproduce what you see with your query so I can not say for sure what is happening for you but one issue was that there was an eager spool step in the query plan that was not needed. That eager spool can be removed by using a trace flag in the first fixed versions of SQL Server. I am not sure if that is needed in later versions. The eager spool was in there "due to a general halloween protection logic". That might explain why you see the bad performance when you insert to an already existing table. If you have a look at the query plans for both your queries you should see the difference if that is the case.

There are also some workarounds suggested that you can try and I think you should try to specify the text node when you fetch text-value.

scen_val.value('(text-value/text())[1]', 'varchar(100)') AS 'scen_value' ,

If you don't do that SQL Server will aggregate all text values below text-value. For your XML you probably don't see a difference in the result but if text-value was a mixed content node with both text and sub nodes you would.

declare @XML xml = '
<root>
  <item>item text <subitem>sub item text</subitem>
  </item>
</root>'

select @XML.value('(/root/item)[1]', 'varchar(50)') as C1,
       @XML.value('(/root/item/text())[1]', 'varchar(50)') as C2

Result:

C1                           C2
---------------------------- ----------------------------
item text sub item text      item text

Use OPTION ( OPTIMIZE FOR ( '@Your_XML_Variable' = NULL ) )

It will avoid compilation of new execution plans, thus making your query faster.

Read here:
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx


SQL Server: SSMS "Save Results As" creates CSV that Excel mangles Using the Save Results As option, shown below.
CSV looks good!

But when Excel opens/imports it:

Long strings are mangled as numbers (if the data is largely numeric)
Dates are not detected properly
My users beef about all this

How do I get a query to a clean excel file (a real xls/xlsx) without dancing? (We like everything to actually in the SQL file itself... I wish we could do "SELECT INTO FILE:C:\SQLOUTPUT\DATA.XLS ...")

First, make sure you've got the query options set up the way you want.

Go to the Query menu, and choose Query Options:

The first of the two highlighted options tells SSMS to include the column headers in your CSV file. The second tells SSMS to put single quotes around columns that include a comma.

I suspect you've already done this, but thought I should mention it for completeness.

If this isn't sufficient to get your data into a format Excel will correctly interpret, my next step might be (as noted in the comments) to use the Import/Export Wizard to generate a SSIS package that does this. However, the wizard does not always get the quoting correct - in particular, it can put things in double quotes, but won't "double up" the double quotes embedded in the string.

Generally at that point, I resort to brute force.

I create a modified query that:

Replaces double quotes in the data (") with double double quotes ("");
Converts numeric/date values to strings, and
Puts all data into double quotes, and (finally)
Outputs all the columns concatenated together, separated by commas.

So, instead of

 ID | First Name | Last Name |      Address
----+------------+-----------+-------------------
  1 |  John "JT" |   Smith   |  123 Wayne's Way

I generate:

One_Big_Column
------------------------------------------------
"1","John ""JT""","Smith","123 Wayne's Way"

If I'm going to use the query regularly, it'll include a sortOrder column (set to 1), and be UNION ALLed with a SELECT that creates the header row. I then wrap the UNION ALLed queries (making them a subquery), so I can select just One_Big_Column, and sort by sortOrder.

SELECT One_Big_Column
  FROM (
        SELECT '"Id","First Name","Last Name","Address"' as One_Big_Column
              ,0 as sortOrder
        UNION ALL
        SELECT '"' + CAST(Id as varchar(30)) + '","'
                    + REPLACE(firstname, '"','""') + '","'
                    + REPLACE(lastname, '"','""') + '","'
                    + REPLACE(address, '"','""') + '"'
              ,1 AS sortOrder
          FROM myTable
       ) sq
 ORDER BY sortOrder
;

You can see this run here.

Note: If you're formatting the CSV manually, you'll probably want both of the check boxes noted above turned off.

Use TOAD For SQL Server. The free version includes export to excel, even after the trial expires.


mysql taking too long to send data I have a simple table with million of records (14,000,000) and for a simple query it is spending too much time "sending data".

The table

CREATE TABLE IF NOT EXISTS details (
  id int(11) NOT NULL,
  date date NOT NULL,
  time int(2) NOT NULL,
  minutes_online decimal(5,0) NOT NULL,
  minutes_playing decimal(5,0) NOT NULL,
  minutes_chatting decimal(5,0) NOT NULL,
  minutes_away decimal(5,0) NOT NULL
  PRIMARY KEY (id,date,time)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

The simple query

mysql> SELECT * FROM details WHERE id = 3014595;

Explain

mysql> EXPLAIN SELECT * FROM details WHERE id = 3014595;
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | details   | ref  | PRIMARY       | PRIMARY | 4       | const | 1482 |       |
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+

Profile for the query

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| checking query cache for query | 0.000078 |
| checking permissions           | 0.000014 |
| Opening tables                 | 0.000126 |
| System lock                    | 0.000011 |
| Table lock                     | 0.000030 |
| init                           | 0.000027 |
| optimizing                     | 0.000117 |
| statistics                     | 0.040077 |
| preparing                      | 0.000029 |
| executing                      | 0.000006 |
| Sending data                   | 7.536960 |
| end                            | 0.000013 |
| query end                      | 0.000004 |
| freeing items                  | 0.000037 |
| storing result in query cache  | 0.000006 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+

As you can see, the SELECT statement used the index and read only 1482 rows. Yet, the query spent 7.536960 seconds sending the data. It is like the query read much more rows it needed.

It is a simple query, with just 7 fields (row avg 59 Bytes) and no fancy function. Any idea what can be causing this?

Note: id is the user ID. Each user may have at least one entry for every hour of every day. Therefore, id is not unique.

Edit: I have another table with the same structure and much more rows (34 Million). If I run the same query on this larger table, it returns the results in less than 1 second.

The only difference is that the larger table does not get as many queries as the smaller table.

Is it possible that the number of queries is slowing down the process? MySQL cache is on. I've also CakePHP caching the queries to reduce the number of queries.
Is it possible that the file where the table is saved is corrupted or something?

Update
The issue was solved by separating the data tier from the web tier. The data tier also got an upgrade on the RAM and is running on raid10.

For anyone who stumble upon this question and wondering, even without upgrade of RAM, why sending data was taking so much longer. It is because sending data actually include time of searching the data that is to be sent.

https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

  The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Create separate index for id:

  alter table details add key d1(id);

To take this index into effect, restart MySQL or

  analyze table details;

If possible, you can also change database to InnoDB for transaction support and other benefits.


How do I use currval() in PostgreSQL to get the last inserted id? I have a table:

CREATE TABLE names (id serial, name varchar(20))

I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I don't understand how to use it.

I have tried with:

SELECT CURRVAL() AS id FROM names_id_seq
SELECT CURRVAL('names_id_seq')
SELECT CURRVAL('names_id_seq'::regclass)

but none of them work. How can I use currval() to get the last inserted id?

If you create a column as serial PostgreSQL automatically creates a sequence for that.

The name of the sequence is autogenerated and is always tablename_columnname_seq, in your case the sequence will be names names_id_seq.

After inserting into the table, you can call currval() with that sequence name:

postgres=> CREATE TABLE names in schema_name (id serial, name varchar(20));
CREATE TABLE
postgres=> insert into names (name) values ('Arthur Dent');
INSERT 0 1
postgres=> select currval('names_id_seq');
 currval
---------
       1
(1 row)
postgres=>

Instead of hardcoding the sequence name, you can also use pg_get_serial_sequence() instead:

select currval(pg_get_serial_sequence('names', 'id'));

That way you don't need to rely on the naming strategy Postgres uses.

Or if you don't want to use the sequence name at all, use lastval()

Different versions of PostgreSQL may have different functions to get the current or next sequence id.

First, you have to know the version of your Postgres. Using select version(); to get the version.

In PostgreSQL 8.2.15, you get the current sequence id by using select last_value from schemaName.sequence_name.

If the above statement doesn't work, you can use select currval('schemaName.sequence_name');


Stored Procedure result is not showing in Datagrid pane in Toad for Oracle I have created a procedure in Toad for Oracle 12. When I execute the procedure it does not show the dataset in datagrid pane, while the procedure is being executed successfully. What I am doing wrong?

CREATE OR REPLACE PROCEDURE FEEDERS_TO_MAKERS_MCB(OpNo CTS_OPERATIONS.OPERATIONNUM%Type ,BName CTS_OPERATIONS.BLENDNAME%Type)
AS
p_recordset Types.cursor_type;
BEGIN
    OPEN p_recordset FOR

    SELECT FILLED_MCB.BARCODE,TILTED_MCB.TILTINGTIME,MAKERS.FEEDERNUM,MAKERS.MAKERNUM
    FROM FILLED_MCB INNER JOIN TILTED_MCB ON FILLED_MCB.FM_ID = TILTED_MCB.FM_ID
    INNER JOIN MAKERS ON MAKERS.M_ID = TILTED_MCB.M_ID
    INNER JOIN CTS_OPERATIONS ON CTS_OPERATIONS.CTS_ID = FILLED_MCB.CTS_ID

    --WHERE CTS_OPERATIONS.OPERATIONNUM=OpNo AND CTS_OPERATIONS.BLENDNAME=BName;
    WHERE CTS_OPERATIONS.OPERATIONNUM=3004 AND CTS_OPERATIONS.BLENDNAME='PG';
END;

---- Execute Procedure ---------------

exec FEEDERS_TO_MAKERS_MCB(3004,'PG');

I think you want an output parameter of the type sys_refcursor. Currently your cursor is assigned to a local variable, which gets destroyed when the procedure terminates.

CREATE OR REPLACE PROCEDURE FEEDERS_TO_MAKERS_MCB(
   OpNo CTS_OPERATIONS.OPERATIONNUM%Type
   ,BName CTS_OPERATIONS.BLENDNAME%Type

   ,p_recordset OUT SYS_REFCURSOR

)
AS
BEGIN
    OPEN p_recordset FOR

    SELECT ...
END;

If your script is correct and you are just not able to view the result in Data Grid then follow below steps, else ignore it.

Execute the script by

Pressing F9, Or
right click on then Execute --> Execute/Compile Statement,  Or
click on the button highlighted in the screenshot:


How to find the same salaries and department names? Write a query to display last name ,department number,and salary of any
employee whose department number and salary both match the department number and salary of any employee using the following table :

employee_id int,
first_name varchar(20),
last_name varchar(25) not null,
email varchar(25) not null,
phone_number varchar(20),
hire_date date not null,
job_id varchar(10) not null,
salary float,
commission_pct float,
manager_id int,
department_id int,
constraint emp_pk primary key(employee_id)

I have tried this :

SELECT * FROM employees e1
WHERE e1.employee_id = ( SELECT employee_id
                           FROM employees e2
          WHERE e2.department_id = e1.department_id
          AND e2.salary = e1.salary);

What is wrong?

This should work:

select e1.*
  from employees e1 inner join (
    select count(employee_id) as CNT, department_id, salary
    from employees group by departmant_id, salary) t1
  on e1.department_id=t1.department_id and e1.salary=t1.salary
  where t1.CNT>1

The problem with your own query is twofold:

the return value of (select...from ..e2..) is from the very nature of your problem not unique, so the "=" relationship should either throw an error or return whichever is the first result (I'm not familiar enough with MySQL to know which applies), which doesn't have to be the one you're looking for.
filtering via "e1.employee_id =" will only ever be true for the record itself, but NOT for another record that matches your criteria

If you insist on using a filter:

SELECT * FROM employees e1
WHERE (e1.department_id,e1.salary) IN
 (SELECT e2.department_id,e2.salary
  FROM employees e2
  WHERE e2.employee_id <> e1.employee_id)

select department_id, salary, count(employee_id) as employees
    from employees
    group by department_id, salary
    having count(*)>1
    order by department_id, salary


Is it possible to make a trigger that does not allow INSERT? I'm currently learning how to implement sqlite commands through C code and I've been trying to figure out the best way to ensure that no data can be inserted to a table (which will only happen during a certain period of time).
I have a database History and in this database exists the table events with the following schema:
CREATE TABLE events
    (
     id integer primary key autoincrement,
     msg varchar(20) NOT NULL,
     ago varchar(30) NOT NULL
    );

I know a mySQL implementation would likely use INSTEAD OF INSERT on the table, though I also know INSTEAD OF can't be executed on tables, only views in sqlite. (To be honest I'm new to databases so I only kinda understand this concept).
Would anyone with some more database experience be able to help me with writing (or at the very least understanding the theory behind) a trigger that does not allow data to be inserted to the table?
Edit: This article is where I first got the idea that this would be possible

It will be something like
CREATE TRIGGER forbid_events_update
INSTEAD OF UPDATE
ON events
FOR EACH ROW
BEGIN
    RAISE(ROLLBACK, 'Events update not allowed'); /* maybe ABORT of FAIL */
END;

And the same for INSTEAD OF DELETE.


Opening an Oracle database with a corrupted redo log I have a database that is not open. And I have a redo log that is corrupted.

What would be the best way for me to open the database? This is a testing db and I am not concerned with data loss - but I need to get the database open and working again.

Some additional notes:
Trying to switch logs tells me that the log is required for recovery
Attempting to recover fails
Trying to open gives the error msg that the log is corrupted

Additional clarification:
I do need some of the data in the db; but I don't need any of the recent data (about a year worth of data). There is some configuration data which I do want.
I don't know the structure of the db, and I have no script to re-create it.

What you are looking for is to clear the redo log.

ALTER DATABASE CLEAR LOGFILE GROUP #;


Does `COUNT` discard duplicates? My professor taught me this SQL statement:
SELECT COUNT(length) FROM product

will return 2 with the following dataset:
product
|id | length | code |
|-------------------|
| 1 |    11  | X00  |
| 2 |    11  | C02  |
| 3 |    40  | A31  |

She justified it by saying that COUNT doesn't count duplicates. I disagreed. After trying a lot of DBMS, I've never found one that has this behaviour. Does such an DBMS exist?

Either your professor made a mistake or you misunderstood what she said. In the context of relational DBMSes, as implemented by various vendors, the aggregate function COUNT(<expression>) returns the number of non-NULL values of <expression> in the result set (or a group).

There is a special case of COUNT(*), which returns the number of rows in the result set or group, not the number of values of anything. This is equivalent to COUNT(<constant expression>), such as COUNT(1).

Many databases support COUNT(DISTINCT <expression>), which will return the number of unique values of <expression>.

maybe she mean in conjunction with unique, But Count does COUNT DUPLICATES.
There are some teachers who do not know their stuff, no worries just inform your classmates/friends so that when they go on to higher db and real life they wont forget, better yet send an anonymous message to your teacher asking her that they did not understand some of the sql functions and want a demonstration, have your teacher come up with a way for the class to suggest what to insert include duplicates (do not have the data be large) and when she uses the function count, you got her. Some people will pickup on it, Also when she says other databases, have your friend ask her which ones, then double snare her and say you tried all those databases and they don't work like she said and that count picks up duplicates.


How to dump an advanced revelation database? Retail store I work with has an ancient inventory/accounting/receiving system that is based off Advanced Revelation.  They run the DOS and Windows clients.  I'd like to get a dump of their data to work on migration or at least create other views of their data, without paying $$$ for the vendor do to this service.

I can find very little documentation of Advanced revelation.  Can anyone help?

Advanced Revelation is a multi-valued database based on the original PICK operating system.  You can find information on PICK and it's spin-offs here:

https://en.wikipedia.org/wiki/Pick_operating_system

I've been developing on PICK and like systems for over 35 years, including Advanced Revelation.  Manuals are a little difficult to find online. You may also have some luck finding more information here:

http://www.jes.com

I have developed software which will automate the extraction of all Revelation, Advanced Revelation (Arev) and OpenInsight data. No knowledge of these platforms or a functioning system is required. All that is necessary is the .LK and .OV data files.
This is the link:
https://www.exportr.co.nz/
or contact me at the Gmail account for jocelyn.amon


Error when start to attach mdf - : parameter name: ncolindex actual value was -1 (Microsoft.Sql Server.Grid Control) Got the following error when first attempted to attach a database

  parameter name: ncolindex actual value was -1 (Microsoft.Sql Server.Grid Control)

What does this mean and how do I correct ?

Try to login with Admin account (not sql account)


User Password Expiry date is null in user_users? Is it possible to find my password expiry date without asking the DBA?

My user does not have privileges to view the DBA_USERS table. Also, I tried select * from USER_USERS table, but expiry_date is empty.

It might be that your account has been assigned the profile with PASSWORD_LIFE_TIME set to UNLIMITED, and thus your password will never expire. You can query the USER_PASSWORD_LIMITS view to determine what password limits are currently in effect for your account:

SQL> select * from user_password_limits;

RESOURCE_NAME                    LIMIT
-------------------------------- ---------
FAILED_LOGIN_ATTEMPTS            10
PASSWORD_LIFE_TIME               UNLIMITED
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7

Issue this command:

select username, account_status, expiry_date, profile from dba_users;


Database mirroring stuck in suspended mode We have an issue with database mirroring on one of the databases on our sql instance where mirroring is suspended for almost 15 hours now.

DB is 200 GB on SQL server 2008R2 Sp3+cu with latest patched done for meltdown.

so far we have done below

Rebuild the endpoints--> no luck
Resume the mirroring with partner resume --> no luck
DBCC CHECKDB shows 0 errors found on either side.

Nothing found from error logs on Principal. However on secondary when we resume error logs seems to have stack dump logged every time.

Any help on how to resume the suspended mirror apart from rebuilding mirroring from scratch?

First thing to try:
ALTER DATABASE Gandalf SET PARTNER RESUME;

Might work!  If you prefer you can do it with the GUI - Tasks / Mirror / Resume
Check logs for an Error 3624.
I get this occasionally - reading up on this, it looks very much like "an error has occurred."
My solution is to stop mirroring and recreate.
I would be interested if anyone has better information.

Sometimes this works:  1) Start and Stop the mirroring endpoints 2) Set Partner Resume, in that order.

ALTER ENDPOINT Mirroring STATE=STOPPED
ALTER ENDPOINT Mirroring STATE=STARTED
ALTER DATABASE databasename SET PARTNER RESUME

If this does nothing, you may have to rebuild the mirror.


Open Transaction that won't go away SPID Ns There is a transaction that has been open on this instance since it was restarted and is preventing us from clearing the ldf.

Here is the transaction information:

Transaction information for database 'MYDATABASE'.

Oldest active transaction:
    SPID (server process ID): 7s
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (54883:155655:1)
    Start time    : Jun 30 2014  5:45:35:820PM
    SID           : 0x01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Does anyone know what 7s means, I have not yet seen a SPID with a letter in it.

Thanks,
Craig

Update 14:03

I ran the resolution suggested here and this is the result after restart:

2014-07-18 14:01:12.83 spid7s      Mise à niveau des paramètres d'abonnement et des objets système dans la base de données [MYDATABASE].
2014-07-18 14:01:13.03 spid7s      Nom d'objet 'MSreplication_subscriptions' non valide.
2014-07-18 14:01:13.03 spid7s      Error executing sp_vupgrade_replication.
2014-07-18 14:01:13.03 spid7s      Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.
2014-07-18 14:01:13.04 spid7s      Saved upgrade script status successfully.

I still have the same problem. Any ideas on how to resolve this?

Update 19/07
Problem solved by adding the  MSReplications_subscriptions table to the database where sp_vupgrade_replication was failing

I guess this is a known issue and Microsoft has released information about it in below links

Cause:

This issue occurs because the "tran_sp_MScreate_peer_tables" transaction was left open by a replication upgrade script during the upgrade. This open transaction prevents usual log truncation.

Transaction log for database is growing with system SPID as open transaction

There is also a documented support article which will help you in understanding the issue

Database transaction log continues to grow after upgrade

SPID followed by s  like 7s here means the Open transaction is on the Secondary node and not this node, so you will  have to kill the transaction on that node.


cursor: pin S wait on X in 11g This has been one of top5 wait events in our database, even though there were not many waits but we see it as a potential threat, we want to find the root cause and solution.
Any advice will be greatly appreciated..

DB Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

AWR:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                            3,887          95.0
db file sequential read             207,505         125      1    3.1 User I/O
direct path read                     33,793          69      2    1.7 User I/O
cursor: pin S wait on X                   5          43   8650    1.1 Concurrenc
db file scattered read               34,229          39      1     .9 User I/O

Why does a wait event that occurred 5 times accounts for 1.1% of your database wait time concern you as a potential threat?  Is there some additional information that leads you to believe that this is a threat?  Obviously, something has to be in your top 5 wait events.  Is this event consistently one of the top wait events?  Or did it just appear on one AWR?

The particular wait generally occurs when one session is trying to pin a cursor while another session is parsing it.  Potentially, some of the 95% of the CPU related wait events could be the result of parsing (though you'd want to look at the number of hard parses elsewhere in the AWR).  If you have an OLTP system, you should really only be doing any quantity of hard parses immediately after a reboot-- everything else should be using prepared statements and, at most, doing a soft parse.  If you've got dynamic SQL somewhere in the system, removing that may help though it may also be a rather drastic step to save a few seconds.

I had this problem after migrating to 10g R2 10.2.0.4 11g R2 11.2.0.2.  A query written in the wrong way with several "AND" in the same column (INLIST).

The solution I found: modify the query ->

Change the parameter "optimizer_features_enable" of 11.2.0.2 to 10.2.0.4.

Hope this helps


How do I move SQL Server database files? I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server.

How can I do this?

You don't have to stop the SQL Server service to move database files, but you do have to take the specific database offline.  This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application.

The process to move them is fairly simple.  Detach/Attach was already described, but it is not nearly this complex.

Change the file locations with an ALTER DATABASE command:

USE master; --do this all from the master
ALTER DATABASE foo
MODIFY FILE (name='DB_Data1'
             ,filename='X:\NewDBFile\DB_Data1.mdf'); --Filename is new location

Note, you do not need to declare the old location in this command.  Changing this path does not take effect immediately, but will be used the next time the database starts up.

Set the database offline

(I use WITH ROLLBACK IMMEDIATE to kick everyone out and rollback all currently open transactions)

ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;

Move/Copy the files to the new location

Just copy the files over using your favorite method (Click 'n Drag, XCopy, Copy-Item, Robocopy)

Bring the database online

ALTER DATABASE foo SET ONLINE;

You can see this described in more detail here.

In case your database has some unusual characters like a dash character:
ALTER DATABASE [SBO-COMMON]
MODIFY FILE (NAME = 'SBO-COMMON', FILENAME = 'D:\Database\SBO-COMMON.mdf');

ALTER DATABASE [SBO-COMMON]
MODIFY FILE (NAME = 'SBO-COMMON_log', FILENAME = 'E:\DatabaseLogs\SBO-COMMON_log.ldf');


Best Indexing for Performance - MySQL I have a question related performance DB MySQL. I have querying like this with big data inside :

SELECT
       *
FROM
       tableA a
       INNER JOIN tableB b ON a.id = b.id AND a.code = b.code
       INNER JOIN tableC c ON a.uniq = c.uniq
WHERE
       a.date = '2020-01-01'
       AND a.type = 'X'
       AND b.merk =  'New';

My question is, what is better using single index or multi-column index?

Ex :

Index in tableA (Example1) :

idx1 (merk, code, date, type)
idx2 (date)
idx3 (merk)
idx4 (code)
idx5 (type)

Index in tableA (Example2) :

idx1 (merk, code, date, type)
idx2 (merk, code)
idx3 (date, type)

What is better? Example1 or Example2? And please explain it.

Notes : maybe tableA will got varian WHERE on another query for future

Thank you

The engine will only be able to make use of one index per table so while the other indexes may help you in the future with other queries, their benefit will be limited in assisting you with this particular query.

Given what we know about your query and limited knowledge of the table and the insert pattern, I would suggest the following.

Table A - (Date, Type)
Table B - (merk, ID, Code)
Table C - (Uniq)

Of course, if you were able to limit the result set columns (getting rid of the SELECT *) then further columns in the index could be of benefit.

These indexes above will allow the engine to seek quickly and get quickly to the rows that it wants.

I do have one question about the "merk" on Table B.  If that is something like a status that changes then I would not index on it, leaving just an index on ID and Code.  If the value of the field changes frequently then it is usually a poor index candidate as it will cause much fragmentation.

The best result can be estimated with the next indexes:

Table A - (id, uniq, date, code, type)
Table B - (id, code, merk)
Table C - (uniq)