Trigger to generate protocol number I need help with creating a trigger that will generate a protocol number. The following sequence must be followed:

323926YYYYMMDDXXXXXX

323926 = fixed number
YYYY   = year
MM     = month
DD     = day
XXXXXX = Number we must automatically generate from 1 that will go to 999999

My difficulty is that this incremental number should return to 000001 after the course of the day, summarizing: Every day the first protocol generated must start with the number 000001

My Code :

CREATE OR REPLACE FUNCTION crm.novo_ticket()
RETURNS trigger AS
$BODY$
BEGIN

NEW.protocolo =  '323926' || TO_CHAR(NEW.data_cadastro,'YYYYMMDD') || '000001';

RETURN NEW;

END $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Sorted out

SELECT
    lpad(CAST((SELECT COUNT(1) + 1 INTO quant
FROM
    crm.ticket a
WHERE
    a.data_cadastro::DATE = CURRENT_TIMESTAMP(0)::DATE;)as VARCHAR),6,'0');

I wouldn't do this, I would store them all separate and generate them with a function.

CREATE TABLE foo (
  protodate date    DEFAULT CURRENT_DATE,
  id        serial,
  whatever  text
);
INSERT INTO foo (protodate, whatever)
VALUES
  ('2000-01-01', 'foo'),
  ('2000-01-01', 'bar'),
  ('2000-01-02', 'foo'),
  ('2000-01-03', 'baz');

CREATE OR REPLACE FUNCTION to_protoid( date, int )
RETURNS char(20)
AS $$
  SELECT '323926' || to_char($1, 'YYYYMMDD') || to_char($2, 'FM099999');
$$
LANGUAGE 'sql'
IMMUTABLE;

Now to query it,

SELECT to_protoid(
  protodate,
  row_number() OVER (PARTITION BY protodate ORDER BY id)::int
) AS protoid, *
FROM foo;
       protoid        | protodate  | id | whatever
----------------------+------------+----+----------
 32392620000101000001 | 2000-01-01 |  1 | foo
 32392620000101000002 | 2000-01-01 |  2 | bar
 32392620000102000001 | 2000-01-02 |  3 | foo
 32392620000103000001 | 2000-01-03 |  4 | baz
(4 rows)

If that's not good enough you can always create a view or materialized view out of it...

CREATE VIEW myfoo
AS
  SELECT to_protoid(
    protodate,
    row_number() OVER (PARTITION BY protodate ORDER BY id)::int
  ) AS protoid, *
  FROM foo;

Why would I do this? More efficient storage. You store the inputs to the function rather than output which I find cleaner. You have easy use of indexes on date or int.

Lastly, if you want to, you can have a trigger insert into another table the id and the result of the call to to_protoid you can do that, though I wouldn't. I wouldn't use the protoid for anything internal. I would use your regular id and generate it as demonstrated.


MySQL Solution to Sync 3 servers data I have 3 servers which there is a load-balancer in front of them and sends the requests to these servers randomly.

The script is the same in all of these 3servers, but I need to sync the DB in all of them, or replicate the DB or whatever you suggest, I don't know it's name exactly! There are a lot of things I've heard:

MySQL Replication (master-master)
MySQL Replication (master-slave)
MySQL Cluster
MySQL Replication (Group replication)
Percona Server
MySQL Galera
Any other scripts like this PHP Package or this

what is the difference of these? which one is the best for my situation?

These are my needs and considerations:

I have 3 Geographically distributed servers which I want to sync them.
I want to use InnoDB (Not preferred to use NDB Cluster engine of MySQL Cluster), also I have some in-memory tables, but not that much important to use memory tables, I can switch to InnoDB.
I want something hidden to the application layer, I want to INSERT/SELECT and etc... like before, without changing any line of my codes.
I want something master/master replication or sync or whatever for when each of my master servers goes down no failure happens, So it must have automatic node failure detection in it.

Would you suggest me a solution, for example percona server or anything else?
I think if I choose percona, I must go with Percona XtraDB Cluster, not normal Percona Server!

You are asking some good questions here but you are at an important crossroads and undoing an architectural mistake after implementing it is extremely difficult and costly.

The first question you should ask yourself is do you really need a multi-master topology? Most organizations and applications function well with MySQL asynchronous replication with a failover script in the event failover is necessary.

If you decide a multi-master topology is the right choice, you need to confirm that your application and requirements are a fit. I see a red flag already with this.

  I have 3 Geographically distributed servers which I want to sync them.

WAN replication with multi-master solutions like Group Replication (MySQL) or Galera (which is used by Percona XtraDB Cluster and MariaDB Cluster) have similar limitations. There are workarounds if your application is tolerant of additional latency and your application also does not process large, long-running write transactions. Which brings us to our next point...

  I want something hidden to the application layer, I want to INSERT/SELECT and etc... like before, without changing any line of my codes.

This is not always possible with multi-master database solutions. The way your application interacts with the database is critical to the success of the database layer. If your application code is written poorly, the database will behave poorly.

I highly suggest you reach out to an expert to help you design an ideal solution for your situation.

FULL DISCLOSURE: I work for Percona, but other vendors could assist here as well.

Good luck!


postgresql materialized views vs versioning I came across a pickle when dealing with question from one of our dev concerning postgresql and materialized views:

can I refresh materialized view with my application user?

