How to store JSON data from APIs and be able to search efficiently I am displaying documentation using Swagger, but the documentations are long so I made a side menu and split up the data in the database. Splitting it up, it allows me to search for specific areas and endpoints of the documentation and display it, and only display what is selected instead of everything, every time.

There are different versions of the documentation so some of the information overlaps. I am not sure the best way to store this, and currently, it is separated by version of the same documentation but there is a lot of redundancy since a lot of data overlaps from the previous versions.

Currently, the structure is similar to this:

table api_data

id
tags
general_path
path_content
product_id
search (keywords to index)
overhead_id
version_id

table api_data_overhead

id
product_id
file_overhead

table api_product_versions

id
active
product_id
name

The overhead for the file contains a lot of common information for every path for that documentation file. It is very long but doesnt need to be indexed or searched.

I am looking for a way to improve this database design as it grows and more documentations are added. I am concerned with performance when searching and uploading data. There is a lot of redundancy right now, and wondering if it can be better.

Keywords for indexing -- Either put it in a TEXT and apply a FULLTEXT index, or put the keywords one-per-row in another table (which is indexed).

To critique a schema, we need to see the main SELECTs that you will have.


MDF and LDF contents in SQL Server Let's say I have a database with a single .mdf and .ldf file.

The database is detached, there are no transactions and SQL Server is stopped.

I am presuming that all the data is already in the .mdf file?

The .ldf file is essentially empty? If the .ldf file is not empty what does it contain?

If I delete the .ldf file and manually create a new 0 bytes long .ldf file with same name as before, I would be able to attach the database again with no data loss?

(Disclaimer for peeps flying in from Google: I do not recommend using the method asked about in this question to reclaim log file space! Use DBCC SHRINKFILE instead.)

  I am presuming that all the data is already in the .mdf file?

Yes, detaching initiates a CHECKPOINT in the database, so all dirty data pages make it to disk.

A database cannot be detached if the transaction log is required by another process (mirroring, replication, etc.) or if exclusive access cannot be obtained (all open transactions in the database need to commit or roll back). Therefore, the log will never contain in-flight transactions like you might see if the power suddenly turned off (i.e., a crash).

  The .ldf file is essentially empty? If the .ldf file is not empty what does it contain?

SIMPLE recovery: the log file will contain the log records describing the CHECKPOINT that occurred before the database was shut down. (A CHECKPOINT also clears the log in this mode.)
BULK_LOGGED and FULL recovery: the log file will contain all the log records that haven't yet been cleared out. (The only process that clears the log in this mode is a log backup.)

Tip: log records in the log file can be inspected using the undocumented TVF sys.fn_dblog.

  If I delete the .ldf file and manually create a new 0 bytes long .ldf file with same name as before, I would be able to attach the database again with no data loss?

Doing this will destroy the file header information, thus corrupting the log file.

To get the database to attach, you would have to remove/rename the log file, and attach the database using ATTACH_REBUILD_LOG to have SQL Server create a new log file from scratch in the default location.

Assuming you did that,

