Arabic characters not displaying in oracle database We have a Oracle database 8.1.7 running for many years on Win-2000. We wanted to move the database on a newer machine running Win 2003. I did the following

I made a cold backup of old database.
Installed the oracle database same ver on the new machine and moved
the data files from the old to the new server.
Imported the data dump taken from export (taken from old server) on
the new server.
Without a full test we shifted live to the new server.
Everything was OK on the new oracle server except for the Arabic
characters. I am getting ?? (questions marks) characters in the
Arabic fields.

I checked the nls_database_parameters on both the servers.

Select * from nls_database_paramters;

The old database had: nls_char & nls_nchar = Ar8mswin1256
The new database had nls_char & nls_nchar = We8iso8859p1

I then did the following in the new server:

Changed the character set (nls_char)in the new database to
AR8mswin1256.
Changed the Nls Nchar set to Ar8mswin1256.

All the above done were by an alter database command as below:

SVRMGR> STARTUP MOUNT;
SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SVRMGR> ALTER DATABASE OPEN;
SVRMGR> AALTER DATABASE NATIONAL CHARACTER SET ar8mswin1256;
SVRMGR> SHUTDOWN IMMEDIATE;
SVRMGR> STARTUP;

I checked nls_database_parameters in the new server both nls_char, nls_nchar was changed to Ar8mswin1256.

I checked the database through our ERP applicatoin but still Arabic characters displaying in ??.

I then checked the registry of new Oracle server. The value of nls_lang is We8iso8859p1
whereas the registry of the old server had value of nls_lang as Ar8mswin1256. I changed
the value in new server to be American_america.Ar8mswin1256. Checked the database still
arabic characters displaying as ??.

The registry of Client has nls_lang as American_america.Ar8mswin1256.

It is a production database. How can I solve the problem of Arabic characters being displayed as ??? in the new server?

I still have the old server database but it is not live. It doesnt contain updated
data.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET INTERNAL_USE AR8MSWIN1256;

SHUTDOWN IMMEDIATE;

 -- or SHUTDOWN NORMAL;

STARTUP;

please see the link below it's "Oracle Database Globalization Support"
and see all character set..
for example : ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256
then copy it in 'regedtit->heky->software->orale->oraclehome -> lock for value (nls_lang)'
http://docs.oracle.com/html/B13805_02/gblsupp.htm