well according to docs (https://www.postgresql.org/docs/11/sql-creatematerializedview.html), it is simple enough, BUT you must be owner of said view.

We have isolated acls for roles we run migrations under (ddl) and roles that are "application" and thus can operate with limited privileges. So I can't create the view during migration, cause application need to update it once a while and I can't create it in application runtime, because application role doesn't have create privilege.

I think someone must have came across similar issue, but search engines are not helping at all. last hope is making application user a little bit more privileged (with create on schema granted).

You can create a function with security definer owned by the MVIEW owner that does the refresh and then grant execute privilege on the function (or procedure) to your app user.


Enforce unique constraint on 1 of n values Let's say I have a table like this.

create table foo (
    id serial primary key,
    kind smallint not null check (kind >= 0 and kind < 3)
);

With that check constraint, Postgres will only allow kind to be an int between 0 and 2, inclusive. So something like this is valid.

id | kind
---------
1  | 0
2  | 0
3  | 1
4  | 1
5  | 2
6  | 2

However is there a way to say, "at most 1 row can have the value 2"?

time=1
id | kind
---------
1  | 0
2  | 0
3  | 1
4  | 1
5  | 2

time=2
id | kind
---------
1  | 0
2  | 2
3  | 1
4  | 0
5  | 0

time=3
id | kind
---------
1  | 0
2  | 1
3  | 1
4  | 0
5  | 0

Different rows should be allowed to have kind=2 at different points in time, but at most 1 row can have kind=2.

Note, the other values should be unrestricted. You can have as many kind=0s and kind=1s as you like.

You can create unique partial index:

create unique index on foo (kind) where kind=2;


What is a "heterogeneous query"? I got the following error message regarding a SQL query I'm running in a program. SQL Server 2005 T-SQL.

  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. (severity 16)

Fixing it is easy, set ANSI_NULLS and ANSI_WARNINGS ON, but I wanted to know what a heterogeneous query is. A Google search brings up dozens of results telling me to set ANSI_NULLS and ANSI_WARNINGS, nothing explaining what the term means. The query is:

UPDATE SRV.DB.DBO.TABLE SET Column=
            (SELECT Column
            FROM SRV1.DB.DBO.TABLE)

I'm thinking this is due to connecting to multiple database engines in one query, as I've never gotten this error otherwise.
Does "Heterogeneous" just refer to querying two different database engines in this context?

Does "Heterogeneous" just refer to querying two different database engines in this context?

Basically, yes. A "heterogeneous" query is executed on a "heterogeneous linked server". All queries to this linked server will be heterogeneous.

And "heterogenous" is a subset of distributed queries

  Distributed data stored in multiple instances of SQL Server.
  Heterogeneous data stored in various relational and nonrelational data sources accessed by using an OLE DB provider.

But "heterogeneous" pops up every now and then:

See linking servers on MSDN

  The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

sp_addlinkedserver

  A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources.

And an MSDN example of querying Active Directory in "Joining Heterogeneous Data"

And MSDN again "Heterogeneous Database Replication"


File name as primary key? I have a few tables I'm going to create for a website, Literature, video, and image. Is there a good reason why I shouldn't use the file name as a primary key.

For instance...

"someimage.png" as the PK for table image.

The only downside I can think of is if I had named someimage.png in multiple directories and each directory someimage.png was not the same image.  This seems like poor design and there isn't any reason why I'd do this anyways.

Thoughts?

In general, surrogate PKs are preferable compared to natural. Usually, primary key columns never get updated; in your case renaming file will require updating primary key which in turn may cause cascade updates. Aside from that, having a long primary key is never a good idea from storage and performance point of view.

Depends on RDMS you use, primary key may also define how the rows stored (the physical order), so even inserting will cause many more page splits and high level of fragmentation.

Summary .I'd strongly recommend not to use file names as primary keys. If they have to be unique, you always have an option to create unique constraint.


Matching data in two tables I'm teaching myself SQL and I ran into a question that may be related to joining tables. I'm just a bit unsure.

Say, you have two tables, one listing the customer's creditcard_number, and one listing the customer's order_number. I am trying to match a customer's creditcard_number with their order_number.

What might this query look like? Would I first have to join the tables?

To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown.

I then loaded these tables with data (see end of post for DML). These examples use both PostgreSQL and MySQL. A note on table names. I use singular names - you can, of course, use plural (as many do) - but decide and stick to one!

A word of advice (and see discussion below), if you have a choice, I would strongly urge you to use PostgreSQL as its dialect of SQL is vastly superior to MySQL's. The -- (double hyphen) is a comment in SQL, insert/remove as appropriate for your server.

CREATE TABLE Customer
(
  Cust_ID SERIAL NOT NULL PRIMARY KEY,                 -- PostgreSQL
  -- Cust_ID  INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- for MySQL
  Name VARCHAR(15),
  CC_Number VARCHAR(16)
);

CREATE TABLE Cust_Order  -- don't want a table named "Order" - SQL keyword
(
  Order_ID SERIAL NOT NULL PRIMARY KEY,  -- PostgreSQL
  -- Order_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- MySQL
  Cust_ID INT,
  Order_Value INT,
  Order_DateTime TIMESTAMP,  -- PostgreSQL
  -- Order_DateTime DATETIME -- MySQL
  CONSTRAINT Order_Cust_fk FOREIGN KEY (Cust_ID) REFERENCES Customer(Cust_ID)
);

Firstly, to answer your specific question about relating credit card number to the order, the query (for both PostgreSQL and MySQL) to run would be this:

SELECT c.Cust_ID, c.CC_Number, o.Order_ID
FROM Customer c
INNER JOIN Cust_Order o
ON c.Cust_ID = o.Cust_ID
ORDER BY c.Cust_ID;

which gives the result:

+---------+---------------+----------+
| Cust_ID | CC_Number     | Order_ID |
+---------+---------------+----------+
|       2 | 4656576576767 |        1 |
|       2 | 4656576576767 |        2 |
|       2 | 4656576576767 |        3 |
|       2 | 4656576576767 |        4 |
|       3 | 9999999999999 |        5 |
|       3 | 9999999999999 |        6 |
|       3 | 9999999999999 |        7 |
|       3 | 9999999999999 |        8 |
|       3 | 9999999999999 |        9 |
+---------+---------------+----------+

The reason that I didn't directly answer the specific question in your post was that it is very unlikely that anyone would be performing such queries. Storing credit card numbers as plain text is a recipe for disaster. Since the question related to JOINs and SQL generally, I was more interested in demonstrating JOINing and SQL than in how to display CC numbers.

SQL is all about aggregations and GROUPing and SUMming, so in my original example, I showed the SUM of sales by Customer and CC number (although, as mentioned, you should never be performing SQL against CC numbers directly). Chances are you'll be interested in sales per customer rather than actual CC numbers.

SELECT c.Cust_ID, c.Name, c.CC_Number, SUM(o.Order_Value) AS Total
FROM Customer c
INNER JOIN Cust_Order o
ON c.Cust_ID = o.Cust_ID
GROUP BY c.Cust_ID, o.Cust_ID                      -- PostgreSQL GROUP BY
-- GROUP BY c.Cust_ID, c.Name, c.CC_Number, o.Cust_ID -- MySQL GROUP BY
ORDER BY o.Cust_ID;

result:

+---------+-------+---------------+-------+
| Cust_ID | Name  | CC_Number     | Total |
+---------+-------+---------------+-------+
|       2 | Jimmi | 4656576576767 | 13736 |
|       3 | Mary  | 9999999999999 | 27775 |
+---------+-------+---------------+-------+

A word of warning about MySQL:

MySQL has a variable sql_mode which is a set of parameters - you can view it by issuing the command 'SELECT @@sql_mode;' in the mysql client (or MySQL Workbench). You can set it in the my.cnf or my.ini (Windows) in the mysqld section:

[mysqld]

sql_mode    = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

The ONLY_FULL_GROUP_BY parameter is the one to set, otherwise MySQL can produce (ahem...) anomalous (i.e. wrong) results!

Finally, and to expand, you might also (as someone who's selling widgets :-) ) be interested in clients who haven't bought anything (possibly in a specified period). I wrote this query using a LEFT JOIN (not INNER). This works for PostgreSQL and MySQL.

The LEFT JOIN will bring in everything in the left (or first, i.e. Customer) table - i.e. all clients whether they've made purchases or not, i.e. whether they have a corresponding entry in the Cust_Order table or not. The INNER JOIN will only match records which correspond in both tables - i.e. it will only show clients to whom a sale has actually been made.

SELECT c.Cust_ID, c.Name, c.CC_Number, COALESCE(SUM(o.Order_Value), 0) AS Total
FROM Customer c
LEFT JOIN Cust_Order o
ON c.Cust_ID = o.Cust_ID
-- WHERE EXTRACT(MONTH FROM Order_DateTime) = 2        -- PostgreSQL
-- WHERE MONTH(Order_DateTime) = 2                     -- MySQL
GROUP BY c.Cust_ID, o.Cust_ID                          -- PostgreSQL
-- GROUP BY c.Cust_ID, c.Name, c.CC_Number, o.Cust_ID  -- MySQL
-- HAVING COALESCE(SUM(o.Order_Value), 0) >= 0
-- ORDER BY SUM(o.Order_Value) NULLS FIRST, c.Cust_ID ASC;  -- PostgreSQL
-- ORDER BY SUM(o.Order_Value) IS NULL DESC, c.Cust_ID ASC; -- MySQL

Result:

 cust_id | name  |   cc_number   | total
---------+-------+---------------+-------
       3 | Mary  | 9999999999999 | 27775
       2 | Jimmi | 4656576576767 | 13736
       4 | Joe   | 2222222222222 |     0
       1 | Billy | 3434343434343 |     0

This is the result with no WHERE, HAVING or ORDER BY clauses.

I would urge you to experiment with the COALESCE function (PostgreSQL and MySQL) and instead just using SUM(o.Order_Value) - instructive for NULLs. Vary it both in the SELECT, the HAVING and the ORDER BY.

Check out the EXTRACT (PostgreSQL) here and MONTH (MySQL) functions and the other functions on those pages. Vary the SUM in the HAVING and the ORDER BY - go mad!! :-)

These servers (as do others) have string functions (PostgreSQL and MySQL) and also maths ones (P and M) - as well as regular expressions (much better supported in PostgreSQL than MySQL - another reason to use PostgreSQL).

This should get you started on the road to SQL mastery! :-)

Take a look at the questions on this forum and make an attempt to answer
them - you'll gradually find that situations that used to baffle
you become clearer.

===================== Data for Queries ===========================

Customer table data:, for both PostgreSQL and MySQL.

 INSERT INTO Customer (Name, CC_Number) VALUES ('Billy', '3434343434343');
 INSERT INTO Customer (Name, CC_Number) VALUES ('Jimmi', '4656576576767');
 INSERT INTO Customer (Name, CC_Number) VALUES ('Mary',  '9999999999999');
 INSERT INTO Customer (Name, CC_Number) VALUES ('Joe',   '2222222222222');

Cust_Order table data: (again, for both PostgreSQL and MySQL).

INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-02-14 11:55:34');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-02-18 14:24:34');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-03-09 09:23:17');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (2, 3434, '2105-03-27 16:02:56');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-01-22 12:45:03');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-02-25 13:34:43');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-02-28 17:50:01');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-03-01 18:30:16');
INSERT INTO Cust_Order (Cust_ID, Order_Value, Order_Datetime) VALUES (3, 5555, '2105-03-17 11:17:17');


Connecting to a self signed mongodb server though mongo shell doesn't require `pem` file I am using nodejs mongodb driver to connect to a self signed mongodb instance. The MongoDB and Mongo Shell version is 4.0.0. Below is the command to launch a self signed mongodb instance:

mongod --port 27018 --sslMode requireSSL --sslPEMKeyFile mongodb.pem  --dbpath data

when I connect to this server with mongo shell, I can use below command without pem file:

mongo --port 27018 --ssl --sslAllowInvalidCertificates

I wonder what the PEM file is used for in the connection.

As per MongoDB documentation here Before you can use TLS/SSL, you must have a .pem file containing a public key certificate and its associated private key.

  Note : For FIPS mode, ensure that the certificate is FIPS-compliant (i.e use of FIPS-compliant algorithms) and the private
  key meets the PKCS#8 standard. If you need to convert a private key to
  PKCS#8 format, various conversion tools exist, such as openssl pkcs8
  and others.

MongoDB can use any valid TLS/SSL certificate issued by a certificate authority, or a self-signed certificate. If you use a self-signed certificate, although the communications channel will be encrypted, there will be no validation of server identity. Although such a situation will prevent eavesdropping on the connection, it leaves you vulnerable to a man-in-the-middle attack. Using a certificate signed by a trusted certificate authority will permit MongoDB drivers to verify the server’s identity.

In general, avoid using self-signed certificates unless the network is trusted.

This operation generates a new, self-signed certificate with no passphrase that is valid for 365 days. Once you have the certificate, concatenate the certificate and private key to a .pem file, as in the following example:

cat mongodb-cert.key mongodb-cert.crt > mongodb.pem

For your further ref here


