Maintaining integrity between 3 tables when reference could be through or direct Say we have the following 3 tables:

tUsers (ID, name, countryID, regionID)
------
1  |  Joe  |  1   |  1
2  |  Sue  | NULL |  2
3  |  Bob  |  2   |  NULL
4  |  Amy  | NULL |  NULL

tCountries (ID, name, regionID)
------
1  |  USA   |  1
2  |  China |  3

tRegions (ID, name)
------
1  |  North America
2  |  Central America
3  |  East Asia
4  |  Europe

Is there a way from the DB to prevent a user from having mismatched Country/Region while still allowing one or both to be NULL?  For example, the query UPDATE tUsers SET regionID=4 WHERE ID = 3 would fail since Bob's country is China which is not in region Europe.

3 foreign keys version

create table tRegions (
  ID int not null primary key,
  name  varchar(20)
);

create table tCountries (
  ID int not null primary key,
  name varchar(20),
  regionID int,
  --any superset of PK is undoubtly UNIQUE
  constraint cu1 unique (ID, regionID),
  foreign key (regionID) references tRegions(ID)
  );

create table tUsers (
 ID int not null primary key,
 name varchar(20),
 countryID int ,
 regionID int,
 foreign key (regionID) references tRegions(ID),
 foreign key (countryID) references tCountries(ID),
 foreign key (countryID,regionID) references tCountries(ID,regionID )
)

insert tRegions (ID, name)
values
(1,'North America'),
(2,'Central America'),
(3,'East Asia'),
(4,'Europe')

insert tCountries(ID, name, regionId)
values
(1,'USA',1),
(2,'China',3)

insert tUsers (ID, name, countryID, regionId)
values
(1,'Joe', 1  ,1),
(2,'Sue',NULL,2),
(3,'Bob', 2  ,NULL),
(4,'Amy',NULL,NULL)

go
--OK
update tUsers set RegionId=3
where ID=3
select * from tUsers
go
--fails
update tUsers set CountryId=2
where ID=2
select * from tUsers


SSIS - Excel destination does not allow characters more than 255 My SSIS package has a ado.net source, which returns columns with width more than 2000 characters,but those columns get truncated when redirected output to excel destination.

Any idea, how to resolve this problem..?

I am using excel 2007, as excel destination.

SSIS Excel Data Source:

  Error with output column “Comments” (5691) on output “Excel Source
  Output” (5596).

The column status returned was:

  “Text was truncated or one or more characters had no match in the
  target code page.”

Reason for this error:

The error is due to the lengthy data in the excel.
SSIS has an inbuilt logic to scan the data in the spread sheet. It will scan the first 8 rows and based on that it will written a logic to build the table's logic for the package. If your lengthy data in not in the 8 row then your ssis wont respond it.

Considering am having a column named "Name"
First 8 rows is of length <255
9th row is of length > 255.
While executing the package you will get the above error, its because your input value will be truncated and SSIS wont allow for that. In this case,
Two types of logics can be followed,

Option1 : Its tricky one, just copy the 9th row to the top (may be as 1 row).Now try to create your package using import/export wizard. Your ssis will create column with width nvarchar(Max) which will accept upto 2 GB.

Option2: Change the Preparation SQL task query, change the data type as varchar(Max) and modify the excel source ->advanced editor and change the output columns type and length and external column type and length.

Please read the blog


PostgreSQL and MONEY data type for currency values For a project, I created a table with a column price MONEY NOT NULL column. And I thought it would handle decimals properly, unlike a floating number (i.e. IEEE rounding issues), but I end up having values like $9.94 (string) being returned from the database, instead of 9.94 (numeric). Having to manually manipulate the field value by removing the dollar sign is silly; is there a way to have a MONEY column without a currency sign?

If not, what's the best data type replacement to handle currency values?

What about this?

A money value can be cast to numeric without loss of precision. Conversion to other types could potentially lose precision, and must also be done in two stages:

SELECT '52093.89'::money::numeric::float8;

Source:
http://www.postgresql.org/docs/9.1/static/datatype-money.html


MySQL: Find Extra Days From List Of Date Ranges I have a following rows

start        end
2015-02-12   2015-02-16
2015-02-17   2015-02-18
2015-02-20   2015-02-20

Now I want to calculate number of days for a date range which are not in these ranges. So for example I have date range like this

2015-02-11 - 2015-02-19

so the result for this input should be 2 days. How? for a clear explanation

start        end
2015-02-12   2015-02-16   // 1 day which is 2015-02-11
2015-02-17   2015-02-18   // 1 day which is 2015-02-19 range
2015-02-20   2015-02-20  // 0 day because this row starting date is greater than input limit.

I want MySQL query for this.

You could use a Dates table to help with this.

This table will hold a single row-per-day from 2015-01-01 into the future:

CREATE TABLE Days
(
  Day date NOT NULL
);