Are there any inherent shortcomings in this schema? This is derived from the SQL generated by sqldbm.com that I simplified because it wasn't generating foreign keys correctly and it seems to me that there's no need to pull in every foreign key from the parent anyway (perhaps I'm mistaken!)

Is there significant value in pulling in every foreign key from the parent and making them part of the child's primary key? Is there a way to automate setting these additional keys? (I.e. a ticket row being a child of an event row which is a child of a location row and the ticket row also includes location_id and needing to make sure that gets set correctly.)

Does anyone see anything in the upper schema diagram that might shoot me in the foot down the road? Not pictured but desired is an additional many-to-many mapping to event questionnaire responses also attached to a ticket (i.e. a registrant providing answers to experience levels in a sport discipline, gear questions, etc as part of their ticket purchase). I am already planning on removing the multiple locations for costs and having option value be the only place cost is found and always having an option value supplied for each event.

Unfortunately, it's very difficult to just cast one's eye over a schema and say, "Yep that's great" or "Yes, that's going to work".

Schema and system design is an iterative process - you start with a few ideas, implement them and test to see how it's working. Then you rejig the bits that aren't working and see - now the rejigging will (in all likelihood) break some bits that were working previously and that you hadn't changed - so you tweak again, and hopefully you asymptotically approach (nirvana...) a decent working app.

I can only advise you to

visit databaseanswers.org - check out databaseanswers - there's lots of links related to tickets and events, and
check out your search engine of choice for "open source reservation systems" (or similar) and download some systems and check out their schemas.

Sorry not to be able to be of more help, but it's analagous to asking "I'm thinking about writing a book, here are the chapter titles, will it be good?".

Start out your system and if you have more specific questions, come back here and ask them - p.s. welcome to the forum.

Rick is right - for the money type, use DECIMAL or more precisely DECIMAL(10,2), most of the time it will suffice. You can also use the prefix tb_ for table names - this can make your code easier for you to maintain.


How do I change Postgresql to be listening on port 5432? I've upgraded Postgresql from 9.3 to 9.6 on my machine (linux mint rafaella- so ubuntu 14.04)

As the previous version was listening on 5432 the new version is listening on 5433, but I want to change that to 5432 so that the previous configuration (rails, phppgadmin etc) work with the new postgres server.

I have changed the port in postgresql.conf from 5433 to 5432 and restarted postgres, but this didn't work:

$ sudo netstat -nltp |grep 5432
$ sudo netstat -nltp | grep 5433
tcp        0      0 127.0.0.1:5433          0.0.0.0:*               LISTEN      25467/postgres

I also tried setting the environment variable and restarting postgres:

PGPORT=5432; export PGPORT

again, still listening on 5433 and nothing on 5432.

trying to connect gives me:

$ sudo -u postgres psql postgres
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

What am I doing wrong?

Output of pg_lsclusters:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

contents of pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

ypercube and deszo were able to get this user's environment up and running (per the chat transcript).

The key seemed to be running:

sudo pg_ctlcluster 9.6 main restart

but not running it as the postgres user.

See the chat log for more details.

In ubuntu 20.04 I solved it like this:
To list which ports is using:
sudo lsof -i -P -n | grep LISTEN

To list the paths of postgres and your respectives ports
grep -H '^port' /etc/postgresql/*/main/postgresql.conf

Choose the path of port you want change. In my case is

/etc/postgresql/13/main/postgresql.conf

then I edit the configurations of port with:
sudo nano /etc/postgresql/13/main/postgresql.conf

Save the changes and restart the service with:
sudo service postgresql restart

Then you can run lsof -i -P -n | grep LISTEN to see the ports is correctly.


Why its failed on linux server? I tried to mark disk and got Fail!!!

[root@oradbserver3 Server]# /etc/init.d/oracleasm createdisk DISK1 /dev/sda2

Marking disk "DISK1" as an ASM disk:                       [FAILED]

You just need to do:

/etc/init.d/oracleasm start

.... to start off with.

Look at your screenshot - you completely ignored the fact that the oracleasm command gave you the command-line usage back, rather than actually doing anything.

Try following

load the kernel module

modprobe oracleasm

other option is using RAW devices for ASM.

raw /dev/raw/rawX /dev/sdX1
chown oracle:dba /dev/raw/raw1
chmod 660 /dev/raw/rawX

This is a  good tutorial on youtube (due to selinux enabled)

http://www.youtube.com/watch?v=JRrNuudTBR4


Return a fully dynamic table from an Oracle function I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of type varchar.

As far as I have seen, I have to always to create an object/record and a table type of it. This means that my idea won't work? The underlying goal is to pass a system command output back to a callee as a table.

Edit: more on the task. I want to issue an OS command, consume the output and return it back as a table. The output from the OS command is going to be CSV-formatted data. At the time of execution I do not know the amount of rows to be returned but only the amount of columns which is passed as the second arg. I was thinking about using Java with a dynamic STRUCT and an ARRAY containing them. Though I would prefer the former approach.

It should look like this:

create function(clob query, list of varchars cols) returns table
begin
  execute system command(query, cols);
  examine sysout from command;
  return tabular data from syscmd as table;
end

It is possible, though quite complicated, to write a pipelined table function that returns a variable structure.  Your pipeline table function could take the two arguments and use the Oracle Data Cartridge interface and the magic of the AnyDataSet type to return a dynamic structure at runtime.  You can then use that in subsequent SQL statements as if it was a table, i.e.

SELECT *
  FROM TABLE( your_pipelined_function( p_1, p_2 ));

A couple more references that discuss the same sample implementation

Dynamic SQL Pivoting
The Implementing the Interface Approach section of the Oracle Data Cartridge Developer's Guide

One solution would be to create an extrnal table based on the Lucene output.
You can easily alter the external table definition (and to point it to multiple files).

So you will have:

function l_query(clob query, list of varchars cols) returns table_name
begin
execute system command(query, cols);
#hopefully we know the output filename
create a new external table mapping the output;
end


Multiple left join hangs server I have a problem with left join query. It connects 3 tables and calculates sales value for categories.

Query:

SELECT
    categories.name,
    categories.id,
    SUM((orders_products.product_price_gross + orders_products.option_price)*(1 - (orders_products.rebate/100))  * orders_products.product_qty) AS suma
FROM product_categories
LEFT JOIN orders_products
    ON product_categories.product_id = orders_products.product_id
LEFT JOIN categories
    ON product_categories.category_id = categories.id
GROUP BY categories.name, categories.id
ORDER BY suma DESC

    categories
id  int(11) NO  PRI     NULL    auto_increment
parent_categories   int(11) NO      0
name    varchar(250)    NO

orders_products
id  int(11) NO  PRI     NULL    auto_increment
order_id    int(11) NO  PRI     0
product_id  int(11) NO      0
product_name    varchar(255)    NO
product_price_gross     double(8,2) NO      0.00
product_qty     int(3)  NO      0
option_price    decimal(8,2)    NO      NULL
discount    decimal(8,2)    NO      NULL
rebate  int(11) NO      NULL

product_categories
product_id  int(11) NO  MUL     0
category_id     int(11) NO      0
ord     int(11) NO      0
id  int(11) NO  PRI     NULL    auto_increment

Tables are big - categories - 2808 rows, product_categories - 84 000, orders_products - 47500

It should display list of categories and sales values for each one. Instead of that it hangs the server totally and restart is needed. I used many different queries from StackOverflow but any of them hangs server. I have no idea why.

MySQL's query optimizer is not very smart, anything you can do to help it will go a long way to improving your query performance.

Couple of issues I see at a glance without being able to see an execution plan (please edit your question with the output of explain select * from table...:

Your first join is going from an indexed column on your left table (product_categories.product_id) to a non-indexed column of your right table (orders_products.product_id). With no where clause, that's going to result in a very heavy nested loop through every record on the orders_product for each row in your product_categories table. To speed it up you could add an index to orders_products.product_id, ideally you would also limit the left table using some sort of criteria in a where clause.
Your second join is doing a similar mistake. Your left table column is not indexed while your right column is a PK. I think you're probably signing up for another long queue of nested loops through each of the tables.

The fact that you're left joining also means that regardless of what's in your right table, you're going to have to inspect every element in your left table. Inner joins can be more performant, but without indexing your columns properly and without the use of some kind of where clause criteria to help limit the rowsets you have to inspect, this is going to be a heavy query.

In summary:

index your join columns
use inner joins where possible
use limiting criteria in your where clause if possible ti reduce the amount of index scanning needed.

I am not so familiar with mysql. BUT. You should always connect relations on primary - foreign key. You join PRODUCT_CATEGORIES with ORDERS_PRODUCT on product_id which are foreign key and I am not sure if they can be NULL. Run EXPLAIN select and you will know what your query planner is doing.


In SQL, is it composite or compound keys? About SQL (Computing/Databases):

When we have two or more fields in a table that, together, identify its records uniquely, what's the proper way of calling them? Composite or compound keys?

I've seen on the web both uses so I'm not really sure.

Both composite key and compound key describe a candidate key with more than one attribute. According to the Relational Database Dictionary (C.J.Date) they mean the same thing.

In ER modelling the term "compound key" also has a more specific meaning. It means a key whose constituent attributes are references to keys in other entities - i.e. a compound key forms an identifying relationship. For most purposes, this isn't an especially useful or important concept so the terms composite/compound are often treated as interchangeable. It's probably best to stick to "composite key" unless you are referring specifically to the ER modelling concept of a compound key.

A compound key is created when 2 or more primary keys from different tables are present as foreign keys within an entity. A composite key is a composition of two or more columns that uniquely identify rows in an entity


Can a foreign key have a default value equal to 0? I have a column whose default value is 0 .Can I make that column as foreign_key

Yes, you can define a column with a default value of 0 as a Foreign Key.  However, for the constraint to work, you would need to have a row in the source table with a value of 0 as well.  Example:

CREATE TABLE dbo.Master
(MasterID INT Primary Key,
 Name NVARCHAR(20));

INSERT into dbo.Master values (0,NULL);
INSERT into dbo.Master values (1,'Full');
INSERT into dbo.Master values (2,'Partial');

CREATE TABLE dbo.Slave
(SlaveID INT Primary Key,
 Description NVARCHAR(20),
 MasterID INT FOREIGN KEY REFERENCES dbo.Master(MasterID) DEFAULT (0));

 INSERT  INTO dbo.SLAVE (SlaveID, Description) VALUES(10,'Quickly');

This works fine.  However, what if Master does not have a 0 MasterID.  Then you would get:

Msg 547, Level 16, State 0, Line 15
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Slave__MasterID__3943762B".
The conflict occurred in database "TEST", table "dbo.Master", column 'MasterID'.

There is a WITH NOCHECK option (in SQL Server) which allows you to create a Foreign Key without the required data existing in the master table.  Likewise, Foreign Key constraints can be disabled and enabled, but I would recommend against doing that for any normal circumstances.  This is defined at:

http://technet.microsoft.com/en-us/library/ms177463(v=sql.105).aspx

You can insert the 0 primary id record in the main table.


Measure the size of a PostgreSQL table row I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some other factor.
I need all of the fields (or nearly all of them), so selecting just a subset isn't a quick fix. Selecting the fields that I want is still slow.
Here's my table schema minus the names:
integer                  | not null default nextval('core_page_id_seq'::regclass)
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   |
integer                  | not null default 0
text                     | default '{}'::text
text                     |
timestamp with time zone |
integer                  |
timestamp with time zone |
integer                  |

The size of the text field may be any size. But still, no more than a few kilobytes in the worst case.
Questions

Is there anything about this that screams 'crazy inefficient'?
Is there a way to measure page size at the Postgres command-line to help me debug this?

Q2: way to measure page size
PostgreSQL provides a number of Database Object Size Functions. I packed the most interesting ones in this query and added some Statistics Access Functions at the bottom. (The additional module pgstattuple provides more useful functions, yet.)
This is going to show that different methods to measure the "size of a row" lead to very different results. It all depends on what you want to measure, exactly.
This query requires Postgres 9.3 or later. For older versions see below.
Using a VALUES expression in a LATERAL subquery, to avoid spelling out calculations for every row.
Replace public.tbl with your optionally schema-qualified table name to get a compact view of collected row size statistics. You could wrap this into a plpgsql function for repeated use, hand in the table name as parameter and use EXECUTE ...
SELECT l.metric, l.nr AS bytes
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.tbl t                     -- provide table name *once*
   ) x
CROSS  JOIN LATERAL (
   VALUES
     (true , 'core_relation_size'               , pg_relation_size(tbl))
   , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
   , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
   , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
   , (true , 'indexes_size'                     , pg_indexes_size(tbl))
   , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
   , (true , 'live_rows_in_text_representation' , txt_len)
   , (false, '------------------------------'   , NULL)
   , (false, 'row_count'                        , ct)
   , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
   , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);
Result:
              metric               | bytes    | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 44138496 | 42 MB        |            91
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    32768 | 32 kB        |             0
 table_size_incl_toast             | 44179456 | 42 MB        |            91
 indexes_size                      | 33128448 | 32 MB        |            68
 total_size_incl_toast_and_indexes | 77307904 | 74 MB        |           159
 live_rows_in_text_representation  | 29987360 | 29 MB        |            62
 ------------------------------    |          |              |
 row_count                         |   483424 |              |
 live_tuples                       |   483424 |              |
 dead_tuples                       |     2677 |              |
For older versions (Postgres 9.2 or older):
WITH x AS (
   SELECT count(*)               AS ct
        , sum(length(t::text))   AS txt_len  -- length in characters
        , 'public.tbl'::regclass AS tbl      -- provide table name as string
   FROM   public.tbl t                       -- provide table name as name
   ), y AS (
   SELECT ARRAY [pg_relation_size(tbl)
               , pg_relation_size(tbl, 'vm')
               , pg_relation_size(tbl, 'fsm')
               , pg_table_size(tbl)
               , pg_indexes_size(tbl)
               , pg_total_relation_size(tbl)
               , txt_len
             ] AS val
        , ARRAY ['core_relation_size'
               , 'visibility_map'
               , 'free_space_map'
               , 'table_size_incl_toast'
               , 'indexes_size'
               , 'total_size_incl_toast_and_indexes'
               , 'live_rows_in_text_representation'
             ] AS name
   FROM   x
   )
SELECT unnest(name)                AS metric
     , unnest(val)                 AS bytes
     , pg_size_pretty(unnest(val)) AS bytes_pretty
     , unnest(val) / NULLIF(ct, 0) AS bytes_per_row
FROM   x, y

UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;
Same result.
Q1: anything inefficient?
You could optimize column order to save some bytes per row, currently wasted to alignment padding:
integer                  | not null default nextval('core_page_id_seq'::regclass)
integer                  | not null default 0
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   |
text                     | default '{}'::text
text                     |
timestamp with time zone |
timestamp with time zone |
integer                  |
integer                  |

This saves between 8 and 18 bytes per row. I call it Column Tetris. See:

Configuring PostgreSQL for read performance
Calculating and saving space in PostgreSQL

Also consider:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

If an average of the current rows size is wanted, you could use  pg_column_size :
SELECT SUM(pg_column_size(table_name.*))/COUNT(*) FROM tablename;

Using it per column :
SELECT SUM(pg_column_size(table_name.column_name))/COUNT(*) FROM tablename;


Delete the second record where a user has two records I have a simple database scheme, which due to previously being badly set up has numerous duplicate records.

record_table

|--------------------------------------------------------|
| id  | subid  | catid  | start_date | end_date          |
|--------------------------------------------------------|
| 1   |  1     | 1      | 2015-01-01 | 2015-12-31        |
| 2   |  1     | 1      | 2015-01-01 | 2015-12-31        |
| 3   |  2     | 1      | 2015-01-01 | 2015-12-31        |
| 4   |  2     | 1      | 2015-01-01 | 2015-12-31        |
| 5   |  3     | 1      | 2015-01-01 | 2015-12-31        |
|--------------------------------------------------------|

I want to delete records 2 and 4, because there is already a record for that subid/catid combo that covers the same dates.

I want to be able to do:

DELETE from record_table WHERE catid='1' AND
    start_date<='2015-02-13' AND
    end_date>='2015-02-13' AND
    [the user has two records for the previous conditions]

Solution

Create a TempTable(record_table_keep) with minimum id for every subid,catid combination
Perform a DELETE JOIN between record_table and record_table_keep using LEFT JOIN
Only delete records from record_table whose id is not in record_table_keep

Proposed Query

CREATE TABLE record_table_keep
SELECT id,subid,catid FROM record_table WHERE 0=1;
ALTER TABLE record_table_keep ADD PRIMARY KEY (id);
INSERT INTO record_table_keep (id,subid,catid)
SELECT MIN(id) id,subid,catid FROM record_table GROUP BY subid,catid;
DELETE A.* FROM record_table A
LEFT JOIN record_table_keep B USING (id)
WHERE B.id IS NULL;
DROP TABLE record_table_keep;

Give it a Try !!!

DELETE FROM record_table a
 WHERE EXISTS( SELECT subid
                 FROM record_table b
                WHERE a.subid= b.subid
                  AND a.id < b.id )


MYSQL showing access denied for use '"@ localhost' when trying to create a new database I just installed a new server and tried to create a new database via phpmyadmin.

When I try to create a new database I get the below error message:

To be precise MYSQL said:

  #1044 - access denied for use '"@ localhost'

Does anyone know what may be causing this error or what steps should I take to further troubleshoot?

1. Try to login Login with any valid mysql user credentials.

If the root user of MySQL doesn't have a password (which is a bad pratice) ensure to set below in config.inc.php file (for eg path: /etc/phpMyAdmin/config.inc.php)

$cfg['Servers'][$i]['AllowNoPassword'] = True;

2.You can also create a new mysql user using below.

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';

GRANT ALL privileges ON my_db.* TO 'new_user'@'localhost'; IDENTIFIED BY 'new_password';

flush privileges;

Try to login again and then create db. Use the default username and password, root and empty password.


Postgres 9.2 - Create View Different Column Names I want to create a view of a table with a column renamed.  I do not want to effect the source table, just the view.

I need to rename the column addr:housename to addr_housename.  We have a 3rd party piece of software that does not like ':' in field names.  We cannot just rename the field due to other software we use.

Can someone please provide an example on how to resolve my issue?

Thank you very much.

addr:housename is an illegal column name to begin with. You could have only created that if you used a quoted identifier (see the manual for details)

To create a view and rename the column, simply provide a column alias in the view's query:

create or replace view v_some_view
as
select ... other columns ...
       "addr:housename" as addr_housename
from some_table;

You can use the following syntax (example):

create view v1(c_new)
as
select 1 as c_old;


Invalid object name after aliasing a table? When I run the following query

select distinct A.movieID, A.avgStars from
(select mID as movieID, avg(stars) as avgStars
from Rating
group by mID) A
where  A.avgStars <= all(select A.avgStars from A);

I get the error "Invalid object name 'A'."

Do you know why I'm getting this?

Thanks!

You can't reference an alias from a subquery at the same scope. You can do this with a CTE, though; perhaps you meant:

;WITH Avgs AS
(
  select mID as movieID, avg(stars) as avgStars
  from Rating group by mID
)
SELECT movieID, avgStars FROM Avgs
WHERE avgStars <= ALL (SELECT AvgStars FROM Avgs);

You shouldn't need DISTINCT there, either.

Try executing with AS
select distinct A.movieID, A.avgStars from
(select mID as movieID, avg(stars) as avgStars
from Rating
group by mID) AS A
where  A.avgStars <= all(select A.avgStars from A);


Deletion of orphaned rows in a violation-free & non-blocking manner: predictive order of execution of predicates in case of double-checked locking I have tables person and toy. There is a foreign key person.favorite_toy_id from person to toy. This foreign key is declared as ON DELETE RESTRICT. I now want to delete all toys which are no longer declared as favorite in a violation-free and non-blocking manner:

I want to delete as much orphaned toys as possible, while avoiding a foreign key violation because we attempt to delete a toy which is still in use.
I do not want to wait for other ongoing transactions, which are possibly introducing a reference to a toy (which requires a key share lock) or simply updating a toy (which requires a (no key) update lock), to finish. Both lock types block our update lock request, required to delete the toy.

The first, naive approach would be:

delete from toy
where       not exists(select 1 from person where person.favorite_toy_id = toy.id)

This will not work in a concurrent environment: after the completion of the not exists predicate, a concurrent transaction could declare the toy in question as favorite. In such case, we end up with a foreign key violation. Also, as said, I prefer this delete to happen in a non-blocking fashion, which is not attempted in this query.

So, my second approach in attempt to avoid this foreign key violation and any blocking is:

delete from toy
where       toy.id in
            (
              select toy.id
              from   toy
              where  not exists(select 1 from person where person.favorite_toy_id = toy.id)
              for update skip locked
            )

However, this doesn't solve the requirement to avoid the foreign key violation, because the lock is taken after the evaluation of the not exists predicate. So there is a small chance that we attempt to delete a toy which is still marked as favorite, resulting in a foreign key violation.

My third attempt to fix this is the following:

delete from toy
where       toy.id in
            (
              select toy.id
              from   toy
              where  not exists(select 1 from person where person.favorite_toy_id = toy.id)
              for update skip locked
            ) and
            not exists(select 1 from person where person.favorite_toy_id = toy.id)

This applies double-checked locking (https://en.wikipedia.org/wiki/Double-checked_locking). This would work if and only if we have the guarantee that the subquery is always evaluated before the additional not exists predicate. As far as I know, there is no such guarantee.

My question is rather educational: can this be solved in a pure SQL query? We can of course implement this in a plpgsql function as shown below, but let's assume we want to solve this in one single plain SQL query.

create function prune_toys() returns void as
$$
declare
  _id int;
begin
  for _id in select toy.id from toy where not exists(...) for update skip locked loop
    delete from toy where toy.id = _id and not exists(...);
  end loop;
end;
$$
language plpgsql;

In all this, I assume the read committed transaction isolation level.

This is more a business process / UI race. It is often solved by deprecating / hiding values for a period before deleting and/or not accepting them as valid anymore.
One approach would be to add a DateDeprecated column to toys. Update it to the current date when you check and nobody has this toy as a favorite. Clear the field if/when the toy is selected as a favorite. In the UI don't show/list deprecated toys at all or don't show toys deprecated for a certain period of time like over 7 days. At some point delete long deprecated toys, say after they have been deprecated for 14 days.

Well, I don't know if this would help but using negative logic like "NOT EXISTS" means that every row has to be examined (locked) to determine if it meets the criteria. If you can find a way to make it positive logic like "EXISTS", it should only evaluate the rows that qualify both speeding up the query and reducing the locks. Just a thought...


How to IFNULL or COALESCE rewrite in a SQL query? I there a way to rewrite a SQL query, so I can avoid IFNULL or COALESCE, because the HXTT DBF JDBC driver (supports SQL92) throws the following error.:

Caused by: java.sql.SQLException: The aggregate function SUM(NETTO) can only be used in select-list, HAVING clause, and ORDER BY clause of SELECT statement.

The following query causes the above error.:

SELECT
(   SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue
    FROM tckopf AS shipManagementInvoice
    WHERE shipManagementInvoice.referenzid = 1
        AND shipManagementInvoice.btyp = 5 )
-
(   SELECT IFNULL (SUM (shipManagementCreditNote.netto), 0) AS shipManagementCreditNoteNetValue
    FROM tckopf AS shipManagementCreditNote
    WHERE shipManagementCreditNote.referenzid = 1
        AND shipManagementCreditNote.btyp = 6 )
 AS shipManagementResult

If I remove IFNULL or COALESCE, there is no error.

There are several ways to rewrite the query and even more because both subqueries use the same base table. Not sure why the error is thrown and who is to blame, the JDBC drivers, the Foxpro or something else, so here are a few alternatives:

(1) using one query for both searches:

SELECT COALESCE(SUM(CASE WHEN t.btyp = 5 THEN t.netto
                         WHEN t.btyp = 6 THEN - t.netto
                                         ELSE 0
                    END), 0) AS shipManagementResult
FROM tckopf AS t
WHERE t.referenzid = 1 ;

(2) pulling the COALESCE() or IFNULL() function call in the external level, either using the original query:

SELECT
  COALESCE( ( SELECT SUM(i.netto)
              FROM tckopf AS i
              WHERE i.referenzid = 1
                AND i.btyp = 5
            ), 0)
- COALESCE( ( SELECT SUM(c.netto)
              FROM tckopf AS c
              WHERE c.referenzid = 1
                AND c.btyp = 6
            ), 0)
  AS shipManagementResult ;

(2b) or the above (1st) query:

SELECT COALESCE(
  ( SELECT SUM(CASE WHEN t.btyp = 5 THEN t.netto
                    WHEN t.btyp = 6 THEN - t.netto
                                    ELSE 0
                END)
    FROM tckopf AS t
    WHERE t.referenzid = 1
  ), 0) AS shipManagementResult ;

try ISNULL(SUM (shipManagementInvoice.netto ), 0)


Selecting rows with condition on two columns together i have table with columns
Number and VAT
How can i select all rows without row which is Number=123 and VAT=15 ?
I tried something like this

SELECT * FROM tablex WHERE <.. zilions of conditions..> AND (Number=123 AND VAT!=15)

But it does not work, i need every rows and with Number 123 too but not in case if VAT is 15.
Any idea ? Thank you

then u can use this one

SELECT * FROM tablex WHERE Number<>123 OR VAT<>15

Try this one,

SELECT * FROM tablex  WHERE Number<>123 AND VAT<>15


Using Mirroring for incremental backups? We have a machine tool application that runs on a SQL server backend.  These tools run all day every day and I am looking for a way to incrementally backup the database without any machine downtime.

I've seen mirroring solutions with two servers, but these solutions would not work for us do to cost and size constraints.  Is it possible to programmatically or through some service to mirror this database to a backup hard drive on the main PC?

Some data loss is acceptable.

None of the SQL Server backups require downtime. Full, differential, log backup are all online.

So just do a normal run-of-the-mill backup scheme (eg. full weekly, diferential daily, log hourly etc) and go with it. Is incremental and is online.

You should look up for sql server replication service. It is designed for this only.


Restricting users to COPY ONLY backups I am looking to deploy log shipping in my production environment. This will mean I will have to manage backup chains which could be disrupted by people taking backups to refresh development environments. If I have a failure and want to use the log backups to restore to a point in time I will also need the backup taken by the developer.

This wouldn't happen if the developers only used COPY ONLY backups. So my question is: Is there a way to restrict users to only be able to perform COPY ONLY backups?

You dont need to have them use COPY_ONLY. Only An intermediate LOG BACKUPS will break the LSN. What you can do is explicitly DENY BACKUP LOG to [user|group] privilege to developers or developer group. Alternatively, just create a ROLE and deny backup log to that role. So all the users in that role will inherit the permissions.

e.g.

USE test_kin
GO
CREATE ROLE [deny_log_backups]
GO
USE [test_kin]
GO
CREATE USER [Kin] FOR LOGIN [Kin]
GO
ALTER USER [Kin] WITH DEFAULT_SCHEMA=[dbo]
GO
use test_kin
GO
DENY BACKUP LOG TO [deny_log_backups]
GO
USE test_kin
GO
EXEC sp_addrolemember N'deny_log_backups', N'kin'
GO

Now test it :

backup database [test_kin]
to disk = 'C:\crap_test\kin_test_full.bak'
with compression, stats =10, init

---- ### success for FULL BACKUP

backup log [test_kin]
to disk = 'C:\crap_test\kin_test_log.log'

 --- $$$ ERROR MESSAGE

Msg 262, Level 14, State 1, Line 3
BACKUP LOG permission denied in database 'test_kin'.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.

What you might be able to do is 1) deny backups (log and/or full) to such users but still 2) allow them to start a job that perform copy-only backup of a specific database (running under an account that is granted backup permission). I have such a solution in place to automate a PROD to PRE-PROD automated restores of databases for investigation purposes; specific users have access to a job (in PRE-PROD) that does a copy-only backup in PROD, moves the file from PROD to PRE-PROD and then restore the moved backup on the PRE-PROD server. Thus making sure such users have no way to break the backup logic on PROD.

(When investigating issues, developers might be tempted to take a full backup to restore it on a test server by actually moving the file from source to test server without knowing that they are breaking the backup logic... so I agree that forcing taking copy-only backups is important.)


Partitioning - when to create new scheme? I have the following partition setup on a table.

I'm wanting to create a new partition that begins on 2019-01-01.

Is the the correct way of doing it, and can it be run now so it's ready to go for 2019?

ALTER DATABASE mydatabase ADD FILEGROUP [Year7FileGroup]
GO
ALTER DATABASE mydatabase
  ADD FILE
  (NAME = N'data_year7',
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\data_year73.ndf',
  SIZE = 5000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Year7FileGroup]
GO
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year7FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20190101');

Firstly, I would encourage you to read Dan Guzman's excellent post on Table Partitioning Best Practices.  It does a great job of explaining why:

  "One needs a good understanding of how the RANGE LEFT/RIGHT
  specification affects partition setup and management."

Now, on to your question.

I'm a little puzzled why you're adding a new partition for '2019-01-01', when your latest partition seems to be for '2016-01-01'.  Since you're trying to add a new partition for '2019-01-01', I would've expected to already see partitions for '2017-01-01' and '2018-01-01'.

In any event, when you're planning on using the SPLIT command, the main thing you want to consider is minimizing data movement between existing partitions.  Pre-defining empty partitions 'before' the minimum value you can have and 'after' the maximum value you can have will allow you to minimize data movement when using the SPLIT.  Referencing Dan's post:

The actions performed by a SPLIT of a RANGE LEFT partition function:

For each table/index using the affected partition scheme(s), move
rows from the existing split partition that are less than or equal to
the new boundary into the newly created partition on the left

The actions performed by a SPLIT of a RANGE RIGHT partition function:

For each table/index using the affected partition scheme(s), move
rows from the existing split partition that are greater than or equal
to the new boundary into the newly create partition on the right

It looks like you might be using RANGE RIGHT partition.  As long as you currently do not have any dates that are greater than or equal to '2019-01-01', there should be no data movement and the SPLIT should execute very quickly.  You definitely want to get the '2019-01-01' partition defined before you get too much data in your table with dates greater than or equal to that date.

The main point I'm trying to stress is to make sure you already have partition boundaries defined well ahead of the data that currently exists on the table.  Many times, you can go ahead and define partitions ranges years in advance of the data, but you should always monitor your partitioned tables to ensure you aren't creeping up on an ending boundary.

Before table partitioning, please think about some point :
1. Is partition column used in where clause.
2. Size of table
3. Partition key good to have integer value or timestamp.
4. If going for date range partition, never user max. Always create some partition in advance for the future range.


restore table from .frm and .ibd file? I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install mysql-server, I believe version 5.1 was installed. After I copy the ddms directory under /var/lib/mysql, some of its tables work fine, these are the tables with an associated set of three files: a .frm file, a .MYD file and a .MYI file.

However, there are two tables with a different set of files: a .frm file and a .ibd file. These two tables didn't show up in the table list in phpMyAdmin. When I look at the error log, it says:

[ERROR] Cannot find or open table ddms/dictionary_item from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.

Please help with restoring these two tables. Thanks.

InnoDB tables cannot be copied the same way that MyISAM tables can.

Just copying the .frm and .ibd files from one location to another is asking for trouble. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.

I wrote two posts in DBA StackExchange about this tablespace id concept

Table compression in InnoDB? (under the heading 'Restoring Databases')
How to Recover an InnoDB table whose files were moved around

Here is excellent link on how to reattach any .ibd file to ibdata1 in the event of mismatched tablespace ids : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should come to the immediate realization that copying .ibd files is just plain crazy.

You could apply the suggestions from the Chris Calendar link, or you could go back to the old installation of mysql, startup up mysql, and then mysqldump the ddms database. Then, import that mysqldump into your new mysql instance. Trust me, this would be far easier.

try running this with mysql utilities

mysqlfrm --diagnostic /Users//data/tbl_user.frm

command/shell promt


Restart the active node services without failover On a 2 node Windows Server Failover cluster configuration, how do I restart the SQL Server services without failing over to the other node?

Use the Failover Cluster Manager application to manually stop the services.

This tells the cluster you are intentionally shutting down the service, so it won't attempt to bring it back up on another node.

The Failover Cluster service will wait until you manually bring the service back online using Failover Cluster Manager, where you can decide which node to use to bring the service up.

You could stop the passive node, drop it out of the cluster, unplug its LAN cable or otherwise disable the passive node before restarting the active services.

The consequences could be .. um .. "interesting", depending on how cleanly this process is handled and how skilled the tech team that configured the cluster were.


Re-order columns values based on values in a table I would like know, how can we re-order the columns in ascending or descending order, while retrieving the data.

Suppose the table contains the data as below with no primary key/unique key constraints:

Col1    Col2    Col3     Col4
----    ----    ----     ----
B        D       C        A
C        Y       M        T
3        5       2        4
5        2       10       7

I want the output in below format: Each row data should be in ascending order, Whether it's Varchar or Numeric. That I guess could be done by taking ascii value of the value set? Is it achievable, if not why?

ColA    ColB    ColC     ColD
----    ----    ----     ----
A        B       C        D
C        M       T        Y
2        3       4        5
2        5       7        10

Please try to provide a solution, which can be generic for any number of columns.

Every row should be re-arranged in ascending order, while we are retrieving the data. I am trying to do something like order by, but not based on column, but row. Is it achievable. It was an interview question for me by Amazon. I tried pivot and unpivot, but haven't been able to achieve this.

More information:

All column datatypes are VARCHAR2.
No specific order/pattern for the values.
The column names does not need to be same, while retrieving the data. Column names can be aliased or can be taken as per your preference. The data retrieved should be in the ascending order by row.
No primary key/unique key constraints.

I would like to understand, if such a solution is achievable, whether inefficient or not. Looking for answers using Oracle 11g or higher.

Solution

select  ColA,ColB,ColC,ColD

from   (select  t.n,t.val

               ,row_number () over
                (
                    partition by n
                    order by     case when regexp_like(val,'^-?\d+$') then to_number(val) end
                                ,val
                ) as rn

        from   (select      rownum as n
                           ,t.*

                from        t
                ) t unpivot (val for col in (COL1,COL2,COL3,COL4)) t

        ) t pivot (min(val) for rn in ('1' as ColA,'2' as ColB,'3' as ColC,'4' as ColD))

order by n
;

For this demo, the numeric values are assumed to be integers.

Walkthrough

Add a row number to each row:

select      rownum as n
           ,t.*

from        t
;

The row numbers will serve as identifiers to be later used when the unpivoted rows are pivoted back. The output produced is like this:

+---+------+------+------+------+
| N | COL1 | COL2 | COL3 | COL4 |
+---+------+------+------+------+
| 1 | B    | D    | C    | A    |
+---+------+------+------+------+
| 2 | C    | Y    | M    | T    |
+---+------+------+------+------+
| 3 | 3    | 5    | 2    | 4    |
+---+------+------+------+------+
| 4 | 5    | 2    | 10   | 7    |
+---+------+------+------+------+

Unpivot the rows.

unpivot (val for col in (COL1,COL2,COL3,COL4)) t

Rank the columns within each row according to their order as sorted first by their numeric value, if applicable, and then alphabetically as strings.

row_number () over
(
    partition by n
    order by     case when regexp_like(val,'^-?\d+$') then to_number(val) end
                ,val
) as rn

For numeric items, the first sorting criterion will evaluate to corresponding numeric values and thus determine the order of the items. For string items, the first term will be null, and so the order will be determined by the second sorting criterion.

The output that you will get for your example after unpivoting and ranking will be this:

+---+-----+----+
| N | VAL | RN |
+---+-----+----+
| 1 | A   | 1  |
+---+-----+----+
| 1 | B   | 2  |
+---+-----+----+
| 1 | C   | 3  |
+---+-----+----+
| 1 | D   | 4  |
+---+-----+----+
| 2 | C   | 1  |
+---+-----+----+
| 2 | M   | 2  |
+---+-----+----+
| 2 | T   | 3  |
+---+-----+----+
| 2 | Y   | 4  |
+---+-----+----+
| 3 | 2   | 1  |
+---+-----+----+
| 3 | 3   | 2  |
+---+-----+----+
| 3 | 4   | 3  |
+---+-----+----+
| 3 | 5   | 4  |
+---+-----+----+
| 4 | 2   | 1  |
+---+-----+----+
| 4 | 5   | 2  |
+---+-----+----+
| 4 | 7   | 3  |
+---+-----+----+
| 4 | 10  | 4  |
+---+-----+----+

Pivot the rows back using the rankings as new column names (optionally aliased following any pattern you like; in the query above – as ColA, ColB etc.) and the row numbers assigned at the beginning as row identifiers.

The result will be the expected output:

+------+------+------+------+
| COLA | COLB | COLC | COLD |
+------+------+------+------+
| A    | B    | C    | D    |
+------+------+------+------+
| C    | M    | T    | Y    |
+------+------+------+------+
| 2    | 3    | 4    | 5    |
+------+------+------+------+
| 2    | 5    | 7    | 10   |
+------+------+------+------+

A live demo is available at Rextester.

This is an oracle solution, made certain assumption, but you can modify to your test.

4 columns as described
max length of data is 20 chars

And here is my code

drop table test1 purge;
Create table test1 (col1 varchar2(20),col2 varchar2(20), col3 varchar2(20), col4 varchar2(20));
insert into test1 values('B','D','C','A');
insert into test1 values('C','Y','M','T');
insert into test1 values('3','5','2','4');
insert into test1 values('5','2','10','7');
commit;
-- results as desited by OP
set sqlformat csv
set echo on
with x as (select rownum r, lpad(col1,20) col1, lpad(col2,20) col2, lpad(col3,20) col3, lpad(col4,20) col4 from test1)
,y as (select * from x unpivot (val for c in (col1, col2, col3, col4)) order by r, val)
,z as (select r, listagg(val,',') within group (order by val) as rlist from y group by r)
select ltrim(regexp_substr(rlist,'[^,]+',1,1)) as col1,ltrim(regexp_substr(rlist,'[^,]+',1,2)) as col2
,ltrim(regexp_substr(rlist,'[^,]+',1,3)) as col3,ltrim(regexp_substr(rlist,'[^,]+',1,4)) as col4
from z;

Output looks like this (i am using sql developer),

"COL1","COL2","COL3","COL4"
"A","B","C","D"
"C","M","T","Y"
"2","3","4","5"
"2","5","7","10"


Adding windows domain group to ORA_DBA not working in Oracle 12c I am running into an odd situation with adding windows domain group to ORA_DBA group to grant sysdba privileges.

I am getting an invalid username/password - logon denied error while connecting to an instance  in Oracle 12c.

But, if I add my individual windows account to ORA_DBA group, I am able to login just fine.

I have tried to research for an entire day but haven't had much luck. Any help would be greatly appreciated.

  Note : The windows domain group is part of the Administrator group on the server.

This is how my sqlnet.ora file looks like:

SQLNET.AUTHENTICATION_SERVICES = (NTS)
SQLNET.INBOUND_CONNECT_TIMEOUT = 120
NAMES.DIRECTORY_PATH =(TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD
NAMES.DEFAULT_ZONE = WORLD
TRACE_LEVEL_CLIENT = OFF
AUTOMATIC_IPC = OFF

Most likely you can't.

As part that oracle documentation is clear, you have to be part of the ORA_DBA group to have the sysdba privilege with your Windows account. Being in a subgroup means that you have all the privileges of the parent group without being part of it As the same logic of oracle privileges and roles.


How to recreate Oracle db from .ora and .dbf? I have a number of files left from a previous Oracle db and I would like to create a new one from the files.

The files include:

init_locdb.ora
16 .dbf files
spfilelocdb.ora
log files
.ctl

Could you tell me is it possible to recreate a new Oracle database from the files so it is identical (almost identical) to the previous one?

The various data files that comprise the physical storage of the Oracle database instance can vary depending on the operating system, endian format, and pretty sure some of the files may have a hostname hiding in them somewhere.  And if those files were obtained from a database instance that was running, there is no way those files could be used to recreate a usable database instance, even on the exact same server as the files came on.

The *.ctl files are updated every 3 seconds or so with timestamps and pointers (oversimplifying but fairly accurate), so sorry, no way.


How to group records in mysql query I have a table (user_access) with the following columns in mysql database

id      int
user    int
access_time    datetime

I am trying to run a query that gives me the number of times users accesses the system (access_time) every 15 minutes between two specified timestamps. I am still developing the query, this is what I have now. Any suggestion will be highly welcomed.

select user, count(user) as users from user_access
where (access_time between '2013-05-28 02:00:00' and '2013-05-28 10:00:00')
group by user

Sample Result will be something like:

Time                User       No of Times
--------------------------------------------------
8:00am - 8:15am     user1          20
8:00am - 8:15am     user2          5
8:15am - 8:30am     user1          15
8:15am - 8:30am     user2          23

This query should do it for you

select
    tm,(tm + interval 15 minute) tm2,user,users
from
(
    select tm,user,COUNT(1) users from
    (
        select user,(access_time - interval
        MOD(unix_timestamp(access_time),900) second) tm
        from user_access where access_time between
        '2013-05-28 02:00:00' and '2013-05-28 10:00:00'
    ) AA
    group by tm,user
) A;

I'll leave the formatting to you

Give it a Try !!!

Try the script below :

SELECT concat(created,'-',(created + interval 15 minute)),
user,count(user)
FROM `usertiming`
where date(created) between '2016-08-24' and '2016-08-24'
group by user


Log backup vs differential backups We're trying to define a backup policy.
We were going to apply following:

Weekly full backup, retained for 30 days.
Nightly differential backups, retained for 7 days

But we're also exploring other option - Transaction Log backups, and Azure backup(which is offering a default of 2 hours)

Am I right in saying that that the differential backup would get us to the last differential backup, and then we can use log backups to get closer to where we want to recover from?

So, backups will be as below:

Sunday 1am- Full backup.
Monday 1am - differential
Tuesday 1am - differential
Wednesday 1am - differential

Every 2 hours since Wednesday Inc - Log Backups.

Lets say Disaster hits at Wednesday mid day!

Recover the differential , which gets us to Wednesday at 1am...
Then we use Log backups to get us to before the disaster struck.

Is that the correct distinction between the types?

We're trying to define a backup policy.

You are starting from the wrong end of things.

Backups are only the tools that you use to support a Recovery Strategy.

Start by working out - in conjunction with your Business partners; you cannot do this alone - how long you can take to get back a "broken" database (Recovery Time Objective) and how much data they're prepared to lose while you go about doing so (Recovery Point Objective).

Once you understand these two constraints, you'll have a better feeling of what you need your backup tools to do.

I used to think Microsoft Azure takes a transaction log backup every 5 minutes. A quick explanation on the backups.

The Full backup backs up the whole database, your differential which I believe is Incremental backs up the changes made since your last full back up while the Transaction log backup is the back up of the changes made  since your last differential/log back up.

You need to note If you are taking transaction log backup every 2 hour, then you probably might loose 2 hours worth of data because you can only restore to the point of the last log backup taken.

So From your scenario, Wednesday Midday, you would  have a full backup on Sunday, Differential from Wednesday 1AM and transaction log backups at 3, 5,7,9 and 11AM to restore from at 12 noon on Wednesday.

In addition to that, you also need to understand your company RTO and RPO to understand how much data the company is fine to loose and how much time is needed to put your databases and applications back on line after a disaster.


What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization? I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful), and am looking for data sets that can defeat my optimization, as this same query will be used in a nearly identical database schema in a different database with different customer use cases.

Specify OPTION(Hash Join, MAXDOP = 8)

MAXDOP effectively tells the query optimizer to look for plans that can exploit parallelism
HASH JOIN is what tells the query optimizer to consider a Bitmap Filter

Non-nullable column filtered on EXISTS clause

When the column is non-nullable, the semi-join (EXISTS clause) guides the optimizer to choose a clustered index scan with a predicate that says something like PROBE(NonNullableColumn, 'IN-ROW')
Combined with the bitmap filter, the PROBE is highly efficient because it uses a bitmap filter.  This is, in effect, equivalent to a perfect Index Seek.  It's like a bookmark lookup on steroids.

However, in thinking through this cleverly constructed query I just made, I'm a little scared I don't know how to break it and when to expect it to stop working and the cost-based optimizer will choose a different plan.

Suppose I have 3000 unique values for NonNullableColumn, each with 30 million rows binned to those unique values.  In my performance tests so far, I passed in a set containing 15 as a unique test value.  This works great and I'm pretty confident this is the optimal plan.  What happens if I pass in a set containing random numbers - will SQL Server stop using the bitmap filter?  What's the "worst case" scenario here?

I've attached a picture of the query plan to re-inforce what I'm trying to test and how I can defeat (or anticipate) Murphy's Law.

In thinking through "what could go wrong", I found this in SQL Server Docs:

  The Repartition Streams operator (or exchange iterator) consumes multiple streams and produces multiple streams of records. The record contents and format are not changed. If the query optimizer uses a bitmap filter, the number of rows in the output stream is reduced.

and:

  SQL Server uses the Bitmap operator to implement bitmap filtering in parallel query plans. Bitmap filtering speeds up query execution by eliminating rows with key values that cannot produce any join records before passing rows through another operator such as the Parallelism operator. A bitmap filter uses a compact representation of a set of values from a table in one part of the operator tree to filter rows from a second table in another part of the tree. By removing unnecessary rows early in the query, subsequent operators have fewer rows to work with, and the overall performance of the query improves. The optimizer determines when a bitmap is selective enough to be useful and in which operators to apply the filter. Bitmap is a physical operator.

  Showplan Logical and Physical Operators Reference

According to Bitmap Filter (Star Join Query Optimization):

  Bitmap filter doesn’t work if:

  It’s not a hash join
  Out of date stats/missing indexes (cardinality/rows)
  Not enough memory
  Not enough threads (BF only works in parallel query – you need more than 1 CPU)

In my case, 1, 2 and 4 are not problems. 3 is evidently not a problem, at least in my example test. The memory requirement essentially means my StarJoinInfo table (Build table) must be smaller enough to fit in main memory; I wish I had a formula that gave me a upper bounds on how many unique values I could create a BitMap for.

I figured out the answer to my question - sort of.

When you specify MAXDOP(8), the optimized bitmap filter optimization will create 8 hash buckets.  The exact spread of integers across those hash buckets is probably Pearson hashing.  I am curious if anyone in the comments knows the exact hashing technique, though.

Quoting: https://sqlserverfast.com/blog/hugo/2018/05/plansplaining-part-5-bitmaps/

  Simple example: Let’s say that only three stores in our database have less than 30 employees. These stores have StoreKey 17, 38, and 82. The first row the Bitmap operator receives is for StoreKey 17; it hashes the number 17 and the result is 29, so bit 29 in the bitmap is set to one. It then reads the next row, for StoreKey 38; hashes the number 38 which results in 15; and sets bit 15 to one. The third row it reads is for StoreKey 82; due to a hash collision this number also hashes to 29 so it will once more set bit 29 to one (effectively not changing anything). There are no more rows so the end result will be a bitmap with bits 15 and 29 set to one and all other bits set to zero.


Performance of using a non-clustered index as primary key Background

I am designing a database for a record management system. It's early stages as I am considering what to use as a primary key: INT or UNIQUEIDENTIFIER.

The reason for considering UNIQUEIDENTIFIER is because there is a good chance we will want a feature that allows records to be moved to other systems (foreign database), and handling all relationships with GUIDs is going to make that so much easier.

Having done some research, there are plenty of debates on the pros and cons of each, the main one I am concerned with is performance. The outcome of my research is that using a UNIQUEIDENTIFIER as the PK will not be a problem as long as it isn't also used as the clustered index for the table.

Ok, so the obvious solution would be to split them into two separate columns:

TableA

ID: INT, Identity, Clustered Index
PK: UNIQUEIDENTIFIER, Non-clustered Index

Question

My concern here is when I am defining relationships, how will this effect join performance? Take for example, this 'child' table:

TableB

ID: INT, Identity, Clustered Index
PK: UNIQUEIDENTIFIER, Non-clustered Index
FK: UNIQUEIDENTIFIER, Non-clustered Index

Should I be concerned with any performance loss, particularly with joining tables, by using this approach?

For example, using a query such as:

SELECT * FROM TableB JOIN TableA ON TableA.PK = TableB.FK

Basically, is this the best design for supporting a GUID based PK, or is this going to lead to bad performance? Are there any significant disadvantages to using the approach I suggested?

Here is what I have seen based on several years of working with GUIDs as clustered, and nonclustered primary keys...there is no one correct answer here; the big thing really comes down to the access methods that you are going to use to get to the data, and the volume of data that you are going to return.

Yes, you will have the fragmentation problem with GUID clustered indexes that will have to be managed, however, if your access method uses that GUID as the query predicate, and you return multiple columns from that table, you may be better off suffering the fragmentation hit in order to improve the performance and lessen the impact of reading that data back out.

Here's a very basic example that show that there is less work required to get the data out when it's the clustered index, and the predicate used to get at the data:

/* NonClustered PK */
CREATE TABLE #T1
    (
      C1 INT NOT NULL ,
      C2 UNIQUEIDENTIFIER NOT NULL ,
      C3 VARCHAR(100) NULL ,
      C4 VARCHAR(20) NULL ,
      CONSTRAINT PK_T1 PRIMARY KEY NONCLUSTERED ( C2 )
    );
CREATE CLUSTERED INDEX T1_C1 ON #T1 (C1);

/* Clustered PK */
CREATE TABLE #T2
    (
      C1 INT NOT NULL ,
      C2 UNIQUEIDENTIFIER NOT NULL ,
      C3 VARCHAR(100) NULL ,
      C4 VARCHAR(20) NULL ,
      CONSTRAINT PK_T2 PRIMARY KEY CLUSTERED ( C2 )
    );

/* Insert 10 rows into each table */
INSERT INTO #T1
        ( C1, C2, C3, C4 )
VALUES  ( 0, '58BBB460-1AFA-4177-BA78-798DA19E0C97', 'some text', 'C4 text')
, ( 1, '17E8163B-BE21-44C7-A7B7-4997265A139D', 'some text', 'C4 text')
, ( 2, '16AACAB8-CD77-4A8D-BE87-9E433CD157EC', 'some text', 'C4 text')
, ( 3, '787D0714-F92A-4963-89E5-3F5DBF518EA7', 'some text', 'C4 text')
, ( 4, '5C720476-D4BE-4047-9F73-DBB1B6B75208', 'some text', 'C4 text')
, ( 5, 'D70F81C5-8AFF-4ABE-BA64-8F5C1A1C6A90', 'some text', 'C4 text')
, ( 6, '1473E5DC-6F3E-4164-988C-E36EE7C695BE', 'some text', 'C4 text')
, ( 7, '648AEA46-4B45-41F9-AA9B-7129062391B4', 'some text', 'C4 text')
, ( 8, '49497ECB-774D-482D-8230-218E97FB2EB4', 'some text', 'C4 text')
, ( 9, 'B90504FA-CEBA-4383-A61F-82F33DAB7A0E', 'some text', 'C4 text');

INSERT INTO #T2
        ( C1, C2, C3, C4 )
SELECT C1 ,
       C2 ,
       C3 ,
       C4 FROM #T1;

/* Index seek on T1 - no lookup as we include the clustering key */
SELECT C1, C2 FROM #T1 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Index seek AND key lookup on T1 because there are columns not contained in the PK */
SELECT C1, C2, C3 FROM #T1 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Clustered index seek on T2 as the predicate is the PK and clustered index */
SELECT C1, C2 FROM #T2 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Stil a clustered index seek on T2 as the predicate is the PK and clustered index */
SELECT C1, C2, C3 FROM #T2 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

The things that you need to take into account, and think about are going to be:

Storage performance
Performance expectations around reads and writes
Access methods (columns returned, and columns used for the predicates)
Space available (bear in mind the clustering key is included in all nonclustered indexes, meaning that you will use more space, and waste more in the cache

Sadly there is no single correct answer for this, you can only get the appropriate expectations, and performing extensive testing to see if those expectations can be met with whatever data model you design, and if not, look to make adjustments to improve them.

  Are there any significant disadvantages to using the approach I suggested?

It is very difficult to say. The only real disadvantage is that, if most of your queries come in via the nonclustered PK, and you are very read heavy you are going to see a great deal of increased IO as you will be performing key lookups on every call.

If your storage can withstand that, then it's great. And on the upside, you don't have the fragmentation problem which can lead to index maintenance hell over time (and can mean that you can't rebuild these things if you are running AGs and don't have maintenance periods).

Your research is wrong.  Index on GUID will fragment if it clustered or non-clustered.  It just takes a little longer to defragment a clustered index.

If you have a PK GUID then what purpose does an int ID serve?


Pull every other row, starting at 0, and concatenate them together I have a temp table that will contain two columns ID and Text. The temp table will contain an unknown amount of rows but there will always be an even amount. What I need to do is select every other row starting at 0 (so 0, 2, 4, 6, etc.) and then concatenate the values of Text together. I found https://stackoverflow.com/questions/4799816/return-row-of-every-nth-record which looks to be how to get every other row but I am not sure how to do the concatenation.

Example Table:

Row ID Text
0   0  This
1   1  Do
2   2  is
3   3  not
4   4  the
5   5  use
6   6  text
7   7  text

The result I would want from that table is This is the text.

DECLARE @x TABLE(ID INT, [Text] NVARCHAR(32));

INSERT @x VALUES (0,N'This'), (1,N'Do'),  (2,N'is'),   (3,N'not'),
                 (4,N'the'),  (5,N'use'), (6,N'text'), (7,N'text');

SELECT LTRIM((SELECT ' ' + [Text] FROM
  (SELECT rn = ROW_NUMBER() OVER (ORDER BY ID), [Text] FROM @x) AS x(rn, [Text])
  WHERE rn % 2 = 1 ORDER BY rn FOR XML PATH, TYPE).value('.[1]','nvarchar(max)'));

Result:

This is the text

If you know your strings can't contain characters like <, > and & then it should be slightly more efficient to leave out the TYPE).value bit:

SELECT LTRIM((SELECT ' ' + [Text] FROM
  (SELECT rn = ROW_NUMBER() OVER (ORDER BY ID), [Text] FROM @x) AS x(rn, [Text])
  WHERE rn % 2 = 1 ORDER BY rn FOR XML PATH('')));

Try this

declare @tTable TABLE(ID INT identity, tText nvarchar(100));
declare @tString nvarchar(4000);

insert into @tTable VALUES
('This'),
('Do'),
('is'),
('not'),
('the'),
('use'),
('text'),
('text')

select @tString = COALESCE(@tString+' ','') + tText
from @tTable
where id%2 = 1

select @tString


Questions about handling NULLs and empty strings in nvarchar and numeric fields I understand that questions similar to these pop up often around here. I have searched before posting these but I didn't find any QA threads that completely answer my questions. In a table, I basically have to treat NULLs, empty strings and (pure) whitespace as 'blanks' and count the number of non-blank cells. The table contains a mix of numeric, bit and nvarchar columns.

Q1 In the table TABLE1, I have a column COLUMN1 nvarchar(32) with the following data distribution:

Value    RowCount
N/A      80             -- string 'N/A'
NULL     20             -- actual nulls

Why does the last of the following queries return unexpected results?

SELECT SUM(CASE WHEN COLUMN1 IS NOT NULL THEN 1 ELSE 0 END)
FROM TABLE1             -- returns 80, as expected

SELECT SUM(CASE WHEN COLUMN1 NOT IN (NULL, '') THEN 1 ELSE 0 END)
FROM TABLE1             -- returns 80, as expected

SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END)
FROM TABLE1             -- returns 80, but I expected 100.

Q2 I have another column COLUMN2 numeric(18, 0) filled with values with no NULLs or empty strings (but it could contain either/both). But the second of the queries below fails due to a reason I don't understand.

SELECT SUM(CASE WHEN COLUMN2 NOT IN ('', NULL) THEN 1 ELSE 0 END)
FROM TABLE1             -- returns full rowcount (100), as expected.

SELECT SUM(CASE WHEN COLUMN2 NOT IN (NULL, '') THEN 1 ELSE 0 END)
FROM TABLE1             --query FAILS! (Msg 8114, Level 16, State 5, Line 1. Error converting data type varchar to numeric.)

Q3 What's an all-encompassing expression for my requirement of checking a column for NULLs, emptystrings and pure whitespace regardless of a column's datatype? If my columnname comes from a (cursorized) variable @column, what should I enclose it in and compare it to? I tried working with cast to nvarchar and using LTRIM/RTRIM, but frankly I am a bit lost at this point.

I am using SQL Server 2008. Thank you for reading this and for your assistance.

Q1

SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END)
-- returns 80, but I expected 100.