Chunk error when upgraded to mongodb 2.6 while uploading doc I was trying to upgrade mongodb 2.2 to 2.6 and upon testing 2.6 received chunk out of order error..
I recieve an unexpected error from the server: exception: chunks out of order at /home/ps203k/.perlbrew/libs/perl-5.20.2@oasis-gsp_oss/lib/perl5/x86_64-linux-thread-multi/MongoDB/GridFS.pm line 236.
it is standalone.. and not sharded..
only once instance of mongodb running in one sever..
the server has old mongodb version : 2.2 and may be old perl version..
package MongoDB;
{
$MongoDB::VERSION = '0.46.3';
}
so we have 0.46.3 driver version of perl..
so do you think that this chunk out of order we are getting because of old driver version...? we need to upgrade perl driver and perl versions..

Did you follow the guidelines?

  To upgrade an existing MongoDB deployment to 2.6, you must be running
  2.4. If you’re running a version of MongoDB before 2.4, you must upgrade to 2.4 before upgrading to 2.6.

I have upgraded from 2.4 to 2.6 with no issues on a sharded cluster.


cannot create a procedure with error [1064] [42000] I am using mysql 5.7 and I have some code which works. But when I try to create procedure with the code, it reports error [1064] [42000]. I try to update the content of an old procedure with my current code and it works. Does someone know why this happens?

Here is how I try to create a procedure.

create procedure dispatch_remaintimecal_12division (in lgtid VARCHAR(20))
begin
my code;
end

ERROR 1064 (42000): states that "You have an error in your SQL syntax".
Can you specify what is being executed as 'my code'. That is where SQL syntax appears to be going wrong.
Make sure you are within the right database before calling your procedure.

eg: USE dbname;


Query to determine percentage composition on a specific grouping of records I want to analyse the Market capital of the Industry per Counterparty of the following table:

--------------------------------------------------
| CPTY | Industry      | Value   |
--------------------------------------------------
| a    | food          | 10      |
| a    | water         | 5       |
| c    | wood          | 20      |
| c    | food          | 20      |
| d    | wood          | 10      |

I would like a breakdown of percentages for each of the CPTY fields in relation to their child Industries.

A sample of the output query I would expect should look like:

| CPTY | Industry      | Value   | %IndustryPerCpty
--------------------------------------------------
| a    | food          | 10      | 66.66
| a    | water         | 5       | 33.33
| c    | wood          | 20      | 50.0
| c    | food          | 20      | 50.0
| d    | wood          | 10      | 100.00

E.g. for CPTY a, the corresponding sectors are food and water. The total value for CPTY a is 10+5=15. Thus, food has 10/15*100% = 66.66% of the total market value for that CPTY. Likewise, water has 5/15 *100% = 33.33% of the total market value for that CPTY.

My original attempts at the query follow the structure adopted in accepted answer here. However, this gives me percentage composition for each row, not the Industry per CPTY.

Note: I don't expect the sum( %IndustryPerCpty ) column to equal 100%, for reasons I hope are clear.

Can anyone suggest a query structure in Ms Access to generate my desired table with %IndustryPerCpty fields?

Assuming your table is called [t], and that the Value column has been called Val (Value is a reserved word and complicates things), you can create a view (query) with the following SQL definition:

SELECT
    t0.CPTY, t0.Industry, t0.Val * 100.0 /
    (SELECT sum(t1.Val) FROM t AS t1
      WHERE t1.CPTY = t0.CPTY) AS PerCentIndustryPerCpty
FROM
    t AS t0;

You can make it using the "SQL view" instead of the graphical designer for views (querys).

You get this result:

If you don't feel comfortable with the SQL view, this can also be done within the GUI, in two steps:

Create a Query named TotalsPerCPTY with a column named TotalValPerCPTY that has the SUMs of Val GROUPed BY CPTY, as shown in the following image:

Create the ViePerCents Query by joining the t table together with the TotalsPerCPTY view, using the CPTY column, with one computed column that computes Val / TotalValPerCPTY * 100.0:


Connect by prior - multiple fields and query efficiency I have 2 tables one of which defines the hierarchy for the other

equipment

id    desc
1     item1
2     item2
3     item3
4     item4
5     item5
6     item6
7     item7
8     item8
9     item9

hierarchy
equipId    parentEquipId    type    attrib_1    attrib_2    attrib3
1          3                D       blue        red         green
2          4                B       green       blue        red
3          2                C       orange      green       purple
4          null             A       pink        yellow      orange
5          3                D       yellow      red         purple
6          3                D       purple      green       red
7          2                C       grey        brown       tan
8          7                D       red         grey        yellow
9          7                D       blue        green       orange

My query is against the items of type D. These can be easily determined through a join on the second table to obtain the type.

select *
from equipment
left join on hierarchy.equipId = equipment.id
where hierarchy.type = 'D'

The tricky part is I also need to obtain the 3 attributes for the various parent types.
Each type D item has an A,B,C parent hierarchy based on the types of the parents. So for the piece of equipment with the id of 1 the ids of the parents are 3(C),2(B),4(A).

I've been using multiple CONNECT BY PRIOR statements to get the attributes of the parent hierarchy. I have only figured out how to get each attribute individually though and this adds up to a lot of connect by prior statements. Even for each of the parent types I still need to do 3 connect by priors, one for each attribute. It would be nice to be able to do a connect by prior once to each of the parent types and pull all 3 attributes at the same time.

Does anyone know if/how it can be done?

Here's my current SQL. It works but I'd like to make it more efficient and less verbose.

Any help is appreciated.

with
e as  (select equipment.*
       from equipment
       left join on hierarchy.equipId = equipment.id
       where hierarchy.type = 'D')