INSERT INTO Days (Day)
SELECT ADDDATE('2015-01-01', INTERVAL (n1.Num + (n2.Num * 10) + (n3.Num * 100) + (n4.Num * 1000)) DAY)
FROM (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n1
CROSS JOIN (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n2
CROSS JOIN (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n3
CROSS JOIN (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n4;

This is the list of ranges you have defined in your question:

CREATE TABLE RangeList
(
  FromDate date NOT NULL
  , ToDate date NOT NULL
);

INSERT INTO RangeList (FromDate, ToDate)
VALUES ('2015-02-12', '2015-02-16')
  , ('2015-02-17', '2015-02-18')
  , ('2015-02-20', '2015-02-20');

This is the range of days you're interested in checking:

CREATE TABLE DaysNotInRange
(
  FromDate date NOT NULL
  , ToDate date NOT NULL
);

INSERT INTO DaysNotInRange (FromDate, ToDate)
VALUES ('2015-02-11', '2015-02-19');

This will show you the missing days:

SELECT Days.Day
FROM Days
    INNER JOIN DaysNotInRange ON Days.Day >= DaysNotInRange.FromDate
        AND Days.Day <= DaysNotInRange.ToDate
WHERE Days.Day NOT IN (
  SELECT Days.Day
  FROM Days
      INNER JOIN RangeList ON Days.Day >= RangeList.FromDate
        AND Days.Day <= RangeList.ToDate
  )
ORDER BY Day;

The results:

╔════════════╗
║    Day     ║
╠════════════╣
║ 2015-02-11 ║
║ 2015-02-19 ║
╚════════════╝


DBCC SHRINKFILE EMPTYFILE not emptying Trying to remove an extra database file from a database using EMPTYFILE on SQL Server 2008 R2.

use [MYDATABASE]
        DBCC SHRINKFILE (MY_DATABASE_7, EMPTYFILE);
        use master
        ALTER DATABASE [MY_DATABASE]
        REMOVE FILE MY_DATABASE_7;

And getting this error

  Msg 2555, Level 16, State 1, Line 2  Cannot move all contents of file
  "MY_DATABASE_7" to other places to complete the emptyfile operation.

The other database files aren't limited in growth in anyway.
There is more than enough disk space

And there is practically nothing left in the 7th file.

So there is no reason why I can't remove this 7th file.

Any thoughts as to why SQL Server is rejecting the EMPTYFILE ?

Increasing the size of the other database files by 1GB resolved the problem.


huge table copy avoid duplicates I've a table with more than 10 billion records. Many NEW records (in quantum of 40-50 million records)are added regularly in a file as input part of batch process at night. The active table is majorly used for read operations throughout the day. To facilitate fast insert we keep active and inactive table. Everyday we first wipe out the data from inactive table, copy the existing data from active table to inactive table and insert new records. Lastly switch the inactive table to make it active.

I need to know how can I avoid duplicates records existing records to be copied from active table and in NEW records while copying.
eg.

transaction_data_active
customer_num, help_number, issue_number issue_details, issue_closed_status

transaction_data_inactive
customer_num, help_number, issue_number, issue_details, issue_closed_status

The table doesn't have partitions and supports global index on customer_num, help_number, issue_number columns. We are using oracle 11g

I am not an Oracle expert, but I can answer from DB2 perspective, you may look for similar features/commands in Oracle. In DB2, you can achieve this by:

Using Import command with INSERT_UPDATE option.
You also have a choice to use MERGE statement to merge data between two tables.

Hope it helps you.


Approaches for Real time copy of database on same SQL Server instance I have a draft of requirements that look like this:

Production database should be copied in real-time to "Pre-prod" database
The Pre-prod database should be available to read and update
It should be possible to make schema and data changes to Pre-Prod
Schema and data changes on Pre-prod should not effect Prod

The databases will be on the same SQL Server instance.

I'm wondering what approach to take? Transactional Replication seems the best option, but applying schema changes to a subscriber seems like a recipe for disaster.

Mirroring is out because the mirror will not be available; log shipping is out for similar reasons.

Are there any other technologies I should be considering?

Transactional replication gets you close, but as you said, changing the subscriber schema is a recipe for disaster.

Transactional replication uses stored procedures to apply the data changes. You could, with every table change, change those procedures to deal with the underlying changes.
That is a lot of additional manual work, but I don't think you will find a solution without that. Those procedures are created automatically when calling sp_addarticle (See value 0x02 for the @schema_option parameter and the @ins_cmd, @upd_cmd and @del_cmd parameters.)

However, you might be better of to take a copy at the beginning of the development cycle and then not apply new changes till the end of the cycle. That is a simple backup-restore with the added benefit that you do not have to constantly adjust testing scripts do deal with the changing data.


Why does importing a MySQL dump cause so many I/O? I imported 100 SQL-format dump files. The total size was 20 GB. The resulting .ibd files after the import had a total size of 40 GB. mysqld.exe wrote 1.1 TB and read 120 GB on the disk. Why so many I/O?

I used the default options of MySQL Workbench to create the SQL-format dump files, namely:

It seems to match pretty well the advice of the section Bulk Data Loading for InnoDB Tables of the MySQL Reference Manual (except for autocommit=0, which is not present in my dumps). The resulting code headers in the dump look like:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `askbot_activityauditstatus`
--
LOCK TABLES `askbot_activityauditstatus` WRITE;
/*!40000 ALTER TABLE `askbot_activityauditstatus` DISABLE KEYS */;
INSERT INTO `askbot_activityauditstatus` VALUES
etc

I know I could try using other dump tools to speed up, but I'm curious. I'm especially impressed by the number of writes.

I use Windows 7 SP1 x64, MySQL 5.6 and MySQL Workbench 6.0.

Restoring a dump file, fundamentally, involves inserting all of the rows and building all of the indexes for all of the data.  In that light, it's not particularly relevant how the dump file is generated, whether you use one or several files, or which of the available options you select (though some of them, like extended-insert could possibly make it worse of not selected).

When you're restoring a file, you're also:

writing the data to the transaction log
writing the data to the double-write buffer
writing the data to the tablespace files
writing the indexes to the double-write buffer
writing the indexes to the tablepace files
writing the data to the binary log if that's enabled ... and if you have the default binlog_format setting of "statement," then you're literally writing almost every byte in the dump file plus overhead... but if your binlog_format is set to "row," you're writing a more compact version to the binlogs... and if your binlog_format is set to "mixed," then which format is actually used in the binlog depends on your default transaction isolation level.

Setting innodb_flush_log_at_trx_commit to the slightly less safe value of 2 or significantly less safe value of 0 from the very expensive but ACID-compliant default value of 1 will speed up your insert for sure, though it isn't likely to reduce actual I/O because that value doesn't change what's written to the transaction log, it merely changes how often InnoDB insists on confirmation from the operating system that the contents of the log have been persisted to disk.  I use "safe" in the sense of safety against the loss of recent transactions if a crash occurs during the time the value is set to 2 or 0; 1 protects against data loss if either MySQL or the system crashes; 2 protects against data loss if MySQL crashes but not the system, and 0 protects against neither.  Once you set it back, it doesn't have any after-effects.

Quick side note, some of the comment-looking things shown are not comments.  The /*!mnnrr format is a MySQL backwards-compatibility extension that tells the server "If you're MySQL version m.nn.rr or higher, execute this statement, otherwise disregard."

/*!40000 ALTER TABLE `table_name` DISABLE KEYS */;
/*!40000 ALTER TABLE `table_name` ENABLE KEYS */;

Each table is surrounded by these in a dump file.  These were more useful with MyISAM than with InnoDB, because DISABLE KEYS directed the storage engine not to update any non-unique indexes until ENABLE KEYS was issued, allowing all of the row data to be written, and then indexed in a batch.  With InnoDB, the indexes are built as the inserts are processed... so there's a lot of I/O potential there as the index trees are being built and shuffled around.

The size of the InnoDB buffer pool is going to play a role in the amount of disk I/O -- possibly a significant one if it's relatively small -- because whatever can't stick around in memory will have to be promptly flushed to disk, only to be read back in when it's needed again, and this will be particularly true with indexes, less so with the actual rows, because in InnoDB, rows are physically stored in primary key order, and mysqldump writes them to the file in primary key order... so they're inserted in the order in which they'll be stored... but the secondary indexes will have to be shuttled back and forth to disk as index pages are updated as the insert operations on a given table progresses.

So there's a baseline of "extra" activity that every insert that happens, because of all the different logging, safety, and ACID mechanisms... and indexes seem like a likely wildcard candidate for potentially creating a substantial amount of additional I/O.


Modifying the Contents of SQL Server Agent Alerts As is generally recommended to do, I have Alerts configured for errors of severity 19 and greater that occur on my SQL Servers.

However, as part of an email notification standardization process, I'm wondering if there is any way to modify the contents of an Alert, including the email title and the body of the message.

From looking around both the options provided by SSMS and through some searching on the Internet, I'm being led to believe that this might not be the case. I am aware that an additional message can be added to the end of a system-generated alert, but I haven't found anything suggesting the system alert itself can be modified.

My other immediate idea to achieve the general goal here would be to not have the alerts send notifications directly, but instead to trigger a job that triggers Powershell that ultimately sends the email. Would this be the only realistic way to do this, or are there easier ways to make an Alert generate a customized notification?

There is no way to directly modify the subject and body fields of the email alerts generated by SQL Server Agent.

Having the alert run a job instead of directly generating an alert email would be the correct way of getting to your desired state.  Having said that, it's not necessary to use PowerShell for that if you don't want to.

Unfortunately, there does not appear to be a way to pass parameters into the job from the alert.  This means you'd need to define a separate job for each alert you have configured.


Load Testing Crash Course Can someone point me at a good resource to get a crash course in load testing?

I have a SQL Server machine that I need to load test but I haven't been thruogh the process before. We do not have any third party software for this, or money to purchase any, so that option would be out.

Here is an excellent post from Kendra Little regarding this.

http://www.brentozar.com/archive/2012/06/load-test-sqlserver/

There are several tools out there, both commercial and free. In past when I had need I used
Adam Machanic's SQLQueryStress

SQL Load Generator on CodePlex is another free tool you can use also.


SQL Server performing slow I have a Microsoft SQL Server running on an IBM server with 10 cores and 64 GB RAM.

I have from 100 to 300 users working concurrently on the server.

Users complain about slowness of the application.  I've checked the server and found CPU usage is 6%, RAM usage is 4GB, and network usage is up to 1Mbps.  I have a 4Mbps dedicated line.

I've tried to collect other information on the server such as Blocking and Wait Statistics:

The Average resource wait time is 40 sec.

I am unable to interpret the cause. Can anyone suggest a way to resolve this?

If any further information is required, I can provide that.

EDIT:

As suggested, I've added the following items:

Page Life Expectancy 3

IO stats Snapshot

Wait Statistics

Server information

Memory status

Query Execution List

Following discussions in chat, we've established:

32bit Enterprise SQL Server.
3.5GB of memory in use (no AWE/PAE).
2.1GB database with 1.3GB buffer pool.
Page life expectancy of 3 seconds.

Suggested fixes are:

AWE/PAE as a quick fix so the database fits in memory.
Investigate the frequently called, highest IO queries identified.
Consider rebuild to 64bit.


GUID as reason for MySQL import slowness We faced MySQL restores DB VERY slow: ~3 min for 15 MB dump. This is a new dedicated server with a lot of RAM.

We were told the most possible reason is GUID is uded as primary key in many tables:

CREATE TABLE `acl_actions` (
  `id` char(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `name` varchar(150) default NULL,
  ........
  `category` varchar(100) default NULL,
  `deleted` tinyint(1) default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_aclaction_id_del` (`id`,`deleted`),
  KEY `idx_category_name` (`category`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The explanation:

  This will certainly affect bulk insert performance as in the case of
  a dump import as the MYI file which holds indexes will be doing random
  I/O rather that the boost it would receive for sequential I/O in the
  case of a monotonically incrementing integer. Your key_buffer_size
  also comes in to play but does not seem to be undersized in this case
  based on key blocks free and the watermark key blocks used.

I'm not sure with that person and moreover, I have not enough experience myself, so I have to ask here:
Why on Earth there will be random IO? Because id is almost random so index's content will be not-so-linear?

Is it possible to decrease performance so drastically because of GUIDs?
Is there any workaround except replace UIDs to autoincremental ID globally?

UPD: after all we found the main reason for slowness: binlog. We have disabled binlog and this reduces import time x12!

The old cliche SIZE MATTERS applies here without a doubt.

Smaller keys would load into a table faster that with biggers (This becomes even more evident with InnoDB). The bigger the columns in the index, the fewer the keys would be residing in BTREE pages. Thus, you get taller BTREE trees. Even a BTREE height of 3 or more, which is bad for a small table, would be the result of having large columns. The same would apply to the name (up to 150 characters) and category (up to 100 characters) fields. Making all columns CHAR instead of VARCHAR would increase SELECT performance, but the significant increase of diskspace + BTREE node management would throw SELECT performance under the bus as the table grows.

Looking at all of your keys I can see this table being extermely lopsided in index size verses table size.

Please run this query

SELECT data_lenth,index_length
FROM information_schema.tables
WHERE table_name = 'acl_action';

You will see the combined sum of all index pages being as large or larger than the table. From a physical standpoint, the file acl_actions.MYI will be bigger that acl_actions.MYD.

Here is where random I/O would come into play: Notice your index idx_aclaction_id_del

Notice that you have it defined as

KEY `idx_aclaction_id_del` (`id`,`deleted`),

If you ever decide to query for deleted records, you will get an full index scan because of the order of trhe columns in this index.

KEY `idx_aclaction_del_id` (`deleted`,`id`),

That way all deleted keys are grouped together. Likewise, all non-deleted rows are grouped together.

Try altering the table design like this

CREATE TABLE `acl_actions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `guid` char(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `name` varchar(150) default NULL,
  ........
  `category` varchar(100) default NULL,
  `deleted` tinyint(1) default '0',
  PRIMARY KEY  (`id`),
  UNQIUE KEY  (`guid`),
  KEY `idx_aclaction_id_del` (`deleted`,`id`),
  KEY `idx_category_name` (`category`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You should run the following query

SELECT name,categrory FROM acl_actions PROCEDURE ANALYSE();

This will recommend the correct sizes for those columns with the given dataset.


Use of Trigger After a Transaction I have a scenario where there is a transaction being executed to update a few tables.  On one of these tables there is a trigger that accesses the data on those tables, but is only planned to be on one of the tables used in the transaction.  The question is:  Will all the data from all of the tables in the transaction be available when the trigger executes?

It depends on when the trigger fires during the transaction.

The trigger itself is part of the transactional context you are currently in. This means that any changes you have already made in the transaction so far will be visible to the trigger.

To take a simple example, consider this data model:

CREATE TABLE Foo (F INT)
CREATE TABLE Bar (B INT)
INSERT INTO Foo VALUeS (1)
INSERT INTO Foo VALUeS (2)
INSERT INTO Bar VALUeS (100)

CREATE TRIGGER Tri ON Foo
FOR UPDATE
AS
DECLARE @BarVal INT
SELECT @BarVal = B FROM Bar

In this case, if you modify Bar before Foo in the same transaction, then changes in Bar by your own transaction will be visible to Tri on Foo. Example:

BEGIN TRAN
  UPDATE Bar
  SET B = 200

  /* Prints 200 */
  UPDATE Foo
  SET F = F + 1
  ROLLBACK
BEGIN TRAN

And:

BEGIN TRAN
  /* Prints 100 */
  UPDATE Foo
  SET F = F + 1

  UPDATE Bar
  SET B = 200
ROLLBACK TRAN

Within the trigger code, you can see both the previous and the new value of the table the trigger fired on by using the special tables inserted and deleted documented here (old article, but still applies):

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

When you have triggers firing triggers, this very quickly becomes complicated. Generally, it is advisable to wrap complex logic operating on several tables in a stored procedure instead. That way you know that all the operations happen in the right order.


PL/SQL Insert Update Before Trigger I have two tables ProjectsTable and ExpenseTable given following and a trigger ExpenseSum which update ProjectsTable when a value is inserted in ExpenseTable table. Now, I want to modify Trigger so that it can check ExpenseTotel whether it exceeds TotalCost or not if it exceeds TotalCost then give a simple message and prevent to insert in ExpenseTable otherwise insert in ExpenseTable and update in ProjectsTable. Can anyone please help regarding this issue ...

CREATE TABLE ProjectsTable
(
  ProjectID     NUMBER(6) NOT NULL,
  ProjectName   VARCHAR2(200) NOT NULL,
  TotalCost      NUMBER(10,2),
  ExpenseTotal  NUMBER(10,2),
  CostRemaining     NUMBER(10,2),
  PRIMARY KEY (ProjectID)
);

CREATE TABLE ExpenseTable
(
  ID            NUMBER(6) NOT NULL,
  ProjectID     NUMBER(6) NOT NULL,
  ExpenseAmount NUMBER(10,2),
  ExpenseDate   NUMBER(4),
  CONSTRAINT fk
  FOREIGN KEY (ProjectID)
  REFERENCES ProjectsTable(ProjectID)
);

CREATE TRIGGER ExpenseSum AFTER INSERT ON ExpenseTable FOR EACH ROW
BEGIN
    UPDATE ProjectsTable P
    SET ExpenseTotal =  ExpenseTotal + :New.ExpenseAmount
    where P.ProjectID = :New.ProjectID;
END;
/

Here's a solution with a rational table design and reasonable reporting requirements.

SQL> -- create test tables
SQL> CREATE TABLE estevens.projects
  2  (
  3    project_id     NUMBER(6) NOT NULL,
  4    project_name   VARCHAR2(200) NOT NULL,
  5    project_budget NUMBER(10,2) not null,
  6    PRIMARY KEY (project_id)
  7  );

Table created.

SQL>
SQL> CREATE TABLE estevens.project_expenses
  2  (
  3    expense_id        NUMBER(6) NOT NULL,
  4    project_id        NUMBER(6) NOT NULL,
  5    expense_amt       NUMBER(10,2),
  6    expense_date      DATE,
  7    CONSTRAINT project_expense_fk
  8    FOREIGN KEY (project_id)
  9    REFERENCES projects(project_id)
 10  );

Table created.

SQL> --
SQL>
SQL> --  populate tables
SQL> insert into estevens.projects
  2           values (1,
  3               'DEMO PROJECT',
  4               1000.00
  5              )
  6  ;

1 row created.

SQL> insert into estevens.projects
  2           values (2,
  3               'ANOTHER DEMO PROJECT',
  4               1000.00
  5              )
  6  ;

1 row created.

SQL> insert into estevens.project_expenses
  2           values (1,
  3               1,
  4               600,
  5               to_date('2017-10-19','yyyy-mm-dd')
  6               )
  7  ;

1 row created.

SQL> insert into estevens.project_expenses
  2           values (2,
  3               1,
  4               600,
  5               to_date('2017-10-19','yyyy-mm-dd')
  6               )
  7  ;

1 row created.

SQL> insert into estevens.project_expenses
  2           values (3,
  3               2,
  4               600,
  5               to_date('2017-10-19','yyyy-mm-dd')
  6               )
  7  ;

1 row created.

SQL> -- daily management report
SQL> col project_name for a10
SQL> select p.project_id,
  2         p.project_name,
  3         p.project_budget,
  4         sum(e.expense_amt) total_expense,
  5         case
  6           when sum(e.expense_amt) > p.project_budget
  7           then sum(e.expense_amt) - p.project_budget
  8           else null
  9         end OVER_BUDGET
 10  from estevens.projects p
 11  join estevens.project_expenses e on p.project_id = e.project_id
 12  group by p.project_id,
 13         p.project_name,
 14         p.project_budget
 15  order by p.project_id
 16  ;

PROJECT_ID PROJECT_NA PROJECT_BUDGET TOTAL_EXPENSE OVER_BUDGET
---------- ---------- -------------- ------------- -----------
         1 DEMO PROJE           1000          1200         200
           CT

         2 ANOTHER DE           1000           600
           MO PROJECT

2 rows selected.

SQL>  --
SQL> drop table estevens.project_expenses;

Table dropped.

SQL> drop table estevens.projects;

Table dropped.

SQL> --
SQL> spo off

You could also expand the idea to have an 'expense remaining' column.

Note that I have also replaced MS-style cAmElcAsE names with oracle-style names.  When in Rome . . .


Stored Procedure Error I am trying to create this procedure in MySQL and show the error mentioned below.

CREATE DEFINER=`root`@`%` PROCEDURE `synctables`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin

insert into tdest
select s.keyval,s.val1,s.val2,s.val3 from tsource s left join tdest d
on s.keyval = d.keyval where d.keyval is null;

declare finished INTEGER DEFAULT 0;
declare keyv decimal;
declare v1 decimal;
declare v2 decimal;
declare v3 decimal;

DECLARE c1 CURSOR FOR select s.* from tsource s inner join tdest d on s.keyval = d.keyval;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;
open c1;

getvals: loop
fetch c1 into keyv,v1,v2,v3;

if finished = 1 then
leave getvals;
end if;

update tdest set val1=@v1,val2=@v2,val3=@v3 where keyval=@keyv;

end loop getvals;
close c1;

end

  Error de SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
  declare finished INTEGER DEFAULT 0;
  declare keyv decimal;
  declare v1 decimal;
  at line 13 */

When I delete this line the procedure is ok:

   insert into tdest
    select s.keyval,s.val1,s.val2,s.val3 from tsource s left join tdest d
    on s.keyval = d.keyval where d.keyval is null;

Any ideas why the insert into is giving me an error?

MySQL is particular about the order in which you do things in a CREATE PROCEDURE/FUNCTION statement. I'll be burned if I can find it in the docs right now, beyond a cursory mention under DECLARE Syntax, but the order MUST BE

Header
Declare variables
Declare cursors
Declare handlers
Do actual stuff

With your INSERT ... SELECT in place, you're attempting to declare variables after you've begun "doing stuff", which isn't allowed. Move the INSERT ... SELECT after the last of your DECLAREs, and things should work much better.


MySQL when a = 0, b = 0, but a  b (binary) Any idea of why this is so,

CREATE TABLE f AS
  SELECT b'000000001' AS a, x'01' AS b;

This creates two columns of

`a` varbinary(2) NOT NULL,
`b` varbinary(1) NOT NULL

However, when I run,

SELECT a=0, b=0, a=b, a<>b FROM f;
+-----+-----+-----+------+
| a=0 | b=0 | a=b | a<>b |
+-----+-----+-----+------+
|   1 |   1 |   0 |    1 |
+-----+-----+-----+------+

What's going on here? From the docs on The BINARY and VARBINARY Types,

  All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 bytes and spaces are different in comparisons, with 0x00 < space.

That explains why a<>b, but why does a=0, or b=0 there?

BINARY is a misnomer.  It really refers to a string of bytes; it does not refer to binary numbers, and it isn't a unique datatype.

Also, = and <> adapt to the datatype(s) involved:

SELECT a=0, b=0,    -- numeric comparisons
       a=b, a<>b    -- BLOB comparisons; length matters
    FROM f;

It is a combination of the Operator and the Datatypes that controls what string vs numeric operations.

Some info on datatypes:

BINARY / VARBINARY / BLOB can be manipulated with "bit" operators | & ~ ^ -- not to be confused with logical operators AND (&&) and OR (||).  BLOB-like columns are used for non-character strings, such as images.  MySQL 8.0 stretches these operators beyond a 64-bit limit.  INT can also be manipulated with bit operators.

The BIT(m) datatype is prefixed with leading zeros if needed to fill out to full byte(s).  (Limit 64 bits.)  This datatype is almost never used.

BINARY(1) and BINARY(22) are 1- and 22-byte datatypes.  CAST(1 AS BINARY(1) converts the first 1 to a string "1", then stuffs in hex 31 (ascii code for "1").  Try SELECT HEX(CAST(1 AS binary(1))), HEX(CAST("1" AS BINARY(1))); -- both yield 31.


Oracle Temp Table Space - Alias clean up ORA-01119 How to drop the temp table space including alias.

SQL> CREATE TEMPORARY TABLESPACE TEMPRM_TEMP;
SQL> ALTER  TABLESPACE TEMPRM_TEMP ADD TEMPFILE '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf' SIZE 2M;
SQL> DROP TABLESPACE TEMPRM_TEMP INCLUDING CONTENTS AND DATAFILES;
SQL> CREATE TEMPORARY TABLESPACE TEMPRM_TEMP;
SQL> ALTER  TABLESPACE TEMPRM_TEMP ADD TEMPFILE '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf' SIZE 2M
         *
         ERROR at line 1:
         ORA-01119: error in creating database file
         '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf'
         ORA-17502: ksfdcre:4 Failed to create file
         +TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf
         ORA-15005: name "rm/datafile/TEMPRM_TEMPFILE_1.dbf" is already used by an
         existing alias

Please explain, how to clean Alias in Oracle ASM  ?
thanks

Resolved by removing Alias

ASMCMD> rmalias '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf'


How can I count distinct columns inside a group? I have the following query:

SELECT DISTINCT name, surname, id_number, gender FROM table1;

How do I filter by gender to get the gender breakdown of the results of the above query? Do I use a subquery? If so, how do I write the subquery?

Note in my table there are duplicate entries for id_number

Sample Data:

-------------------------------------
Name    | Surname   |ID_No   |Gender|
-------------------------------------
John       Doe        1234A6    M
Jane       Doe        2553B7    F
Steve      Jobs       2636A8    M

Expected Output

--------------
  M   |   F  |
---------------
  2       1

I'm pretty sure that this will work for you.  I don't have a MySQL instance available to me to check my syntax, but this should get you close.

SELECT gender, COUNT(id_number)
FROM (  SELECT DISTINCT
            name
            , surname
            , id_number
            , gender
        FROM table1
        ) AS D
GROUP BY gender;


Availability Group database stays in Reverting for too long after a failover Architecture:
I have 2 Node Sync-Commit AlwaysOn configuration running on Multi-Subnet Failover Cluster. Primary node is in Europe and Secondary node is in US. I have only one database in the Availability Group which is OperationsManager db of SCOM.

Primary and Secondary Hosts are identical.
SQL Server Version on both boxes: 13.0.5237 & Windows Core
Update: I patched both servers to 10.0.5270.0 , it didn't help.
DB VLF count is only 27.

Problem:
When I initiate a failover, database fails over from Primary to Secondary node successfully in seconds. However, new secondary(old primary) database goes into Reverting / In Recovery phase and stays there for 30 minutes approximately.I also experienced same thing while failing back to original primary box so it's a problem which occurs both ways.

Findings:
I searched about this on internet and read documentation to investigate the issue. When role change from Primary to Secondary is finished, new secondary database goes through 3 phases:

Synchronization State: “NOT SYNCHRONIZING” ; Database State: ONLINE

Synchronization State: “NOT SYNCHRONIZING” ; Database State: RECOVERING

Synchronization State: “REVERTING” ; Database State: RECOVERING

In my case, all time has spent on last step. I also monitored the undo process by looking into perfmon counter "SQLServer:Database Replica Log remaining for undo"

I checked primary site before failover tests to spot any long running transactions or open transactions but couldn't find one. After failover, "Log remaining for undo" was around 30MB and it took 30 minutes for secondary database to go back to "Synchronized" state. When taking into consideration that we are running in Sync-Commit mode and there is a little workload on primary, it shouldn't take 30 minutes for redo phase imho.

SQL Server Error Log: I found this strange messages.

Remote harden of transaction 'RECEIVE MSG' (ID 0x000000004d52c65a 0001:01c4e415) started at Feb 22 2019  2:55PM in database 'OperationsManager' at LSN (2558:107841:1) failed.
Remote harden of transaction 'GhostCleanupTask' (ID 0x000000004d6d15aa 0001:01c4eaa0) started at Feb 22 2019  2:59PM in database 'OperationsManager' at LSN (2558:107843:46) failed.

Failover starts:

Failover ends:

All in all

Have you ever seen this problem before? Do you have any recommandations?

One thing to check whenever database recovery is running long, whether it's a normal restore or an AG failover, is your VLF count.  Having lots of VLFs (thousands or tens of thousands), or VLFs of unusual size (one or two extremely large VLFs) will cause this process to slow to a crawl.

Run the following command on your the database in question:

USE YourDatabaseName;
GO

DBCC LOGINFO;

Note: if you're on SQL Server 2016 SP2 or newer, you can use this dynamic management function instead of the DBCC command: sys.dm_db_log_info

The number of rows that comes back is the number of VLFs you have.  If that number is very large, or if the FileSize column shows extreme outliers among your VLFs, then you can likely resolve the slow recovery problem by (at a high level):

shrinking the log file as small as possible
growing it back out to its target size
making sure autogrowth is set to a reasonable number based on your typical log growth rate and the frequency of your transaction log backups

The details of fixing VLF sizing issues have been covered extensively elsewhere, here is one example: A Busy/Accidental DBA’s Guide to Managing VLFs


How to remove secondary file group We have a SQL Server database with two filegroups; PRIMARY and SECONDARY, with a single file in each file group.

All the clustered indexes are stored in the secondary filegroup. How can we remove the SECONDARY filegroup?

The clustered index structure is the table in SQL Server.
In order to remove a filegroup, you first need to remove all data from the filegroup.  There are several ways of doing that, including using the GUI, like this:

Right-click the table, and choose "Design"
Right-click the "empty" area of the top design panel, and choose "Properties" like this:

In the "Properties" pane (typically shown to the right of the "Design" pane), you open the "Regular Data Space Specification" and change the name of the "Filegroup or Partition Scheme Name" to the name of the target filegroup:

Be aware, this method will result in SSMS generating code that actually drops-and-recreates the table.
An alternate method of doing that would be to rebuild the indexes for the table, specifying the target filegroup in the rebuild command.  I've written a blog-post showing the code I use to accomplish that over at SQLServerScience.com


Can SSRS XML data sources contain spatial data? I've been given the task of producing a report which will extract data from a web service.

The web service provides data from a list of sites including aggregates. Lets say total sales by month.

Id like to plot these values on a map and locate them using geometry points.  The developer is investigating how he can include the spatial reference in the xml reply.

But before i get him change the Web service is there any limitation or restriction in ssrs that would stop this working?

If so, what are my alternatives? If i can retrieve the easting and northing in the xml can i convert to a spatial datatype with an expression on the fly?

As far as i have been able to read/research it seems that the answer is no.
However i have found that I  can merge 2 datasets to achieve what I wanted to do.


How to prevent data loss during intra-site failover We are looking to build a MS SQL server (either SQL server 2016 standard or enterprise edition) solution but are having some troubles. This post can be lengthy and if you think it is TLDR, our question is simply as:

Given two physical sites, each site has two SQL servers with requirement that at any given time application is able to access one of its local SQL server instance to write/read data. Then the data need to be synchronized cross site and all four servers will contain synchronized data.  Which SQL server’s HA solution or replication solution or combination of both should we pick to achieve such result?

Starting here I will be trying to explain what we have tinkered with and the issues we are running into. We use SQL server 2016 developer edition for our testing. Note that it is treated as if it is a standard edition as of now.

Set up:

Two SQL servers per physical site are joined together in a Basic High Availability group with one being primary and one being passive secondary. Then we wrote some T-SQL script based on this link to set up bidirectional replications on the database level. For simplicity, let’s say we have Server 1 and Server 2 on physical site A and Server 3 and Server 4 on physical site B. Initially, Server 1 is primary on site A and Server 3 is primary on site B. The two are also syncing with each other which means whatever the change is written to Server 1’s database is replicated over to Server 3’s database and vice versa.

Issue:

So after the set up, we tinkered with intra-site fail over. For example, manually failing over from Server 1 to Server 2 at site A. what we observed is that during this failover, if any data in a table was modified or added from site A or site B. After we re-run our script to clean up orphaned replication and re-setup bidirectional transactional replication between Server 1 and Server 3, the data added/modified during intra-site failover will not be synced. For example, both Server 1 and Server 3 touched a table. Server 1 inserted a new entry Test 2 into the table and Server 3 inserted a new entry Test3 into the table.

Server 1 table view

Aaa    Bbb
Test1  Test1
Test2  Test2

Server 3 table view

Aaa     Bbb
Test1  Test1
Test3  Test3

After the bidirectional replication script is executed on both Server 1 and Server 3, the added data is not synched. However, changing on Test 1 will be reflected on the other server. This makes sense because the script or bidirectional transactional replication doesn’t know how to deal with tables that have different data?

Essentially during intra-site failover, there is a short time window that both database can be accessed without bidirectional transactional replication yet. As a result, when the bidirectional transactional replication is set up again, two database won’t be fully synched with each other. This is not acceptable because requirement needs all four SQL server instance to be fully synched with one another. Pleas advice on which HA solution or combination of different solutions we should deploy our servers with.

Sorry for the lengthy post since I wanted to provide as much information as possible and thank you for your help.

The crux of your issue is:

  Given two physical sites, each site has two SQL servers with requirement that at any given time application is able to access one of its local SQL server instance to write/read data. Then the data need to be synchronized cross site and all four servers will contain synchronized data. Which SQL server’s HA solution or replication solution or combination of both should we pick to achieve such result?

The "one size fits all" answer is, the current offerings that SQL Server has won't fit into this. SQL Server doesn't work well in a multi-master zero data loss game. Other database solutions have similar issues where multi-master has the potential (like SQL Server) for data loss. I don't believe you're going to get a single bullet one size fits all solution here from the database layer itself.

The only way, with SQL Server, to get what you're asking for it to create the application or middle tier around this philosophy and put the onus on the application/middle tier to make sure all databases are in sync and let the application/middle tier determine how to handle what happens when they aren't (for example, a write fails on one database but 3 the other databases successfully committed - do you proceed?).


Finding the longest entries for a given column (nvarchar(100)) I am trying to find the biggest / longest entries of a column which is of type nvarchar(100). I need to understand if I can maybe restrict that column to be only nvarchar(20) or something. Is there a way to do so? I know that I can't change the type to nvarchar(20) if I have any longer entries.

You can achieve this with this code snippet:

SELECT TOP 1 LEN(column), column
FROM yourTable
ORDER BY LEN(column) DESC

Just replace column with your column which holds the nvarchar(100)


Proper drive partitioning in Linux for Oracle I have a current production physical server running on RHEL and I'm going to be building a new Oracle Linux VM to take it's place. Below is a listing of the existing physical drive partitions.

Can anyone tell me why the previous DBA would have created all of these and how you might have created these differently if it was your VM that you were going to build?

It just seems to have more partitions than what I've seen in the past.

The other server looks like this:

It all looks fairly logical from a historical point of view.  You generally want to separate out your redo, data, exports, backups etc to separate disks if you can.  You may find that these are all mounted from different physical disks which is the 'correct' way of doing things (e.g. you don't want your data and redo on the same disks if you can help it).

However, from my point of view it's a fairly "old school" build.

For the Oracle 11gR2 environments that I am currently building, I only have the following Oracle specific mounts:

/u01/ (oracle homes)
+FRA (ASM) _or_ /FRA (depending on requirements)
+DATA (ASM)
/backups/ (so that Netbackup plays nicely with RMAN backupsets)

I could split it up more, but seeing as everything's coming from the same SAN device and I can't persuade my infrastructure team to spend the time ensuring that they're all coming from different LUNs/physical disks, I'm not too worried yet.


upgrading SQL Server 2017 Standard to 2019 Enterprise We are about to upgrade our SQL Servers from 2017 standard to 2019 enterprise.  Does anyone have any real world experience on the best way to do this?  We do not use HA, so our one prod server needs to be upgraded.  We have a "Staging" server that is a day old restore of the prod database so my first thought is to try to upgrade staging to 2019 and make sure everything works, then move Production to 2019 if there are no concerns.  Is the only "Backout" plan if we find something does not work that is a show stopper completely remove SQL on the prod server, reinstall 2017 and restore all system databases and user databases?  Is there a "Rollback" if there is an issue?  How have you done this?  upgrading to standard -> enterprise should not break anything since it just adds features, correct?

you are asking two things.

edition upgrade
version upgrade

edition upgrade is when you want to change from standard to enterprise.
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-to-a-different-edition-of-sql-server-setup?view=sql-server-ver15
It is quite easy, mount the sql image and in the menu select maintenance then edition upgrade. Try it on your staging server and go easely.
Version upgrade is when you change sql server version from 2017 to 2019. This is something you have to test carefully because can generate performance regression in query plans or other issues in appplication activities.
MIcrosoft suggest to evaluate it using this model:
https://docs.microsoft.com/it-it/sql/database-engine/install-windows/choose-a-database-engine-upgrade-method?view=sql-server-ver15
I suggest you to upgrade on a new server as a side-to-side migration, starting from DMA:
https://docs.microsoft.com/it-it/sql/database-engine/install-windows/prepare-for-upgrade-by-running-data-migration-assistant?view=sql-server-ver15
Then do some test migration to a new sql server 2019 env end test deeply applications.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly


Update categorywise in MySql I have a table for article that save the category for each article.
I want to update the sort order, but categorywise using a single query.
I am using this for data migration using bulk inserts and updated, so it looping the data is not a feasible solution.

id      category_id         sort_order
10          5                   1
11          5                   2
12          5                   3
13          6                   1
14          6                   2
15          6                   3
16          7                   1
17          8                   1
18          8                   2
19          7                   2
20          8                   3

Is it possible to do this in a single query?

I have written the query for update sequentially on all rows. But unable to do it categorywise. The query i have written is below

SET @so = 0;
UPDATE article A
INNER JOIN  category M ON A.category_id = M.id
SET A.sort_id = (@i := @i + 1)
WHERE A.user_id = 1;

SQLFIDDLE

This is rather tricky to do in MySQL compared to other DBMS:s. One way to try to overcome the limitations in MySQL, is to use user-defined variables. I must admit I find them almost counter-intuitive to use, so there may be errors in here:

update article a
join (
    select case when @category <> category_id
                then @rownum := 1
                else @rownum := @rownum+1
           end rownum, id
         , case when @category <> category_id
                then @category := category_id
           end
    from article b
    cross join (select @category := -1) c
    order by b.category_id, b.id
) as r
    on r.id = a.id
set a.sort_order = r.rownum;

The idea here is that we traverse category according to order by b.category_id, b.id. We initialize @category to a category that does not exist (select @category := -1) c. Every time that category changes, we reset rownum to 1, if category is unchanged we increase rownum. The result:

http://sqlfiddle.com/#!9/aa754b/1/0

I would probably look for other solutions if I were you. Ideas you may want to try is to use a cursor, I would even consider doing the update in a host language a viable option.


How to store formula method in the table I am trying to figure out how to store formula method in the table (I could create category_formula table).

Each category will have different way of formula method to calculate the point and I need to assign formula method on each category. What the best way to do this?

For example:

CPU(category_id=1), to calculate the point the formula would be: (commision + fulfilment + bonus) - (cost) / 10

Hard Drive(category_id=2), to calculate the point the formula would be: (commision + cost) / 10

And formula would be different for other categories.

mysql> select * from category;
+----+------------+
| id | name       |
+----+------------+
|  1 | CPU        |
|  2 | Hard Drive |
+----+------------+

mysql> select * from items;
+----+-------------+-------------------------+--------+-----------+------------+-------+
| id | category_id | name                    | cost   | commision | fulfilment | bonus |
+----+-------------+-------------------------+--------+-----------+------------+-------+
|  1 |           1 | Intel CPU Core i7 3770K | 300.00 |     30.00 |       15.5 |    10 |
|  2 |           2 | 160GB Samsung Spinpoint |  50.00 |     15.00 |          0 |     0 |
+----+-------------+-------------------------+--------+-----------+------------+-------+
2 rows in set (0.00 sec)

Update:
I would use PHP to do the calculation. For example Admin on the website can select a formula setting on each category and save it. The formula setting could be saved in category_formula table but how category_formula table should be designed?

Create:

--
-- Table structure for table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`id`, `name`) VALUES
(1, 'CPU'),
(2, 'Hard Drive');

-- --------------------------------------------------------

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `cost` decimal(6,2) NOT NULL,
  `commision` decimal(6,2) NOT NULL,
  `fulfilment` float NOT NULL,
  `bonus` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `category_id`, `name`, `cost`, `commision`, `fulfilment`, `bonus`) VALUES
(1, 1, 'Intel CPU Core i7 3770K', '300.00', '30.00', 15.5, 10),
(2, 2, '160GB Samsung Spinpoint', '50.00', '15.00', 0, 0);

You could create a view to select from and return the value the calculated value.

This is the SQL to create the view for the CPU formula:

CREATE VIEW cpu_value
AS

SELECT id, name, cost, commission, bonus,
((commision + fulfilment + bonus) - (cost) / 10)) AS value
FROM items WHERE category_id = 1;

This would be the one for the HDD:

CREATE VIEW hdd_value
AS

SELECT id, name, cost, commission, bonus,
((commision + cost) / 10)) AS value
FROM items WHERE category_id = 2;

To query them you would just do:

SELECT value FROM cpu_value WHERE id = 1;

this would perform the formula for the specific cpu with id = 1.

If you wanted to recombine them you can always create an additional view that merges the two views back together.


What is the best way to store an email address in PostgreSQL? What would be the right datatype to store email addresses in PostgreSQL?

I can use varchar (or even text), but I wonder if there is a more specific data type for emails.

Custom DOMAINs

I don't think using citext (case-insensitive) is enough[1]. Using PostgreSQL we can create a custom domain which is essentially some defined constraints over a type. We can create a domain for instance over the citext type, or over text.

Using HTML5 type=email spec

Currently the most correct answer to the question what is an e-mail address is specified in RFC5322. That spec is insanely complex[2], so much so that everything breaks it. HTML5 contains a different spec for email,

  This requirement is a willful violation of RFC 5322, which defines a syntax for e-mail addresses that is simultaneously too strict (before the "@" character), too vague (after the "@" character), and too lax (allowing comments, whitespace characters, and quoted strings in manners unfamiliar to most users) to be of practical use here. [...] The following JavaScript- and Perl-compatible regular expression is an implementation of the above definition.

/^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$/

This is likely what you want, and if it's good enough for HTML5, it's probably good enough for you. We can make use of that directly in PostgreSQL. I also use citext here (which technically means you can simply the regex a bit visually by removing either the upper-case or lower-case).

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

Now you can do...

SELECT 'asdf@foobar.com'::email;

But not

SELECT 'asdf@foob,,ar.com'::email;
SELECT 'asd@f@foobar.com'::email;

Because both of those return

ERROR:  value for domain email violates check constraint "email_check"

Because this is based on citext too

SELECT 'asdf@foobar.com'::email = 'ASdf@fooBAR.com';

returns true by default.

Using plperlu/Email::Valid

As an important note, there is a more correct method of doing this that is far more complex using plperlu. If you need this level of correctness you do not want citext. Email::Valid can even check if the domain has an MX record (example in docs of Email::Valid)! First, add plperlu (requires superuser).

CREATE EXTENSION plperlu;

Then create the function, notice we mark at as an IMMUTABLE:

CREATE FUNCTION valid_email(text)
  RETURNS boolean
  LANGUAGE plperlu
  IMMUTABLE LEAKPROOF STRICT AS
$$
  use Email::Valid;
  my $email = shift;
  Email::Valid->address($email) or die "Invalid email address: $email\n";
  return 'true';
$$;

Then create the domain,

CREATE DOMAIN validemail AS text NOT NULL
  CONSTRAINT validemail_check CHECK (valid_email(VALUE));

Footnotes

Using citext is technically wrong. SMTP defines local-part as being case sensitive. But, again, this is a case of the spec being stupid. It contains its own identity crises. The spec says local-part (the part before the @) "MAY be case-sensitive" ... "MUST BE treated as case sensitive" ... and yet "exploiting the case sensitivity of mailbox local-parts impedes interoperability and is discouraged."
The spec for an email address is so complex, it's not even self-contained. Complex is truly an understatement, those making the spec don't even understand it.. From the docs on regular-expression.info

  Neither of these regexes enforce length limits on the overall email address or the local part or the domain names. RFC 5322 does not specify any length limitations. Those stem from limitations in other protocols like the SMTP protocol for actually sending email. RFC 1035 does state that domains must be 63 characters or less, but does not include that in its syntax specification. The reason is that a true regular language cannot enforce a length limit and disallow consecutive hyphens at the same time.


What's the latest on MySQL Proxy? Does somebody here know the latest state of the development of MySQL Proxy?
Most materials I found on web are outdated.

From an email on the MySQL lists sent earlier this month, Shawn Green (a MySQL Principal Technical Support Engineer) reports:

  Thanks to the corporate changes of the last few years, the sole developer has been too busy doing his primary job to really put any more time into the project and no person or group from the community has stepped forward to help push the project to a final deliverable state.

So I suppose the latest state of development is it's on hold.


How to store data in a database without tables? All I learned in school was SQL which saves data to tables.
Right now I am working on a project where data is stored in XML-files. Additionally every XML contains a reference to visual files (JPEG).

The XML itself contains over one thousand coordinate points, plus additional information on the data.

In my opinion it would make no sense to store this information in tables. Besides I couldn't store JPEG-files with SQL either.

What would be appropriate solution, or is there an error in reasoning on my side?

As you can see I am pretty new to databases. So any constructive suggestions, links and advice is welcome.

All you need is persistence of your XML. Use a NoSQL solution or the file system.

There is no benefit in using an RDBMS, unless you want to use it instead of NoSQL or the file system.


Database table data transfer between different servers using bcp.exe and mysqldump I have 2 servers, both are exact clones of another, one is PRODUCTION live use and the other is strictly for backend DATAENTRY.

Using MSSQL on two different servers I am using the following command lines to transfer table data between servers...

Export table data from DATAENTRY:

bcp.exe "SELECT * from database.dbo.table WHERE Modified > '{MM/DD/YYYY} 00:00:00.000'" queryout C:\export\dbtabledump.bcp -n -U{username} -P{password}

##FTP upload file: dbtabledump.bcp to PRODUCTION##

Import table data into PRODUCTION:

bcp.exe database.dbo.table in "C:\import\dbtabledump.bcp" -n -U{username} -P{password}

Now I would like to trim down the DATAENTRY server to the bare essentials and use LAMP instead of the current WIN/IIS/MSSQL combo. Database schema will be identical on both platforms.

Here are my following quesitons:

What is the equivalent of MSSQL BCP.EXE in MYSQL?

Currently I have found mysqldump command

Is the exported mysqldump file compatible with MSSQL BCP.EXE?
If not, how do I prepare the MYSQL table data dump file for MSSQL BCP.EXE to import?
I'm open to any other ideas, is there a more elegant solution to my workflow?

No you can't take the output of mysqldump and use it with bcp.  Mysqldump creates a SQL Script which can then be run.  If the schema is the same you can probably just run the outputted script using sqlcmd.


Do I need GRANT for INSERT to table If has EXECUTION GRANT for procedure? I have five domain users and five roles in my database. GRANT EXECUTE adds to the appropriate roles. Roles are included in other roles, i.e. at most we have one role that has grants for two procedures, below two more roles that have access to four and three procedures, and finally we have two roles. With this solution, if I give the GRANT EXECUTE permission to the highest role, all roles will have access to this procedure.
My question:
Let's say the procedure has UPDATE and INSERT operations. Is it enough to GRANT EXECUTE to a given procedure for a given role so that the user can use this procedure and actually do UPDATE and INSERT on the table, or maybe apart from GRANT EXECUTE I need to grant the role UPDATE and INSERT to all tables within the given procedure?

Permissions on objects used by the stored procedure are not checked with an unbroken ownership chain. Consequently, there is no need to grant permissions on objects used by stored procedures when all objects involved have the same owner, which is typically the schema owner. Users need only stored procedure execute permissions in your case, which includes permissions inherited via role membership.
This assumes no dynamic SQL within stored procedures and objects reside in the same database. See the authorization and permissions documentation for details.


Mysql reliable with 1000 new entries / minute? I have been developing an application that in the WORST case writes 1000 entries each minute into a database for over a year...

I wanted to use Mysql as DB, but I have read that with high datatransfers it becomes unreliable when writing. Is this true? Is 1000 entries considered a high amount of data? What would be such a high amount of data? Would corrupt data mean that I miss one entry or that I lose the whole table?

Thanks

Prrffzzt. No, this isn't exactly high write load.

You should test yourself with your own data, of course. It depends on the kind of write query, the size of the table, the type of keys you're using. Explain  is your friend.

We have used MySQL as a backend for a major social networking website (high write load) without reliability problems. You can build impressive setups (as in: racks of servers just running the database) with MySQL.

You should consider MariaDB since many of the original MySQL developers moved there. You should also consider hiring a consultant if you are really worried about optimization and if your current solution doesn't scale.


Join two tables and create a new one in SQL I am new to SQL and trying to apply a concept of Joins in MS-access.

I have two tables Table 1 and Table 2:

I want a query which will create a Table 3:

Ideally i want all the data from both tables but wherever the ID's are matching in both tables, the category should become '3'

Can someone please help me with this query. Thanks in advance.

I have attached the samples for your reference

You need to UNION ALL the two table entries
Then take the SUM(Category) by GROUP BY ID, Name

So the query will be

SELECT ID, Name, SUM(Category) AS Category
FROM  (
    SELECT ID, Name, Category FROM Table1
    UNION ALL
    SELECT ID, Name, Category FROM Table2
) GROUP BY ID, Name

You can insert the entries into the third table.


How to Fix log_reuse_wait_desc=LOG_BACKUP? LOG Backup not possible I checked sys.databases to find out which databases need LOG backups. It surprised me that I had quite a few where log_reuse_wait_desc=LOG_BACKUP meaning, the JOB I have set up to run LOG backups on these databases isn't actually creating LOG backup files.

After doing some searching, it had been suggested to change database modes from FULL to SIMPLE, then back to FULL to reset things. After doing this I am still unable to get a LOG backup to successfully run.

I am using SQL Server 2005, and use the ola hallengren method of backup found here http://ola.hallengren.com/sql-server-backup.html.

Thank you

Now that you've changed the recovery model on the database from full to simple and back to full again, the database is running in 'pseudo-simple' mode. Taking a database backup is required now before a log backup will do any meaningful work. See here for some further details. To summarize:

In order for a log backup to be useful for a restore, there needs to be a database backup along with log backups that cover all the LSNs since the time of that database backup.
When you change the recovery model to simple, SQL Server breaks the log backup chain. Then when you change it back to full, the database continues to act like it is in simple mode until someone creates a new log backup chain by taking a database backup. This is known as pseudo-simple mode. The reason this is implemented this way is to prevent someone from taking log backups that could never be used for recovery because they aren't part of a log backup chain that begins with a database backup.
Once a database backup is taken, a new log backup chain begins and the database begins functioning in the full recovery model. At this point your log backups should work, or at least fail for another reason with a more descriptive error.

This will be at least one reason why your log backups aren't functioning now.


The actual cause of transaction log full under below scenario in one of the instance, it shows

The transaction log for database 'DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The instance has at least 100g harddisk empty storage and the transaction log allowed 2,097,152MB (2T) to grow, growth rate at 10%.

The database recovery mode is FULL (at least it displays FULL in the SSMS)

In this case, what can cause the exception of full transaction log?

When the transaction log have enough physical storage to grow and the log size is not bigger than the upper bound

First of all do what the message says and run

SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'your db name'

I'd also check sys.database_files in your database to confirm the actual settings for the transaction log file.

Once you have the reuse identifier then check it against the values at https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017


Computed column with less size than the "referring column" I have a column with data type nvarchar(max) and I wold like to index this. However, since it's too big it's not possible. So I figured I could create a persisted computed column based on that column with the formula:

left(isnull([fieldValue],''),500)

However this column also gets a data type of nvarchar(max) so I can't create an index for it. Is it possible to index it somehow without using a full-text index?

Just do a simple cast in your computed column:

create table YourTable
(
    -- your other columns....
    YourCompCol as cast(left(isnull(fieldValue, ''), 500) as nvarchar(500))
)
go

create index IX_CompCol
on YourTable(YourCompCol)
go

Nota Bene

The maximum key length is 900 bytes, and nvarchar(500) has a maximum length of 1000 bytes.  In other words, an INSERT or an UPDATE could potentially fail.


Need for recursive lookup, stuck in query design In PostgreSQL 9.4, here is my table:

with r(pk, userid,partneruserid) as
(values
(1,1,2),
(2,1,3),
(3,1,6),
(4,2,5),
(5,2,6),
(6,3,1),
(7,4,1),
(8,5,3),
(9,6,2),
(10,6,3)
) select * from r;

In a table that stores pairs of user id, i.e. two integer columns (user id and partner), we are requested to find this kind of relations:

User m and user n in the same record
(both cases userid = m && partneruserid = n and  userid = n && partneruserid = m)
indirect partnership with a third X partner in common (in either two columns)
more indirect partnership with a third and a fourth X and Y partner in the chain which are partners between them: m .. x .. y .. n
So on and so forth until there are no more relationships or until the nest level is 5, meaning relation with five steps like:

  users m .. x .. y .. z .. a .. b .. n (always containing the cases in either two columns).

In this table, if we ask for the relationship between users 1 and 3, the function should return:

Approach 1

first, the obvious row 2: 1-3 (single record, no hops)
Result set so far: [1,3]
and the not so obvious row 6: 3-1 (idem)
Result set so far:

[1,3]
[3,1]

then if we follow the paths from user 1 (which are users 2, 3 and 6) and are looking for a partner one hop from user 1, we would then reach rows in which partners of user 1 (user 2 and user 6) are in the record as partners of user 3. These rows could have been [2, 3], [3, 2], [6, 3] and [3, 6] if they existed, but only row 10 does, being the result set so far:

[1,3] [3,1] [6,3]

the next step would be search for rows that have user 2 and user 6 (first hop) partnered with partners of user 3 (1, 5 and 6) (this is the second hop), reaching: row 4 (1..row1..2..row5..6..row10..3), row 5 (1..row1..2..row4..5..row8..3) and row 9 (1..row1..2..row9..6..row10..3).
Result set so far:

[1,3]
[3,1]
[6,3]
[2,5]
[2,6]
[6,2]

then with these last rows added to the result set, we would need to find all users that are partners of user 3 of user 5 that are partners of user 3. But as users 2 and 6 take us back to the first iteration, only remains user 5 for exploring his partnerships with a partner of user 3 (1, 5 and 6), being reached no more rows, hence no more hops or indirect partnerships can be found.
The result set is still the same as the previous step.
here would be nice to have the quantity of partnerships are repeated allowing us to reduce the result set to:

[1,3] qty: 2
[6,3] qty: 1
[2,5] qty: 1
[2,6] qty: 2

That would be the desired result. I hope not have made a mistake trying to make sense. As I posted previously, any advice that points me in the right direction, would be much appreciated!

Approach 2 (cannot imagine how)

Get the direct partners of user 1 (2, 3 and 6)
Get the direct partners of user 3 (1, 5 and 6)
Make a tree of each, and look for points in common.

Data model

First of all: fold duplicate entries. I don't see why you would allow reverse duplicates to begin with, so your setup becomes:

CREATE TEMP TABLE tbl(
     pk serial PRIMARY KEY
   , userid int NOT NULL
   , partneruserid int NOT NULL
   , ct int DEFAULT 1 NOT NULL  -- this part is unclear
   , CONSTRAINT user_partner_uni UNIQUE (userid, partneruserid)
   , CONSTRAINT userid_smaller   CHECK  (userid < partneruserid)
   );

INSERT INTO tbl
VALUES
  (1,1,2,1)
, (2,1,3,2)  -- folded
, (3,1,6,1)
, (4,2,5,1)
, (5,2,6,2)  -- folded
, (7,1,4,1)
, (8,3,5,1)
, (10,3,6,1);

Recursive query

Building on the sanitized model (the constraint userid_smaller is not required for this query, though). This recursive query does basically everything you need:

WITH RECURSIVE  ids (a, z) AS (SELECT 1, 3)  -- enter values here
, cte AS (
   SELECT t.pk, CASE i.a WHEN t.userid THEN t.partneruserid ELSE t.userid END = i.z AS found
        , ARRAY[CASE i.a WHEN t.userid THEN t.partneruserid ELSE t.userid END, i.a] AS path
   FROM   tbl t, ids i
   WHERE  i.a IN (userid, partneruserid)   -- constraint user_id_smaller simplifies this task

   UNION ALL
   SELECT t.pk
        , CASE c.path[1] WHEN t.userid THEN t.partneruserid ELSE t.userid END = i.z AS found
        , CASE c.path[1] WHEN t.userid THEN t.partneruserid ELSE t.userid END || c.path
   FROM   cte c, tbl t, ids i
   WHERE  c.path[1] IN (t.userid, t.partneruserid)
   AND    CASE c.path[1] WHEN t.userid THEN t.partneruserid ELSE t.userid END <> ALL (c.path)
   AND    NOT c.found
   AND    array_length(path,1) < 4
   )
SELECT path, array_length(path,1) - 1 AS steps
FROM   cte c
WHERE  found;

SQL Fiddle

Except for the count. It's unclear how you count duplicate connections exactly. Strictly speaking, you should multiply ct with every step ...

Explanation

The adapted table forces userid to be the lower ID, so the initial predicate can be simplified: userid matches lower ID, partneruserid matches higher ID. If your input is parameterized, you can sort with LEAST (x,y) and GREATEST(x,y) Update: That shortcut only works for short-circuiting in a single step. We need to consider both IDs as starting point for longer paths. Query fixed.

I pick the lower input value (a) as start (could be the other way round) and walk the graph of partnerships until I reach the higher input value (z). Each step can connect to userid or partneruserid, which necessitates the CASE expressions: the match could be on either column, we need to continue with the other column respectively.

We have to remember the path so far (path) and rule out repetitions so not to walk in circles (... <> ALL (c.path)).

I save the last node of the path as first element (path[1]), just to avoid another column for that information. Either way may be more convenient / faster ...

Stop traversing the graph when z is reached (found) or when a given maximum length for the path has been exhausted (array_length(path,1) < 4).

Only rows where z is reached (WHERE  found) are in the final result set. I only collect the bare minimum of information along the way. You can add more columns to the query or / and  JOIN to the original table in the outer query to return more fields.


SQL Server Error - The connection is broken and recovery is not possible I have a .NET application that connects from A Windows 10 client to an SQL Server 2016 Express database running on Windows Server 2012 R2. Whilst a connection is successfully established, it is frequently throwing the following error on subsequent connection attempts:

"The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts."

I have researched this and have not been able to get to the bottom of why this is suddenly occurring after the system has been stable for months.

It would appear that the cause of this was that the server was stuck in a perpetual reboot loop and was losing and regaining connectivity. Power cycling the server resolved the issue.


Deduplicate SELECT statements in relational division I have a query that does a lot of duplicate work:

SELECT visitor_id, '1'::text AS filter
FROM events
WHERE id IN (SELECT event_id FROM params
             WHERE key = 'utm_campaign' AND value = 'campaign_one')
  AND id IN (SELECT event_id FROM params
             WHERE key = 'utm_source'   AND value = 'facebook')
GROUP BY visitor_id

UNION ALL

SELECT visitor_id, '2'::text AS filter
FROM events
WHERE id IN (SELECT event_id FROM params
             WHERE key = 'utm_campaign' AND value = 'campaign_two')
  AND id IN (SELECT event_id FROM params
             WHERE key = 'utm_source'   AND value = 'facebook')
GROUP BY visitor_id

As you can see, it performs different filtering on the params table 4 times. I'm using Redshift and although it scans this table very quickly, I have quite a few of these statements UNIONed together. Is there a way to rewrite the SQL using CASE/IF statements?

The example uses key = 'utm_source' AND value = 'facebook' in both, but this is not necessarily true for all selects.

I see a possible minor simplification with a CTE (available in Redshift) for the repeated subquery with identical predicates:

WITH p2 AS (
   SELECT event_id
   FROM   params
   WHERE  key = 'utm_source' AND value = 'facebook'
   )
SELECT e.visitor_id, '1'::text AS filter
FROM          p2
JOIN   params p1 USING (event_id)
JOIN   events e ON e.id = p2.event_id
WHERE  p1.key = 'utm_campaign' AND p1.value = 'campaign_one'
GROUP  BY e.visitor_id

UNION ALL
SELECT e.visitor_id, '2'::text AS filter
FROM          p2
JOIN   params p1 USING (event_id)
JOIN   events e ON e.id = p2.event_id
WHERE  p1.key = 'utm_campaign' AND p1.value = 'campaign_two'
GROUP  BY e.visitor_id;

Plain joins may also be faster than multiple IN semi-joins.

This multicolumn index should allow index-only scans on params:

CREATE INDEX foo_idx ON params (key, value, event_id)

Add another index on just (event_id) if you don't have one, yet.

Arsenal of available query techniques for relational division under this related question on SO:

How to filter SQL results in a has-many-through relation

Avoid multiple SELECTs

As commented by @Andriy, we can squeeze out some more:

WITH p2 AS (  -- repeated, immutable filter
   SELECT event_id
   FROM   params
   WHERE  key = 'utm_source' AND value = 'facebook'
   )
, p3 (value, filter) AS (  -- values for variable filter
   SELECT text 'campaign_one', text '1'
   UNION ALL SELECT 'campaign_two', '2'
   )
SELECT e.visitor_id, p3.filter
FROM          p3
JOIN   params p1 USING (value)
JOIN          p2 USING (event_id)
JOIN   events e ON e.id = p2.event_id
WHERE  p1.key = 'utm_campaign'  -- repeated for p1
GROUP  BY 1, 2;

In Postgres we could use a shorter, faster VALUES expression, but that feature is not currently supported in Redshift:

...
, p3 (value, filter) AS (
     VALUES
     (text 'campaign_one', text '1')
   , (     'campaign_two',      '2')
   )
...

For just two SELECT in the UNION, this won't buy much. But it should be a substantial improvement for more - like you mentioned.

2nd query without CTE

The CTE is not necessary for the second query. You can simplify to:

SELECT e.visitor_id, p3.filter
FROM  (
   SELECT text 'campaign_one' AS value, text '1' AS filter
   UNION ALL SELECT 'campaign_two', '2'
   ) p3                            -- values for variable filter
JOIN   params p1 USING (value)
JOIN   params p2 USING (event_id)
JOIN   events e ON e.id = p2.event_id
WHERE  p1.key = 'utm_campaign'     -- repeated, immutable filters
AND    p2.key = 'utm_source'
AND    p2.value = 'facebook'
GROUP  BY 1, 2;

About CTEs

Resources for Common Table Expressions (as requested in the comment):

The manual.
Tag info for CTE.

Data-modifying CTEs are particularly useful. Example:

How do I insert a row which contains a foreign key?

Basics with an added advanced example on this related answer on SO:

Are SELECT type queries the only type that can be nested?


Postgres primary key sorted in the reverse order. Will it use the index efficiently? My Rails database is backed by Postgres database.  As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed.

My list views show records in the reverse chronological order.  So I just sort my result set in the reverse order of the primary key.

My question is: will the query use the primary key index?  And if yes then as efficiently?  How do I verify that?

Thanks in advance for your time.

Bharat

From Postgres documentation: Chapter 11. Indexes (note that the same holds even for really old versions like 8.3 chapter 11. Indexes):

  By default, B-tree indexes store their entries in ascending order with nulls last. This means that a forward scan of an index on a column x produces output satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The index can also be scanned backward, producing output satisfying ORDER BY x DESC (or more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST is the default for ORDER BY DESC).

Now, whether this index will be used for a particular statement, it really depends on the statement. For a query that returns the whole table or a large part of it:

SELECT *
FROM tablex
ORDER BY tablexID DESC ;

it will have to scan the whole table anyway, so the optimizer may decide that it's cheaper to just read the whole table and then sort it in descending order (and not use this index).

If it's a query like this though, I would bet that it would use only the index (because it doesn't need any other data but the IDs that are stored in the index):

SELECT tablexID
FROM tablex
WHERE tablexID <= 5000
ORDER BY tablexID DESC ;

So, whether an index will be used or not, really depends on the statement that you are executing, the cache settings, the types of joins, the conditions you have, all the indices that are available/relative, the statistics of the tables and indices (sizes, distribution, cardinality, etc), the version of Postgres (different version means different optimizations), the phase of the moon and possibly a lot of other factors I'm forgetting.

To check which indices, if any, will be used for particular statement at a particular time, you can see its execution plan using EXPLAIN:


Disabling SSL mode to AzureSQL Is it possible to disable SSL/TLS on connections to AzureSQL? I have a driver which cannot handle SSL.

I know it is a terrible idea BTW since the traffic would then be open to snooping around.

Can't be done. Azure SQL DB will not accept a non-secure connection. There's no configuration parameter you can set to change this behavior. This is documented in multiple places (e.g. https://technet.microsoft.com/en-us/library/hh351833.aspx). Also, it's not just about the traffic getting sniffed. Azure requires certificate validation also to protect against mitm attacks. Without these, the risk includes credential theft, not just snooping of your traffic.

Btw, what driver are you using? I'm surprised there are drivers that support Azure SQL DB but cannot do it with a secure connection (it's actually TLS, SSL has long been disabled). If you provide more details, folks here might have alternate solutions for your unsafe driver.


Remote connection issue from windows machine I have installed database 11g on machine A (Windows). Now I need to access that database from machine B (Windows), where my application is placed. I am able to ping B --> A but telnet is not working. Can someone help me on this. Some help will be really appreciated. Below are few files for reference.

Below are few things which I have already tried:

Disabled firewall.
Opened ports using inbound and outbound rules.
Started the services.

listener.ora

# listener.ora Network Configuration File: C:\app\rajhegde\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\rajhegde\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\rajhegde\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rajhegde-lap2)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER = C:\app\rajhegde

tnsnames.ora

# tnsnames.ora Network Configuration File: C:\app\rajhegde\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rajhegde-lap2)(PORT = 1522))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rajhegde-lap2)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Output of lsnrctl status

C:\Users\rajhegde>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 09-SEP-2015 18:56
:36

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                09-SEP-2015 17:40:03
Uptime                    0 days 1 hr. 16 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\rajhegde\product\11.2.0\dbhome_1\network\admin\
listener.ora
Listener Log File         c:\app\rajhegde\diag\tnslsnr\RAJHEGDE-LAP2\listener\al
ert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=RAJHEGDE-LAP2.oradev.oraclecorp.com)
(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Your LOCAL_LISTENER parameter is set to listener_orcl, which is defined in your tnsnames.ora as being on localhost port 1522. But your listener is on RAJHEGDE-LAP2.oradev.oraclecorp.com port 1522, so the database is unable to register itself. You can change your tnsnames.ora entry to match the listener.ora definition. After the database is restarted, or you do alter system register, the lsnrctl status output will show your service name as registered as well.

But your SQL Developer connection isn't getting that far. You can't telnet to the listener port, which means either a firewall is present (which you've assured us is not the case), or you're running a virtual machine you haven't told us about (I'm suspicious about the .oradev.oraclecorp.com domain the listener is reporting), or you're connecting to the wrong IP address.

Check that RAJHEGDE-LAP2 on machine A and RAJHEGDE-LAP2.oradev.oraclecorp.com on both machine A and machine B, and whatever host name you are using for your telnet test and connection, all resolve to the same IP address. You can also use netstat on machine A to check which IP address is listening on port 1522. You may have more than one interface and the listener isn't on the one you expect - perhaps because of a mismatch between a static address in your hosts file and what's assigned by DHCP and/or in DNS, for example.


How to properly increment an int identity in SQL Server if the Identity Increment isn't set? When I use SQL Server with an int based identity column, I always use the auto increment.  But I'm needing to insert into an existing table that doesn't have the property set, and I'm wondering what is the best practices way of doing this.

The naive approach would be to query the data, increment it, and then use that for an insert.

For example:

INSERT INTO myTable
(
  ID,
  Data
)
Values
(
  (SELECT MAX(ID) FROM myTable) + 1,
  "My actual data."
)

But I'm unsure if this is actually the best approach.  Namely, since this is all one statement, is there a risk for a heavily utilized system to have another row inserted between selecting the MAX(ID) and the insert.

Edit:
Sql Server version 2012.

Edit 2: To specify, I'm looking for a DML solution as I do not have DDL rights to modify the table.

The best option is to use the SEQUENCE object, introduced in 2012. Since it is an independent object, you don't run the risk of querying it at the same time and retrieving the same value - it'll always provide the next in the chain.

Set the object with a specific start and increment value, then call it to get the next value desired. One of the biggest benefits here is that you can also specify a number of values to be cached for faster retrieval if you're working with rapid inserts.

CREATE SEQUENCE seq_obj
    START WITH 1050
    INCREMENT BY 1
    CACHE 1000
GO

Then to get the next ID value, use:

SELECT NEXT VALUE FOR seq_obj

Which will provide 1050, then 1051, and so on and so forth.

MS Docs for full information on other properties.


what is difference between database developer and administrator? i am a doing graduate degree, and i want to go in database field as i find this subject very interesting. For becoming the DBA ,is there a need of becoming developer first for graduate passout and career beginners.

At a masters level you should be pretty good at both

Administrator - DBA

install and configure hardware
maybe you say that is outside of DBA but most DBA can do hardware
install and configure database software
optimize the database configuration
users, groups, and security
backups
index maintenance
monitor
need basic SQL just to query systems tables and validate data
build tables and indexes

Developer

database design
write, queries, procedures ....
index planning and implementation
build tables
performance monitoring

Mix

if you get into partitions and other exotics the database design gets into hardware
index maintenance - if the DBA is going to do this need to understand indexes
data load - is this a DBA or developer?
if it is the DBA then need a lot of developer skills

At a master level should know at least one language / platform.  If you load data there going to be times you need to scrub it row by row.

In a large software shop you may have a developer on a programming team that really only does pure development.   Lets say you are running a packaged application like SAP you would have a DBA that only does DBA and not even have a developer.

Also have application developers that are also database developers.  For big high volume applications the back end developer(s) need to be part database developers.  For small apps that cannot afford a database developer the application developer has no choice but to do it all.

If you are a DBA expect to do some mail, web, group policy, and other system tasks at some point in you career.


Can I import a specific vssettings file when launching SSMS? In the interest of disambiguating between when I am connecting to non-production and production, I utilize vssettings for a specific theme in Management Studio (fire-and-brimstone style when in prod, calming blues in non-prod).

Previously our non-production access was via one account, and our production access was via a more restricted account.  With that method I was able to set a theme for each account one time and all was well.  Now all access is using the elevated account.

Is there a way to tell SSMS at launch time to use a specific theme?  Or a programmatic way to do this without going through "Tools > Import and Export Settings" every time?  I'm using the SQL 2014 version of SSMS.

Thanks.

I haven't played with themes, but you can set per-connection properties, including the color of the connection info bar at the bottom of the query tab. And these settings are saved.

If you have a query tab open, go to "Change Connection" (either the button in the button bar, or via the Query menu, top item should be Connection >, and when it pops up the "Connect to a Database Engine" dialog, click on the Options >> button. Go to the Connection Properties tab and the bottom option is Use custom color:. Check the check-box for it and click on the Select... button on the right.

Just use different colors for the different servers/environments.

Technically speaking, there is a means of programmatically interacting with SSMS: write a plug-in / extension. I will list some resources below that deal with this topic. I have not tried it yet myself, but it seems as though it might be a lot of effort for a one-time config update. On the other hand, if SSMS will not record the setting for 100, let alone 400, separate connections (not sure what the limit is), then maybe this would be worth the effort.

The art and science of creating SSMS 2012 add-ins – Part 1 – Laying the Foundation (this is a 3 part series with links to parts 2 and 3 towards the bottom; towards the bottom of part 3 are 6 links to other resources, the first of which I will duplicate as the next item)
How to write own add-in for SSMS 2012 (Final release version)
Automation and Extensibility Reference (for Visual Studio, but it should be very similar with SSMS, at least as of SQL Server 2012)


Store lat lon in addition to the geography data type? It's my first time using Postgis, I have been doing some reading on using it. I have added a geography data type to my table, but the values are stored in a binary format. Is it good practice to also store the lat and lon in separate columns as text or points??

The coordinates can be computed with ST_X/ST_Y/ST_Centroid, when needed.
If you just want to view the data, use ST_AsText or ST_AsEWKT.

You need separate columns only if there is some client software that needs these values, but is not able to use those SQL functions.


How mysql innodb know which transaction should rollback after crash restart? I know when I execute dml sql before commit , innodb will write the result to the innodb page cache which can flush to data page and write to the undo log file ; but at the same time , mysql crash and restart, how mysql innodb which transaction should rollback ?

That's all about journaling. Write is performed in three stages. First all changes are written along with the old data, then all references are relinked to the new values and if that two stages are passed ok then the whole transaction is marked as commited, all old links are purged and journal entry is removed. If engine at start find some uncommited transactions in the journal then all related changes are rolled back to the initial state.


Tracking Unintended Row Changes I have a custom eCommerce web site, using third-party web services, that is processing dues payments for our members. Twice in the last day a specific data row for 50,000 users is having two of the dollar amount columns change to $0 for all 50,000 rows. Just those two columns are updated, nothing else in the row changes.

We have reviewed our coding and SPs and can find nothing that would update those rows.

I have approximate times that the events occurred, how would I find out what was running on the SQL Server at the time that could have made the change? I have looked through the Windows Event and SQL Server logs and do not see anything out of the ordinary. So I'm assuming that the update is coming from an authorized user.

I realize I may not be able to see the past if I don't have logging set up properly. The SQL Server (12.0.5557.0) is very vanilla configuration. But any clue to looking at history or setting up for future logging would be greatly appreciated.

Brian,

Unfortunately the Windows event log and SQL log will not provide that information.

Fortunately there are several ways to audit a such activity. SQLShack has a good explanation on the various techniques and 3rd party tools

https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/

I had a similar situation at my work where I need to track some unintentional updates to a table that seemed to happen a couple of times a week.

I decided to use out of the box SQL Server auditing features. I was able to track down who, where and when certain DML changes were occuring..

You need to first setup a server audit then create a database audit..

You can tailor your database audit to "update" statements on your specific table.. Limiting the audit to your table and "update" statements will limit the amount of auditing taking place.

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-2017

You set up the server audit to log the activity to a file locally or somewhere on your network. Make sure the SQL Service account has write permissions to whatever location you choose.

You will have to use the function sys.fn_get_audit_file to read the audit file.

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql?view=sql-server-2017

After you have solved the issue you can simply disable or delete the server and database audit.


MySQL Cluster -- Storing table data on data nodes I have setup MySQL Cluster with the MySQL server node and the Management node on a single server, and 2 data nodes (each on a different server for 3 servers in all). After installing, I run ndb_mgm and run SHOW at the prompt and everything seems to look OK, with the following headers.

[ndbd(NDB)] 2 node(s)
[ndb_mgmd(MGM)] 1 node(s)
[mysqld(API)]   1 node(s)

When I run mysql -uroot on the mysql server and create a database and populate a table with data I notice that the database directory and table files are being created in the local datadir, not the datadir on my Cluster's data nodes.

I have set /var/lib/mysql-cluster/config.ini to point at the data nodes and their datadir. The /etc/my.cnf file looks like this on all 3 servers. I have hidden the DNS, both are the same

[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine
ndb-connectstring=domU-xx-xx-xx-xx-xx-xx #location of management server

[mysql_cluster]
# Options for ndbd process:
ndb-connectstring=domU-xx-xx-xx-xx-xx-xx #location of management server

Why is the mysql table data files being stored locally? Shouldn't the data files (.ndb and .frm files) be on the data nodes only?

Thanks!

everything is in order!

 The data nodes don't have individual files for each table and so you won't see the table names there. What you're seeing with the MySQL Server directories is not the files containing the contents of the tables but a copy of the data dictionary/schema definitions for those tables (e.g. if you add more rows to these tables then the files you see with the data nodes should increase in size but the ones with the MySQL Servers should not).

Regards, Andrew.