Why would you expect 100 rows? You have 20 rows where the column is NULL. Your expression evaluates to:

SELECT SUM(CASE WHEN COLUMN1 <> '' THEN 1 ELSE 0 END)

Since NULL means unknown, an equality or inequality comparison will yield unknown (and in this case false or, more pedantically accurate, not true). When column1 is null, SQL Server can't tell you if it is equal to 'foo' or not equal to 'foo'.

Q2

The error is due to implicit conversion and the order of the expressions. In the first query, you are comparing to a string first, then to NULL. The NULL becomes a string, because it was referenced later, and so the underlying column (as you should see in the execution plan) was implicitly converted to a string. In the second query, you are comparing to NULL first, therefore to determine the data type of the expression, it must go check the table. The table contains a numeric, so the first argument is the same as CONVERT(NUMERIC(18,2), NULL), and then it tries to convert the empty string to numeric. Try this to see why it doesn't work:

SELECT CONVERT(DECIMAL(10,2), '');

Q3

In order to use the same expression on all data types, you must be able to convert them all to the same data type. So let's say I have a table:

CREATE TABLE #foo(a VARCHAR(30), b NUMERIC(18,2));

INSERT #foo SELECT '1', NULL;
INSERT #foo SELECT NULL, 4.5;
INSERT #foo SELECT '', 5.5;