In SIMPLE recovery, no user data would be lost as all that disappeared were relatively unimportant CHECKPOINT log records.
In the other recovery models, it would break the log backup chain, and obviously any deleted log records could no longer be backed up (and probably weren't already backed up). This has obvious implications should the database need to be restored.

So... technically, no, there wouldn't be any immediate data loss; however, this method is certainly not a good practice, particularly in a recovery model other than SIMPLE.


string search in table with partial text and without all field names I use postgresql database and the need is to return all the rows that contain a given string in any column of that table. And string could be like just half . For examaple search string can ve IT   and I should return record of ITALY .
my table is something like

currently using this query but it is not working for half text. and don't want to user fields name in   IN like (1st field ,2nd filed, 3rd filed ) is there something * like generic there ?
currently using this
select *
from the_table
where 'Italy' IN (name, native, place);

Build a string consisting of what you want to match against, and test against that. It could be as simple as casting the whole row to text:
testdb=# create table t(userid integer, uname text, native text, place text);
CREATE TABLE
testdb=# insert into t select 1, 'Richard', 'Italy', 'San Francisco';
INSERT 0 1
testdb=# insert into t select 2, 'Elon', 'Europe', 'New York';
INSERT 0 1
testdb=# insert into t select 3, 'John', 'Vietnam', 'Italy';
INSERT 0 1
testdb=# select t::text from t;
                 t
-----------------------------------
 (1,Richard,Italy,"San Francisco")
 (2,Elon,Europe,"New York")
 (3,John,Vietnam,Italy)
(3 rows)

testdb=# select * from t where t::text ~ 'It';
 userid |  uname  | native  |     place
--------+---------+---------+---------------
      1 | Richard | Italy   | San Francisco
      3 | John    | Vietnam | Italy
(2 rows)

Or, if you want to ensure that commas in the search string don't cause it to match more than one column, or if you need to omit any of the columns, use concat() to separate the values with unprintable chars instead.
testdb=# select concat(uname, e'\x01', native, e'\x01', place) from t;
              concat
-----------------------------------
 Richard\x01Italy\x01San Francisco
 Elon\x01Europe\x01New York
 John\x01Vietnam\x01Italy
(3 rows)

testdb=# select * from t where t::text ~ 'rd,It';
 userid |  uname  | native |     place
--------+---------+--------+---------------
      1 | Richard | Italy  | San Francisco
(1 row)

testdb=# select * from t where concat(uname, e'\x01', native, e'\x01', place) ~ 'rd,It';
 userid | uname | native | place
--------+-------+--------+-------
(0 rows)


SQL Server 2016 AlwaysOn DBCC CHECKDB on async replica We have an AlwaysOn environment, which includes a replica in our DR site that is setup with async commit and readable secondary = No.

When we were running on SQL Server 2014 SP2 we were able to run DBCC CHECKDB against the databases on our DR replica. But since upgrading to SQL Server 2016 we're unable to and our weekly integrity job is failing with the error.

'The target database is participating in an availability group and is currently not accessible for queries.
Either data movement is suspended or the availability replica is not enabled for read access.
To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information,
see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

Obviously something has changed in 2016 to prevent this, I just don't know what?

I can say that the error message you are getting is because of a bug in SQL Server 2016. Microsoft knows about it and has fixed it in CU3 for SQL Server 2016 RTM.
Note: The user error is reported but the CHECKDB should continue as is.
See FIX: Error 976 occurs when you run the DBCC CHECKDB command on an unreadable secondary replica of SQL Server 2016 Availability Group
Workaround
You can take snapshot of the databases and can run checkdb on it. In this case it would not fail.Database snapshot is enterprise feature I am sure you are not using basic availability group


Total time execution of a query I`m trying to obtain the total time of a query execution

I have this query but not allways get the output that I hope, For example for the same parallel 4 execution of a query sometimes I obtain 5 rows (query controller and 4 parallels) but other times I only obtain 2 parallel process result (the parallels processes only open in the same instance)

Is this query correct or I need to add something more?

select inst_id, sql_exec_id,session_id, QC_SESSION_ID,program,sql_id,sql_plan_hash_value,round((count(*))/60/60, 2) as Horas
from    gv$active_session_history ash
where   sql_id='7u0wv0g5r3y2n'
and sql_exec_id is not null
group by    inst_id, sql_exec_id,session_id,QC_SESSION_ID,program,sql_id,sql_plan_hash_value
order by    2,1,4,5 desc;

I use set events 10046, for the explain plan and 10053 for the waits.
Once you run the queries that you want timings for, assuming that you are using 10g/11g/12c go to the diag trace folder, find your trace files and run tkprof against the trace files.

ALTER SESSION SET tracefile_identifier = 'test_plan1';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

/*
run your queries here
*/

ALTER SESSION SET EVENTS '10053 trace name context OFF';
ALTER SESSION SET EVENTS '10046 trace name context OFF';

go to the <diag>/rdbms/<sidname>/<sidname>/trace directory
tkprof <in file name> <out file name> waits=yes explain=syste/<pwd>

This will give really good information about how the queries ran, how long,
how much logical/physical IO, explain plan and waits. Plus you don't need
any optional components to get the information.

-- use logging package to log and not reraise errors
CREATE OR REPLACE TRIGGER application_logon_trace
       AFTER LOGON ON DATABASE
DECLARE
   v_db_user VARCHAR2(64) := SYS_CONTEXT ('SESSION_USERID', 'PROXY_USER' );
   v_host    VARCHAR2(64) := SYS_CONTEXT ('USERENV',        'HOST'    );
   v_ddl_cmd VARCHAR2(2000);
BEGIN
-- You can have an IF clause that will only turn on logging for certain users if you like
-- or you can only log sessions coming from certain servers/pcs if you want.
   IF INSTR(v_host, '.') > 0
   THEN
      v_host := SUBSTR( v_host, 1, INSTR(v_host, '.')-1);
   END IF;

   v_ddl_cmd := 'ALTER SESSION SET tracefile_identifier = ''application_logon_trace_'|| v_db_user || '_' || v_host ||'''';
   DBMS_OUTPUT.PUT_LINE( v_ddl_cmd );
   EXECUTE IMMEDIATE v_ddl_cmd;

   v_ddl_cmd := 'ALTER SESSION SET EVENTS=''10046 trace name context forever, level 12''';
   DBMS_OUTPUT.PUT_LINE( v_ddl_cmd );
   EXECUTE IMMEDIATE v_ddl_cmd;

   v_ddl_cmd := 'ALTER SESSION SET EVENTS=''10053 trace name context forever, level  1''';
   DBMS_OUTPUT.PUT_LINE( v_ddl_cmd );
   EXECUTE IMMEDIATE v_ddl_cmd;
EXCEPTION WHEN OTHERS THEN NULL; -- feel free to call you favorite error logging package here.
END;
/


SQL Server Linked Server to Progress is slow with an openquery view We have a SQL Server database setup with a Linked Server setup connecting to a Progress OpenEdge database.  We created a SQL Server view (for using with SSRS) of some of the OpenEdge tables using code similar to the following:

CREATE VIEW accounts AS SELECT * FROM OPENQUERY(myLinkedServerName,
'SELECT * FROM PUB.accounts')

CREATE VIEW clients AS SELECT * FROM OPENQUERY(myLinkedServerName,
'SELECT * FROM PUB.clients')

For some reason the queries seem to bring back the whole table and then filter on the SQL side instead of executing the query on the Progress side.  I'm a dev and not a DBA so I'm hoping the experts can hook me up with a solution or at least an explanation as to why.

Thanks.

in your query above, every time some one views your view, an OPENQUERY is executed to your server

why don't you run a job that transfers the data from your progress server to your sql server and create a view pointing to a table in your server locally.

that will be much faster

CREATE PROCEDURE [dbo].[copydatafromProgress]
AS
BEGIN

insert into tbl_accounts
SELECT * FROM OPENQUERY(myLinkedServerName,'SELECT * FROM PUB.accounts')

insert into tbl_clients
SELECT * FROM OPENQUERY(myLinkedServerName,'SELECT * FROM PUB.clients')

End

CREATE VIEW accounts AS SELECT * FROM tbl_accounts
CREATE VIEW clients AS SELECT * FROM tbl_clients

after that create a job that runs the stored procedure once a day or depending on the schedule you need.


How do you find where a stored procedure is used (in other stored procedures) I have a stored procedure which I want to refactor, in a database which has thousands of SPs. Is there a quick way of finding references to that stored procedure in other SPs so I can be sure that I'm not breaking any other code when I refactor.

In the application code I can look for calls to the SP easily enough, and I can do a text search over all the various sql files which define the SPs, but it's possible there may be some SPs in the database which may be missed that way.

EDIT: The stored procedures I'm trying to locate are part of a package.

EDIT: I'm running on Oracle 11g

DBA_DEPENDENCIES view has all the answers to such questions.

select * from DBA_DEPENDENCIES
  where referenced_owner='HR' and referenced_name='STORED_PROCEDURE_41';


How to improve simple update statement causing blocking issues, inneficient index scan in query plan Please bear with me - I am on the development side but I've been tasked with finding the cause of several blocking issues after upgrading from sql server 2008 to 2016. The blocking is intermittent, everything runs fine for a couple weeks, then maybe once or twice a month it gets so bad the entire application is unusable. This is one of the repeat offending queries when that happens:

UPDATE campaign_log
SET date_modified = N'2019-06-18 14:28:19'
    , mail_disposition_status = '1'
    , status_date = N'06/17/2019'
    , activity_date =N'05/22/2019'
WHERE id = N'03938240-0112-437C-AF97-ECCF2EF78E77'

It seems like this https://www.brentozar.com/pastetheplan/?id=H1YyzoqJS query execution plan is telling me the index scan [SUGARCRM].[dbo].[campaign_log].[idx_mail_disposition_status] is really inefficient.

I'm not sure why Mail_disposition_status column is indexed or why this index is being chosen to run this query. It seems like maybe it should be using an index on id? There are 2,622,020 rows in the campaign_log table.

Can anyone point me in the right direction on what I should be looking at to fix this? I'm honestly pretty lost on what to even search for.

Editing to add definition of table - apologies but I don't have permissions to get a Create statement, so these are the fields. Also wanted to mention this is a 3rd party product so the table design is just stock.

id, varchar(36)
campaign_id, varchar(36)
target_tracker_key, nvarchar(36)
target_id, varchar(36)
target_type, nvarchar(100)
activity_type, nvarchar(100)
activity_date, datetime
related_id, varchar(36)
related_type, nvarchar(100)
archived, bit
hits, int
list_id, varchar(36)
deleted, bit
date_modified, datetime
more_information, nvarchar(100)
marketing_id, varchar(36)
prospect_address_id, varchar(36)
mail_disposition_status, nvarchar(50)
status_date, datetime
mail_index, int

Here is the definition for Index idx_mail_disposition_status.

There are a few easy things to try:

Stop passing in GUIDs as NVARCHAR

It looks like the log table has id stored as a UNIQUEIDENTIFIER or VARCHAR. You should take the N prefix off the GUID literal string.

The idx_mail_dispositionindex might not be ideal

It's unclear what the index definition is, but if id isn't the leading column, you may not get a seek to the id you're looking for anyway. You could try just creating an index on id, if one doesn't exist already. If it's truly unique, create a unique index on it. The single row estimate makes it attractive.

The reason this plan is slow is because you need to read the entire index because of one or both of the reasons above.

For more related information, check out my recent blog post about this: Index Key Column Order And Locking


Select records between different work shifts depending on datetime I have a data table called "Requests" that records the requests made by employees in a company, I used to use a Stored Procedure to get all the request from a single workday (starting at 6am and ending at 6am of the next day)

USE [DataBase]
GO
/****** Object:  StoredProcedure [dbo].[GetRequests]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetRequests]
as begin
DECLARE @from datetime
set @from = getdate();
if (DATEPART(HOUR, @from) < 6) set @from = dateadd(day, -1, @from);

DECLARE @start_date datetime = CONVERT(datetime, CONVERT(char(9), @from, 112) + '06:00');
DECLARE @end_date datetime = DATEADD(HOUR, 24, @start_date)

select * FROM Requests
where RequestDate between  @start_date and @end_date
end

Now I need another stored procedure to get all the request from a specific work shift.

The company has 3 work shifts (1st: 6am to 2pm / 2nd: 2pm to 10pm / 3rd: 10pm to 6am).

Is there a way to get all the requests from a single shift?

For example: if the current time is 5am, to only show the requests from the 3rd shift (10pm to 6am) and if it is 3pm to only show the requests from the 2nd shift (2pm to 10pm)

I feel like this is harder than it needs to be. I like to imagine that in your dataset you have a datetime column that is stored when each request is submitted. If that is the case I think you would have a much better time writing a proc that filters that column.

However, I used your proc and just branched off. I didn't test this since i'm not sure what your dataset looks like but I think you may be looking for something along the lines of this

create procedure [dbo].[GetRequests]
as begin
DECLARE @from datetime
set @from = getdate();
if (DATEPART(HOUR, @from) < 6) set @from = dateadd(day, -1, @from);

DECLARE @start_date datetime = CONVERT(datetime, CONVERT(char(9), @from, 112) +     '06:00');
DECLARE @end_date datetime = DATEADD(HOUR, 24, @start_date)
declare @fshift datetime = dateadd(hh, 8, @start_date)
declare @tshift datetime = dateadd(hh, 8, @fshift)
declare @trshift datetime = dateadd(hh, 8, @tshift)
declare @night datetime = dateadd(hh, 8, trshift)

select *
FROM Requests
where RequestDate in (select *
                      FROM Requests
                      where RequestDate between  @start_date and @end_date)
    and RequestDate between  @fshift and @tshift
    and requestdate between @tshift and @trshift
    and requestdate between @trshift and @night
end


Same query uses 2 different plans in 2 sessions I am running same query in 2 different DB sessions but it is ending up picking 2 different plans. One is faster and other takes hours.
Any ideas on how to make them use same plan:

This is Plan for faster query

This is plan for slower query

Only difference between 2 plans is order of collection iterator pickler fetch

EDIT:

Just to add more details, above query is not really a standard query as it uses Table function which accepts a dynamic array list.

You could create an optimizer extension to handle statistics for your collection, so the optimizer doesn't have to guess the value of its cardinality, selectivity and cost.

This can be done with the SQL statement ASSOCIATE STATISTICS:
http://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL339

In order to implement the statistics collection interface, please have a look at:
http://docs.oracle.com/database/121/ADDCI/ext_optimizer.htm#ADDCI2110

Another option is to use the undocumented CARDINALITY hint, which I would personally not recommend.

For an example of statistics association, please check the code below:

-- Creates a collection type
CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/

-- Creates a function wrapper to the collection
CREATE FUNCTION collection_wrapper(
                p_collection IN varchar2_ntt
                ) RETURN varchar2_ntt IS
BEGIN
  RETURN p_collection;
END collection_wrapper;
/

-- Create an object type specification to handle statistics collection
-- on the function wrapper
CREATE TYPE collection_wrapper_ot AS OBJECT (
  dummy_attribute NUMBER,

  STATIC FUNCTION ODCIGetInterfaces (
                  p_interfaces OUT SYS.ODCIObjectList
                  ) RETURN NUMBER,

  STATIC FUNCTION ODCIStatsTableFunction (
                  p_function   IN  SYS.ODCIFuncInfo,
                  p_stats      OUT SYS.ODCITabFuncStats,
                  p_args       IN  SYS.ODCIArgDescList,
                  p_collection IN varchar2_ntt
                  ) RETURN NUMBER

);
/

-- Statistics interface implementation
-- Basically returns the count on the collection as its cardinality
CREATE TYPE BODY collection_wrapper_ot AS

   STATIC FUNCTION ODCIGetInterfaces (
                   p_interfaces OUT SYS.ODCIObjectList
                   ) RETURN NUMBER IS
   BEGIN
       p_interfaces := SYS.ODCIObjectList(
                           SYS.ODCIObject ('SYS', 'ODCISTATS2')
                           );
       RETURN ODCIConst.success;
   END ODCIGetInterfaces;

   STATIC FUNCTION ODCIStatsTableFunction (
                   p_function   IN  SYS.ODCIFuncInfo,
                   p_stats      OUT SYS.ODCITabFuncStats,
                   p_args       IN  SYS.ODCIArgDescList,
                   p_collection IN  varchar2_ntt
                   ) RETURN NUMBER IS
   BEGIN
      p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
      RETURN ODCIConst.success;
   END ODCIStatsTableFunction;

END;
/

-- Associate the statistics type with the function wrapper
ASSOCIATE STATISTICS WITH FUNCTIONS collection_wrapper USING collection_wrapper_ot;

Now you can check the effects on the execution plan:

SQL> set autotrace traceonly explain

SQL> SELECT *
  2  FROM   TABLE(varchar2_ntt('A','B','C'));

Execution Plan
----------------------------------------------------------
Plan hash value: 1748000095

--------------------------------------------------------------
| Id  | Operation                             | Name | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |  8168 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|      |       |
--------------------------------------------------------------

SQL> SELECT *
  2  FROM   TABLE(
  3            collection_wrapper(
  4               varchar2_ntt('A','B','C')));

Execution Plan
----------------------------------------------------------
Plan hash value: 4261576954

------------------------------------------------------------------------
| Id  | Operation                         | Name               | Rows  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |     3 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| COLLECTION_WRAPPER |       |
------------------------------------------------------------------------

PS.: The code above was adapted from http://www.oracle-developer.net/display.php?id=427.


Database not able to startup showing control file is inconsistent with another Our windows server 2008 R2 is shutdown due to power failure. When I am trying to startup Oracle 11g database its shows an error.

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shut immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2180024 bytes
Variable Size            2415922248 bytes
Database Buffers          771751936 bytes
Redo Buffers               16982016 bytes
ORA-00214: control file 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL'
version 145627 inconsistent with file
'D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL' version 145625

Can any one help me how to resolve this issue?

You should be able to resolve this by copying CONTROL01.CTL over CONTROL02.CTL since the first one has a higher version number. Make a copy of CONTROL02.CTL in case it does not work


SQL Server 2000 to SQL Server 2008 linked server issue Here's my scenario: I've got a linked server going from a SQL Server 2000 machine to a SQL Server 2008 machine. To aid in moving databases with a minimum of config changes, most of our connections point to [dbname.database.organization.com]. That is also the name of the linked server on the 2000 box.

Most of the tables work fine. There is one exception: the biggest table of the bunch (450285 rows) gives this error (the values in the curly brackets have been changed from our real values):

  An error occurred while executing batch.
  Error message is: Processing of results from SQL Server failed because
  of an invalid multipart name
  "{DBNAME}.DATABASE.{ORGANIZATION}.COM.{DBNAME}.dbo.{TableName}", the
  current limit of "4" is insufficient.

I would expect that behavior if didn't have square brackets around the linked server name (which the error message is just omitting). The rest of the tables I need work just fine the exact same way.

I'm wondering if there is a maximum size setting I'm missing, etc.

This application currently depends on a job that copies the entire table from the SQL Server 2008 machine to the SQL Server 2000 machine, nightly. I would really like to do away with that.

Thanks!

I've had this problem before, and while I didn't find a fix (as it appears from the error message, SQL Server seems to 'lose' the brackets when it's processing the query), I did find a couple of workarounds:

Using OPENQUERY will allow you to use the linked server name as-is.
(My preferred solution): create a synonym for the linked server and use that in your query:

CREATE SYNONYM MyLinkedTable FOR [DBNAME.DATABASE.ORGANIZATION.COM].DBName.schema.TableName

Then use it in place of that huge mess:

SELECT * FROM MyLinkedTable


Querying special characters I work with language with special characters (example: e -> é). I have a query with a LIKE but when I search a word with special char and my query doesn't have the special char, I don't get the results I expect:

select * from a where name like '%e%'

The above returns no results when the database contains "dés". Is there a way to have this query return the desired results.

If the collation of your database is set to accent insensitive, then this should work (for most accented characters).

For example in the collation Latin1_General_CI_AS, the CS means Case Insensitive, Accent Sensitive.  You would want the collation to be Latin1_General_CI_AI which means Case Insensitive, Accent Insensitive

Be warned though, that not all "accented" characters are created equally, but for spanish and french searches I found things work out as expected.


Get the rank of a fruit after ordering I have the following table, called sales

+--------+--------+
| fruit  | amount |
+--------+--------+
| cherry |    124 |
| plum   |     23 |
| pear   |     19 |
| apple  |     13 |
| banana |      4 |
| orange |      2 |
+--------+--------+

And using this to get the rank

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC;

.

+------+--------+--------+
| rank | fruit  | amount |
+------+--------+--------+
|    1 | cherry |    124 |
|    2 | plum   |     23 |
|    3 | pear   |     19 |
|    4 | apple  |     13 |
|    5 | banana |      4 |
|    6 | orange |      2 |
+------+--------+--------+

So now I want to get the rank of apple but I couldn't figure out how exactly to do it.

You can place your query inside a subquery and do a WHERE against it

PROPOSED QUERY

SET @rank=0;
SELECT rank FROM
(SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
WHERE fruit='apple';

SAMPLE DATA

DROP DATABASE IF EXISTS Alw7SHxD;
CREATE DATABASE Alw7SHxD;
USE Alw7SHxD
CREATE TABLE sales
(
    id INT NOT NULL AUTO_INCREMENT,
    fruit VARCHAR(20),
    amount INT DEFAULT 0,
    PRIMARY KEY (id)
);
INSERT INTO sales (fruit,amount) VALUES
('cherry',124), ('plum'  , 23), ('pear'  , 19),
('apple' , 13), ('banana',  4), ('orange',  2);
SELECT * FROM sales;
SET @rank=0;
SELECT rank FROM
(SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
WHERE fruit='apple';

SAMPLE CODE EXECUTED

mysql> DROP DATABASE IF EXISTS Alw7SHxD;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE Alw7SHxD;
Query OK, 1 row affected (0.00 sec)

mysql> USE Alw7SHxD
Database changed
mysql> CREATE TABLE sales
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     fruit VARCHAR(20),
    ->     amount INT DEFAULT 0,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO sales (fruit,amount) VALUES
    -> ('cherry',124), ('plum'  , 23), ('pear'  , 19),
    -> ('apple' , 13), ('banana',  4), ('orange',  2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM sales;
+----+--------+--------+
| id | fruit  | amount |
+----+--------+--------+
|  1 | cherry |    124 |
|  2 | plum   |     23 |
|  3 | pear   |     19 |
|  4 | apple  |     13 |
|  5 | banana |      4 |
|  6 | orange |      2 |
+----+--------+--------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET @rank=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT rank FROM
    -> (SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
    -> WHERE fruit='apple';
+------+
| rank |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


How to use WHERE condition when using ON clause I have looked at similar posts and have tried them but dint seem to work. In the below query on a MSSQL 2015 database, I need to filter out inactive users. That is, only return results where 'Active' = '1'. I tried:

ON Users.[Name] = Skills.[Name] where Users.[Active] = 1 ---- Incorrect syntax near the keyword 'LEFT'.

ON Users.[Name] = Skills.[Name] where Users.[Active] = '1' ---- Incorrect syntax near the keyword 'LEFT'.

ON Users.[Name] = Skills.[Name] AND Users.[Active] = '1' ---- Query gets executed but shows all results.

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
    ON Users.[Name] = Skills.[Name]
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
    ON Users.[Name] = WeekTimeslip.[employeename]
LEFT JOIN [master].[dbo].[Project] AS Project
    ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
    ON Users.[Name] = Utilisation.[employeename]
ORDER by Users.[Name]

Can you please let me know what I'm missing?

You can put the filter in your join like this:

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
    ON Users.[Name] = Skills.[Name] AND Users.[Active] = '1'
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
    ON Users.[Name] = WeekTimeslip.[employeename] AND Users.[Active] = '1'
LEFT JOIN [master].[dbo].[Project] AS Project
    ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
    ON Users.[Name] = Utilisation.[employeename] AND Users.[Active] = '1'
ORDER by Users.[Name]

Or just put it in a where clause like this:

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
    ON Users.[Name] = Skills.[Name]
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
    ON Users.[Name] = WeekTimeslip.[employeename]
LEFT JOIN [master].[dbo].[Project] AS Project
    ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
    ON Users.[Name] = Utilisation.[employeename]
WHERE Users.[Active] = '1'
ORDER by Users.[Name]

Usually, SQL will generate the same execution plan and the optimization process will do pretty much the same thing.


How to change aggregate function without duplicating SQL (by using SQL) In SQL Server 2016 I have a scenario where data will be processed according to different aggregation functions in a large GROUP BY ROLLUP. I would like to have a stored procedure that has a parameter that specifies which aggregation function to use to describe the groupings in a way that does not risk SQL injection and takes advantage of compilation (it is a heavy stored procedure).

My thoughts are to use a collection of queries that summarize the data's groupings on a particular aggregate function. (e.g. agg.DataMin, agg.DataMedian, agg.DataWeightedAverage, and so on).  Then use these with the parameter in a CTE

WITH AggData AS
(
   SELECT * FROM agg.DataMin WHERE @AggFunction = 1
   UNION ALL
   SELECT * FROM agg.DataMedian WHERE @AggFunction = 2
   UNION ALL
   SELECT * FROM agg.DataWeightedAverage WHERE @AggFunction = 3
)
SELECT ...

My concerns are query performance and industry best practice. The data table is of a reasonable size (2+ Gig). I will have to add many aggregate queries with some being inline table-valued functions for some leave-out aggregations.

In the above, will the queries/table-valued functions only execute when the @AggFunction matches the WHERE condition or will they all execute and filter after the results are returned? If the latter, is there a method to short-circuit the evaluation of the unneeded queries at run-time?  Also, is there some standard method to perform this in SQL that I have overlooked?

Contradiction Detection could kick in to make sure only one of the statements is run, and in my simple test it did as long as there was a statement-level recompile hint, but why risk it?  For example:

USE tempdb
GO

-- CREATE SCHEMA agg
--DROP TABLE agg.DataMin
--DROP TABLE agg.DataMedian
--DROP TABLE agg.DataWeightedAverage
--GO

CREATE TABLE agg.DataMin ( x INT PRIMARY KEY )
CREATE TABLE agg.DataMedian ( x INT PRIMARY KEY )
CREATE TABLE agg.DataWeightedAverage ( x INT PRIMARY KEY )
GO

INSERT INTO agg.DataMin ( x )
SELECT object_id FROM sys.all_objects

INSERT INTO agg.DataMedian ( x )
SELECT object_id FROM sys.all_objects WHERE type = 'P'

INSERT INTO agg.DataWeightedAverage ( x )
SELECT object_id FROM sys.all_objects WHERE type = 'X'
GO

-- Are there some situations when it wouldn't...
DECLARE @AggFunction INT = 1

;WITH AggData AS
(
   SELECT * FROM agg.DataMin WHERE @AggFunction = 1
   UNION ALL
   SELECT * FROM agg.DataMedian WHERE @AggFunction = 2
   UNION ALL
   SELECT * FROM agg.DataWeightedAverage WHERE @AggFunction = 3
)
SELECT *
FROM AggData
OPTION ( RECOMPILE )

My results:

In this simple example, only one table is scanned on the left with the recompile, and 3 tables are scanned on the right, without the recompile.  The recompile hint allows the optimizer to "see" the parameter value and act accordingly.  In a stored procedure where parameter sniffing would be used, a recompile would also be needed to get the same behaviour, either at statement or stored-proc level.

However I cannot say if there are no situations where contradiction detection would not occur; and you can't prove a negative.  To put it another way, I cannot prove contradiction detection would always occur even with a recompile.  There may be some unknown situations where even with a recompile it does not occur; excessive complexity springs to mind.

Also, there is no real advantage to using the CTE in your example, so why not keep it simple?  You could just write some simple procedural SQL with IF...THEN...ELSE which would guarantee only one of your statements would fire, eg

DECLARE @AggFunction INT = 99

IF @AggFunction = 1
   SELECT * FROM agg.DataMin
ELSE IF @AggFunction = 2
   SELECT * FROM agg.DataMedian
ELSE IF @AggFunction = 3
   SELECT * FROM agg.DataWeightedAverage
ELSE
    RAISERROR( 'Unknown value for parameter @AggFunction (%i).', 16, 1, @AggFunction )

Add some parameter checking while you're at it.  Hopefully this meets your requirements of guaranteeing only one statement is compiled when needed, is safe and hopefully simple to implement.

HTH


Update only if entry does not exist I have to update many records. I don't want to duplicate the entries.  The table concatenates the value to the end.  So in theory, I could add the value twice.

I want to put a check to make sure the entry is not already there; if it is, then skip the record.

update table1
set value=concat(value,',test_data')
where field1='abc';

UPDATE table1
SET value=CONCAT(value, ',test_data')
WHERE field1='abc'
  AND NOT INSTR(CONCAT(',',value,','), ',test_data,');

But I'd recommend to normalize your data structure... CSV data field is not safe.


What is the cause of this problem with CONVERT()? Consider the following two statements:

PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0);
PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0);

Both statements return -1; isn't that incorrect since the second binary value is decimal 65,536 higher than the first value, is it not?

Surely this cannot be due to silent truncation?

If I run the following statements:

PRINT CONVERT(NUMERIC(38, 0),   0x00000001, 0);
PRINT CONVERT(NUMERIC(38, 0),   0x00010001, 0);

I am presented with the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varbinary to numeric.

How can I diagnose what is happening here?

I'm running this on SQL Server 2012, v11.0.5058.  The results are the same on SQL Server 2008 R2 SP2, SQL Server 2005, and SQL Server 2000.

Decimal and whole numbers are encoded very differently in varbinary. Decimals need more space. Try:

SELECT CONVERT(VARBINARY(32), 1), CONVERT(VARBINARY(32), 1.0);

As for your ultimate goal, storing whole numbers as varbinary to save space, I think you've answered that question yourself - not worth it.


Populate nested associations using PostgreSQL I have three tables in my PostgreSQL database:

user
address
city

My tables have self-associations like user -[ address -[ city where -[ represents a 1:n association.

I have a simple query:

SELECT
    *,
    ST_Distance_Sphere(ST_MakePoint(-22.4149023, -47.56513940000002), "geolocation") AS distance,
    row_to_json(addr.*) as address
FROM "user" as u
LEFT JOIN
(
    SELECT *, row_to_json(cty.*) as city
    FROM "address"
    LEFT JOIN "city" ON address.city_id = city.city_id
    WHERE user_id = u.user_id
) as addr
WHERE u.user_name LIKE '%Tod%'
ORDER BY distance ASC

How can I retrieve a nested object in one query, populating all columns of all tables to obtain a result like:

  {
    "userId": 148,
    "userType": 1,
    "userName": "Jimmy's Restaurante",
    "email": "jimmys6@jimmys.com",
    "phoneNumber": "588559944",
    "password": "$2a$10$q9j2O.240kXTxa91KgocD.FPZyUC847S8B9GcJ5fNqPE9jntZuH8y",
    "oppId": "1cd1f75f99c6606061996a2e5561a86b9b043210",
    "accessToken": null,
    "deviceToken": null,
    "addressId": 66,
    "dni": null,
    "birthDate": null,
    "status": false,
    "owner": "Jeremy Hopkins",
    "description": "This is the Jeremy Hopkins Restaurant, a.k.a         Jimmy's",
    ,
    "address": {
      "addressId": 66,
      "addressLine1": "Rua Dona Alexandrina 2450",
      "addressLine2": "Piso2 Dto.4",
      "postalCode": "13566-290",
      "city": {
        "cityId": 1,
        "provinceId": 1,
        "name": "La Plata"
      }
    },
    "distance": 263.67136658372544,
    "imageUrl": "http://cdn2.colorir.com/desenhos/pintar/loja-com-toldo_2.png"
  }

My database SQL structure:

User table

CREATE TABLE public."user"
(
  user_id integer NOT NULL DEFAULT nextval('user_user_id_seq'::regclass),
  user_name character varying(255) NOT NULL,
  email character varying(255) NOT NULL,
  user_type integer,
  phone_number character varying(255),
  password character varying(255) NOT NULL,
  opp_id integer,
  birth_date date,
  dni character varying(255),
  access_token character varying(255),
  device_token character varying(255),
  status boolean,
  owner character varying(255),
  cuit character varying(255),
  description character varying(255),
  geolocation geometry(Point),
  image_url character varying(255),
  pin character varying(255),
  "createdAt" timestamp with time zone NOT NULL,
  "updatedAt" timestamp with time zone NOT NULL,
  CONSTRAINT user_pkey PRIMARY KEY (user_id),
  CONSTRAINT user_email_key UNIQUE (email),
  CONSTRAINT user_phone_number_key UNIQUE (phone_number),
  CONSTRAINT user_user_name_key UNIQUE (user_name)
)

Address table

CREATE TABLE public.address
(
  id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
  description character varying(255),
  address_line_1 character varying(255),
  address_line_2 character varying(255),
  postal_code character varying(255),
  "createdAt" timestamp with time zone NOT NULL,
  "updatedAt" timestamp with time zone NOT NULL,
  city_id integer,
  user_id integer,
  CONSTRAINT address_pkey PRIMARY KEY (id),
  CONSTRAINT address_city_id_fkey FOREIGN KEY (city_id)
      REFERENCES public.city (city_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT address_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES public."user" (user_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL
)

City table

CREATE TABLE public.city
(
  city_id integer NOT NULL DEFAULT nextval('city_city_id_seq'::regclass),
  name character varying(255),
  "createdAt" timestamp with time zone NOT NULL,
  "updatedAt" timestamp with time zone NOT NULL,
  CONSTRAINT city_pkey PRIMARY KEY (city_id)
)

PostgreSQL 9.6+

PostgreSQL 9.6 adds jsonb_insert

You need to know that when you SELECT tablename FROM tablename you get a composite type that represents the row. You can feed this to the to_jsonb like such,

SELECT address.id AS address_id, to_jsonb(address)
FROM address;

SELECT user_id, to_jsonb(user)
FROM user;

SELECT city_id, to_jsonb(city)
FROM city;

Then realizing that the user is your outermost part of the JSON object, work inward by inserting the row type with jsonb_insert

SELECT jsonb_insert(
  to_jsonb(u),
  'address',
  a
)
FROM user AS u
JOIN address AS a
  ON u.id = a.user_id;

This should display the basic user with address, now you need the city too.

SELECT jsonb_insert(
  to_jsonb(u),
  'address',
  jsonb_insert(to_jsonb(a), 'city', to_jsonb(c))
)
FROM user AS u
JOIN address AS a
  ON u.id = a.user_id
JOIN city AS c
  USING (city_id);

And, that should work for you.


Having trouble with insert query on linked server Two servers. Server A and Server B both has database named KPS. KPS has two tables complaint and attachment. KPS database on server A gets the value from the Web.

Both table if the submitted value = 1 then run the insert query which will insert the col1,col2 and col3 from Server A KPS to Server B KPS. They both are linked server. Both table attachment and complaint submit value needs to be 1 if it is 0 then do not insert. There will be two insert query one for complaint and one for attachment.

Can i also check that i m not inserting same data over and over ?

Thank you..

So I assume that you already have the queries to insert the data. So the question is really "Can I also check that I m not inserting the same data over and over again?".
There are a couple of ways you can do this. One simple way is to create a constraint on the columns in your table. Let's assume that they are col1,col2 and col3. So you could create a unique constraint on the table like this:
ALTER TABLE table_name
ADD CONSTRAINT Constraint_Name unique(col1,col2,col3)

A composite primary key will also achieve this, but you may already be using the  primary key in a different column.


Why is MySQL MariaDB GREATEST(timestamp,NOW()) padding appending results with zeros? Everything works as expected on my development machine, but when deploying to quality assurance environment, tests are failing because MariaDB is padding the result set with extra precision zeros after a decimal.

For example, when I run this query:

SELECT GREATEST(  '2016-04-14 15:06:30', NOW() )

On dev, running Ubuntu 14.04.4 LTS (Trusty Tahr) mysql Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3, I get this expected result:

  2016-04-18 09:07:42

On test, running Red Hat Enterprise Linux Server 7.2 (Maipo) mysql  Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1, I get this unexpected result:

  2016-04-18 09:07:42.000000

I don't see anything in the GREATEST() documentation that would explain this behavior, so I think it must be a server configuration setting in my.cnf but I couldn't find anything in online search results.

Even more curiously, when running the following queries, I get the same unpadded results on both machines:

SELECT GREATEST(  '2016-04-14 15:06:30', '2016-04-14 16:06:30' )

  2016-04-14 16:06:30

SELECT GREATEST(  NOW(), NOW() )

  2016-04-18 09:24:22

SELECT GREATEST(  5, 10 )

  10

Why is MariaDB GREATEST() padding/appending my result set with zeros on timestamp comparison with NOW()?

The MariaDB doku claims for NOW():

  Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS'
  or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is
  used in a string or numeric context.

So this might be related to the fact that '2016-04-14 15:06:30' may look like a timestamp, but is actually a Varchar.

Try using timestamp literals:

SELECT GREATEST( TIMESTAMP '2016-04-14 15:06:30', NOW() )

Or functional conversion:

SELECT GREATEST( STR_TO_DATE('2016-04-14 15:06:31','%Y-%m-%d %H:%i:%s'), NOW() )


The SELECT permission was denied - View querying data from a different database I created a view SchemaX.VW_VIEW on a DatabaseB. This view is querying data from another database DatabaseA of the same server.

I have granted the SELECT permission on VW_VIEW to a user. However when the user tries to query the view, it says "The SELECT permission was denied on the object 'OriginTable', database 'DatabaseA', schema 'SchemaY'.

SchemaY and SchemaX are both owned by 'dbo' so there shouldn't be any break in the ownership chaining.

How can I fix this issue without granting read access to the underlying tables?

To maintain an unbroken ownership chain among databases with views, the databases must be owned by the same login in the case of dbo-owned objects. Also, the DB_CHAINING option needs to be enabled for the databases involved. The user needs connect permissions to all databases accessed by the view but only permissions on the view need to be granted.

Be aware that of the security implications with DB_CHAINING. Only enable the option when you fully trust users who can create objects in databases with chaining enabled. This is less of a concern when only sysadmin role members can create objects and review scripts before deployment.


Difference between MongoDB's find and findone calls I am working on a project and I am unsure if there is a difference between the way the find cursor works and the way the findOne cursor works. Is findOne just a wrapper for find().limit(1)? I was looking around for it and maybe someone knows if mongodb has a special method for it or not. I am working with the PHP API for mongodb if that makes a difference.

Based on my own benchmarks, find().limit(1) is orders of magnitude faster than findOne().

There is either an error in the MongoDB documentation or a bug in findOne(). findOne() performs more like find().limit(N) where N is the number of documents the query would return. I figured this out while trying to figure out why my simple queries were so slow!

update: response from a 10gen (MongoDB) engineer:

  The two queries you are executing are very different. A find query
  returns a cursor, this is essentially a no-operation scenario, as no
  actual data is returned (only the cursor information). If you call
  findOne, then you are actually returning the data and closing the
  cursor. The docs should definitely be clearer :-)

Update: Indeed, if the find().limit(1) document is retrieved, the orders of magnitude speed difference seems to disappear. Also, I could not reproduce the major speed difference with the MongoDB JavaScript driver. I originally benchmarked using the MongoDB Java driver.


Unique Constraint with multiple null columns I have two tables

PC(Id,EmpName Not NULL, PCName NULL, HostName NULL,.... PhysicalLocation NULL, PCType Not NULL)
PCNetwork(Id, EmpName Not NULL, PCName NULL, HostName NULL,IPAddr,....,PhysicalLocation NULL, PCType Not NULL)

Id is a surrogate key, EmpName and PCType are not null fields and the rest can be null.
I created Unique Constraint on PC with these columns and FK on PCNetwork. If there is a null in one of the column of a record then the constraint works perfect but if more than one column has null the constraint is not working. This is a follow up question of this post

Edit:- My unique constraint and foreign key constriant is not working

alter table PC add constraint UK_PC_EMPName_PCName_PCType
unique (EMPName, PCName, HostName, PhysicalLocation, PCType);

alter table PCNetwork add constraint FK_PC_PCNetwork
foreign key (EMPName, PCName, HostName, PhysicalLocation, PCType)
references PC (EMPName, PCName, HostName, PhysicalLocation, PCType);

You can create a unique filtered index. Here is a simpler example, since I don't know what your goal is. We have a baseball team and some players have been assigned numbers; some haven't chosen yet.

CREATE TABLE dbo.PlayerUniforms
(
  PlayerID INT PRIMARY KEY,  -- FOREIGN KEY etc.
  UniformNumber TINYINT NULL -- CHECK (COALESCE(UniformNumber,0) < 100)
);

CREATE UNIQUE INDEX x ON dbo.PlayerUniforms(UniformNumber)
  WHERE UniformNumber IS NOT NULL;

Now you can enter multiple NULLs in the UniformNumber column like ANSI meant it to be, and still enforce uniqueness among the non-NULL values.


The job failed. The Job was invoked by Schedule 1012 I am new on maintaining database backups, i have problem on my scheduled transaction logs, it was working before until i encountered the error below.

Message:

The job failed.  The Job was invoked by Schedule 1012 (DATABASE01-Trans1PM.Subplan_1).  The last step to run was step 1 (Subplan_1).

The job was supposed to run at exactly 1pm daily, it is working before until i noticed that it's been 6 days since the same error occurs and the backup job was not successful.

Anyone have an idea on that kind of error message ?

Thank you in advance.

Regards

Since you indicated your 'transaction log' backup maintenance plan had been working, but was now failing, double check to make sure none of the databases involved in the maintenance plan have somehow gotten changed to SIMPLE recovery - transaction logs cannot be backed up in SIMPLE recovery mode.

Run this query to identify the recovery mode of all of your databaes.

select name,recovery_model_desc from sys.databases

To begin taking transaction log backups again, you'll need to re-establish the log chain by taking at least a DIFFERENTIAL backup (although, to keep things nice and clean, I'd recommend a new FULL backup) to bridge the gap between the time the database was changed to SIMPLE and now.

For more information about the transaction log and SIMPLE recovery, please check out these links:

Backup Under the Simple Recovery Model

Transaction Log Truncation


Database design for application with multiple useres We have a task to design a software using entity frame core and a Central SQL DB. The software will have multiple users with RBAC(Role Based Access Control). DB design shall implement user group table and users with many to many relationship.

I've read about RBAC and RLS (Row Level Security) in SQL but I can't link it to code. It will take alot of effort to create secured layer in the Database but I don't know how to use this in the code. I mean putting effort in the database design tables will be implemented in the code using the poco-class but putting all this effort in data security will affect the performance and will make the software very dependent on the the database

How can I implement RBAC in the code?
Where to start searching?
Should I just but my restrictions in the code using the queries and GUI?
Should I depend on the DBMS to manage my security or use it just as a backup layer?
What is the best practice or design pattern for the application and the Database? Should I map users in the database with RBAC and RLS and then just using them to sign in the application?

Note: it is a limited scale project with less than 100 user in the Database using a company intranet

Just to give you some ideas here are some of the ways I have seen this implemented:

all interactions with the database use stored procedures and views to limit access
groups are implemented in Active Directory. Members are added to groups. When a user logs on and is authenticated to the domain by AD the application calls a stored procedure which returns all the groups the user is a member of. The membership is cached in memory for the duration of the session. Behind the stored procedure are tables for groups, users, user => group, group=> permission  (create a unit of work, process, close...)
in the application every time a page or view loads you check what the user is authorized to do.  It leads to some less than elegant code like   IF CAN_READ then ....ELSIF CAN CREATE then....  There are undoubtedly more sophisticated ways to set the permissions.
the database has an active directory group for the administrators and a generic app user with permissions on all the stored procedures. This method means the application enforces the permission structure set in Active Directory not the database.  Others prefer the database to have roles which set user permissions but then you lose your Active Directory Management.


Clustered indexing is now must - why? Earlier,  there were non-conclusive to me debates / discussions on whether to (always) engage / avoid clustered indexes.

Well, I understood that they are to be used sometimes with proper + specific purposes and context.

SQL Azure Database Clustered Index Requirement:

  "SQL Azure does not support tables without clustered indexes. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table"

does not fit in the previous conclusions, rationale and explanations.

What is the rationale, that I have missed from prev explanations, of rigidly imposing ubiquity of clustered indexes without any exceptions?

Read Inside SQL Azure:

  SQL Azure provides logical databases
  for application data storage. In
  reality, each subscriber’s data is
  actually stored multiple times,
  replicated across three SQL Server
  databases that are distributed across
  three physical servers in a single
  data center. Many subscribers may
  share the same physical database.

Clustered keys are required so that the three replicas of your data can be kept in sync. W/o  a key, is impossible to know which rows were updated. Heaps (tables w/o a clustered index) have only physical 'keys' (fileid:pageid:slot) and since your 3 replicas of the logical database share physical database with other logical databases the physical address on one server has no meaning on the other replicas, hence heaps could not possibly be replicated.


Select Data from two tables ONLY IF ID on both tables are SAME I need to select returns result

SELECT wl.id FROM wishlist AS wl
LEFT JOIN wishlist_products AS wp ON wl.id = wp.wishlist_id

Only IF ALL of wp.product_id EXISTS in wishlist_products_bought.product_id

Thanks

Try this:

SELECT wl.id
FROM wishlist AS wl
LEFT JOIN wishlist_products AS wp ON wl.id = wp.wishlist_id
WHERE  wp.product_id in (select product_id from wishlist_products_bought)


DayOfYear as per Indian fiscal year I want to get a sequence number according to the Indian fiscal year. I tried to write a function using SQL built-in function:

DATEPART(DAYOFYEAR,'2015-04-01')

but I am facing a challenge to get day number throughout the financial year.

For the financial year 2015: First Day is '2015-04-01' and Last Day is '2016-03-31'.

Note: For this period my financial year 2015 is a leap year, should have 366 days.

The sequence number of a day in a year that begins on april 1 can be calculated this way:

SELECT 1+
       DATEDIFF(day,
           DATEFROMPARTS(
               DATEPART(year, DATEADD(month, -3, [Date])),
               4,
               1),
           [Date]) AS FYdayNumber
FROM tableWithDates;

In essence, we're subtracting three months from the current date, then using that year to create a start-of-year date (year, april, 1), then calculating the number of days between that start-of-year date and the current date. Finally, add 1 in order to make the sequence start at 1.

If you just want the first day of your fiscal year, you can use

SELECT DATEFROMPARTS(
           DATEPART(year, DATEADD(month, -3, [Date])),
           4,
           1) AS firstDayOfFY
FROM tableWithDates;

As @MarkSinkinson pointed out in another answer, avoid (user-defined) scalar functions for performance and parallelisation reasons.


Stock management - needs suggestions I need to create a database which will have below modules:

Items [Food items, stationary]
Items Stock Management [stock in out]
Customers
Items Sale Management

Issue:

The system will have different types of items & so can have different 'Unit of Measurement'. Possible Units of Measurement can be bags, pieces, Kilograms.
Another things is that certain items can be sold as a whole 'unit of measurement' OR 'part of unit of measurement', for example tomato can be sold 2 kilograms or 450 grams.

Questions:

How should i handle the 'Unit of measurement'?
What is the best way to keep the stock in out?

EDIT:

For stock in out, i am thinking about to have a separate stock transaction table that will store any in/out transaction.
I am still not clear about the 'Unit of measurement'.

Please forgive me if my question is not clear.

Thanks for your valuable time & help.

Not sure how much of help this will be for you. but I had to have an inventory database for a pub. I had similar problem with different measurements, in that at times they could sell a whole bottles and at times n number of liquid units from the barrel. it is not as complex as your problem, but it has similarities.

What I ended with, is defining a few rules:

Every product has volume
Every product can have a Base product.
Every product must either multiply or divide it's base.

Now, let's say they brought a barrel of 20 liters, and they want to sell mugs of 500ml, 330ml or whatever they have for that matter.

They'd first define a single ml of beer with a volume of 1. then they can define a whole barrel of 20kml and based that on the beer while saying that the barrel is multiplying the beer. then if they want to sell mugs of 500ml, they will define a new product based on the 20k ml barrel that has a volume of 500, but this time it will divide from the beer.

We can now buy different quantities from the supplier and distribute it in different quantities to the customers.

Your problem is slightly more complex. but perhaps this will give you a general direction.


How to make this Select an Update How can I turn this into an update query?

SELECT p.Pallet_code, Count(ps.SOHP_ID) as "Roll Count"
FROM PALLET p, PSOH ps
WHERE p.pallet_code = ps.SOHP_bin(+)
     and p.Plant_Code = 36
     and p.Pallet_Status = 1
     and p.pallet_CREA_TIME <= DATE '2017-4-11'
GROUP BY p.pallet_code
HAVING count(ps.sohp_id) = 0;

I tried this but I get a missing expression error.

I want to update pallet_status to 2:

UPDATE pallet
SET    pallet_status = 2
WHERE in (SELECT p.Pallet_code,
                 COUNT(ps.SOHP_ID) AS "Roll Count"
          FROM   PALLET p,
                 PSOH ps
          WHERE  p.pallet_code = ps.SOHP_bin (+)
          AND    p.Plant_Code = 36
          AND    p.Pallet_Status = 1
          AND    p.pallet_CREA_TIME <= DATE '2017-4-11'
          GROUP BY p.pallet_code
          HAVING COUNT (ps.sohp_id) = 0);

UPDATE pallet
SET    pallet_status = 2
WHERE  pallet.pallet_code in (SELECT Pallet_code
                              FROM (
                                   SELECT p.Pallet_code,
                                          COUNT(ps.SOHP_ID) AS "Roll Count"
                                   FROM   PALLET p, PSOH ps
                                   WHERE  p.pallet_code = ps.SOHP_bin
                                   AND    p.Plant_Code = 36
                                   AND    p.Pallet_Status = 1
                                   AND    p.pallet_CREA_TIME <= DATE '2017-4-11'
                                   GROUP BY   p.pallet_code
                                   HAVING     COUNT(ps.sohp_id) = 0
                                   ));


How to list connections in Secondary Nodes In my cluster on Atlas, have a many connections in secondary nodes.
How to list this connections?

When execute this command, list the current connections in primary node.

db.currentOp(true).inprog.reduce(
  (accumulator, connection) => {
    ipaddress = connection.client ? connection.client.split(":")[0] : "unknown";
    accumulator[ipaddress] = (accumulator[ipaddress] || 0) + 1;
    accumulator["TOTAL_CONNECTION_COUNT"]++;
    return accumulator;
  },
  { TOTAL_CONNECTION_COUNT: 0 }
)

{
    "TOTAL_CONNECTION_COUNT" : 229,
}

But, I have 700 connection in replicaSet.

Thank's

Go to the command line tools

Copy mongostat command under Set Up Diagnostics section

Update password and add --discover option at the end
Run it in any command-line tool
You should be able to see the number of connections as below for all primary and secondaries


Joining timestamped records with the most-recent-prior records from another timestamped table Say I have 2 tables:

Table: sales
  timestamp   timestamp without time zone
  amount      numeric

Table: prices
  timestamp   timestamp without time zone
  price       numeric

How can I get a result set that tells me, for each record in sales, the last price from before that sale took place?

Once I have that, I can multiply amount by the most-recent-prior price to get the total sale price.

(I did not design these tables, so I don't have the option of storing the price on each sale.)

I'm using PostgreSQL 9.3.

Edit: I can write something that works using nested subqueries that require O(n^2) space and/or time, but the sales table has millions of rows, so I'm hoping there's a better way to do it. Maybe some clever use of window functions or the Postgres-specific lead or lag?

Guess I don't need to tell you that this is a seriously awkward data model. Anyway, I think this query would do what you're looking for:

SELECT subq2.sales_time, subq2.num_sold, subq2.effective_price,
   subq2.effective_price * subq2.num_sold AS total_sale_price
FROM (

  SELECT subq1.sales_time, subq1.num_sold, subq1.max_price_ts,
    (SELECT price FROM prices
     WHERE timestamp = subq1.max_price_ts) AS effective_price
  FROM
    (SELECT sales.timestamp AS sales_time, sales.amount AS num_sold,
      (SELECT MAX(prices.timestamp) FROM prices
       WHERE prices.timestamp <=  sales.timestamp) AS max_price_ts
    FROM sales
    ) AS subq1

  ) AS subq2

ORDER BY subq2.sales_time DESC;

There's probably a more concise way to write the above, perhaps using DISTINCT ON and ORDER BY to save having to fetch the price for the MAX time in a separate subquery, but I'll leave that as an exercise for the reader.

EDIT Alright, here's a simplified version which I think should work out to be equivalent but much faster.

SELECT DISTINCT ON (sales.timestamp)
       sales.timestamp AS sales_time,
       sales.amount AS num_sold,
       prices.price AS effective_price,
       prices.price * sales.amount AS total_sale_price

      FROM sales
INNER JOIN prices
        ON prices.timestamp <= sales.timestamp
  ORDER BY sales.timestamp DESC, prices.price DESC

If that doesn't work for you, it would be helpful for you to post a lot more information such as a minimal testcase and EXPLAIN ANALYZE showing how slow the query is for you.


Decomposition In Boyce-Codd Normal Form may lose relations? Let be the following Realtion R={UtilisateurID, Nom, Prenom, AdresseEmail, Login, Passwd, ServeurMail} with the functional dependencies:

F = { UtilisateurID → Nom, Prenom
      UtilisateurID, ServeurMail → Login, Passwd;
      AdresseEmail → UtilisateurID;
      AdresseEmail → ServeurEmail;
}

What are all minimal keys?

I said that it was K = { AdresseEmail } as far it gived every others.

In order to put it BCNF I had the following algorithm

We take X→A from F

We create R¹(X,A)

F¹={X → A} R¹ BCNF

    R²=R-{A}

        E¹:F²=FD from F except those that affect A.

        E²: IF R² is BCNF → END

        ELSE We decompose R2 returning to E¹

So I did:

R isn't BCNF because no FD looks like key  → attribute ¬key

R¹={AdresseEmail, ServeurEmail}

E1: F¹={AdresseEmail  → ServeurEmail}

R²=(UtilisateurID, Nom, Prenom, AdresseEmail, Login, Passwd)

F²=(UtilisateurID  → Nom, Prenom

    AdresseEmail → UtilisateurID

   )

So my BCNF decomposition would actually be:

R¹=(AdresseEmail, ServeurMail) and

R²=(UtilisateurID, Nom, Prenom, AdresseEmail, Login, Passwd).

But we lost AdresseMail  → ServeurEmail

It isn't trivial, X is a (sur)key and A hasn't key attributes therefore it is BCNF.

Is my decomposition right? Did I did a mistake when designing the key?

Your decomposition is not correct, since in R2 you still have dependencies that violates the BCNF, for instance UtilisateurID → Nom (UtilisateurID is not a key of that relation).

The problem is that your algorithm is not correct. When you find a dependency X → A that violates the BCNF, you should decompose a relation in two relations, the first with X+, not XA, and the second one with T – X+ + X. Then you should repeat the algorithm, if you find in one of the two decomposed relation some other dependency that violates the BCNF.

So, in your example, a correct decomposition is:

R2 < (AdresseEmail ServeurMail UtilisateurID) ,
{ AdresseEmail → UtilisateurID
AdresseEmail → ServeurMail } >

R3 < (Nom Prenom UtilisateurID) ,
{ UtilisateurID → Nom
UtilisateurID → Prenom } >

R4 < (Login Passwd ServeurMail UtilisateurID) ,
{ ServeurMail UtilisateurID → Login
ServeurMail UtilisateurID → Passwd } >

Note that this decomposition preserves the functional dependencies.

Addition

How the decomposition is obtained? Starting from the original relation:

R(AdresseEmail Login Nom Passwd Prenom ServeurMail UtilisateurID)

let's consider a dependency that does violates the BCNF, for instance:

ServeurMail UtilisateurID → Login

the closure of ServeurMail UtilisateurID is (Login Nom Passwd Prenom ServeurMail UtilisateurID, so we decompone initially in two relations:

R1(Login Nom Passwd Prenom ServeurMail UtilisateurID)
R2(AdresseEmail ServeurMail UtilisateurID)

R1 is not in BCNF, since the key is ServeurMail UtilisateurID, so for instance the dependency UtilisateurID → Nom violates the normal form. Applying the algorithm, R1 is decomposed in:

R3(Nom Prenom UtilisateurID)
R4(Login Passwd ServeurMail UtilisateurID)

Both relations are in BCNF, and the final decomposition is given by R2, R3, and R4.

Finally, note that sometimes the algorithm can produce different solutions, depending on the functional dependency chosen at each step.


How to Choose Between VALUES and SELECT for INSERT? This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:

CREATE OR REPLACE FUNCTION insaft_function()
   RETURNS TRIGGER AS
$func$
BEGIN
   INSERT INTO file_headers (measurement_id, file_header_index_start
                                           , file_header_index_end)
   VALUES (NEW.measurement_id, TG_ARGV[0]::int, TG_ARGV[1]::int);

   RETURN NULL;  -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

VALUES work with many rows but with SELECT you can do much more.
The only requirement here is to do the above INSERT to the table.
You can assume that there are 100k of such INSERTs done per cycle in continuous quality assurance of a system.

I noticed these differences with my data where selected three median values here:

VALUES
real      user      sys
-------------------------------
0m0.353s  0m0.256s  0m0.028s
0m0.327s  0m0.252s  0m0.036s
0m0.358s  0m0.252s  0m0.040s
so average real 0.34s

SELECT
real      user      sys
-------------------------------
0m0.362s  0m0.256s  0m0.024s
0m0.383s  0m0.236s  0m0.056s
0m0.356s  0m0.264s  0m0.032s
so average real 0.36s

So this small subset of data says that VALUES is faster with such a simple INSERT. I am interested in requirements for concurrent processes and real time data analysis.

How can you decide between SELECT and VALUES for INSERT?

The measured difference is almost certainly noise. Run some more iterations, you won't get consistent result. The difference in performance (if any exists) won't be measurable.

You can use either method here. Both are equally good for the purpose.
There are often multiple ways in SQL. And sometimes there is no clear winner.

The more important issue concerning performance here:

  100k of such INSERTs done per cycle

For big bulk-inserts it would be faster to INSERT in both tables instead of firing a trigger for every row.

If you are using a serial PK that's generated automatically, you can employ the RETURNING clause in a data-modifying CTE

WITH ins1 AS (
   INSERT INTO measurement (measurement)
   VALUES ...   -- OR SELECT ... if data comes from inside the DB :)
   RETURNING measurement_id  -- generating a serial ID?
   )
INSERT INTO file_headers (measurement_id, file_header_index_start, file_header_index_end)
SELECT measurement_id,  1, 666  -- here it *must* be SELECT
FROM   ins1;

A numerical constant like 666 (no quotes, just digits)  defaults to type integer automatically.

May not be applicable, depending on your workflow.

Related:

Bulk insert in multiple tables


CXPACKET wait on SQL Server Recently I am experiencing performance issue on a SQL Server database and I have two queries that show me the wait as shown below, one of the databases on this server is a member of Always On replica, but the load is on the other database.

The first query is my own query which collects the waits stat during 10 seconds and calculates the difference and the second list if from Pauls's query:
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Regarding the differences, I'm wondering should I have to ignore CXPACKET and Redo_Thred_Pending_Work wait and work on the root cause of PageIOLatch_SH wait (maybe poor indexing).
MAXDOP value is 0 and the server has 36 cores, any thoughts?

It's very hard to comment unless we know the nature of performance issue you are having which could be because of various factors.

Also waits you have captured are of 2 different means. One collected for the waits witnessed during the time you calculated for specific queries run. While other from Paul Randal is the aggregated one since last restart pointing CXpackets which does not necessarily indicate issue with it. That it collected wait stats for all the process that would have run since last restart. So it depends if you are looking to troubleshoot overall server performance or just those queries whose waits were captured.

Also depending upon the version of your sql server there is better way of interpreting CXpackets waits. Post sql 2016 SP2 you now have cxconsumer as well and hence can distinguish between good and bad CXpacket wait stats. Read  here for more on this by Aaron Bertrand.

Also I see you have MAXDOP 0 which should be tested with best magical number depending upon logical processors to be tweaked. Per my experience and support from MS the best magical number that works for most of our numbers is between 4 to 8 where logical processors is greater than 8. You can also see dbatools to look for a number recommended in your environment.

In addition to MAXDOP don't forget to check on cost threshold of parallelism for the server. 5 the default does not work well.

However you should also look other factors like indexing strategies finding opportunity to tune them thus helping in gaining performance


Error while deploying package to catalog I am getting below error while deploying package to SSIS catalog on the last step, I am sysadmin and have access to the below views. Can someone help please. Let me know if you need more info. Any help is appreciated

  The SELECT permission was denied on the object 'database_principals',
  database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server,
  Error: 229)

The issue may occur if the user has Deny privilege on the target objects as well. Please check the permissions settings on the target database.

You should remove this roles from your account.

db_denydatareader
db_denydatawriter


PostgreSQL: What is data replication? I read from a post about comparing MySQL and PostgreSQL, there is one who said:

  The biggest disadvantage of PG for me had been the lack of
  replication, but it's recently gotten both asynchronous and
  synchronous replication.

Source: https://www.quora.com/What-are-pros-and-cons-of-PostgreSQL-and-MySQL

So my question is: What does that "PostgreSQL lacks replication" mean?

It means nothing, really. PostgreSQL has a number of replication features, as the relevant chapter in the manual details.

That article is apallingly wrong and outdated. Ignore it. "No referential integrity" in MySQL? Bah.

If you have an account on that site, please ask for it to be removed or fixed or whatever can be done. As it stands it's dangerously misleading and wrong, and has no place claiming to be current accurate information.


Creating indexes vs Rebuilding indexes (larger db/log file) I am a bit lost on what it the right way to do this.
I am migrating data from one db to another (corrupt errors) so i did the following:

Create a new blank db
Generate a object script from my source db (not including indexes because I want my data copy to be fast), run on the new db
Generate a script for all my indexes from source
Copy all my data from source to dest using tsql, all is well
Run my create index on the dest, all is well, my new db size is approx 1.76GB with approx 760MB being the log file
Shrink the database (doing this for testing only at this point)

Now if I run the following command (to rebuild all the indexes)

EXECUTE sp_msForEachTable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD;

The new sizes are 3600mb for the db and 1608MB for log

My question is doesn't creating indexes on a fresh db same thing as rebuilding or is there more to it than that. Also should I be using rebuild all indexes?

I guess I am trying to find out what I should be doing in the future, drop/recreate indexes every once in a while, or just run the rebuild command.

My database size is getting too large for the space I have left at my hosting.

I am trying to keep my db sizes down as much as I can, because I have 10 dbs and limited space on my VPS and recently I was getting errors that my db is corrupted (I do not have the backups) so this is why I am creating new dbs, moving data and starting "fresh" so I want to make sure everything is as optimized as possible going forward.

I'd be a bad human being if I didn't start by saying this:

YOU NEED TO START TAKING BACKUPS

After looking at your other questions, I'd be an even worse human being if I didn't say this next:

MOVE TO A NEW SERVER

It sounds like there's a more serious problem here if you're running into data corruption over and over again that repairing isn't fixing. Simply moving to a new database on the same server may lead you right back into corruption.

With that out of the way:

  Do I need to rebuild nonclustered indexes after I create them?

Rebuilding indexes immediately after creating them doesn't really do anything for you. A newly created index is about as "optimized" as an index can get. Rebuilding indexes should be a rare event, typically reserved to correct an issue that isn't fragmentation. Rebuilding indexes is a size-of-data operation. However big your largest index is is how much space your log file needs when it's rebuilt.

  How do I keep my database as small as possible?

Check your recovery model. If you're in Full, you need to take log backups to control the size of the log file. Typically, more frequent will keep them smaller. If you're not in the mood to take log backups, put your database in Simple recovery. If you're unsure about which recovery model is right for you, start here.
If you're dead set on rebuilding indexes in the future, try using the SORT_IN_TEMPDB option.
If you're on Enterprise Edition, or SQL Server 2016 SP1 Standard Edition, you can use Page or Row compression to keep index sizes down.
Look at archiving data. The best way to keep database sizes small is to keep data small.
If they suit your workload and query patterns, filtered indexes can help, because you're not putting all the table data into an index.
Make sure your tables have clustered indexes. Heaps can do weird things to space used.
Check your data types. Are you using CHAR or NCHAR? Are you using NVARCHAR where VARCHAR would do? DATETIME where you just need a DATE? If you're worried about database sizes at the 4GB mark, bytes saved here can matter.

  Why did my log file grow so much?

Memory seems to serve that when you issue the REBUILD ALL command, this rebuilds all your indexes in a single transaction, whereas issuing individual CREATE statements will give your log backups or Simple recovery model CHECKPOINT magic a chance to kick in.

If you're looking to bring some sanity to index maintenance, check out Ola Hallengren's free scripts, and some examples of how to not run them foolishly here.


Replication with FileStream I need to setup database replication (for high availability) on a database that uses FileStream. This is on SQL Server 2012 Standard Edition, so Always On or Peer-to-Peer replication is not an option. Also, database mirroring does not work with FileStream.

The only option I have left is transactional or merge replication. I have found that both replication types work fine when all machines are on, but what happens if one machine is off?

This is what I have (servers run on Microsoft Azure as virtual machines):

Server A is DC server
Server B is SQL Server 2012 Standard and Application server (IIS)
Server C is SQL Server 2012 Standard and Application server (IIS)

Microsoft performs maintenance at least once a month, where at most one of the servers is turned off each time. How can I set up replication with these machines so I can achieve HA when one server is down?

Although replication may not be High Availability in design, it depends on your definition of HA.  Certainly it has been used for HA by many people.

If replication is down long enough it can be marked as Inactive.  To automatically reactivate a replication, you could try using Kin's response: SQL Server replication subscriptions marked as inactive

This runs a job that periodically checks the status of replication and reactivates it.


SQL 2005: Can we determine how much the rebuild index maintenance job can grow database log files? On SQL server 2005, if all the databases are in Full mode (with hourly transaction log backups), is it possible to determine if rebuilding all indexes of a database can grow log file of a database? And how much can it grow?

If there is no straight answer then any directions would be really appreciated.

Thanks in advance.

Yes, you should look at the excellent whitepaper on this topic although it refers to the online index rebuild, it still has lot of good info

http://technet.microsoft.com/en-us/library/cc966402.aspx

If the log files are auto growing then you can find that information using the default trace after the action is completed.

DECLARE @filename VARCHAR(255)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;

--Check if the data and log files auto-growed. Look for tempdb, log files etc.
SELECT
    gt.ServerName
    , gt.DatabaseName
    , gt.TextData
    , gt.StartTime
    , gt.Success
    , gt.HostName
    , gt.NTUserName
    , gt.NTDomainName
    , gt.ApplicationName
    , gt.LoginName
FROM [fn_trace_gettable](@filename, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass in ( 92, 93 ) --'Data File Auto Grow', 'Log File Auto Grow'
ORDER BY StartTime;
--


Create table from another table in Oracle Procedure I have a SQL Server stored procedure wherein I am creating a table using another table.

Select Distinct
 col1
,col2
,col3
,col4
into table_2
from table_1;

Above statement will create a new table as ' table_2' with the selected list of columns.

I tried to convert the above query into Oracle syntax:

Create table table_2 as
select
 col1
,col2
,col3
,col4
from table_1;

This is working fine as expected when I am running it in the editor. However, the same statement is giving me errors when I am trying to use this in Oracle Procedure.

I have gone through some links or so, which says that need to create some temp variables or storage or loop in to perform the same task.

I am getting very confused now and don't know how to convert this simple statement into valid Oracle syntax.

If you want to create a table (or do any other DDL) in a stored procedure, you'd need to use dynamic SQL

EXECUTE IMMEDIATE 'create table table_2 as select col1, col2, col3, col4 from table_1';

If you create the table dynamically, though, you'd also need to use dynamic SQL every time you wanted to subsequently query the table.  That generally makes your code much harder to build and test.  And you won't be able to have two sessions running the procedure at the same time since their create table statements would conflict with each other.

Taking a step back, in SQL Server, it is very common to create local temporary tables inside procedures.  It is very, very rare to do that in Oracle.  It rarely makes sense to try to port logic from SQL Server to Oracle (or vice versa) directly like this.  If you do, you'll find that Oracle is lousy at running SQL Server code (and SQL Server is lousy at running Oracle code).  You'd generally want to take the logic from SQL Server and figure out the best way to implement that logic in Oracle.

Oracle does have global temporary tables where the table definition is global but the data is local to your session (or transaction).  Potentially, you want to do

create temporary table table_2 (
  col1 number,
  col2 number,
  col3 varchar2(10),
  col4 date
);

and then insert into that table in your stored procedure rather than trying to create the table dynamically.


Error 5123 when creating a database with my SQL Server on Linux I followed the steps of this MS Guide and I managed to run a localdb and create a Testdb with this script:

CREATE DATABASE Test
ON
    (NAME='Test_DAT',
    FILENAME='/home/dlag/Documentos/Proyectos/BDtest/Test.mdf',
    SIZE=10,
    MAXSIZE=50,
    FILEGROWTH=5)
LOG ON
    (NAME='Test_Log',
    FILENAME='/home/dlag/Documentos/Proyectos/BDtest/Test.ldf',
    SIZE=5,
    MAXSIZE=25,
    FILEGROWTH=5);

GO
use Test;
GO

Time after, I try to create another db with the following script:

CREATE DATABASE BconDatos2
ON
    (NAME='BconDatos2_DAT',
    FILENAME='/home/dlag/Documentos/Proyectos/BDs/BiblioConDatos2/BconDatos2.mdf',
    SIZE=10,
    MAXSIZE=50,
    FILEGROWTH=5)
LOG ON
    (NAME='BconDatos2_Log',
    FILENAME='/home/dlag/Documentos/Proyectos/BDs/BiblioConDatos2/BconDatos2.ldf',
    SIZE=5,
    MAXSIZE=25,
    FILEGROWTH=5);

GO
use BconDatos2;

and I get this errors:

Msg 5123, Level 16, State 1, Line 4

AND SAYS(translated from Spanish):

  The instruction CREATE FILE found OS error: 2(The system cannot find the file specified.) when trying to open or create the physical file.
   '/home/dlag/Documentos/Proyectos/BDs/BiblioConDatos2/BconDatos2.mdf'.

Msg 1802, Level 16, State 4, Line 4

(translated from Spanish):

  CREATE DATABASE error. Can not create some of the files in the list. See related errors

Total execution time: 00:00:00.081

On both DB I'm in master and the path for mdf and ldf files are previously created.

It's strange because in the script of 'Test' DB I can make SELECTS for Tables, but when I run:

SELECT Name from sys.Databases

This database doesn't appear, but another empty DB created later appears.

Anyway on 'BconDatos2' DB script I can't create DataBases...

Problems with permissions on scripts?

The problem isn't solved, but I just donwloaded the software 'Azure Data Studio' developed for linux and I have been able to see that the tables were being created in master. It's seems this app works and is more convenient to me for learn msslq by now. So maybe we can close the post.


"Restore With Replace" or drop/restore with Instant Initialization on? What exactly does the restore argument "With Replace" do? I'm looking at needing to restore a database back to a beginning point on a regular basis, and I've been trying to figure out if there are any disadvantages to using Restore With Replace versus Dropping/Deleting the database entirely and restoring it.

Will "With Replace" wipe the log files and reset whatever bits might be left in system databases as well? It seems that it would be much quicker, as I don't have to wait for the database to finish dropping (the database in question is around 2TB). I've already checked the TechNet article on the Restore arguments, it doesn't go into this specific question.

A RESTORE DATABASE ... WITH REPLACE will completely overwrite the database and database log files without the need to drop them first.

On a replace the database settings are also repopulated in master.  For example, if a database was in SIMPLE recovery model, but the RESTORE WITH REPLACE was in FULL recovery model, the restored database would still be in FULL recovery mode.

Additional note: RECOVERY can be considered risky, since it skips over certain safety checks.  See REPLACE Option Impact at http://technet.microsoft.com/en-us/library/ms186858.aspx

But if you know your restore is replacing the correct files then it works like a charm.


Azure Sql Serverless Vs Basic Tier Does anyone have experience of moving a database from an Azure SQL Basic, or Standard tier, to Serverless?

I'm currently investigating whether to do this for our development, UAT and staging databases, to hopefully save a bit of money. They're currently averaging between £5-£40/month each.

The workload for them seems ideal for serverless, a few hours use, then nothing for days / weeks at a time, but despite staring at lots of Microsoft's docs about DTU vs VCore pricing, I wouldn't say I feel entirely confident that the billing would actually be reduced if we did switch over.

Would switching to Serverless, at as similar a performance level as possible definitely reduce the bills? Has anyone got any real world experience of this working out?

That's exactly the benefit the programming manager of one of the companies I work with highlighted, he told me "with serverless we can test with the same performance level of the production database, but paying less. We will pay only for the specific period of times when we test".

Azure SQL Serverless application scenarios:

Examples:

New apps when compute sizing requirements are unknown.
Development/test workloads
Line-of-business applications
Content management systems, e-commerce and more.

Typical serverless usage patters are:

Intermittent, unpredictable usage and lower average compute utilization over time.
Frequently rescaled databases.
New databases without usage history.

Usage patterns when serveless is not recommended:

More regular, predictable usage and higher average compute utilization over time.
Databases sensitive to performance trade-offs from scaling.
Multiple databases that can be consolidated into elastic pools.

Price-performance shape (serverless).

In terms of auto-scaling, min and max vcore range available to database.  Memory and IO limits proportional to vCore range selected.
Database is auto-paused if inactive for long enough. Database is autoresumed when login is received.
In terms of billing, compute billing is per second based on vcores and memory used. Minimum compute billed based on min vcores and min memory configured. Only the storage is billed when the database is paused.

The following image compares provisioned compute vs serverless compute.


Adding a new slave slowing down previous replicas? I have a master I was going to start slaving to a new server to do some tests on. This master is currently being slaved to 3 other boxes.

After bringing the new slave online it became clear it wasn't able to keep up.  I was hoping this was just an issue of caches needing to get warmed, but after a couple hours it was clear it was just falling farther and farther behind.

It's not as beefy hardware wise as the other slaves (less ram, slower disks) so that seemed like likely culprites.

However one thing that seemed odd was right around the time the new slave started going the other preexisting slave, which had no problem keeping up with the load for that time of day started falling farther and farther behind as well.

Any pointers on what to look at or why this might have happened.  We're running MySQL 5.1.  The new slave is in the same DC as the master and preexisting slave.

Edit: After we stopped slaving on the new slave to get some faster disks in, the preexistings slave's replication started to catch up again.

Edit: (Solution Found) I had copied a my.cnf from another slave (the one that was falling behind mysteriously).  I updated some configs such as innodb buffer size to be more appropriate for the memory the new slave had but neglected to update the server-id.  Apparently mysql does nothing to prevent this but everything described in http://bugs.mysql.com/bug.php?id=16927  was going on (Slave error's mentioned in the error log, an INSANE amount of relay logs getting generated).

I updated the server id on the new slave and things look to be going well.  Well, insofar as it's not causing the other slaves to fall behind.

Your new slave server can possibly be slow in some respect and not able to process the queries in the binary log as quickly as the other servers.

You should also look at the possibility that you may have run a very heavy query that caused the new slave to lag as well as the other slaves.

Regarding the new slave, you may have a performance problem that needs to be resolved. Possibilities could include:

Slow hard disks (you mentioned you are buying new ones)
Bad my.cnf configuration. Flushes too often to disk? flush method isn't O_DIRECT?
Long running queries from master server - consider using row-based replication if you feel it helps your overall system performance.
Caches on the new slave aren't warm for running the queries from the master - do you use INSERT INTO ... SELECT .. FROM statements? or non-deterministic insert statements like ones with sub-queries?
Raid card with battery - do the other servers have it and the slave doesn't?


Why is MySQL running as a single process? I have two MariaDB(InnoDB) linux servers. Even if my.cnf is almost the same they behave very differently. On the bad machine MariaDB is ver 10.0.16-MariaDB-1, on the good machine is 10.0.27-MariaDB-0+deb8u1.

They act on different same-size databases (around 3GB) similar in structure and usage. The bad machine has a few more clients (7 against 4) reading the data. DBs don't perform any calculation: they just provide storage.

Checking the OS processes with top/htop I see that the one that misbehaves has only ONE process, while the good one has 31 distinct PIDs. I could imagine that this is the bottleneck.

MySQLTuner returns a big difference for table cache hit rate: bad machine 6% (400/6K opened), good machine 118% (400/338 opened).

This is the configuration of the bad one.
Where can I set the max number of processes? Or is there something else I didn't take in account?

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = XXXXXX
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
innodb_file_per_table  = 1
innodb_file_format     = barracuda

skip-name-resolve

bind-address        = XX.XX.XX.XX

#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 128M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M

#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M

#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

innodb_buffer_pool_size should not be set to 0.  It should be at least 100M, up to about 1000M for a 4GB machine that is running only MySQL.

This may be indirectly causing your problem.


Extended Events replacement for Virtual file stats Is there a way to monitor virtual filestats data with Extended Events, instead of doing (select * from sys.dm_io_virtual_file_stats) in SSMS?

Extended Events is not a replacement for DMVs; they are a replacement for SQL Trace.  Periodically querying the DMV is going to be less expensive than using an Extended Events Session.

The SQL Server storage engine always updates the file stats, whether you choose to query them or not.

Adding an Extended Event Session that captures each individual IO would introduce additional overhead for each read and write, and should only be done for debugging or troubleshooting, not performance monitoring.


In Postgres, is it possible to get all rows from a table where a column can be cast to a different type? I have a table in a Postgres DB that has a TEXT column, named data, that I use to store both IP addresses and domain names. I'm wondering if it's possible to select all the rows in the table whose data column is a valid IP address.

Can this be done, say with a WHERE clause based on a type cast?

You can create a function for this:

create function is_valid_ip(p_data text)
  returns boolean
as
$$
begin
  return p_data::inet is not null;
exception
  when others then return false;
end;
$$
language plpgsql;

This function has the advantage that the same validation is done when casting and when filtering the rows.

BUT this will be quite slow, because an exception block in PL/pgSQL is quite expensive and catching one is even more expensive. So if you have a large number of invalid addresses this is going to be really slow.

You can use the function like this:

select *
from the_table
where is_valid_ip(address)

Another option is to use a regular exception for this:

create function is_valid_ip(p_data text)
  returns boolean
as
$$
  select p_data ~ '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}';
$$
language sql;

This function will perform a lot faster than the one that uses casting, but has the downsize that it uses a slightly different rule for checking if the IP address is valid. The regex only check IPv4 addresses, so will most probably also want to extend the regex to check for IPv6 addresses as well (which the cast will do automatically)


Backup a hot standby database (streaming replication) whilst it is in recovery Synchronous Streaming Replicating is set up between a master and a hot standby database.

It seems like a good idea to perform daily backups on the standby database to avoid any performance issues on the master database.

The backup of the standby db was successfully restored using pg_basebackup. Archived WAL files were transferred locally and the restore_command was added in recovery.conf to point to them - the following errors were recorded in the log files whilst starting up the database:

cp: cannot stat ‘/archivedir/wal_arch/00000002.history’: No such file or directory
2016-05-12 17:33:52 BST LOG:  starting archive recovery
cp: cannot stat ‘/archivedir/wal_arch/0000000100000000000000E7’: No such file or directory
2016-05-12 17:33:52 BST LOG:  redo starts at 0/E7000CA0
2016-05-12 17:33:52 BST LOG:  consistent recovery state reached at 0/E7000D80
2016-05-12 17:33:52 BST LOG:  invalid resource manager ID 100 at 0/E7000D80
2016-05-12 17:33:52 BST LOG:  redo done at 0/E7000D48
cp: cannot stat ‘/archivedir/wal_arch/0000000100000000000000E7’: No such file or directory
cp: cannot stat ‘/archivedir/wal_arch/00000002.history’: No such file or directory
2016-05-12 17:33:52 BST LOG:  selected new timeline ID: 2
cp: cannot stat ‘/archivedir/wal_arch/00000001.history’: No such file or directory
2016-05-12 17:33:52 BST LOG:  archive recovery complete
2016-05-12 17:33:52 BST LOG:  MultiXact member wraparound protections are now enabled
2016-05-12 17:33:52 BST LOG:  database system is ready to accept connections
2016-05-12 17:33:52 BST LOG:  autovacuum launcher started

Is it a good idea to backup the standby database instead of the master?

Yes, it can be a good idea.  In fact, allowing this was a major feature in the release of PostgreSQL 9.2.