select e.*
  ,(select h.attrib_1
    from heirarchy h
    where h.type = 'A'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeA_attrib_1
  ,(select h.attrib_2
    from heirarchy h
    where h.type = 'A'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeA_attrib_2
  ,(select h.attrib_3
    from heirarchy h
    where h.type = 'A'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeA_attrib_3
  ,(select h.attrib_1
    from heirarchy h
    where h.type = 'B'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeB_attrib_1
  ,(select h.attrib_2
    from heirarchy h
    where h.type = 'B'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeB_attrib_2
  ,(select h.attrib_3
    from heirarchy h
    where h.type = 'B'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeB_attrib_3
  ,(select h.attrib_1
    from heirarchy h
    where h.type = 'C'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeC_attrib_1
  ,(select h.attrib_2
    from heirarchy h
    where h.type = 'C'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeC_attrib_2
  ,(select h.attrib_3
    from heirarchy h
    where h.type = 'C'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeC_attrib_3
from e

Here are some additional questions that would help me answer this question better: (1) how often is this query executed; (2) does the result set have to be returned as a single row per ID; (3) in the actual environment can one item have more than 3 levels of depth?

I am going to assume that data sample is not representative of your actual environment and that the depth of this hierarchy may go deeper than 3 levels.  In order for a single-row query to be successful in each case there must be a lot of potentially redundant SQL to account for all possible levels of depth.  If your query accounts for an item having up to 5 levels of depth, but only one of your items has that many levels then you are putting unnecessary load on the database and potentially face performance delays with long running queries.  A multi-row query could get much better performance, but that probably isn’t how you need the data returned (like below).

SELECT  H.*
FROM    HIERARCHY_TBL H
START WITH H.EQUIP_ID = '1'
CONNECT BY PRIOR H.PARENT_EQUIP_ID = H.EQUIP_ID

If this is an ad hoc query occurring infrequently then dealing with performance delay may be an acceptable option so you could stick with what you have.  If this query is executed very frequently then performance delay is going to be unacceptable by the users.

As far as suggestions, if this SQL is being executed from within an application then you could look into using dynamic SQL.  You could also check into creating a SQL profile to see if that would improve performance.  You could also create a view that does most of this leg work for you and then hit that view with your query.  The view option won’t reduce execution time, but it may help with clarity.  If the data in these tables doesn’t change very often you could also look into using a materialized view that is refreshed from time to time – this will give you a lot of performance gain, but materialized views have their own challenges and depending on the environment some DBAs may discourage their usage.

I hope this helps.


Why do temporal tables log the begin time of the transaction? When updating a row in a temporal table the old values for the row are stored in the history table with the transaction begin time as the SysEndTime. The new values in the current table will have the transaction begin time as the SysStartTime.

SysStartTime and SysEndTime are datetime2 columns used by temporal tables to record when a row was the current version. Transaction begin time is the time the transaction containing the updates started.

BOL says:

  The times recorded in the system datetime2 columns are based on the
  begin time of the transaction itself. For example, all rows inserted
  within a single transaction will have the same UTC time recorded in
  the column corresponding to the start of the SYSTEM_TIME period.

Example: I start updating all the rows in my Orders table at 20160707 11:00:00 and the transaction takes 5 minutes to run. This creates a row in the history table for each row with SysEndTime as 20160707  11:00:00. All the rows in the current table will have a SysStartTime of 20160707  11:00:00.

If someone were to execute a query at 20160707  11:01:00 (while the update is running) they would see the old values (assuming default read committed isolation level).

But if someone was to then use the AS OF syntax to query the temporal table as it was at 20160707 11:01:00 they would see the new values because the their SysStartTime would be 20160707  11:00:00.

To me this means it doesn't show those rows as they were at that time. If it used the transaction end time the problem wouldn't exist.

Questions: Is this by design? Am I missing something?

The only reason I can think it's using the transaction begin time is that it is the only 'known' when the transaction starts. It doesn't know when the transaction will end when it starts and it would take time to apply the end time at the end which would invalidate the end time it was applying. Does this make sense?

This should allow you to recreate the issue.

The idea is to track logical time vs physical time. Logical simply refers to what a user/app expects the time of an insert/update/delete to be. The fact that the DML operation may take a while for whatever reason, isn't meaningful or even easily determined and understood by a user. If you've ever had to explain lock vs latch contention to an accountant (I have), it's a comparable situation.

For instance, when Bob "tells" the app that all employees in Bob's department will start making $42/min at 20160707 11:00:00, Bob (and his employees) expects everyone's pay is now calculate at $42/min from that time. Bob doesn't care that for this to be effected, the app has to make 2 reads and 6 writes across the database per employee and their data + log files sit on a bunch of RAID-5 SATA II drives so it takes about 7 minutes to finish the task for all 256 of Bob's employees. Bob, his accountant and the payroll manager care that all his employees are paid $42/min starting 20160707 11:00:00. Else, the employees that were updated at 20160707 11:00:01 will be slightly annoyed while those whose records were updated at 20160707 11:00:07 will be gathering outside the payroll department.

There are valid use cases to track physical time such as debugging and forensics but to the end user, it's generally meaningless. The Tlog keeps both ordering and timing information for each of the write operations (among other things) so it's there if you know how to look.


Slow Parallel SQL Server query, almost instant in serial I have a SQL Server query as follows (obfuscated):
UPDATE  [TABLE1]
SET     [COLUMN1] = CAST('N' AS CHAR(1))
FROM    [TABLE1]
WHERE   (COLUMN1 = '2' AND COLUMN2 IN('VAL1', 'VAL2', 'VAL3')) OR
        (COLUMN1 <> 'N' AND (
                                    SELECT  COUNT(*)
                                    FROM    TABLE2 wle
                                            JOIN TABLE3 wl
                                                    ON wl.COLUMN3 = wle.COLUMN3
                                    WHERE   TABLE1.COLUMN4 = wle.COLUMN4 AND
                                            (wl.COLUMN5 = '1' OR wl.COLUMN6 = '1') AND
                                            wle.COLUMN7 = (
                                                            SELECT  MIN(alias.COLUMN7)
                                                            FROM    TABLE2 AS alias
                                                            WHERE   TABLE1.COLUMN4 = alias.COLUMN4
                                                            )
                                ) > 0

        )

We have just upgraded our (test) server to SQL Server 2016 SP2 from SQL Server 2014 SP3.
The performance of the query above appears to have fallen off a cliff as a result of this.
When the server was on SQL Server 2014, the database compatibility level was 120. Now it is on SQL Server 2016, the compatibility level for the database is still 120, however I have tried the query in 110,120 and 130, all with the same result.
When I run sp_whoisactive, I can see the wait_info is (48847425ms)CXCONSUMER suggesting that the query has been waiting for CXCONSUMER for the last 48847425ms (814 minutes) The query has currently been running for 13:34:07.587.
The wait info suggests that the query has been waiting on CXCONSUMER for the majority, if not all of its execution time.
This to me suggests some issue with paralellism so I ran the query with the hint OPTION (MAXDOP 1) and it finished in an acceptable time (around 30 seconds)
The plan shape is as follows.

The plan shape for the MAXDOP 1 query is as follows:

(the same plan with no parallelism operators)
When I run the parallel query with the live execution plan enabled, the operator highlighted in green (Clustered Index scan on TABLE2) shows 100% but it's execution time continues clocking up.
The operator highlighted in red (Clustered Index Scan on TABLE1) gets "stuck" on 4 rows of 180,215.
What could be causing this problem? In my head am I thinking it is parallelism skew (uneven workload) but given that the serial query finishes in less than a minute I would have thought even if the query went parallel but only used one thread it would still complete in a time close to the serial query.
Also, given that the live plan appears to show the red clustered index scan not progressing at all I am unsure what is happening.
Processor and I/O affinity are set to to Automatic
I have found this article that describes similar behavior though the purpose of the article appears to be showing that CXCONSUMER is not necessarily a benign wait and doesn't say how / if it can be fixed.
In terms of fixing it, I know the code can be re-written in a more efficient way (both the COUNT and MIN subqueries could be selected into variables) but unfortunately changing the query isn't an option I have.
I could force a MAXDOP hint but again, this means changing the code, perhaps I could force it with a plan guide, though such practice of forcing the opimizer usually isn't advisable?
What causes this problem?
Why is the query running slow in SQL Server 2016?
Why is the Clustered Index Scan on TABLE1 getting "stuck" on 4 rows of 180,215?
Is there a way to fix this without changing the code?

Is there a way to fix this without changing the code?

Create the following index to eliminate the eager index spools:
-- Give this index a better name
CREATE INDEX i ON dbo.TABLE2 (COLUMN4, COLUMN7) INCLUDE (COLUMN3);

With that in place, you should get a plan similar to:

The other problems described in the question will almost certainly disappear once you give the optimizer good indexes.
It is possible (though unlikely) your query suffers from an undetected intra-query parallelism deadlock (IQPD). A very detailed investigation would be needed to confirm that. The vast majority of IQPDs are detected and can then be resolved by spilling exchange buffers to tempdb. An undetected IQPD will result in the query being stuck forever.


Performance: order of tables in joined statement I have the following SQL statement, running on a SQLite database on a Windows mobile device.

SELECT
    table1.uniqueidentifier1, table1.int1, table1.varchar1,
    table1.decimal1, table1.decimal2
FROM table1
INNER JOIN table2 On table1.PK = table2.FK
WHERE table2.uniqueidentifier2 IN (uniqueidentifier1,uniqueidentifier2,....)
ORDER BY table1.varchar1

As there are several hundred thousand records in each table and the device isn't really new this takes some time.

Would the performance be better, if I switched the tables, somehow like this:

SELECT
    table1.uniqueidentifier1, table1.int1, table1.varchar1,
    table1.decimal1, table1.decimal2
FROM table2
INNER JOIN table1 On table1.PK = table2.FK
WHERE table2.uniqueidentifier2 IN (uniqueidentifier1,uniqueidentifier2,....)
ORDER BY table1.varchar1

Please note: in the first statement I select from table 1` and join table 2, in the second, it is switched.

Why or why not is it faster?

SQLite automatically chooses the estimated optimal join order; the table order in the query has no effect.

There are two ways to optimize this query.
If the filter on uniqueidentifier2 removes most table1 records from the result, then it would be fastest to look up table2 records with matching uniqueidentifier2 values first, then to look up the corresponding table1 records, and then to sort the result.
This would require the following indexes:

CREATE INDEX t2_uid2_idx ON table2(uniqueidentifier2);
-- CREATE INDEX t1_pk ON table1(PK);  -- primary keys have this automatically

If most table1 records will show up in the result, then it would be more efficient to go through table1 in the proper order and look up correspondig table2 records.
This would require the following indexes:

CREATE INDEX t1_vc1_idx ON table1(varchar1);
CREATE INDEX t2_FK_uid2_idx ON table2(FK, uniqueidentifier2);

(Having uniqueidentifier2 in the second index optimizes for this particular query, but might be not worth the storage and update overhead if you have many other queries.)

To check how queries are actually implemented, execute EXPLAIN QUERY PLAN.


WAIT STATS - high Wait Time on ASYNC_IO_COMPLETION I currently look at my wait stats. I followed best practices explained by Paul Randal and go through the documents he references.

Looking at the statistic slices I take once / day for a while now from all production servers, I see significant high values for Avg_Wait_S, meaning the relation of total Wait in Seconds divided by WaitCounts. The value for wait count is very low compared to all the other wait types (the lowest), but on each of my servers the AvgWait_S for this type is between 120 - 2493 seconds! This looks like a massive wait time.

I read this can anyhow be caused by backups. But I wonder if this is kind of a 'normal' value? How to get more details about it?

Here is a sample resultset of my wait stats:

WAITTYPE            Wait_S      Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S
BACKUPIO            46151.80    45093.07    1058.74     22819151    26.55       0.0020      0.0020      0.0000
CXPACKET            45057.81    27926.59    17131.22    20387211    25.92       0.0022      0.0014      0.0008
BACKUPBUFFER        16658.83    15867.71    791.12      8993341     9.58        0.0019      0.0018      0.0001
PAGEIOLATCH_SH      15326.95    15284.65    42.30       2131848     8.82        0.0072      0.0072      0.0000
ASYNC_IO_COMPLETION 14203.17    14203.17    0.00        9           8.17        1578.1303   1578.1303   0.0000
WRITELOG            8570.20     8377.81     192.39      2136964     4.93        0.0040      0.0039      0.0001
PAGEIOLATCH_EX      7691.32     7673.31     18.01       1777069     4.43        0.0043      0.0043      0.0000
SOS_SCHEDULER_YIELD 4548.90     43.44       4505.47     13294384    2.62        0.0003      0.0000      0.0003
LCK_M_S             3018.76     3018.06     0.71        1704        1.74        1.7716      1.7712      0.0004
ASYNC_NETWORK_IO    2678.83     2147.78     531.05      4372825     1.54        0.0006      0.0005      0.0001
LCK_M_SCH_S         2612.09     2612.08     0.01        37          1.50        70.5971     70.5968     0.0003

As mentioned

  The value for wait count is very low compared to all the other wait
  types (the lowest)

the count being only 9, i.e. the wait has only showed up for 9 times, during the run of gathering wait stats for a complete day, wont be a concern here for me.

However, its worth checking the other wait types BACKUPIO and BACKUPBUFFER .

This wait type generally shows up when you are taking the backup over the network with not a good NW connectivity or on the tape etc (i.e. any other extremely slow backup system)

We had this similar wait on one of our SQL server 2005 for backing up the database approx 200 GB in size. As the size being large and we did not had compression feature available at that time, witness lots of waits. we had to go with third party compression tool and saw those wait types count going down.So i believe it could be a good start if you start checking on the backups. Make sure you use the SQL server compression feature for backing up the databases.

However just to check if this wait type is causing any problems, you need to check for any issues in backup throughput.

May be you can go around and test the backups by doing native backups on same network as compared to other network and check for other counter values.

Also, you can refer A cause of high-duration ASYNC_IO_COMPLETION waits for more explanation by Paul on this wait.


How to search Query Store for queries with multiple plans? There are a number of blog posts about why a single query with multiple plans is bad.  But they mostly address when you find them, not how to find them.

Query Store does not offer a report, that focuses on finding them, nor is it an obvious metric you can choose.

You can use the configure option to filter for queries with multiple plans on specific metrics.

How do you find all the queries with multiple plans?

You can search the query store plan.  The query below returns all the query_ids with multiple plans.  You can then use the Tracked Queries report to look at it with the GUI.  You will probably want to use the configure option to extend the time interval.

Select query_id
, COUNT (plan_id) as 'Plan_Count'
from sys.query_store_plan
Group by query_id
Having COUNT (plan_id) >= 2

If you have exported the Query store data to another database as described here removing the leading sys. and the query runs on your exported data.

Select query_id
, COUNT (plan_id) as 'Plan_Count'
from query_store_plan
Group by query_id
Having COUNT (plan_id) >= 2

Exporting the data and adding indexes, can have a huge impact on query cost.  In the example below my exported data is in seperate database that also has Query Store enabled.  The top query with from sys.query_store_plan returns results from the databases Query Store, while the second query with from query_store_plan returns results from the table that was created when I exported the data to it.


Should a table be split if only a particular column gets updates? I have a table in my MySQL 5.7 database, which has 9 columns. One column is an AUTO INCREMENT PRIMARY KEY, where as the other 6 contain some data, which isn't going to be updated in a year. Now, the last 2 columns are of type JSON and they are going to be updated frequently, multiple times in a second(could be 5 times a second, and as system scales could be much more than that).

My question is, should i split my table into 2 tables, having a one to one relationship? So that all the non changing data goes in one table and the 2 columns which are to be updated frequently go to the other table.

It depends.

Will you be accessing (SELECTing) only the 'static' data frequently?  With complex WHERE clauses?  If so, "vertical partitioning" is beneficial.  It would keep the SELECTs from interfering with the UPDATEs.

Will the JSON columns be large, and the 'static' columns be relatively small?  If so, that adds to the argument for vertical partitioning.

When you are ready to update a JSON field, do you have the PRIMARY KEY in hand?  Searching around for which row to change may interfere with other UPDATEs.

Sorry there is no straightforward answer; "your mileage may vary".

Rephrasing

(This discussion applies to any InnoDB table with "big" fields -- TEXT/BLOB/etc.)

If you usually access the big field (JSON, in your case) --> 1 table
If you often don't look at the big field --> 2 tables
If you usually do a "point query" (ie, you have the id of the row and don't need to search for it) --> 1 table
If you often check lots of rows to find the one (or few) to fetch/update --> 2 tables
But... If most rows are bigger than 8KB (and you are using InnoDB), then, because of the way 'large' columns are stored off-block, there is not much advantage for 2 tables.


Can I query data_space names when not in context of the database for multiple databases? I have a data set pulled from sys.master_files.  The data set looks something like this:

database_id filename         physical_name data_space_id
----------- ---------------- ------------- -------------
6           PRIMARY_06       some location 1
6           primary_09       some location 1
21          PRIMARY_01       some location 1
26          PRIMARY_01       some location 1
52          PRIMARY_01       some location 1
75          PRIMARY_01       some location 1

I would like to join in the FILEGROUP name to this dataset.  It should just be a matter of joining on database_id and  data_space_id, but I can't seem to find a function or system view analogous to sys.master_files for filegroups/dataspaces.

The brute force method would be to create this list as a temporary lookup using sp_MSForEachDB, but I would like to know if there's a better way.

Sorry. Afraid not. you have to query sys.filegroups and/or sys.data_spaces in each database.


update table with data from other table in remote oracle db I have table X from remote Oracle DB A, and table Y in local Oracle DB B.

The data in Y is a partial copy of the data in X (X has some columns that aren't needed in Y; the columns I need in Y have the same names in both tables).

I periodically need to synchronize the data Y with what's in X (this is unidirectional - there's nothing in Y that needs to be copied back to X). Y has constraints that prevent simply dropping all the data in Y and repopulating it; I need to update what's already in 'Y', and insert any new rows from X that don't already exist in Y.

I've looked at a couple of options that won't work:

The copy feature in SQL Developer would copy X into a brand new table, not into Y
The export option would insert everything as new records, not update existing rows

What's the best way to do this?

I did it by:
creating dblink from db A to DB B
and using : merge into Y y using select X.columns from X@dblink x on (x.idX=y.idY)
when matched then update set
y.columns=x.columns --here ids are not included
when not matched then
insert (y.columns) values(x.columns) --ids are included
x.colums and y.colums mean shared columns


64 bit Postgres 9.1 Limitions on Windows 2008 R2? When Deploying Postgres on Windows Server 2008 R2:

Are there any limitations on windows that don't exist on Linux?
Does the postgres scale in the same way on windows as it does on Linux?
Is performance on windows reasonably close to performance on Linux say within 10%?

No clue.
What kind of scale (up or out)? From what little that I've found on the web, it appears that Pg on Linux can handle more connections than on Windows.
Not if you are running anti-virus software on the Windows box ;^). There is an old (8.2 vintage) pgsql-performance thread here regarding performance differences. The take away seems to be "it depends" (based on usage) and that you need to do benchmarking to really know how much performance difference there will be. There is also some discussion regarding performance differences on serverfault.


How to isolate single values from multiple records Hello ladies and gentlemen.

If we have the following records (as example) from a query:

is it possible to present the following sql result with pl/sql oracle query ?

We need to have the single ALPHA CODE record, the analytic BETA CODE record related to the alpha code from the first query, and SUM of the VALUE record = 30 and not 90 (3 x 30).

Thank you very much for your help in advance.

Best regards

Tom

This kind of formatting should be usually done in the front end, not in SQL.

You can however get the display you want using window functions, no PL/SQL required.

select case when rn = 1 then alpha_code end as alpha_code,
       beta_code,
       case when rn = 1 then value end as value
from (
  select alpha_code, beta_code, value,
         row_number() over (partition by alpha_code order by beta_code) as rn
  from the_table
)
order by beta_code;

SQLFiddle example: http://sqlfiddle.com/#!4/28ca3/1


Find records with same string with extra character OK, so I have a Microsoft SQL Server 2014 database table called owner with around 90,000 records with owner information, another called vehicle with vehicle information

Owner_Name                   owner_id       V_name     owner_id    exempt
-------------------------------------       ------------------------------
JACOB JAMISON & JESSICA           35        Civic            35        H3
JACOB JAMISON M & JESSICA B       39        Accord           39        H3
BLACKSON BARRINGTON               56        Bugatti          56        H6
BLACKSON BARRINGTON H             98        SSC              98        H7
BRUSTER MICHAEL                   107       Corvette         107       H9

I'm trying to find all records that have more than one exemption on a vehicle
   ( H0 means no exemption). This code below has worked well, as long as the name is exactly the same. However, if there's a variation, such as an extra letter or if it's entered backwards, then it won't return those records. I've looked at things like SOUNDEX, but this wouldn't work in my scenario.

SELECT Owner_name
     , COUNT(Owner_name) AS 'xNameAppears'
     , COUNT(v.exempt) AS 'ExemptionCount'
FROM owner o
INNER JOIN vehicle V ON V.owner_id = o.owner_id
WHERE v.exempt <> 'H0'
GROUP BY O.owner_name
HAVING COUNT(v.exempt) > 1

Is there a solution that would allow me to return records like so, not knowing which owner_name's may be similar? Basically trying to get the server to search through the owner_name column and if there's a similarity such as
JACOB JAMISON & JESSICA and JACOB JAMISON M & JESSICA B then it'll return those records like so:

Owner_Name                      xNameAppears      ExemptCount
-------------------------------------------------------------
JACOB JAMISON & JESSICA           2                         2
JACOB JAMISON M & JESSICA B       2                         2
BLACKSON BARRINGTON               2                         2
BLACKSON BARRINGTON H             2                         2

Thank you in advance!

The SOUNDEX function can be applied to a column as well.

But since

  there's thousands like that

I wouldn't suggest just writing a query to join on a function to do that.

This will likely not perform very well on larger tables:

SELECT *
FROM dbo.vehicle AS v
JOIN dbo.vehicle AS v2
ON SOUNDEX(v2.Owner_Name) = SOUNDEX(v.Owner_Name)
AND v2.Owner_Name <> v.Owner_Name;

I'd rather do something that will make finding this easier in the long-term.

Here's an example:

CREATE TABLE dbo.vehicle (Owner_Name VARCHAR(50));
INSERT dbo.vehicle ( Owner_Name )
SELECT *
FROM (
VALUES
('JACOB JAMISON & JESSICA'),
('JACOB JAMISON M & JESSICA B'),
('BLACKSON BARRINGTON'),
('BLACKSON BARRINGTON H'),
('BRUSTER MICHAEL')
) AS x (Owner_Name);

I'm going to add a computed column based on the function, and then add an index to aid my query.

ALTER TABLE dbo.vehicle ADD Owner_Soundex AS SOUNDEX(Owner_Name);

CREATE INDEX ix_whatever ON dbo.vehicle (Owner_Soundex, Owner_Name);

Validate that everything looks good...

SELECT *
FROM dbo.vehicle AS v

Use a query like this to find imprecise matches:

SELECT *
FROM dbo.vehicle AS v
JOIN dbo.vehicle AS v2
ON v2.Owner_Soundex = v.Owner_Soundex
AND v2.Owner_Name <> v.Owner_Name;


MariaDB uses all /tmp space I'm running a Galera cluster with MariaDB 10.

One of my nodes reports 100% usage disk for the /tmp table.

   /dev/mapper/vg0-LV--Tmp      1,8G    1,7G  228K 100% /tmp

I can't see files in /tmp with the ls command, but lsof reports:

mysqld     1236            mysql   14u      REG              254,5           0         17 /tmp/ibrpksHg (deleted)
mysqld     1236            mysql   15u      REG              254,5           0         19 /tmp/iboJmq1H (deleted)
mysqld     1236            mysql   16u      REG              254,5           0         20 /tmp/ibJLXol9 (deleted)
mysqld     1236            mysql   17u      REG              254,5           0         21 /tmp/ibKM4Kq5 (deleted)
mysqld     1236            mysql   21u      REG              254,5           0         22 /tmp/ibFyx8KA (deleted)

If I restart the node, the space is released, but the issue comes back after some time.

I don't understand what's happening here.

Any idea?

SHOW VARIABLES LIKE 'tmpdir'; -- You could change that (and restart) to immediately workaround the problem.  But that is not a long-term solution.

Complex queries with inadequate indexes are likely to be the cause.  Set long_query_time=1 and turn on the slowlog; wait a day; run pt-query-digest or mysqldumpslow -s t to see the 'worst' queries.  Focus on them.

If you don't see how to improve a query, post a question with EXPLAIN SELECT..., SHOW CREATE TABLE and a tag it [mysql] [query-optimization].


Next step in progression from 3NF to BCNF I am working with relation H that is detailed below:

H = {A,B,C,D}

It shows the following functional dependencies (FD[s]):

F = { A -> B, B -> C, {C,D} -> A}

The candidate keys are

{A,D}, {B,D}, {C,D}

I belive relation H is in 3NF.

I also believe there are two FD that prevent this relation from satisfying BCNF:

A -> B and B -> C

Then I have:

A -> B
H1 = {A,B}  F1 = {A -> B} <- this is BCNF
H2 = {A,C,D} F2 = {A -> C, {C,D} -> A} <- this isnt' BCNF

B -> C
H11 = {B,C} F11 = {B -> C} <- this is BCNF
H12 = {A,B,D} F12 = {A -> B} <- this is in BCNF

The questions

I have partial information about this algorithm, so I am not sure about certain things:

What should I do with H2? Just throw it away and say that H1, H11, H12 are decompositions of H to meet BCFN?
Can this relation be converted to BCNF with functional dependency preservation? (I would say no! because it is impossible to derive the FD {C,D} -> A from F1 sum F11 sum F12. Can you confirm that?

First, you assumption about the 3NF is correct.

Then, in the analysis algorithm to find the BNCF, when you start to remove a dependency X → Y since it violates the BCNF, you should put in the first relation H1 not only XY, but X+, while in the second relation you should have H2 = H - X+ + X.

So, in the first step, the two resulting relations are:

H1 = {A,B,C) with dependencies A → B and B → C and key `A`,
H2 = {A, D} (with no dependencies)

In H1 the dependency B → C violates the BCNF since B is not a superkey, and you can decompose it in:

H11 = {A, B} with the dependency A → B and key A
H12 = {B, C} with the dependency B → C and key B

So, at the end, the final decomposition is H11, H12 and H2. Note that this decomposition determines the loss of the dependency {C, D} → A, and this happens also if we start by considering first the dependency B → C (obtaining the same decomposition).


Oracle 12c. Is it possible to export a TDE encrypted table using expdp / impdp to a database without TDE / wallet enabled? I have an Oracle 12c database where I have encrypted certain columns of several tables, I want to import or load these tables into another database which does not have TDE enabled, as would be recommended with datapump? or dblink? And if it were the last one, could it be done without having TDE in the destination? thanks

TDE encrypts the data at rest only. As soon as the data is read off the disk, it is decrypted.
So if you need to copy the data to a new database instance you can just do a CTAS or IAS over the db link.
You should be able to use datapump also, but you have db link between the two database it is the easiest solution.


How can I create a bool column for a view based on the result of a statement? I'm using PostgreSQL as my database.

CREATE TABLE groups (
  id        SERIAL        NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE users (
  id        SERIAL        NOT NULL,
  group     INT           NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (group)
    REFERENCES groups(id),
  UNIQUE (group),
  UNIQUE (name)
);

I want to create a view that includes a boolean column stating whether or not a group has any users based on the has many / belongs to relationship of the two tables above. If it's easier, I'd even be fine with an integer column stating how many users a group has in it. How can I do this in my view?

CREATE VIEW group_info AS
  SELECT DISTINCT
    g.name        AS name,
    ????          AS empty
  FROM groups AS g
  JOIN users AS u
    ON (g.id = u.group)
;

I've looked into using BOOL_OR and/or COUNT to help with testing the existence of a group ID in the users table, but I'm not seeing how to put it all together.

"a boolean column stating whether or not a group has any users"
Use EXISTS:
CREATE VIEW group_info AS
SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty
FROM   groups g;

This returns 1 row per group, no matter whether there are users or not - not one row per user like you had, but probably didn't want - so we don't need DISTINCT.
It's largely irrelevant whether you write SELECT 1 or SELECT * or SELECT 'foo' in the EXISTS subquery. Only the bare existence of at least one row matters.
With number of users
CREATE VIEW group_info AS
SELECT g.name, count(u.group) AS ct_users
FROM   groups     g
LEFT   JOIN users u ON u.group = g.id
GROUP  BY g.name;

LEFT JOIN is crucial to retain groups without users.


Accessing unsharded database in a MongoDB shard cluster without going through mongos? As far as I can understand, not all databases in a MongoDB shard cluster have to be sharded; I can keep some databases unsharded, on only one of the shards.

So suppose I have a shard cluster, with shards (i.e. replica sets) rs0 and rs1. rs0 has a database foo, which is unsharded. I have no plan to shard it or move it to rs1.

Is there any harm in letting some clients connect to rs0 directly and use the foo database without going through mongos?

I have only tested that it is possible to connect directly to rs0 and make queries in foo, but I don't know if it is potentially dangerous in my use case.

Mongo will happily let you directly access any database on any individual node you hit. If that database is sharded, you can still access it but will only get results from that shard. This also means that you can have unsharded databases on a node and it'll return results from that unsharded database.

Yes, it is safe to directly access the replicaset members for that unsharded database.

It's also possible to do it through the MongoS process same as the sharded access. This may make code-paths easier, though it does add a bit of latency to the entire call.

Database is a feature of Nodes.
Sharding is a feature of Databases.
Replica status is a feature of Databases.
MongoS and Config servers represent the management layer needed to do Sharding.

A database that is not sharded does not need MongoS or Config, and can be directly accessed safely.


String search is very slow with OR statement I have the following tables:
product, product_stock, and product_offer
I need to optimize the search when looking for a product that is available in product_stock or that is currently on offer. First, when I add one subplan, things look ok
explain select * from product p
where
   p.product_number like '%T%'
and
  p.id in
      (select c.product_id from product_stock c where c.quantity > 0)

This gives me the following feedback:
Gather  (cost=1000.42..36876.20 rows=22531 width=53)

Which is very good for a table over 1m records
However, when I add an OR statement as following:
explain select * from product p
where
   (p.product_number like '%T%')
and
  (p.id in
      (select c.product_id from product_stock c where c.quantity > 0)
   or p.id in
      (select product_id from product_offer where now() between offer_start and offer_end)
  )

This results in a very slow query as following:
Gather  (cost=313039.34..166666168.02 rows=31023 width=53)
  Workers Planned: 2
        ->  Parallel Bitmap Heap Scan on product b  (cost=312039.34..166662065.72 rows=12926 width=53)
            Recheck Cond: ((product_number)::text ~~ '%T%'::text)
            Filter: ((SubPlan 1) OR (hashed SubPlan 2))
            ->  Bitmap Index Scan on product_trgm_gin  (cost=0.00..310574.23 rows=41364 width=0)
                Index Cond: ((product_number)::text ~~ '%T%'::text)
            SubPlan 1
              ->  Materialize  (cost=0.00..17907.26 rows=558188 width=8)
                  ->  Seq Scan on product_stock c  (cost=0.00..12935.32 rows=558188 width=8)
                           Filter: (quantity > 0)
            SubPlan 2
              ->  Seq Scan on product_offer (cost=0.00..1308.68 rows=59468 width=4)

Get rid of these silly subqueries and use UNION rather than OR:
select p.* from product p
   join product_stock c on p.id = c.product_id
where c.quantity > 0
  and p.product_number like '%T%'
union
select p.* from product p
   join product_offer o on p.id = o.product_id
where now() between o.offer_start and o.offer_end
  and p.product_number like '%T%';


Why securityAdmin does not have enough permission? I am a server securityAdmin.

The securityAdmin role should be treated as equivalent to the sysadmin role, according to Microsoft, but I failed to do below 2 things

make myself as sysAdmin
grant myself write right to a database. (currently in db_denydatawriter role )

Did I miss anything?

Thanks

UPDATE

per RDFozz's comment, I was able to add a new 'dummy' login, but failed to set this login as a user of this database.

UPDATE 2 -- final script that works!

sepupic's answer is great! following his instruction, here is the SQL.

first, use myLogin to create a new super_user.

CREATE LOGIN [super_login] WITH PASSWORD=N'yourPassword'
GRANT CONTROL SERVER TO [super_login]
Grant IMPERSONATE ON LOGIN::[sa] TO [super_login]

then login with the super_login, run this.

Execute as login = 'sa'
  ALTER ROLE [db_denydatawriter] DROP MEMBER [mylogin]
  --ALTER ROLE [db_owner] add MEMBER [mylogin]
  ALTER server ROLE [sysadmin] add MEMBER [mylogin]
Revert

The securityAdmin role should be treated as equivalent to the sysadmin
  role, according to Microsoft, but I failed to do below 2 things

  make myself as sysAdmin
  grant myself write right to a database. (currently in
  db_datadenywrite role )

All the above is wrong. Secutiry admin is not sysadmin, if it was so for what purpose 2 different server roles were created?

Security admin has these 3 permissions at server level:

CONNECT SQL
ALTER ANY LOGIN
VIEW ANY DATABASE

CONNECT SQL and VIEW ANY DATABASE permissions are granted to public, so there is only one additional permission, ALTER ANY LOGIN.

At the database level this role adds you the possibility to grant permissions to other users, but you don't have alter any user / alter any role, so you cannot add yourself or any other user to database role.

The phrase

  The ability to grant access to the Database Engine and to configure
  user permissions allows the security admin to assign most server
  permissions. The securityadmin role should be treated as equivalent to
  the sysadmin role.

in my opinion means the following: security admin can elevate his permissions to sysadmin in some way.

I can give you an idea of this "way".

If you are security admin you can create SQL Server login ( I'll call him super_login) and grant him CONTROL SERVER permission.

The next step is to log in as super_login and give all the permissions you want to your login. You can make yourself db_owner of the database where now you are db_datadenywriter, or drop yourself from db_datadenywriter or merely add your login to sysadmin fixed server role.


Similar data with fields redundant in one table I need four different plans to be stored in one or more tables. All four plans share the fields however some plans have extra fields.

All plans fit into the plans-table:

PLANS(id, provider_id, plan_type, name, desc, price, link)

However based on the plan type it requires more fields to be added and/or some fields will be empty.

As an example it may be that if plan type A is chosen there are no empty fields but if plan type B is chosen there will a requirement of two additional fields like:

PLANS(id, provider_id, plan_type, name, desc, price, link, strength, acceptable)

In other cases plan C requires all fields except strength field.

Leaving empty fields means it's not fully normalised, but my reasons for leaving it like so is because I want to use one QUERY to find all the different types of plans for a provider using a GROUP BY with a server side script.

I could use an additional table to store the additional fields with a 1:1 relationship or have 4 separate tables?

If I do decide to have 3 additional tables (with only the extra fields for each plan) then I could just do a JOIN when needed.

Here's what it could possibly look like (I've omitted some fields):

Is this a good approach?

A good way to do this is to use the Entity-Attribute-Value approach when modelling sparse attributes (https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model).

For you the relationships would probably look like this:

Your tables would then look something like:

Note I haven't specified the PlanTemplate table, this would show all possible fields for a plan.

There are a few caveats with this approach:

It requires that you almost always pivot and join the PlanFieldValue table, performance can be hampered. Usually views are necessary, there is a dependency here on what DBMS you are using. Almost all support pivoting, but there are varying levels of support and performance
Querying and displaying data from the PlanFieldValue on to your UI is onerous, requires using a view and updates/inserts need to be handled differently. More dev time required.
Indexing and performance of the tables is often hampered if millions of rows are in the table, for instance
All attribute information has to be checked manually, for instance field length and nullability needs to be checked manually against the template, possibly using another column in PlanField that has a Nullable flag.

Otherwise its a good approach that I have used quite a few times. One big advantage is you can add new fields without changes to your database structure - a huge bonus if the field values are changeable or likely to be increased.

However if the number of fields for each Plan is low and it is unlikely you will add more fields, a single plan table with individual columns still isn't too bad. It doesn't really matter having some columns blank but it does make it hard to track nullability in the database.


Will frequent creation and deletion of TABLE (not indexes) cause fragmentation in database? Does frequent creation and deletion of physical table in database causes fragmentation? I am frequently creating and deleting database for loading data warehouse load. I am using these table to hold records temporarily  for batch updates.

This question is asked primarily from SQL Server 2016 and Azure Data Warehouse (MPP) point of view. But i would like to extend this question for any databases (if true)

There are quite a lot of factors as to whether the staging table will be fragmented, and if so, how much.  The most obvious that come to mind are:

How much data is being added to the table
How much other tables are adding or deleting rows at the same time
Where exactly in the database file the table was created and if there are a lot of contiguous free extents

So basically, the answer is that it is quite likely that the table will be fragmented.  They won't necessarily cause your other permanent tables to be fragmented much, but again, if they are growing at the same time and need new extents, then yes.

All that said, the more important questions are:

Does the fragmentation cause a performance problem?  Not likely.  Fragmentation is not the problem it used to be when you had 3 hard drives in a RAID 5 array.  On Azure your data is using 100s of disks, so the combined read and write power reduces the performance impact of fragmentation to almost nil.  See  Stop Worrying About SQL Server Fragmentation"
Is there anything I can do about it?  Nothing reasonable to stop it unless you want to create a different filegroup on another volume dedicated to the staging tables.  You could also rebuild your other tables and indexes with a lower fill factor and that may stop them from becoming fragmented as much if data is being inserted into someplace other than then end of the table (which might indicate poor table design), but again, see point above.  Perhaps the most reasonable option is to simply run your reindexing job after the ETL job is done.


Database - Resource usage statistics I would like to find (using SQL DMVs) whether a database is actually in use and if so then how much of that servers resources it is taking.

I need some ideas on how we can properly collect usage statistics on all of these databases across all of these servers and then create a simple report to understand resources usage.

The purpose of this report is to determine what are the critical counters that we need and what is the simplest and least impact method to collect them.

We don’t need minute to minute, I’m only looking for summary information once per day or so.

Can you please advise on this?

Yes there are DMV's for this like sys.dm_db_index_usage_stats, but that alone will not help in analyzing the unused databases.

Here you can analyse on unused databases with help of SQL Server Audit as explained here by Aaron Bertrand When was my database / table last accessed?

In addition you may need various other methods to track the database usage like:

1) If there are any transactions count going up for the database in question with help of counters info, using below query:

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name like 'Transactions/sec%'
and instance_name like 'instance_name_here%';
GO

2) You can also ask the execution plan cache if queries have been running against the database. This query takes advantage of the dm_exec_text_query_plan DMV as explained in the article Querying Information from the Plan Cache, Simplified

3) You can check from the login history as well on those databases using Adam Mechanic's SP_WhoIsActive

Once you have the data collected all together ,and listed databases to be taken offline execute

ALTER DATABASE [database_name] SET OFFLINE;

Monitor for a duration and if an application goes down for the reason being database of that being taken offline, you can simply take those databases back online:

ALTER DATABASE [database_name] SET ONLINE;


Optimize series of queries that recycle the same filter I have about 30 queries across 50 tables in total which each pull or aggregate information to be later all joined together. The original code provided used entirely temp tables, but it is difficult to maintain. A common theme across the queries is the filter in the WHERE clause which selects the records we want.

I tried to convert the temp tables into a series of chained CTE's and used the WHERE clause as a filter CTE which is inner joined to all child CTE's so that when that filter is changed, it cascades to all other queries. Also, when new queries are added, they simply need to join to this CTE and the filter is applied. The idea is we start with the base filter table with only the IDs of the desired records, then left join to append the measures we want.

The problem is that this has a huge performance degradation compared to the temp tables. We've sacrificed performance for consistency, modularity, and ease of maintenance. Is there any way to tweak the performance in our favor, though? The biggest hits seem to happen on those that use windowing functions to concatenate strings across multiple rows into one row per record.

I've never attempted something of this nature before, and CTE's seemed like the logical approach. What was once a 4-5 minute query is now taking 30 minutes.

How it looks with temp tables:

select
    ...
INTO
    #temp1
FROM
    ...
WHERE
    <repeated filter>
    AND
    <temp1 filter>
select
    ...
INTO
    #temp2
FROM
    ...
WHERE
    <repeated filter>
    AND
    <temp2 filter>

SELECT
    ...
FROM
    #temp1
        join
    #temp2
WHERE
    <repeated filter>

And with the CTE's:

WITH Filter AS (
    SELECT
        ...
    FROM
        ...
    WHERE
        ...
), Query1 AS (
    SELECT
        ...
    FROM
        ...
            INNER JOIN
        Filter
    WHERE
        <query1 filter>
), Query2 AS (
    SELECT
        ...
    FROM
        ...
            INNER JOIN
        Filter
    WHERE
        <query2 filter>
)
SELECT
    ...
FROM
    Filter
        LEFT JOIN
    Query1
        LEFT JOIN
    Query2

The key thing to realize is that a common table expression (CTE) is not a table; it's an expression. In SQL Server at least, a CTE is not materialized; the query is not run once, with the results re-used if the CTE appears more than once in the main query. Every time you reference the CTE, that query is re-executed.

So, you may want to consider a hybrid approach.

I would build a temporary table with the basic filtered data - what's currently returned by the Filter CTE. I would also expand it slightly. Currently you say it only returns an ID value. If there are any other values that you'll need in the final query (especially if they come from tables that aren't involved in intermediate queries), include that in the temp table - otherwise, you'll just have to go back and get it later.

Note that you can index a temporary table. Unless the temp table rows are wider than I'd expect, I'd put a clustered index on the IDs. This may help with other joins. (Note also that populating a table using an ORDER BY may happen to insert data in a particular order, but it guarantees nothing that the DB engine will recognize - it will consider the data as unsorted).

Now, use the temporary Filter table in your list of CTEs. It will not have to be reconstructed for every query it's used in, which should improve things quite a bit.

If there are other CTEs in your list that are used more than once, you may want to consider making them temporary tables as well. However, depending on the complexity of the query, two or three uses may not cause a significant performance loss, compared to the benefit of maintainability.


Can one of Windows failover cluster nodes run on Linux? As SQL Server is available for Linux can I configure Clustering on Linux nodes? I mean mixed with Windows - some nodes are on Linux, others on Windows in the same cluster?

As SQL Server is available for Linux can I configure Clustering on Linux nodes?

You can configure Corosync and pacemaker for Linux. Good luck.

  I mean mixed with Windows - some nodes are on Linux, others on Windows in the same cluster?

In the same cluster, no. Someone will argue with me and say, "Well, Sean, you can do read-scale availability groups like this!" and that may be true, however it isn't a cluster.

The only way to do this would be to have two different clusters, spanned by a distributed availability group. If you want to make life complicated then this would be the setup to do it.


How to prevent specific applications from connecting to Oracle? Is there a way to prevent certain applications (e.g. MS Excel or Access) from connecting to Oracle database?

A bit of context:

My company uses an Oracle database as our live production database. We don't want any Excel or Access files being linked to this database. I don't really care if users use something like Toad to run a few queries by hand but I don't want a bunch of Excel or Access files linked to this database. We replicate this database and all users who need this data should get it from the replicated database, not the live database.

I can of course view the list of sessions and what program was used to connect with this query, but I'm not sure how to prevent the sessions from being opened in the first place:

SELECT SID,SCHEMANAME,OSUSER,TERMINAL,PROGRAM FROM GV$SESSION

The PROGRAM column of this query will tell me if something is opened with Excel or Access.

You could use a logon trigger for that, something along these lines:

create or replace trigger logon_trigger
  after logon on database
declare
  v_prog varchar2(50);
  v_sid number;
begin
  v_sid := to_number(sys_context('USERENV', 'SID'));
  select program into v_prog FROM GV$SESSION where sid = v_sid;
  if lower(v_prog) like 'access' then
      raise_application_error(-20000, 'Login not allowed');
  end if;
end;
/

PS. Not tested.


Dynamic access to record column in plpgsql function How can I address a column from a record in a plpgsql function dynamically?

In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the IF constrol structure. I'm looking into how I can replace the foobar part in the snippet below with entity.colname. Is that possible in plpgsql?

IF NEW.foobar IS NULL THEN
    RAISE EXCEPTION '% cannot be null', foobar;
END IF;

This is something that could but doesn't work.

IF NEW.entity.colname IS NULL THEN
    RAISE EXCEPTION '% cannot be null', entity.colname;
END IF;

The example above is just for illustration what I'm looking to achieve, don't judge the functionality. :)

I'm using PostgreSQL 9.1.

That's tricky, because identifiers cannot be variables in plain SQL. You need to use dynamic SQL with EXECUTE - which is still tricky, because variables are not visible inside EXECUTE.

Here is a demo how to get around this:

CREATE TYPE mytype AS (id int, txt text);

DO
$body$
DECLARE
    _val    mytype  := (1, NULL)::mytype;
    _name   text    := 'txt';
    _isnull boolean;
BEGIN
    EXECUTE 'SELECT $1.' || quote_ident(_name) || ' IS NULL'
    USING _val
    INTO _isnull;

    IF _isnull THEN
        RAISE NOTICE 'Column "%" cannot be null', _name;
    END IF;
END;
$body$

Improved with with @Jack's idea in the comment.

You cannot use plpgsql built-in FOUND because it is not set by EXECUTE (except for RETURN QUERY EXECUTE - more here). That's why I used GET DIAGNOSTICS ... initially. But finally simplified with @Jack's idea.

quote_ident() makes sure the name is syntactically valid and protects against SQLi.


LEFT JOIN on BETWEEN not using indexes I am trying to join two tables on the criteria that the value one column of table1 is between the values of two columns of table2.

Table 1

CREATE TABLE `values`(
  `id` INT,
  `name` VARCHAR(50),
  `num_addr` BIGINT UNSIGNED
);

Table 2

CREATE TABLE `ranges`(
  `id` INT,
  `range_name` VARCHAR(50),
  `range_start` BIGINT UNSIGNED,
  `range_end` BIGINT UNSIGNED,
  INDEX `idx_start` (`range_start` ASC),
  INDEX `idx_end` (`range_end` ASC),
  INDEX `idx_range` (`range_start` ASC, `range_end` ASC)
);

The query:

SELECT
  `v`.`name`,
  `v`.`num_addr`,
  `r`.`range_name`
FROM
  `values` `v`
  LEFT JOIN `ranges` `r` ON `v`.`num_addr` BETWEEN `range_start` AND `range_end`

An EXPLAIN EXTENDED for the query shows that no index is used, with the information 'Range checked for each record (index map: 0x7)'. This is a performance issue as the ranges table has over 500,000 rows and the query is time sensitive.

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, r, ALL, idx_start,idx_end,idx_range, , , , 1, 100.00, Range checked for each record (index map: 0x7)

A FORCE INDEX ON JOIN actually makes things worse as the optimizer only sees the suggested index, but does not use it.

Is there a way to use an index on such joins?

Additional notes:

Changing the BETWEEN to value >= range_start AND value <= range_end does not change the execution plan.
Removing idx_start and idx_end indexes does not improve the situation.
Adding an index to num_addr does not affect the join execution.
The intervals (range_start, range_end) can overlap.
There are num_addr that are not in a range.
A good analogy is phone numbers: In the ranges table there would be a record: ('UK', 44000000000000, 44999999999999) and another ('UK Vodafone', 44700000000000, 44799999999999).

Overlapping ranges is an especially difficult problem to optimize.  However, here is a technique that provides significant performance, but it requires major change to the schema.

Add another table; let's call it Prefixes.  It contains two columns:

prefix DECIMAL(4,0) NOT NULL,
range_id INT,   -- for JOINing to `ranges`
PRIMARY KEY(prefix)

Then, to look for the number 4432109..., first look up 4432 in Prefixes.  This will lead to one or morerange.id` values for checking in your existing table.

Note that, for your example, there will 100 entries in Prefixes for 'UK' and 10 entries for 'UK Vodafone'.  This implies extra code to maintain Prefixes.

A variant on this is to move the rest of the ranges columns into my new table, and get rid of the table ranges.  (Whether to do this depends on the number and nature of the columns, the hassle in the code, the size of the 'prefix', the size of the table(s), etc, etc.)


Clear schema from database without dropping it I'm working on a school project where I have a SQL Server with a database for my team.

I already imported a local database created with Entity Framework.

Now the model has changed, table properties were added/deleted and I want to update my full database.

However, the teachers didn't gave us the create rights so dropping the whole database isn't really an option.

Now is my question, is it possible to drop all the tables currently in the database and just import the newly created one without problems? Or do I really need to drop the whole database?

I assume you are trying to push your changes back to the database from your entity model? If so you can do that from studio. (Right click in the entity modeler, save changes to the database.)

If you have to recreate it - you can also just change where the EF is pointing in your configuration settings and push the model out to the database if you have create permissions. Really just depends what rights you were given.