Now compare the results of these four expressions:

SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') <> '';
SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') = '';

SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') <> '';
SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') = '';

The answers already posted are correct, but here's the practical "take-away":  For my purposes, "A null entry is the same thing as a blank entry".  If that's true in your world as well then do this:  For your evaluating statements, get in the habit of using isnull([fieldname],'') for varchars and isnull([fieldname],0) for numeric fields.  That way your null fields are always evaluated consistently, and you'll ALMOST never have unexpected results.


How to access a SQL Server database from other computer connected to the same workgroup? I have created a C# application which uses a SQL Server database. I have other computers connected to me and to each other in a workgroup. I have shared my C# application with others. When they open the application they get the error

  A network related or instance-specific error occured while establishing a connection to SQL Server. the server was not found or was not accessible

But the application is working fine on my PC. The connection string I am using is

Data Source=ASHISHPC1\SQLEXPRESS;Initial Catalog=ACW;User ID=ash159;Password=ashish159

which is stored in a .config file.

The application is working fine on my PC. What must I do? I have enabled the TCP/IP in the server but the same error persists. Some change in connection string or something else?

There are two levels of security that need to be changed in order to allow remote access.

SQL Server configuration. By default in Express, Developer, and Enteprise Evaluation editions, connecting by the TCP/IP protocol is disabled. Enable this using SQL Server Configuration Manager.
Windows Firewall. While disabling the firewall entirely will work for this component, doing so is not a security best-practice (nor is it required). (Note: in this section, I assume a default configuration. There are many settings that can be changed which affect these steps slightly.)

There are two cases depending on the type of SQL Server instance you're connecting to:

Default instance (connect by computer name only). Add an allow incoming rule either on TCP port 1433 or the database engine service.
Named instance (connect by computer name + instance name). Add an allow incoming rule on UDP port 1434 to access to the SQL Browser service. Add an allow incoming rule on the database engine service.

This procedure is fully documented here, or you can watch me explain and then walk through the configuration for a named instance here.

I turned the windows firewall off for Home Network and it worked fine.. Thank you all.. :)


Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL I had to write a simple query where I go looking for people's name that start with a B or a D :

SELECT s.name
FROM spelers s
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1

I was wondering if there is a way to rewrite this to become more performant. So I can avoid or and / or like?

Your query is pretty much the optimum. Syntax won't get much shorter, query won't get much faster:
SELECT name
FROM   spelers
WHERE  name LIKE 'B%' OR name LIKE 'D%'
ORDER  BY 1;

If you really want to shorten the syntax, use a regular expression with branches:
...
WHERE  name ~ '^(B|D).*'

Or slightly faster, with a character class:
...
WHERE  name ~ '^[BD].*'

A quick test without index yields faster results than for SIMILAR TO in either case for me.
With an appropriate B-Tree index in place, LIKE wins this race by orders of magnitude.
Read the basics about pattern matching in the manual.
Index for superior performance
If you are concerned with performance, create an index like this for bigger tables:
CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

Makes this kind of query faster by orders of magnitude. Special considerations apply for locale-specific sort order. Read more about operator classes in the manual. If you are using the standard "C" locale (most people don't), a plain index (with default operator class) will do.
Such an index is only good for left-anchored patterns (matching from the start of the string).
SIMILAR TO or regular expressions with basic left-anchored expressions can use this index, too. But not with branches (B|D) or character classes [BD] (at least in my tests on PostgreSQL 9.0).
Trigram matches or text search use special GIN or GiST indexes.
Overview of pattern matching operators

LIKE (~~) is simple and fast but limited in its capabilities.
ILIKE (~~*) the case insensitive variant.
pg_trgm extends index support for both.

~ (regular expression match) is powerful but more complex and may be slow for anything more than basic expressions.

SIMILAR TO is just pointless. A peculiar halfbreed of LIKE and regular expressions. I never use it. See below.

% is the "similarity" operator, provided by the additional module pg_trgm. See below.

@@ is the text search operator. See below.

pg_trgm - trigram matching
Beginning with PostgreSQL 9.1 you can facilitate the extension pg_trgm to provide index support for any LIKE / ILIKE pattern (and simple regexp patterns with ~) using a GIN or GiST index.
Details, example and links:

How is LIKE implemented?

pg_trgm also provides these operators:

% - the "similarity" operator
<% (commutator: %>) - the "word_similarity" operator in Postgres 9.6 or later
<<% (commutator: %>>) - the "strict_word_similarity" operator in Postgres 11 or later

Text search
Is a special type of pattern matching with separate infrastructure and index types. It uses dictionaries and stemming and is a great tool to find words in documents, especially for natural languages.
Prefix matching is also supported:

Get partial match from GIN indexed TSVECTOR column

As well as phrase search since Postgres 9.6:

How to search hyphenated words in PostgreSQL full text search?

Consider the introduction in the manual and the overview of operators and functions.
Additional tools for fuzzy string matching
The additional module fuzzystrmatch offers some more options, but performance is generally inferior to all of the above.
In particular, various implementations of the levenshtein() function may be instrumental.
Why are regular expressions (~) always faster than SIMILAR TO?
The answer is simple. SIMILAR TO expressions are rewritten into regular expressions internally. So, for every SIMILAR TO expression, there is at least one faster regular expression (that saves the overhead of rewriting the expression). There is no performance gain in using SIMILAR TO ever.
And simple expressions that can be done with LIKE (~~) are faster with LIKE anyway.
SIMILAR TO is only supported in PostgreSQL because it ended up in early drafts of the SQL standard. They still haven't gotten rid of it. But there are plans to remove it and include regexp matches instead - or so I heard.
EXPLAIN ANALYZE reveals it. Just try with any table yourself!
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO 'B%';

Reveals:
...
Seq Scan on spelers  (cost= ...
  Filter: (name ~ '^(?:B.*)$'::text)

SIMILAR TO has been rewritten with a regular expression (~).
Ultimate performance for this particular case
But EXPLAIN ANALYZE reveals more. Try, with the afore-mentioned index in place:
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ '^B.*;

Reveals:
...
 ->  Bitmap Heap Scan on spelers  (cost= ...
       Filter: (name ~ '^B.*'::text)
        ->  Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ...
              Index Cond: ((prod ~>=~ 'B'::text) AND (prod ~<~ 'C'::text))

Internally, with an index that is not locale-aware (text_pattern_ops or using locale C) simple left-anchored expressions are rewritten with these text pattern operators: ~>=~, ~<=~, ~>~, ~<~. This is the case for ~, ~~ or SIMILAR TO alike.
The same is true for indexes on varchar types with varchar_pattern_ops or char with bpchar_pattern_ops.
So, applied to the original question, this is the fastest possible way:
SELECT name
FROM   spelers
WHERE  name ~>=~ 'B' AND name ~<~ 'C'
    OR name ~>=~ 'D' AND name ~<~ 'E'
ORDER  BY 1;

Of course, if you should happen to search for adjacent initials, you can simplify further:
WHERE  name ~>=~ 'B' AND name ~<~ 'D'   -- strings starting with B or C

The gain over plain use of ~ or ~~ is tiny. If performance isn't your paramount requirement, you should just stick with the standard operators - arriving at what you already have in the question.

Probably faster to do a single character comparison:

SUBSTR(s.name,1,1)='B' OR SUBSTR(s.name,1,1)='D'


Finding Item's distribution over customer routes Please help me to find a solution.

I have two tables [CustomerMaster and SalesData] as shown below.

CustomerMaster

| CODE |       NAME | ROUTE | DIVISION |
|------|------------|-------|----------|
| 1001 |  Customer1 |    R1 |     Div1 |
| 1002 |  Customer2 |    R1 |     Div1 |
| 1003 |  Customer3 |    R1 |     Div1 |
| 1004 |  Customer4 |    R2 |     Div1 |
| 1005 |  Customer5 |    R2 |     Div1 |
| 1006 |  Customer6 |    R2 |     Div1 |
| 1007 |  Customer7 |    R3 |     Div2 |
| 1008 |  Customer8 |    R3 |     Div2 |
| 1009 |  Customer9 |    R3 |     Div2 |
| 1010 | Customer10 |    R4 |     Div2 |

SalesData

| CUSTOMERCODE |               DATE | ITEM | BRAND | QUANTITY |
|--------------|--------------------|------|-------|----------|
|         1001 |  January, 01 2014  |  IT1 |   BR1 |        2 |
|         1001 | February, 01 2014  |  IT2 |   BR2 |        5 |
|         1003 |  January, 01 2014  |  IT1 |   BR1 |       20 |
|         1003 | February, 01 2014  |  IT3 |   BR1 |        6 |
|         1005 |  January, 01 2014  |  IT1 |   BR1 |        5 |
|         1005 | February, 01 2014  |  IT2 |   BR2 |       11 |
|         1007 |  January, 01 2014  |  IT1 |   BR1 |       15 |
|         1007 | February, 01 2014  |  IT2 |   BR2 |        7 |
|         1007 |    March, 01 2014  |  IT3 |   BR1 |        9 |
|         1007 |    April, 01 2014  |  IT4 |   BR2 |       18 |

I need to answer below questions

To get the Brand distribution over Route and Division

ex:BR1 reached 2 customers out of 3 in R1
ex:BR1 reached 3 customers out of 6 in Div1

To get the Item distribution over Route and Division

ex:IT2 reached 1 customers out of 3 in R1
ex:IT2 reached 2 customers out of 6 in Div1

I am looking for something like this

| Item  |  Brand|Route  | Division  | TotalCustomersCount   | BuyingCustomersCount|
|-------|-------|-------|-----------|-----------------------|---------------------|
| IT2   |BR2    |R1     | Div1      |                   3   |                   1 |
| IT2   |BR2    |R4     | Div1      |                   1   |                   0 |
| IT1   |BR2    |R1     | Div1      |                   3   |                   2 |
|       |       |       |           |                       |                     |

I am trying some queries like

select s.item,s.brand,c.division,c.route,
TotalCustomersCount=(select COUNT(distinct  x.CODE) from  CustomerMaster x where x.Route=c.route),
BuyingCustomersCount=(select COUNT(distinct y.CUSTOMERCODE) from  SalesData y where y.CustomerCode=c.Code and y.item=s.Item )
from CustomerMaster c join SalesData s on c.code=s.customercode

It will be a great if someone can help me.
My logic is wrong?

select sd.item,
       sd.brand,
       cm.Route,
       cm.Route,
       count(*) as TotalCustomersCount
from SalesData sd with (nolock)
  inner join CustomerMaster cm
    on sd.CUSTOMERCODE=cm.CODE
group by cm.ROUTE

I am not sure what BuyingCustomersCount is.


How to solve ORA-011033: ORACLE initialization or shutdown in progress I have followed link  to solve the problem.

When it is giving ORA-00600: internal error code, arguments: [kcratr1_lostwrt] then I am using:

SQL> startup mount

the above line should return ORACLE Instance started
but it is showing "cannot start already-running Oracle - shut it down first".

ORA-00600 is Oracle's generic message for un-handled internal exceptions i.e. bugs.  Usually they require us to raise an SR with Oracle Support.    This can be a problem for sites who are using Oracle without a support contract.  In that situation we are thrown on the resources of the internet.

The problem you are experiencing is due to a corrupted block.  The top result in Google offers a simple workaround: find out more.

SQL> shutdown immediate;
SQL> startup mount;
SQL> recover database;
SQL> alter database open;

(Of course, this is only a solution if you are running in archivelog mode, and so can recover the lost block).

Instead of using

SQL> alter database open;

command use  the follwing command

SQL> alter database open resetlogs;


How to see the list of the Postgres databases via the linux terminal? I have connected a database using the command
sudo -i -u postgres

And after I have tried \l  and \d too, to view the list of all available databases, it is not working. Instead, I get the following output:
d:command not found and
l:command not found error is showing

What to do, how to get the list of the postgresql databases?

If you want to execute the command from the shell, you can use the -c option in psql:

psql -c '\l'

Example:

peter@blackrain:~$ sudo su  postgres
postgres@blackrain:/home/peter$ psql -c '\l'
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 blackrain  | peter    | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 redpepper  | peter    | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 x          | peter    | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)

postgres@blackrain:/home/peter$

For getting the database names only:
echo "SELECT datname FROM pg_database;" | psql | tail -n +3 | head -n -2 | egrep -v 'template0|template1|postgres'


Database mirroring, "WITH NORECOVERY" Recently I have started to implement database mirroring and faced with some trouble. According to Books online, full database backup and T-Log backup must be restored in the mirror instance in WITH NORECOVERY mode:

  The mirror database must be in the RESTORING state for mirroring to
  work. When preparing a mirror database, you must use RESTORE WITH
  NORECOVERY for every restore operation. Minimally, you will need to
  restore WITH NORECOVERY a full backup of the principal database,
  followed by all subsequent log backups.

But when I start mirroring, I receive following exception:

  "Database "MyDB" cannot be opened. It is on the middle of
  restore(Microsoft SQL Server, Error 927)

Can anyone explain, whether I misunderstood something ?

Create endpoints on all servers:

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
                   ENCRYPTION = REQUIRED ALGORITHM RC4)

For a witness server, change ROLE = PARTNER to ROLE = WITNESS in the witness's endpoint.

Run the first SET PARTNER command on the mirrored server:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'

Notice that it is pointing to the principal server in the command, but you are running it on the mirror.

Then run the next two commands on the principal server, pointing the SET PARTNER at the mirror:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY OFF --OFF=async, FULL=sync

If you are setting up a witness for automatic failovers, use SET SAFETY FULL above instead of SET SAFETY OFF and then run this command on the principal:

ALTER DATABASE DatabaseName SET WITNESS = N'TCP://WitnessServer:PortNumber'

Here I go with this amazing workaround...
- I have the same situation , the same errors...
- I was working with SQL2016, as you know, it doesn't has Management Studio
- Wiht the downloaded Management Studio of SQL2017 I was working
- So moving and make the configuration of mirror with Management Studio of SQL2012 work with out problem !!!


Store n:m data with most n:1 tuples I have these tables:

create table data (
    id int identity not null,
    ref int not null
);

create table reference (
    refid int not null
);

with ~2 million rows in data and ~200k rows in reference. I add ~5k rows to data each day, resulting in 500-5k additional rows in reference per day.

In most of the cases I need to save only one reference for one data column (multiple data entries can have the same reference) but in some cases (currently about 0.1%) I need to save more than one reference.

I probably could use a third table like:

create table data_reference (
     dataid int not null,
     refid int not null
);

to create a n:m mapping but for most of the values it will not have any gain here, it will just create another join...

I could also create a second column in each data row, because in most of the cases I have only one additional reference (but in theory there could be more) and save null if no other reference is there - which seems to me quite bad because there could be tuples with 3 or more references.

Is there an efficient and easy to maintain way to save such data?

Using an intermediate table is the proper way to do this.  With proper indexes and good maintenance it won't hurt performance much at all.

Avoid the multi-column solution (refid1, refid2 etc.) - that's repeating groups and not properly normalised.  Plus your queries will degenerate into a dog's breakfast of OR clauses and CASE statements, which will hurt performance.

You may get some benefit from subtyping your data rows so most use your current arrangement but those with multiple reference use a different one.  Your queries will UNION ALL the two sets to get the total.  I really doubt the overhead from this will compensate for the mostly-redundant join to data_reference.

The subtyped schema would look like this:

create table data (
    id int identity not null,
    ref int not null,       -- set to a standard value for rows with >1 reference.
    is_single_reference bit not null
);

create table reference (
    refid int not null
);

create table data_reference (
     dataid int not null,
     refid int not null
);

The query would be something like:

select
    <whatever>
from data as d
inner join reference as r
    on d.ref = r.refid
where d.is_single_reference = 1

union all

select
    <whatever>
from data as d
inner join data_reference as dr
    on dr.dataid= d.id
inner join reference as r
    on r.refid = dr.refid
where d.is_single_reference = 0;

This is fairly ugly.  You'd be lucky to avoid two scans of data.  Ideally you'd want filtered indexes but IIRC these aren't availble until SQL Server 2008.  The applicaiton would have to know how many reference rows there were per data, set flags accordingly and maintain those if the number changed.  Just use the n:m approach.

One way to do it - have a varchar column with a delimited list of additional references if they exist (123,458,658,587 etc). Then create a UDF to split that data if it exists, which you can use in SELECT queries. Should be easy to design the UDF to extract the data. Since it is rare it shouldn't hurt resources much. A varchar column should also mean minimum extra space requirements.


In PostgreSQL URL I can't use a password containing special characters I have a postgresql database in Azure

The passwd string has special characters like these:

)mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P?APOU

psql --dbname=postgresql://db_user@mydemoserver:)mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P?APOU@mydemoserver.postgres.database.azure.com:5432/mydb

when executing the psql command, it shows me in following error:

psql: invalid percent-encoded token: "iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P"

I already tried putting the passwd string in quotation marks but it does not work

What other options do I have?
I can not change the passwd

I'm interested in using the psql client, because I have to do very large backups

You have to use URI escapes for all problematic characters.

For this user:

CREATE ROLE "weird@name" PASSWORD '\/ @&?' LOGIN;

You can login like this:

psql postgresql://weird%40name:%5C%2F%20%40%26%3F@localhost:5432/test

The documentation has it:

  Percent-encoding may be used to include symbols with special meaning in any of the URI parts, e.g. replace = with %3D.

URI encode the password using JavaScript's encodeURIComponent() or analogous function in other languages.


MSSQL: Auto-numbering rows in commit order General setup

I have an event table with an ID field and some data. Multiple producer threads should be inserting events to this table at the same time. Each batch of inserts is done in a separate transaction, as it should be atomic with respect to other data.

I also have some consumers reading from this table periodically. Each consumer has a pointer to the last ID processed within this event table.

Issue faced

After some experimenting with identity column it seems IDs are assigned to new records at insert time, not commit time. This means that I situation like this might arise:

Producer A inserts event EA into table (ID assigned: 1)
Producer B inserts event EB into table (ID assigned: 2)
Producer B commits
Consumer reads latest event EB, and sets "last processed" pointer to 2
Producer A commits
Consumer will never process event EA, because it has a lower ID, assuming already processed

Question

How do I ensure, that events (DB rows) are numbered in increasing commit order? I don't mind gaps in
the series if transactions are rolled back etc., but there should never be committed an item with a lower ID than what has already been committed (and therefore maybe already processed), and of course they need to be unique.
I would like not to lock the entire table, since many transactions run in parallel and some might run for a while.

Can I use some kind of insert trigger, stored procedure or similar to ensure this, or is it better to do some kind of post-processing in application code?

How do I ensure, that events (DB rows) are numbered in increasing commit order?

You don't.  At least not with eliminating concurrent writers.  You simply don't use a high-water mark for your readers.  Delete the rows after reading, or update them to show that they were processed.

See eg Using tables as Queues

Try sequence instead of identity . In a loop ,Before committing EB read table with nolock and check , if you get a lower number than EB, wait for delay and loop again


Inner Join Table (Mapping table) this is first time i am try mapping table in mysql  i write this procedure  but not work ? whats wrong ?

        DELIMITER $$

       CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_GetInformation`(
                    IN cos_ID INT
                    )
      BEGIN
      SELECT
                     students.ID as 'StudentID',
                     course.ID as 'CourseID',
                     students.name,
                     course.course_name,
                     course.cost_fee,
                     students_Course.net_cost
            FROM
                     students INNER JOIN  students_course on students.ID     = students_course.students_ID
                     inner join course on course_ID = students_course.course_ID
            where
                     course.ID = cos_ID;

END

SELECT
                 students.ID as 'StudentID',
                 course.ID as 'CourseID',
                 students.name,
                 course.course_name,
                 course.cost_fee,
                 students_Course.net_cost
        FROM
                 students_course INNER JOIN  students on students.ID     = students_course.students_ID
                 inner join course on course_ID = students_course.course_ID
        where
                 course.ID = cos_ID;

select a.emp_code,employee_name.full_name, a.date_of_joining,a.date_of_posting,
a.personal_contact,facility_master.description,departments_master.description,
designation_master.description,emp_type_master.description,category_master.description,
gender_master.description,district_master.description
from hshrc_data.mas_employees a
inner join facility_master  on a.facility_name=facility_master.id
inner join departments_master  on a.department_code=departments_master.id
inner join designation_master  on a.designation_code=designation_master.id
inner join emp_type_master  on a.emp_type=emp_type_master.id
inner join category_master  on a.emp_category_code=category_master.id
inner join gender_master  on a.gender_code=gender_master.id
inner join district_master  on a.post_district=district_master.id
inner join employee_name on a.emp_code=employee_name.emp_code;


Cannot log in to SQL Server 2008 R2 I have problem with my SQL Server 2008 R2 instance. I want to log in using windows authentication and server name "local", but error 2 is returned. I've tried to login with a server name of "IP address of my server" but again it failed. Does anybody know what should I do?

Error message:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

Make sure you TCP/IP is enabled under SQL SErver Network Configuration

If it is disabled than please enable it and restart services for that instance.

you will be able to log-in.