First, you are using an old method to connect. The newer and better method is using MongoClient, which is supported in all recently released supported drivers. It's explained in detail here: https://mongodb.github.io/node-mongodb-native/driver-articles/mongoclient.html. Note that although the link discusses the node driver implementation of it, it is also relevant for pymongo.

Second, you're not iterating on the returned cursor. This means that the find() query was not executed on the server. I presume you see a really fast result in this test, which will not be the case if you're actually getting data from the server.

Third, Python is single-threaded by its nature. Pymongo is smart enough to realize you're not iterating the cursor, so it reuses the connection instead of creating a new one. Creating a new connection is very expensive, so drivers will not do it unless it's really necessary. The two connections you're seeing is likely one for monitoring server status, and the other for executing your query. The cost of opening a new connection leads to drivers using a connection pool instead.

Fourth, Robo3T is a GUI, so it naturally needs to open more connections since presumably it requires a lot of information from the server, perhaps asynchronously. This is a very different situation vs. a driver. You can't really compare the two.

Finally, performance testing is a tricky subject and must be done in a very controlled manner. Some things that needs planning:

How is the server provisioned, how big are the documents, how compressible are they, etc.
Can you determine whether any performance bottleneck is due to the server, the network, the driver, the query, or how the language is used. For example, if you try to iterate on the cursor using list(db.test_db.find()), how can you tell if the database is slow, or is it Python's list() method that is slow?
Running the testing code in the same machine as the database server might introduce resource contention, artificially skewing the result.


Changes in Oracle Database implicit conversion rules for select statement

So you have a VARCHAR column, and a NUMBER filter. The implicit conversion indeed allows a NUMBER -> VARCHAR conversion, but the problem is that when applying the filter, Oracle tries to do it the other way, ie. to convert the VARCHAR column into a number, thus the error gets thrown :)

Don't really see any solutions here except updating the SQL query. This is why nobody recommends the usage of implicit conversions. They could also cause a slight decrease of performance.

EDIT: If you don't have access to the query and have to wait for the dev team, but instead you have enough privileges on the database, and know for sure that the VARCHAR column holds only number, you could try to alter the table and convert the column type.


verify barman backed up files after failure

Use the standard barman commands: barman status, barman show-backup and barman check-backup. See documentation.


There are many possible explanations:

UUIDs have terrible performance on large tables.
What was the value of innodb_buffer_pool_size?  It should be about 70% of available RAM.  Poor caching, especially due to UUIDs could ba a problem.
That UPDATE needs INDEX(SourceId, ParentKey) (in either order).
Why prefix the index on OperationId when it is already that length?
Don't use utf8 on hex strings.
Packing UUIDs into BINARY(16) would shrink the table, thereby providing more speed.
Updating 80K rows at once takes a lot of effort, especially when planning for a possible ROLLBACK.  This may partially explain why InnoDB seems to be slower than MyISAM.  Will you often update that many rows?  Sound like a design flaw.


Optimizer gets the wrong number of estimated rows even with updated stats

Have a look at this DBCC UPDATEUSAGE command:

DBCC UPDATEUSAGE(N'db_name',N'dbo.table_name') WITH COUNT_ROWS


Select multiple rows based on date interval from two fields

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

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

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

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

--SELECT    * FROM  cteSeries OPTION (MAXRECURSION 0)

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

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

and the output :

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


"Access denied for user" error when creating a procedure

From  MySQL Documention:

  The CREATE ROUTINE privilege is needed to create stored routines (procedures and functions).

Example:

GRANT CREATE ROUTINE, ALTER ROUTINE ON yourDB.* TO youUser;

Max


In Oracle 12c, how can we implement pagination on large table using rowid?

You need to try following by yourself multiple times to see if you are getting any improvement in performance with the correct result.

I tried different approaches and Following is giving good performance in my case.

DROP TABLE STUDENT;

CREATE TABLE student (RNO NUMBER, NAME VARCHAR2(1000));

-- I have just added 2,000,000 records
INSERT INTO STUDENT
SELECT LEVEL, 'STUDENT - ' || LEVEL
FROM DUAL CONNECT BY LEVEL <= 2000000;

-- selecting the 10th page of length 100 each
SELECT /*+ ordered use_nl(p s) */
    *
FROM
    (
        SELECT
            ROWNUM RN,
            RD
        FROM
            (
                SELECT
                    ROWID RD
                FROM
                    STUDENT
                ORDER BY
                    RNO
            ) T
        WHERE
            ROWNUM < 1000
    ) P,
    STUDENT S
WHERE
    RN > 900
    AND P.RD = S.ROWID;

A Rowid is made by OOOOOOFFFBBBBBBRRR
O is object id，F is file id，B is block id，R is row number.
Starting from 11g some new mechanism is introduced. If the next rowid is in the same block with the current row, then the current block can be reused. Hence, "Consistent gets" increase.

I hope you will get direction in solving your issue.

Cheers!!


There's no way to do this automatically but you can query the catalogue views to find constraint names not in the desired pattern then generate the desired script that way.

Something like

DECLARE @Script NVARCHAR(MAX);

WITH FK
     AS (SELECT *,
                'FK_' + OBJECT_NAME(parent_object_id) + '_' + OBJECT_NAME(referenced_object_id)                  AS provisionalName,
                ROW_NUMBER() OVER (PARTITION BY parent_object_id, referenced_object_id ORDER BY create_date) - 1 AS RN
         FROM   sys.foreign_keys),
     Combined
     AS (SELECT name COLLATE DATABASE_DEFAULT        AS name,
                OBJECT_SCHEMA_NAME(parent_object_id) AS schemaName,
                provisionalName + CASE
                                    WHEN RN = 0 THEN ''
                                    ELSE CAST(RN AS VARCHAR)
                                  END                AS provisionalName
         FROM   FK
         UNION ALL
         SELECT name,
                OBJECT_SCHEMA_NAME(parent_object_id)       AS schemaName,
                type + '_' + OBJECT_NAME(parent_object_id) AS provisionalName
         FROM   sys.key_constraints)
SELECT @Script = x.rename.value('.', 'NVARCHAR(MAX)')
FROM   (SELECT 'exec sys.sp_rename ''' + QUOTENAME(schemaName) + '.' + QUOTENAME(name) + ''', ''' + provisionalName + ''';
'
        FROM   Combined
        WHERE  provisionalName <> name
        FOR XML PATH(''), TYPE) x(rename);

PRINT @Script;
--EXEC (@Script);


Install MariaDB without password prompt in Ubuntu

A mixture of the mysql/mariadb-Nameing has worked for me:

export DEBIAN_FRONTEND=noninteractive
debconf-set-selections <<< 'mariadb-server-5.5 mysql-server/root_password password rootpass'
debconf-set-selections <<< 'mariadb-server-5.5 mysql-server/root_password_again password rootpass'


Group and count array elements using intarray

Keep the basic query you already have to cheaply identify rows that have any relevant array elements using an index.
Then unnest only the intersection (tag_ids & '{1,2,4,11}') in a LATERAL join. Finally, aggregate:
SELECT tag_id, count(*) AS ct
FROM   items i
     , unnest(tag_ids & '{1,2,4,11}'::int[]) tag_id
WHERE  tag_ids && '{1,2,4,11}'::int[]
GROUP  BY tag_id
ORDER  BY count(*) DESC;

Once again, the intersection operator & of the intarray module is instrumental.
Previous related answer:

Order result by count of common array elements

Without intarray
If you don't have intarray installed, or for any other array type, we need another join instead:
SELECT tag_id, count(*) AS ct
FROM  (
   SELECT *
   FROM   items
   WHERE  tag_ids && '{1,2,4,11}'::int[]
   ) i, unnest(tag_ids) t(tag_id)
JOIN unnest('{1,2,4,11}'::int[]) x(tag_id) USING (tag_id)
GROUP BY tag_id
ORDER BY count(*) DESC;

Subtle difference: the intersection operator folds duplicates to produce distinct elements, while this query does not. Only matters if there can be duplicate array elements ...
SQL Fiddle  demonstrating both and with an additional row that has a mix of matching and not matching elements to demonstrate the need of the intersection or additional join to eliminate unwanted elements.


Quite convoluted but should work and return the 2 rows with values below the @repid, the row with @repid and the 2 rows with values above the @repid (assuming that id is a unique key).

If there are less than 2 values below or above, the query will get more from the other side. In total a maximum of 5 rows will be returned.

Note: the ORDER BY is needed. All three of them (not the 4th, that's only if you need the results in specific order):

with a as
  ( select top (4)
        *, rnk = row_number() over (order by id desc)
    from tblLookups
    where Id < @repID
    order by id desc
  ),
b as
  ( select top (5)
        *, rnk = row_number() over (order by id) - 1
    from tblLookups
    where Id >= @repID
    order by id
  ),
c as
  ( select top (5) *
    from
      ( select *
        from a
      union all
        select *
        from b
      ) x
    order by rnk
  )
select *
from c
order by id ;

Tested at SQLfiddle.


Shutting down mongod while mongodump is running

No we cannot . other wise it will give error of server not reachable. As it  connects to DB via mongod service like normal connections. follow below link https://docs.mongodb.org/v3.0/tutorial/backup-and-restore-tools/  clearly mentioned that Mongos or mongod service should be running on machine.


Distribute n questions to m students with all students having the same questions but in different orders

TL;DR - eventually figured out how to do this in a single pass - that answer's at the end. I left the original answer in place in order for people here to maybe learn from my own (creaking ;-) ) thought processes!

Update: there's an alternative answer provided below.

This proved to be quite tricky. To solve this, I did the following (see fiddle here)

I created tables:

CREATE TABLE test_question
(
  tq_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  q_text VARCHAR (256) NOT NULL
);

the text of the questions doesn't really matter for the purposes of this demonstration:

INSERT INTO test_question (q_text) VALUES ('q1');
INSERT INTO test_question (q_text) VALUES ('q2');
INSERT INTO test_question (q_text) VALUES ('q3');
INSERT INTO test_question (q_text) VALUES ('q4');
INSERT INTO test_question (q_text) VALUES ('q5');
INSERT INTO test_question (q_text) VALUES ('q6');

Then the assigned question part:

CREATE TABLE assigned_question
(
  aq_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  st_id INTEGER,  -- Student id
  qu_id INTEGER,  -- Question id
  q_text VARCHAR (256) -- text of the question
);

Then, to generate a random list of student_question combinations, i.e. filling the assigned_question table, I used two RECURSIVE WITHs (or CTEs):

INSERT INTO assigned_question (st_id, qu_id)
(
  WITH RECURSIVE student AS (
    SELECT 1 AS s_no
    UNION ALL
    SELECT s_no + 1 AS value
    FROM student
    WHERE student.s_no <= 5 -- 6 students
),
question AS (
    SELECT 1 AS q_no
    UNION ALL
    SELECT q_no + 1 AS value
    FROM question
    WHERE question.q_no <= 4 -- 5 questions
)
SELECT * FROM student
CROSS JOIN question
ORDER BY s_no, RAND()
);

The important bit here is the RAND() function which randomly orders the question part of the result set. You assign a student a random student number and then tell them to select from the master table according to their id number.

Now, I can't guarantee that every student will have a completely different set of questions, but the majority of them should have a different list of questions - if you don't allow them to speak to each other and tell them to answer all questions, in order, even if the answer is blank, then you have a reasonable way of checking that they were answering their own set of questions. The few students who do have matching question lists won't be able to communicate anyway!

then, join this assigned_question table to the test_question table thus:

SELECT aq.aq_id, aq.st_id, aq.qu_id, tq.q_text
FROM assigned_question aq
JOIN test_question tq
  ON aq.qu_id = tq.tq_id;

Result (sample, see fiddle for full results):

aq_id   st_id   qu_id   q_text
    1       1       2       q2
    2       1       5       q5
 ... results omitted for brevity
    6       2       4       q4
    7       2       3       q3

So, we can see that student 1 will have to answer questions 2 and 5 first whereas student 2 will be answering questions 4 and 3 first (full results below).

This is probably the best you can do in pure MySQL.

Update - single pass query:

I've "fiddled" with this a bit more (pardon the pun! :-) ) and it can in fact be done in one pass, as follows:

(Aside: also see this more elegant fiddle which more closely resembles my second answer).

INSERT INTO assigned_question (st_id, qu_id, q_text)
SELECT s_no, q_no, q_text FROM
(
SELECT * FROM
(
  WITH RECURSIVE student AS (
    SELECT 1 AS s_no
    UNION ALL
    SELECT s_no + 1 AS value
    FROM student
    WHERE student.s_no <= 5 -- students - 6 questions
),
question AS (
    SELECT 1 AS q_no
    UNION ALL
    SELECT q_no + 1 AS value
    FROM question
    WHERE question.q_no <= 4 -- students - 5 questions
)
SELECT * FROM student
CROSS JOIN question
ORDER BY s_no, RAND()
) AS z
JOIN test_question tq ON z.q_no = tq.tq_id) AS a
ORDER BY a.s_no, RAND();

Result:

aq_id   st_id   qu_id   q_text
    1       1       3       q3
    2       1       2       q2
    ... results again omitted for brevity...
    6       2       4       q4
    7       2       3       q3

So, student 1 will answer questions 3 and 2 first and student 2 will answer 4 and 3 first. On inspection, the results I generated with this last fiddle show that every one of the 6 students has the first 2 questions different, so it appears to be working well. Remember however that the RAND() function does not guarantee this and there may be students with the same question order - but with 30 students and 20 questions, this is highly unlikely! HTH! p.s. welcome to the forum! :-)

Alternative Answer:

What you can do is "stagger" (see explanation of results below) the questions asked of the students. All the necessary DDL table construction and DML data is at the bottom of the post - I'll just go through the "core" of the SQL below. Fiddle available here.

SELECT
  ROW_NUMBER() OVER () AS "Num",  -- all records - not strictly necessary
  t.s_id,
  CONCAT
  (
    t.q_id,
    CASE
      WHEN t.q_id % 100 BETWEEN 11 and 13 THEN "th"
      WHEN t.q_id % 10  = 1               THEN "st"
      WHEN t.q_id % 10  = 2               THEN "nd"
      WHEN t.q_id % 10  = 3               THEN "rd"
      ELSE "th"
    END
  ) AS "Q_ID_2",
  ((s_id + q_id - 2 ) % (SELECT COUNT(*) FROM question )) + 1 AS "Question_id",
  q.question_text
FROM test t
JOIN question q ON ((s_id + q_id - 2 ) % (SELECT COUNT(*) FROM question )) + 1  = q.question_id
ORDER BY s_id, q_id;

The key bit here is the modulus (%) operator and adding the s_id and q_id. The whole CONCAT bit to get the ordinals is just showing off! :-) Ordinal snippet found here.

Results:

Num     s_id    Q_ID_2  Question_id     question_text
  1        1       1st            1     question_1_txt
  2        1       2nd            2     question_2_txt

So, as we can see, the first student's first question is question_id 1 which is question_1_text. "So what?", you might say, that's not what the original question was aksing!

But, when we look at the 2nd student's questions we get this:

Num     s_id    Q_ID_2  Question_id     question_text
  9        2       1st            2     question_2_txt
 10        2       2nd            3     question_3_txt

Now, the 2nd student's first question is question_id 2, i.e. question 2 which is not the same as his neighbour's 1st question - which is what the OP asked for!

The cool thing is, if we look at the "interface" between, say students 4 and 5, we get this:

Num     s_id    Q_ID_2  Question_id     question_text
 31        4       7th            2     question_2_txt
 32        4       8th            3     question_3_txt
 33        5       1st            5     question_5_txt
 34        5       2nd            6     question_6_txt

The last questions answered by student 4 are 2 & 3 - i.e. the questions have "wrapped around" - so no student misses any question.

This was really just an intellectual exercise. For (close to) true randomness, my first answer is better - the only advantage I can think of is that it would make distributing the papers easier - all the invigilators (people who supervise exams) would have to do is move one sheet to the back at each new desk. Hope you enjoyed! :-)

=============================== DDL AND DML ================

CREATE TABLE question
(
  question_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  question_text VARCHAR (30) NOT NULL
);

INSERT INTO question (question_text) VALUES ('question_1_txt');
INSERT INTO question (question_text) VALUES ('question_2_txt');
INSERT INTO question (question_text) VALUES ('question_3_txt');
INSERT INTO question (question_text) VALUES ('question_4_txt');
INSERT INTO question (question_text) VALUES ('question_5_txt');
INSERT INTO question (question_text) VALUES ('question_6_txt');
INSERT INTO question (question_text) VALUES ('question_7_txt');
INSERT INTO question (question_text) VALUES ('question_8_txt');

CREATE TABLE student
(
  student_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_name VARCHAR (50) NOT NULL,
  UNIQUE (student_name)
);

INSERT INTO student (student_name) VALUES ('student_1');
INSERT INTO student (student_name) VALUES ('student_2');
INSERT INTO student (student_name) VALUES ('student_3');
INSERT INTO student (student_name) VALUES ('student_4');
INSERT INTO student (student_name) VALUES ('student_5');
INSERT INTO student (student_name) VALUES ('student_6');

CREATE TABLE test
(
  test_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_id INTEGER NOT NULL,
  q_id INTEGER NOT NULL
);

INSERT INTO test (s_id, q_id)
SELECT * FROM (
SELECT s.student_id, tab1.question_id
FROM student s
CROSS JOIN (SELECT q2.question_id FROM question q2)
AS tab1) AS tab2
ORDER BY student_id, question_id;


Formatting a DATE type in an Oracle database to only accept MMDD

No.  A date in Oracle will always have a year, month, day, hour, minute, and second.  You can choose to display only certain components of the date in your front end and to give a consistent default to the other components of the date.  It's pretty common, for example, to let the time component of the date default to midnight.  You could force the year to be some default as well that is never shown.  I'm not sure, though, where that would ever be useful.

Depending on what you are actually trying to represent, you may be better off storing the data as an interval.


If you want a fast, easy, free health check that flags common issues
  in seconds, and for each warning, gives you a link to a web page with
  more in-depth advice.

First steps for you:

Sp_Blitz
SP_BlitzFirst


You don't need multiple archive_commands:

archive_command = 'cp %p /.../%f && (command to archive to S3)'

The && is interpreted by the shell. If the cp has a non-zero return code, that will be the result of archive_command. If the result is 0 (success), the second command is executed, and its return code will be the result of archive_command.

You can also defer archiving to S3 to the standby server, but then you have to set archive_mode = always there. The disadvantage is that the configuration on primary and standby will be different, which makes failover more complicated. Does the command to archive a file to S3 really cause a substantial performance impact?


Fetching records using some unknown join

I got my answer. Actually I was looking for JOIN SAME TABLE MULTIPLE TIMES which I resolved from here :

  https://stackoverflow.com/a/12562027/9573341

And for my specific question here is the answer:

SELECT
    ratings.*,
    by_user.id,
    by_user.name,
    by_user.id,
    to_user.name,
FROM
    `rating_table` ratings
LEFT JOIN `users` by_user ON
    ratings.rt_rateBy = by_user.id
LEFT JOIN `users` to_user ON
    ratings.rt_rateTo = to_user.id


SQL Joins : How can i get latest record from 2nd table (one to many relationship)

row_number()

select * from
(
select *
     , row_number() over (partition by table2.prod_code order by table2.date desc) as rn
from table1
join table2
  on table1.prod_code = table2.prod_code
) tt
where rn = 1


Best practice database design - user rating tables

The first design is normalized, the second design is in 0NF (un-normalized).  You haven't stated any reason to denormalize and you should never denormalize without a good reason.

Here is why the second design is very bad:

Think about what your queries to calculate an average rating would look like in each case.
Think about what you're going to do when your number of users exceeds your DBMS' limit on the number of columns per table.
You stated in a comment that you are concerned about duplication of data because of foreign key values, however, how much space are you wasting by having a sparsely populated table?  If you have 1,000 users and only 10 of them have rated each item on average, then you're wasting 990 bits of null flag per item.
Constantly changing your table width as users enroll can be a big performance hit because of having to move chunks of data around when the new column doesn't fit in the available free space.

You asked for best practice.  Best practice for transactional data is third normal form (or higher, if applicable).


Mysql show no more than 5 concurrent connection

The solution was actually very easy:

The Bitnami LAMP stack I'm using is configured with PHP-FPM, so the configuration is not on the apache side but in php/etc/common.conf

pm=ondemand
pm.max_children=5
pm.start_servers=2
pm.min_spare_servers=1
pm.max_spare_servers=3

inreasing the pm.max_children=5 solved the issue.


Generate create table DDL from any arbitrary select query?

One way to accomplish this, using Java, would be to prepare the query statement, then use the PreparedStatement.getMetaData() method to obtain the ResultSetMetaData object that has the list of columns that would be returned, as well as their data types.

Other database interfaces, such as ODBC and OCI, have similar functionality.


If the restore completed, then the database was restored fully. What kind of backup did you take (show the code, or explain the steps if using a 3rd party tool or SSMS GUI)? How did you restore that database (show the code, or explain the steps)? What information was missing?

This answer and the link to Paul Randal's article will help explain how a full backup happens and should answer your Are the log files anywhere question, assuming you did a Full backup. You can, of course, do transaction log backups too. - scsimon

I've seen cases where backup was done appending to the file and then restore was done from the first file (which is default) from that file. I.e., restoring not-the-latest-backup. Check using RESTORE HEADERONLY. - tibor-karaszi


Is a weekly rebuild of indexes a good idea?

Dont just blindly go for rebuilding all the indexes. There is a much intelligent way of doing it. (hint: SQL Server Index and Statistics Maintenance by Ola hallengren)

This is an official guideline from Microsoft (and its a good starting point - instead of blindly rebuilding all the indexes):

click here for enlarge

Also, if you are using sql server enterprise edition then Rebuild is an ONLINE operation (REBUILD ONLINE = ON)

Also, read : Online Index Rebuild - Can Cause Increased Fragmentation -  when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF directives.

As a side note: Paul Randal has a nice checklist of VLDB Maintenance best practices from his MS days :-)

The benefit of using Ola's script is that it has logging functionality [logs to dbo.CommandLog table]  which over time will become a gold mine and you can get real value out of it.

e.g.

If you are deploying index maintenance, then you can use dbo.CommandLog to find out what indexes are frequently getting fragmented often, the time taken to defragment them and if you join to sys.indexes then you can tie up FILL FACTOR and adjust it.  Check - monitoring-for-endless-index-defragmenting.
Also, if you have DatabaseIntegrityCheck - CHECKDB solution, then you can find out how long it takes to perform CHECKDB.


How to get desired type of records for desired number of times in oracle

Please try this query:

select * from PROFILES where
SCORING_RULE = 'double_scoring' and
ROWNUM <= 6
union
select * from PROFILES where
SCORING_RULE = 'single_scoring' and
ROWNUM <= 4

Check it here: http://sqlfiddle.com/#!4/7ecc95/1


I managed to resolve the issue. I wasn't able to uninstall SQL Server but I downloaded an installer, converted it to a .zip and extracted it and then pointed the setup to that as the install media. Doing this I was able to get Management Studio up and running.

Thanks for all the suggestions!


How to check for row lock level when FOR UPDATE is used in PostgreSQL?

Row locks are not permanently stored in the shared lock table, but on the row itself, so you cannot simply query for them.
To figure out which rows in a table are locked by concurrent transactions, you could run
SELECT id FROM mytable
WHERE id NOT IN (SELECT id FROM mytable
                 FOR UPDATE SKIP LOCKED);


A correlated subquery against a VALUES table should do the trick:
SELECT
    t.A, t.B, t.C,
    (SELECT COUNT(DISTINCT v.n) FROM (VALUES(t.A),(t.B),(t.C)) AS v(n)) AS cnt
FROM table t

If you wanted separate calculations on the values such as both counting and summing, you could use a lateral join (a.k.a APPLY) over VALUES instead:
SELECT A, B, C ,D, E, v.countDistinct, v.countNotNull, v.Total
FROM  test,
LATERAL (
    SELECT COUNT(DISTINCT v.n), COUNT(v.n), SUM(v.n)
    FROM (VALUES (A),(B),(C), (D), (E)) AS v(n)
) AS v(countDistinct, countNotNull, Total)


Database for efficient range aggregate queries?

Using SQL Server ColumnStore indexes

Well, okay, just one -- a clustered CS index.

If you want to read about the hardware I did this on, head over here. Full disclosure, I wrote that blog post on the website of the company I work for.

On to the test!

Here's some generic code to build a pretty big table. Same warning as Evan, this can take a while to build and index.

USE tempdb

CREATE TABLE t1 (Id INT NOT NULL, Amount INT NOT NULL)

;WITH T (N)
AS ( SELECT X.N
     FROM (
      VALUES (NULL), (NULL), (NULL),
             (NULL), (NULL), (NULL),
             (NULL), (NULL), (NULL),
             (NULL) ) AS X (N)
           ), NUMS (N) AS (
            SELECT TOP ( 710000000 )
                    ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS N
            FROM   T AS T1, T AS T2, T AS T3,
                   T AS T4, T AS T5, T AS T6,
                   T AS T7, T AS T8, T AS T9,
                   T AS T10 )
INSERT dbo.t1 WITH ( TABLOCK ) (
    Id, Amount )
SELECT NUMS.N % 999 AS Id, NUMS.N % 9999 AS Amount
FROM   NUMS;

--(705032704 row(s) affected) --Aw, close enough

Well, Evan wins for simplicity, but I've talked about that before.

Here's the index definition. La and dee and dah.

CREATE CLUSTERED COLUMNSTORE INDEX CX_WOAHMAMA ON dbo.t1

Looking at a count, every Id has a pretty even distribution:

SELECT t.Id, COUNT(*) AS [Records]
FROM dbo.t1 AS t
GROUP BY t.Id
ORDER BY t.Id

Results:

Id  Records
0   5005005
1   5005006
2   5005006
3   5005006
4   5005006
5   5005006

...

994 5005005
995 5005005
996 5005005
997 5005005
998 5005005

With every Id having ~5,005,005 rows, we can look at a pretty small range of IDs to get you a 10 million row sum.

SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 3;

Result:

Records     Total
10010012    50015062308

Query profile:

Table 't1'. Scan count 6, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 2560758, lob physical reads 0, lob read-ahead reads 0.
Table 't1'. Segment reads 4773, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 564 ms,  elapsed time = 106 ms.

For fun, a larger aggregation:

SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

Results:

Records     Total
500500505   2501989114575

Query profile:

Table 't1'. Scan count 6, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 2560758, lob physical reads 0, lob read-ahead reads 0.
Table 't1'. Segment reads 4773, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1859 ms,  elapsed time = 321 ms.

Hope this helps!


I believe Kendra is using a front end application called SQL Sentry Plan Explorer.  It's a really great tool and gives a little bit more verbose info about the execution plan.  Here's an example of a query I'm working on right now with Plan Explorer:

It's also free-ninety-nine! (the paid version gives you some more bells and whistles but the free version is fantastic for basic execution plan review).  It's one of my favorite tools in my DBA toolbelt, for sure.

Link to product: http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view


Is it possible to use SQLite as a client-server database?

SQLite is an embedded database and it is not intended to be used as a client/server DB. If you really want to, you can use SQLitening.

What SQLitening is

  SQLitening is a client/server implementation of the very popular SQLite database.

  SQLitening is a programmer's library in standard Win32 DLL form. It is installed as a standard Windows Service. In addition to client/server mode, the library allows the programmer to also access SQLite databases in local mode. In either mode (local or client/server), the database is extremely fast and robust.
   -- Source: http://www.planetsquires.com/sqlite_client_server.htm


db2 database alias not listed via list database directory but still unable to create that database

db2 list db directory is for the system level directory. There is also db2 list db directory on <path> which lists any databases on that particular path.

Wondering if you have tried to uncatalog or catalog it first? You could see if cataloging it allows you to drop it.

Worse case you may need to locate the spot on disk with the database and remove it manually (as long as it isn't cataloged).


InnoDB Read and Write speed constantly spikes

max_heap_table_size = 3072M     --> 1G
tmp_table_size = 3G             --> 1G
innodb_buffer_pool_instances=2  -->   16

Other details may be in the STATUS.  For further analysis, please provide

SHOW VARIABLES;
SHOW GLOBAL STATUS;


DNA sequence data - requirement for a long and wide table of experimental data

Is what you suggest a standard method of storing DNA sequencing results? Apart from the technical difficulties - good luck finding a database that will allow that many columns (SciDB?) - it will make querying tricky. I would look into ways of having your tables be tall and skinny rather than short and fat.

I would do something like the following:

CREATE TABLE sample
(
  bed INTEGER NOT NULL,
  sample_no INTEGER NOT NULL,
  ..
  .. other fields?
  .. chromosomal location? sequence? phenotype?
  .. dna_type (gene | promoter | mRNA | tRNA... &c.)?
  ..
  CONSTRAINT sample_pk PRIMARY KEY (bed, sample_no)
);

I believe that such a schema will facilitate querying and be far more technically feasible - it will conform far better to the relational model and will ultimately save you a lot of grief! I asked above Is this a standard method of storing DNA sequencing results? - this work has been done before so why are you reinventing the wheel?

Don't get me wrong - I'm all in favour of experimenting and trying to get a new perspective on biological phenomena, that's what science/molecular biology is all about, but others must have done this before - you may want to look at their work? For the record, I have a Masters in Cellular and Molecular Genetics from the Univeristé de Paris. HTH...


Is it possible to edit sys.messages texts?

I don't think it is possible or advisable to edit messages below 50000 in SQL server. You can create user defined messages for ids greater that 50000 using sp_addmessage.

Another possibility is to use RAISERROR to return dynamic messages. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.


Performance difference between MySQL and PostgreSQL for the same schema/queries

MySQL and PostgreSQL are quite difference performance-wise.  InnoDB and PostgreSQL tables are optimized for different sorts of queries.  Understanding these differences is important to understanding how to get good performance out of either.

As an example, let's look at the most obvious difference.

PostgreSQL vs MySQL/InnoDB Table Structure and What This Means for Performance

In general, on complex work-loads, PostgreSQL will be faster, but on simple primary key lookups MySQL with InnoDB will be faster.

PostgreSQL tables are heap tables.  There is no option to build a table which is not a heap table.  The cluster command simply rewrites the heap ordered by a specified index.  Indexes then provide heap locations for tuples with various values.  Indexes cannot be traversed in physical order, only logical order so they have a lot of random disk I/O while reading a table sequentially usually means a lot of sequential disk I/O, since you can read a table in physical order.  Sequential disk I/O gets to use read-ahead cache and some other OS-level optimization.

What this means is that if you need a significant portion of records or over a few pages, it is usually faster to just read the pages from disk.  On the other hand, a primary key lookup for a table requires hitting the index, lookup up the location in the file then hit the heap table and pull the record.  This means a number of pieces of random disk I/O.

InnoDB uses a different approach.  With InnoDB, the table is a b-tree index with the actual data in the index payload.  This means that a primary key lookup already gets to pull the data from the leaf page, and so less random disk I/O is required for this.  At the same time, an index scan requires traversing two indexes instead of one, meaning use of any index other than the primary key ends up being slower and sequential scans are slower still.

Getting Diagnoses in PostgreSQL

I think you want to use something like:

 EXPLAIN (analyse, buffers, verbose)
 [query];

That will give you the query plan, initial estimates, actual times, buffer usage, and much more.


The problem is that you forgot to put double quotes around the -e parameter

String cmd =  mysql + " --user=" + username + " --password=" + password +  " -e\"" + "source " + restoreFile + "\"";

An alternative would be to redirect the file into the  mysql client

String cmd =  mysql + " --user=" + username + " --password=" + password +  " < " + restoreFile;

I also just noticed that you forgot to set the target database

String cmd =  mysql + " --user=" + username + " --password=" + password +  " -D" + mydb + "-e\"" + "source " + restoreFile + "\"";

String cmd =  mysql + " --user=" + username + " --password=" + password +  " -D" + mydb + "  < " + restoreFile;

Give it a Try !!!


Missing options in Feature Selection when installing SQL Server 2016 on existing server

I don't think Sql Server 2016 will run on a Windows 2008 R2 server.

See the requirements for OS here
https://msdn.microsoft.com/en-us/library/ms143506.aspx#TOP_Principal


MySQL, IF statement, variables, and NULL

INSERT INTO test (number)
SELECT COALESCE(MAX(number), 1)
FROM test;

UPDATE

If record(s) exists we need in the next value. So:

INSERT INTO test (number)
SELECT 1 + COALESCE(MAX(number), 0)
FROM test;


Getting last modification date of a PostgreSQL database table

There is no reliable, authorative record of the last modified time of a table. Using the relfilenode is wrong for a lot of reasons:

Writes are initially recorded to the write-head log (WAL), then lazily to the heap (the table files). Once the record is in WAL, Pg doesn't rush to write it to the heap, and it might not even get written until the next system checkpoint;
Larger tables have multiple forks, you'd have to check all the forks and pick the newest timestamp;
A simple SELECT can generate write activity to the underlying table due to hint-bit setting;
autovaccum and other maintenance that doesn't change the user visible data still modifies the relation files;
some operations, like vaccum full, will replace the relfilenode. It might not be where you expect if you're trying to look at it concurrently without taking an appropriate lock.

A few options

If you don't need reliability, you can potentially use the information in pg_stat_database and pg_stat_all_tables. These can give you the time of the last stats reset, and activity stats since the last stats reset. It doesn't tell you when the most recent activity was, only that it was since the last stats reset, and there's no information about what happened before that stats reset. So it's limited, but it's already there.

One option for doing it reliably is to use a trigger to update a table containing the last-modified times for each table. Be aware that doing so will serialize all writes to the table, destroying concurrency. It will also add a fair bit of overhead to every transaction. I don't recommend it.

A slightly less awful alternative is to use LISTEN and NOTIFY. Have an external daemon process connect to PostgreSQL and LISTEN for events. Use ON INSERT OR UPDATE OR DELETE triggers to send NOTIFYs when a table changes, with the table oid as the notify payload. These get sent when the transaction commits. Your daemon can accumulate change notifications and lazily write them back to a table in the database. If the system crashes, you lose your record of most recent modifications, but that's ok, you just treat all tables as just-modified if you're starting up after a crash.

To avoid the worst of the concurrency issues you could instead log the change timestamps using a before insert or update or delete or truncate on tablename for each statement execute trigger, generalized to take the relation oid as a parameter.  This would insert a (relation_oid, timestamp) pair into a change-logging table. You then have a helper process on a separate connection, or called periodically by your app, aggregate that table for the latest info, merge it into a summary table of most recent changes, and truncate the log table. The only advantage of this over the listen/notify approach is that it doesn't lose information on crash - but it's even less efficient, too.

Another approach might be to write a C extension function that uses (eg) ProcessUtility_hook, ExecutorRun_hook, etc to trap table changes and lazily update stats. I haven't looked to see how practical this would be; take a look at the various _hook options in the sources.

The best way would be to patch the statistics code to record this information and submit a patch to PostgreSQL for inclusion in core. Don't just start by writing code; raise your idea on -hackers once you've thought about it enough to have a well defined way to do it (i.e. start by reading the code, don't just post asking "how do I ..."). It might be nice to add last-updated times to pg_stat_..., but you'd have to convince the community it was worth the overhead or provide a way to make it optionally tracked - and you'd have to write the code to keep the stats and submit a patch, because only somebody who wants this feature is going to bother with that.

How I'd do it

If I had to do this, and didn't have the time to write a patch to do it properly, I'd probably use the listen/notify approach outlined above.

Update for PostgreSQL 9.5 commit timestamps

Update: PostgreSQL 9.5 has commit timestamps. If you have them enabled in postgresql.conf (and did so in the past too), you can check the commit timestamp for the row with the greatest xmin to approximate the last modified time. It's only an approximation because if the most recent rows have been deleted they won't be counted.

Also, commit timestamp records are only kept for a limited time. So if you want to tell when a table that isn't modified much is modified, the answer will effectively be "dunno, a while ago".


Why does user not have access to database?

It could be that the database was originally restored from a different
  server, which also has a ls_readonly user. I guess then that the user
  identification is not based on the username?

Normally, all the logins have the same sid with corresponding users. That is the sense of CREATE USER FROM LOGIN command: the database principal is created with the same sid (security identifier) that corresponding login has.

This user sid is now stored in your database, and when you backup/restore your database the sid is also preserved.

Imagine now that you have 2 servers and both of them have ls_readonly login. to make it simple, on the first server ls_readonly has sid = 1, on the second sid = 2. You have the database MyDB where this login is mapped. On the first server MyDB stores sid = 1, on the second sid = 2.

You backup MyDB database on the first server and restore it on the second. Now your login has sid = 2 and in the database MyDB it's sid = 1.

You log in as ls_readonly and try to access MyDB, server is checking if there is sid = 2 in the database, there is no, so for the server ls_readonly is not mapped to MyDB at all.

This can be fixed by doing

alter user ls_readonly with login = ls_readonly;

This command just update database's sid of ls_readonly user with the sid of ls_readonly login. All the permissions are preserved.

If you recreate user by dropping it, once you drop the user, all the permissions granted to it are dropped and you need to re-grant them.


SQL Query From two Table satisfying a condition

You want to run a query like this (see db-fiddle here):

SELECT
  st.student_name,
  -- st.student_dept,  -- <<= uncomment for an insight into
  -- sm.semester_dept, -- <<= how the query works!
  sm.semester_name
FROM student st
JOIN semester sm
ON
  st.student_dept = sm.semester_dept
WHERE st.student_dept IN
(
  SELECT
    sm.semester_dept AS sem_dept
  FROM semester sm
  GROUP BY sm.semester_dept
  HAVING COUNT(sm.semester_dept) = 1
)

Result:

Student name  Semester name
___________________________
         Bob         Summer
      Briyan         Winter
       James         Summer

This query will work (I believe) for any/all of the major RDBMS servers - works on MySQL and (my reference implementation, PostgreSQL here). Oh, yes, just a point to note - the student_name field should be a PRIMARY KEY (or at least UNIQUE with NOT NULL) in order for this to be guaranteed to work properly!

This solution is much more elegant than my original query solution (here) - thanks to @ypercube for the hint!

============== DDL and DML ==================

CREATE TABLE student
(
  student_name VARCHAR (20) NOT NULL,
  student_dept VARCHAR (4) NOT NULL
);

INSERT INTO student VALUES
('Alex',   'CSE'),
('Bob' ,   'EEE'),
('Briyan', 'ME'),
('John',   'CSE'),
('James',  'ETE'),
('Mike',   'CE');

CREATE TABLE semester
(
  department VARCHAR (4) NOT NULL,
  semester   VARCHAR (6) NOT NULL
);

INSERT INTO semester VALUES
('CSE', 'Summer'),
('CSE', 'Winter'),
('EEE', 'Summer'),
('ETE', 'Summer'),
('ME',  'Winter'),
('CE',  'Summer'),
('CE',  'Winter');


Programatically testing slave status - What fields in SHOW SLAVE STATUS indicated slave status is OK / in ERROR?

SCENARIO #1
You may want to monitor Relay_Log_Space. It represents the some total of relay logs the Slave has to process. When should you worry about it?
Give this scenario

Slave_IO_Running is Yes
Slave_SQL_Running is No

This indicates that that the IO Thread is still downloading entries from its Master's binary logs. The longer you take to address the SQL error, the more the Relay_Log_Space will pile up. If the Master is in a heavy-write environment, relay logs will pile up quickly and puts the Slave at risk for disk space issues.
If you do not want to monitor Relay_Log_Space, you can always set the relay_log_space_limit. This force a cap on the amount of disk space allow for relay logs. For example, if you set this:
[mysqld]
relay_log_space_limit=8G

and restart mysql, this causes the IO Thread to stop collecting new entries from the Master when the sum of all relay log file sizes >= 8GB.. After the SQL Thread is done processing the oldest relay log, that relay log gets rotated out and the IO Thread starts getting entries from the Master where it left off. This provides a decent throttling mechanism for relay logs.
Another field you may want to look at is Last_SQL_Errno. Once Slave_IO_Running is Yes
and Slave_SQL_Running is No, the Last_SQL_Errno will be set to something nonzero. On very rare occasions, it is possible to have this:

Slave_IO_Running is Yes
Slave_SQL_Running is No
Last_SQL_Errno is 0

When you have this unique phenomenon, mysqld has encountered an undocumented error (Don't laugh, I have seen this).
SCENARIO #2
Something else to keep in mind (not necessarily for monitoring)
Give this scenario

Slave_IO_Running is No
Slave_SQL_Running is No

Under what three circumstances would this occur?

Someone ran STOP SLAVE; by hand or in a backup script
The option skip-slave-start is in /etc/my.cnf and mysql was started
Issuing service mysql start --skip-slave-start or service mysql restart --skip-slave-start

Make sure not to monitor false positives should both replication threads are not running.
SCENARIO #3
Give this scenario

Slave_IO_Running is No
Slave_SQL_Running is Yes

This quickly indicates that there is a communication error because of one of the following

The Master went down
mysqld on the Master went down
Network Intermittency


forcing innodb recovery of a corrupted database

InnoDB complains about wrong LSN because ib_logfile-s were deleted. You shouldn't have deleted those. But MySQL can survive after that. Next time InnoDB updates a page it will overwrite its LSN in the header and the error message will go away. Just wait till it starts (the error log shows neither successful nor unsuccessful start).

To get 100% clean tablespace you need to start MySQL with innodb_force_recovery=4, take mysqldump and restore it on a fresh instance of InnoDB (by fresh I mean you have to delete ibdata1, and all databases directories).

UPDATE:

At this point MySQL is started with innodb_force_recovery=x (x != 0)

Take dump of all databases:

# mysqldump --skip-lock-tables -A > alldb.sql

Check where MySQL keeps its files(in my case it's /var/lib/mysql/):

# mysql -NBe "SELECT @@datadir"
/var/lib/mysql/

Stop MySQL

# mysqladmin shut

Move old MySQL files to safe place

# mv /var/lib/mysql /var/lib/mysql.old

Create new system database

# mkdir /var/lib/mysql
# mysql_install_db

Start MySQL

# /etc/init.d/mysql start

Restore the dump

# mysql < alldb.sql

Restore may take long time if the database is big.
Another trick may work in that case. Run ALTER TABLE ... ENGINE INNODB on each InnoDB table. It will rebuild all InnoDB indexes and thus the errors will go away.


The blog post is using the terms incorrectly.

Those are two different things.

"Hot Standby" is the state the replica is in and means it is available for read-only queries. A replica can also "just" be a standby, but then it does not allow queries, it just sits there and waits to take over.

A replica needs to have exactly the same data as the master and the way the data is sent from the master to the replica is what "streaming replication" refers to.

A hot standby can also be setup using log shipping (sometimes also referred to as "continuous WAL archiving") or even streaming replication together with log shipping.

Quote from the manual

  In standby mode, the server continuously applies WAL received from the master server. The standby server can read WAL from a WAL archive or directly from the master over a TCP connection (streaming replication).


Which is more efficient for searches on JSON data in Postgres: GIN or multiple indexed columns?

The advantage of JSON is versatility: you can add any keys without changing the table definition. And maybe convenience, if your application can read and write JSON directly.

Separate columns beat a combined json or jsonb column in every performance aspect and in several other aspects, too: More sophisticated type system, the full range of functionality (check, unique, foreign key constraints, default values, etc.), the table is smaller, indexes are smaller, queries are faster.

For prefix matching on text columns you might use a text_pattern_ops index:

Why would you index text_pattern_ops on a text column?

Or, more generally, a trigram index supporting any LIKE patterns:

Full Text Search With PostgreSQL
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

While you stick with JSON (jsonb in particular), there are also different indexing strategies. GIN or Btree is not the only decision to make. Partial indexes, expression indexes, different operator classes (in particular: jsonb_path_ops) Related:

How to get particular object from jsonb array in PostgreSQL?


Why Mysql Fabric is end of life, and what is an alternative?

In my opinion, Fabric was doomed because it had a single-point-of-failure, namely the 'fabric node'.  Otherwise, it was probably considered mature.

The replacement: Group Replication (InnoDB Cluster).  This has a robust design that seems to have no major holes.  Galera Cluster (built into MariaDB and PXC, add-on for Oracle's MySQL) competes head-to-head with it and is more mature; there are advocates on both sides.

Group Replication (and Galera) have valid solutions for HA (High Availability) in that they use multiple servers and can recover from any single server failure.  (This, of course, requires geographical disbursement of the servers.)

Group Replication is becoming mature and has only one flaw (arguably minor): It has no built-in way to handle the "critical read" problem.  (This is where a user writes something to the database, but the subsequent SELECT does not see it because it is sent to a different server.)

I am not prepared to address "sharding" in GR.  Clustrix is a 3rd party that claims to have a very good sharding solution.  What do you expect from "application level Sharding"?  And is Sharding, not HA, your main goal?

Perhaps you saw this?  Mysql Group replication vs Mysql Cluster


Find rows where column matches one of n strings

You can use IN,

SELECT *
FROM t
WHERE email IN ('foo', 'bar', 'baz');


Is there any impact to changing IP address of MySQL Server?

When you use the server name (DNS name) then a change of IP address will be used by your clients as soon as all DNS servers have this new address. Since you change your subnet you must be able to 'reach' this subnet from your clients. The only 'problem' can be that you loose the open connections.


Try removing the nesting levels, mysql can not delete from a table that's referenced as a child, instead there's a propriety trick like:

    DELETE t1.*
    FROM regulatory T1
    JOIN regulatory T2
        ON t1.group_ID=v_user_id
       AND t2.group_ID=v_user_id
       AND TRIM(t1.title)=TRIM(t2.title)
       AND TRIM(t1.spare1='red.gif')
       AND t1.docref=v_temp
       AND t1.DocumentID>t2.DocumentID

Also, like @Mihal suggested try removing all function calls like trim from predicates, they are expensive.


Unable to view Registered Servers on Central Management Server

Did you ,from the machine where you are running SSMS, specify the machine where the server registration exists? I.e., you need to "point out" the CMS instance. Right click the CMS folder, and "Register Central Management Server", and in there you specify the SQL server instance where the CMS list exists in the table in msdb.


SQL Server - Queries are much faster on the application when run second time

When I click on a screen on my vb application, it takes approx 6/7 seconds to display the results. When I click off the screen and click back again into the screen, it returns immediately.

Does the delay reappear when you restart the Application or when you restart the Database?

If it's the former, then the Application may be doing some caching of its own and, the first time you run it, it's building that cache, which takes a while.

If it's the latter, then it's probably the data being loaded into the database's Buffer Cache from disk.  Once you've loaded the data once (and assuming that nothing more important flushes it out again) then the data is simply sitting in the Buffer Cache waiting to be [very quickly] reused.


Impact of changing char(10) to varchar(12) on massive table

Based off this and this post I would say, yes, the change from char(10) to varchar(12) will touch every row and hence grow the log.  My reasoning being that the column has moved from the fixed-length to the variable-length part of the row structure and the storage engine will have to persist this information straight away.

Variable length columns will have a two byte overhead to track the actual length (see above).  If the average length of your data is 8 bytes or fewer you are on to a winner with this chage.  Otherwise you will be taking up more space with a varchar.

The digits you require could be stored in 9 bytes with a numeric type.  This would be too much of a change to your application, I suspect, even if it is the proper way to hold this data.

I'd suggest you add a new nullable column of the right type and length.  From the above post this will be a meta data only operation with minimal logging.  Put triggers in place to keep the new column in sync with the existing one.  Then your application can keep working while the following steps happen.  Copy the existing data across in chunks.  Test on your development box to find what chunk size gives the right balance of elapsed time and log size for your system.  In my experience 10,000 is about right.  Once everything's in place, drop the old column and triggers, and rename the new column.  An index rebuild will be necessary to reclaim space.  Here are some SO posts which deal with this subject.


If you're searching documents, a text engine like solr could be your friend.  Combine that with ajax for real-time document discovery.


Do I need to stop the service to back up the database?

In SQL Server you do not backup the database's files directly.  You issue a BACKUP command which creates a new file, usually with a .bak extension.  You do not need to stop SQL Server to do this.  It is this new file which is moved to tape & off site.  To restore you will present this .bak file to a RESTORE command.  Here's a previous question on the subject and the Microsoft site.  I imagine the other RDBMSs would work similarly but I claim no expertise with them.  Read the publisher's documentation.

For a flat file system I imagine you would definitely have to stop all write activity to ensure integrity of the data within the files you may hope to restore later.  Again, ask the developers.


First query is really slow with WiredTiger, not with mmapv1

As per MongoDB blog documentation How to Tell if Your MongoDB Server Is Correctly Sized For Your Working Set For MongoDB users, knowledge of working sets is critical. Understanding the interactions between your working set and physical memory can make a major difference in how your system performs.

In order to optimize MongoDB performance you should consider and know the following:
How working sets fit into the scheme of MongoDB in the first place;
Why it's important to fit your working set within your available RAM;
Why you should avoid pulling from disk; and
When and how it's possible to tell that there's a mismatch between your working set and your RAM.

What is a working set?

MongoDB's documentation defines a working set as follows:

  A working set represents the total body of data that the application
  uses in the course of normal operations… For best performance, the
  majority of your active set should fit in RAM.

Why's this important? Fitting your working set to RAM is vital for performance: Going to disk is costly. As this breakdown from a UC Berkeley study on latency numbers shows, reading from memory is significantly faster than reading from disk. As this visualization of 2017 speeds shows, even if you're using SSD, accessing from memory is an order of magnitude more efficient than accessing from disk.

There are two primary storage engines available for MongoDB:

MMAP

As Mongo's documentation
explains, "MMAPv1 is MongoDB’s original storage engine based on
memory mapped files. It excels at workloads with high volume inserts,
reads, and in-place updates." As of MongoDB version 3.2, however,
MMAP is no longer MongoDB's default storage engine—WiredTiger now
holds that spot.
With MMAP, MongoDB will use all available memory, as necessary. There
are no configuration options to limit the data kept in-memory.

WiredTiger

WiredTiger has an internal data cache, but is configured to also
leave memory for the Operating System’s file cache. It's generally
recommended to leave your cache size at its default setting.
These defaults
settings will use the larger of either of the following: 50% of
RAM minus 1 GB.
256 MB.
Outside WiredTiger's cache, the Operating System will cache
frequently accessed files for the database in its own file cache.
WiredTiger compresses data files by default, so leveraging the OS
cache actually lets more data stay in the server's physical memory.

How can I tell if my working set fits my RAM?

For MMAP, a high number of page faults is a leading indicator that instead of finding data in RAM, it's going to disk to fetch it. That, of course, means the working set isn't fitting within your available RAM. Inversely, you can look directly at your RAM usage: As explained above, MMAP uses all available memory automatically. Therefore, if you're not using all of the RAM, then you can safely conclude that the working set fits into RAM, even with room to spare.

The total number of page faults. The extra_info.page_faults counter may increase dramatically during moments of poor performance and may correlate with limited memory environments and larger data sets. Limited and sporadic page faults do not necessarily indicate an issue.

Windows draws a distinction between hard page faults involving disk I/O, and soft page faults that only require moving pages in memory. MongoDB counts both hard and soft page faults in this statistic.

"freeMonitoring" : {
   "state" : <string>,
   "retryIntervalSecs" : <NumberLong>,
   "lastRunTime" : <string>,
   "registerErrors" : <NumberLong>,
   "metricsErrors" : <NumberLong>
},

For WiredTiger, you can also begin by looking directly at memory usage. If your server has free RAM, that inherently means the working set fits into your memory, as WiredTiger automatically uses its set cache plus your operating system's cache. If your server looks to be using most of its memory, take a look at the pages-read-into-cache and unmodified pages evicted metrics reported for WiredTiger by MongoDB’s serverStatus command. High activity for those two metrics indicates that the server is cycling data through the cache, which means your working set does not fit in memory.

The last metric to consider, of course, is volume of disk reads — if you see a lot of read activity on your drive, it's generally a good sign that your working set is overflowing your memory. Because WiredTiger interacts with your OS cache, this may be a more reliable metric to evaluate working set ratio — e.g. you may be able to see high cache evictions but low IO because of how your cache behaves.

Even though as documented here WiredTiger allows for tuning some of the storage engine parameters. The 3 most important to consider are the cache size, checkpoint interval and logging.

for further your ref here , here and here


Looking for simple contains method when searching text in postgresql

There are two typical ways to express this.

With LIKE infix search:

SELECT title FROM snippets WHERE description LIKE '%evil%';

or with position:

SELECT title FROM snippets WHERE position('evil' in description) > 0;

Note that neither are indexable by default. Search for "infix search index" for more info on that, and look into pg_trgm if you need it.


Is there a tool that can check the availability of an Oracle 11g database on a remote machine?

The only way to be sure that the remote database is up and that the listener is up and that the database is registered with the listener properly would be to actually make a connection.  You could use the SQL*Plus utility (assuming the Oracle client is installed on the linux box your application runs on) to attempt to make a connection.  Something like

Create a file check_db_up.sql

whenever sqlerror exit 1;
connect username/password@<<TNS alias>>
select 1 from dual;
exit 0;

Then invoke this script in your bash shell script and look at the return code

sqlplus /nolog @check_db_up.sql

If that returns 1, there was an error and the database isn't up.  If it returns a 0, the database is up and accepting connections.


How to remove Parameters and Filters in a T-SQL?

ClearTrace will do the thing you need. Its free to download as well.

  Normalized SQL Statements
  ClearTrace "normalizes" the SQL statements to eliminate transient differences in SQL statements.  For example, the following statement

  Select * From qryLinksByNewsletterID Where NewsletterID=19

  is normalized to

  SELECT * FROM QRYLINKSBYNEWSLETTERID WHERE NEWSLETTERID={##}


Sync Server Objects when having two or more Availability Groups

The server object are at the server level e.g. logins, jobs, etc. So you can just use Sync-DbaAvailabilityGroup

Syncs depdendent objects for availability groups. Such objects include:

SpConfigure
CustomErrors
Credentials
DatabaseMail
LinkedServers
Logins
LoginPermissions
SystemTriggers
DatabaseOwner
AgentCategory
AgentOperator
AgentAlert
AgentProxy
AgentSchedule
AgentJob

What you are using is a much bigger umbrella that folks use for migration from one server to another.


Database cannot be opened. It is in the middle of a restore?

This is kind of how log shipping has to work. You need exclusive access to a database to apply a log backup, which means your script has to set the database to single_user or otherwise evict all the users before it can apply the log backup(s).

Some workarounds:

Schedule your restores for outside of business hours, and let them continue querying stale data in the meantime.
Queue up multiple log backups and apply them, say, every hour or every two hours. Users get periodic updates throughout the day, with interruptions less frequent than the log backup schedule on the primary.
Don't force your script to kick users out. This means the log restore will wait and wait and wait until there are no active connections to the database. I've never actually tried this, and its feasibility really depends on how active your secondary is.
Have multiple secondaries and cycle through them. I've written pretty extensively about this approach.


Spool Unicode data in sqlplus to ISO-client without character set conversion

Without setting NLS_LANG it's going to be tough.

How good are you at simple coding or parsing/manipulating strings?

You can do:

select DUMP(field) from table;

Then write some sort of conversion code to parse the comma-delimited numbers from the dump into Unicode.


I have written posts of this nature before

How can I optimize a mysqldump of a large database?
recover a single mysql database on a busy master-slave replicated system
Set up a MySQL slave using mysqldump to get the initial data

You will find code samples and additional advice on its usage

I shy away from Snapshots as done by LVM if

90%+ Data is InnoDB
innodb_file_per_table is disabled
All Data Exceeds 200GB
Heavy Write, Heavy Read

I wrote about this before : Ibdata usage and Recommendations?

While my personal preference is mysqldumps, if you have a small data volume, LVM snapshots would be just fine. If you are running MySQL 5.0/5.1, I would recommend running

SET GLOBAL innodb_max_dirty_pages_pct = 0;

to flush up to 99% of the InnoDB Buffer Pool before performing a file-system snapshot. Then, set it back to

SET GLOBAL innodb_max_dirty_pages_pct = 90;

This is not required for MySQL 5.5

An in-between solution would be Percona's XtraBackup. Percona XtraBackup is free, open-source, and GPL


Problem in PostgreSQL replication - ALTER DATABASE

I assume that you are talking about “streaming replication”, not some other kind of replication (streaming replication is the only kind of replication that I know that replicates DDL).

Streaming replication should replicate ALTER DATABASE ... RENAME TO ... just like any other change.

If it doesn't, there is probably some replication conflict that delays the application of the change. What is max_standby_streaming_delayset to? try to disconnect from the standby server and see if that does the trick.


When exactly does pg_stat_all_tables.last_autovacuum get updated?

I also wanted to know this, so I dug through the Postgres src code a bit to try and find out...
https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211a88f43/src/backend/catalog/system_views.sql#L584
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,

https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211a88f43/src/backend/utils/adt/pgstatfuncs.c#L267
result = tabentry->autovac_vacuum_timestamp;

https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211a88f43/src/include/pgstat.h#L740
TimestampTz autovac_vacuum_timestamp;   /* autovacuum initiated */

So, if I followed the code correctly, it appears that last_autovacuum timestamp is when the process started, not when it finished.


How do you index a text column in MySQL?

In MySQL Key Length is added as a type-modifier and placed in parenthesis colname(), you can provide it to CREATE INDEX like this,

CREATE INDEX foo_bar_idx ON foo ( bar(500) );

It's part of index_col_name, (except it's not optional on text so ignore the [])

index_col_name:
  col_name [(length)] [ASC | DESC]

Alternatively you may want an Full Text Search (FTS) index instead providing MATCH functionality

CREATE FULLTEXT INDEX foo_bar_ftsidx ON foo ( bar );

For more information see the MySQL docs on Full Text Search


Create table in Oracle as select from SQL Server - varchar size

This is a feature that's controllable by a parameter. From the documentation:

  Multi-byte Character Sets Ratio Suppression This feature optionally suppresses the ratio expansion from SQL Server database to
  Oracle database involving multi-byte character set. By default, Oracle
  gateways assume the worst ratio to prevent data being truncated or
  insufficient buffer size situation. However, if you have specific
  knowledge of your SQL Server database and do not want the expansion to
  occur, you can specify HS_KEEP_REMOTE_COLUMN_SIZE parameter to
  suppress the expansion.

Basically, Oracle is assuming that all of the data in the source table may take up 3 bytes per character, due to characterset conversion.


Will DROP TABLE of an empty table with a large tablespace file cause a long lock?

What version of MySQL are you using?

I'd do a RENAME table then delete rows in chunks (something like DELETE FROM TABLE LIMIT 5000; in a loop) once it's empty you should be able to drop it.

Adjust the LIMIT 5000 up or down depending on how long that operation takes, slave replication falls behind, or it's affecting other queries performance.

If it's an empty table then no, it shouldn't cause a long lock.


Is there any way in Postgres to parameterise a procedure for sort order column, when the columns are of different types?

Be careful with conditional ordering, it can create bad query plans sometimes forcing table scanning. If the filtering and joining clauses, or just the size of the actual data, mean that you have a small number of rows to sort at the end then this is not an issue and something like this will work:
ORDER BY CASE WHEN ordering_column = 'id'        THEN id        ELSE NULL END
       , CASE WHEN ordering_column = 'timestamp' THEN timestamp ELSE NULL END

In fact it will work anyway, it might just be inefficient for a large amount of data.
For larger outputs your workaround may be more efficient as it may be able to make better use of indexes for the sorting. Another alternative is to have two procedures, one for each sort, and either call each as needed or have your main procedure call the others depending on the sort order it is passed in the parameter. Depending on how postgres handles cached query plans for procedures this may[†] avoid issues of a cached plan for one case being used for another where it is vastly less efficient.
[†] I'm no expert at all on pg's internals, but single "kitchen sink" procedures and queries with conditional sorts etc. can be a performance killer in SQL Server for this sort of reason.


Using XPath .exist to look for elements within a decimal range

exist return 1 if the expression you use returns at least one node. Put your expression in a query and look at what you get back.

select id
      ,clientxml.value('(//*/caruwmarketfactor)[1][text()]', 'decimal (22, 7)')
      ,clientXml.query(N'(//*/caruwmarketfactor[xs:decimal(text()[1]) gt xs:decimal("0.70")])
                          and (//*/caruwmarketfactor[xs:decimal(text()[1]) lt xs:decimal("0.75")])')
from @table t

Result:

id
----------- --------------------------------------- ---------
1           0.7468000                               true
2           0.5500000                               false
3           0.7600000                               false

You get one node back with the value true or false.

You can change your expression to return the value if it matches by doing the logic in the same predicate something like this.

select T.id,
       T.clientxml.value('(//*/caruwmarketfactor)[1][text()]', 'decimal (22, 7)')
from @table as T
where T.clientXml.exist('//*/caruwmarketfactor/text()[xs:decimal(.) gt xs:decimal(0.70) and
                                                      xs:decimal(.) lt xs:decimal(0.75)]') = 1;

The expression within the predicate returns true or false and if it returns true it will return one node to the exist function. If the predicate returns false there will be no node returned.

Logical Expressions (XQuery)


Get last Reoganized OR Rebuild index date

As Aaron mentioned in the comment, the indexes with a lot of fragmentation you are seeing probably got skipped due to the index being too small or too big (default for this is no index is too big).

You can search for the below parameters in the sp definition, and check the job to make sure a different value is not being passed through:

@MinNumberOfPages INT = 1000,
@MaxNumberOfPages INT = NULL,

Another possibly is that the index is being rebuilt and then re-fragmenting again. This to me would be a sign that constant rebuilds of that index actually aren't a good help.

If you're looking for when\if the index was ever rebuilt, the Ola sp does have the ability to log to a table and the previous DBA hopefully utilized this feature. If so, you can use the below to figure out when the last time an index was rebuilt.

SELECT
'[' + DatabaseName + '].[' + SchemaName + '].[' + ObjectName + ']' AS ObjectName,
MAX(StartTime)

FROM
dbo.CommandLog

WHERE
CommandType = 'ALTER_INDEX'

GROUP BY
'[' + DatabaseName + '].[' + SchemaName + '].[' + ObjectName + ']'


configure new linked server from SQL Server 2016 to Oracle 12c

Try changing your Data Source and Provider String to MYALIAS.  Looking at the three Oracle Linked Servers I have on my server, that is how I have them setup.


If you are looking for a particular string formatting of a datetime data type value then you need to work with the convert() function with a particular style.  In this case, you are looking for the style with a value of 110 (resulting in the format of mm-dd-yyyy):

select convert(char(10), getdate(), 110);

(Note: I use getdate() just to generate a datetime value, in which case you would just reference your datetime column) My output is this:

06-08-2014


SQLite function to specify exactly how the data is stored internally

The storage size has no effect at the SQL language level, so there is no SQL function to determine it.

However, the record format is documented; SQLite always uses the smallest serial type into which the value fits.


I'm not sure if that's your desired syntax or not. Check your syntax for UPDATE

Currently, that's

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

So if you provide table t1, it's getting parsed as a table named table. Actually, to do that you need to have it in quotes "table" t1 which you're doing, or your library is doing.

As a design note, don't do that. In fact, don't name anything any SQL keywords.
But, if you want to have some fun and see what's happening we can play..

Let's create some text data,

CREATE TABLE "table" AS
SELECT x AS column, x AS column2
FROM generate_series(1,12345) AS t(x);

Now we can try your original query and get your original result,

UPDATE "table" t1 SET t1.column=0 WHERE t1.column2=1234;
ERROR:  column "t1" of relation "table" does not exist
LINE 1: UPDATE "table" t1 SET t1.column=0 WHERE t1.column2=1234;

And that's the problem you're getting. As with the table, if you're going to use a SQL keyword, you need to quote it. Interestingly, that's not enough here.

UPDATE "table" t1 SET t1."column"=0 WHERE t1.column2=1234;
ERROR:  column "t1" of relation "table" does not exist
LINE 1: UPDATE "table" t1 SET t1."column"=0 WHERE t1.column2=1234;

In addition to that, it seems that table aliasing is not supported in the SET list, regardless of whether or not the column is reserved keyword.

UPDATE "table" t1 SET "column"=0 WHERE t1.column2=1234;

Why it's currently Working As Designed

Why you can not use aliases, xocolatl from IRC helps with that,

  < xocolatl> EvanCarroll: the reason you can't use the alias on the left of the = is because of composite types

  < xocolatl> EvanCarroll: so, it's not a bug but WAD

So in code to CREATE a table with a custom composite type an execute an UPDATE on it.

CREATE TYPE foo AS ( x int, y int );

CREATE TABLE foobar AS
  SELECT v::foo AS mycol
  FROM ( VALUES (1,2), (2,100) ) AS v;

UPDATE foobar SET mycol.x = 9;

So the syntax that permits the . is mycol.type-address, not tablealias.col-name.

Solving the ambiguous syntax problem

If that didn't make sense, any behavior but this behavior would give you an ambiguous syntax,

CREATE TYPE foo AS ( mycol int, x int );

CREATE TABLE mytable AS
  SELECT v::foo AS mycol, 1 AS x
  FROM ( VALUES (1,2), (2,100) ) AS v;

UPDATE mytable AS mycol SET mycol.x = 9;

What does mycol.x refer to there? As is it's not ambiguous, table-referencing and table-aliasing is disabled, so it's definintely 100% of the time a composite-type named mycol, on the table mytable.


SSIS Script Task doesn't appear to run when scheduled

I've resolved this. I installed the SSDT Feb update and targeted the version to SQL Server 2014, as suggested by Dave. I did have to tweak one script (unrelated package and script task) but then I managed to build and deploy. It's processing happily.
The Pre/Post Execution messages were all clean, but based on the advice on this question, I did analyse them in more detail so I've picked up a bit of knowledge here too!
Thanks for all the feedback, especially the comments from billinkc:

Look at the Execution Log for the package that has run on the server. My guess is that you will eventually discover there is a permissions issue but at the moment, it could be a number of places.
Specifically, what I'd look at is the Pre/Post execute entries in the Execution report. that would indicate whether the Script task ever fired. If there's no pre/post, then look for informational events related to your Foreach Loop Containers.
If a permission issue prevents the FELC from seeing a directory, it does not error out. Instead, it fails gracefully, which trips up 99.999% of the population
If it is a validation issue (somefile.txt doesn't exist when the package begins b/c it determines what the current file is), then you will want to set the DelayValidation property to true on the Connection Manager and any tasks that reference said Connection Manager.


Postgres database zero down time production deployment

Answer originally left as a question comment.

For zero downtime during tables refactoring, we create new tables along with old ones, and switch in small steps.

  From Developing Low-Maintenance Databases by Alex Kuznetsov on Simple Talk.

  Instead of making all the changes at once, we make multiple incremental changes, as follows:

  Make sure that all the modules accessing the table are well covered with automated tests.
  Create new tables. Alter all procedures that modify the old table, so that they modify both old and new tables.
  Migrate existing data into new structure. Do it in smallish batches, so that it does not seriously impact the overall performance on the server.
  Verify that the migration of data succeeded.
  Redirect some of the selecting procedures from the old table to the new ones. Use automated tests to make sure that the changed modules are still correct. Make sure their performance is acceptable. Deploy the altered procedures.
  Repeat the previous step until all the reports use the new tables.
  Change the procedures that modify the tickets, so that they only access the new tables.
  Archive the old table and remove it from the system.

  It is true that this approach has many steps and is not very simple; yet we love it – it is far better than its alternatives. Each step incurs very little risk, and if needed, we can roll it back in a split second. We can set the migration aside and switch to another task, or enjoy our weekend, at any time – the system keeps working in any of these intermediate states for as much as needed. Our users do not experience any disruptions.

See the original article for full details.


How does compound index help speed up query?

Indexes are in most cases a B-Tree structure (or some sort of). There are DBMS that supported different indexing types.

As you are talking about longitude/latitude PostgreSQL with it's GIST indexes come to mind. Oracle has Bitmap indexes in addition to the B-Tree indexes. I'm sure SQL Server and DB2 also have some special index types. And then there are full text indexes which make searching text very efficient.

A B-Tree index is very efficient in finding a specific value - think of a primary key index where all values are different. If the index only contains the PK column(s) (i.e. it is not a clustered index) then typically looking up a row by a specific PK values takes not more than (roughly) 3-4 IO operations (at least with Oracle). 2-3 to find the index block and an additional one to read the whole row. This gets more efficient if the index contains additional columns so that the lookup of the actual table row is not needed. The term for that is "covering index" or "index only retrieval".

Now for doing "range lookups" (e.g. where foo > 42) an index is very helpful as well as in most DBMS the index can also be scanned according to a predicate. Usually (again this highly depends on the DBMS) this is slightly less efficient than a direct lookup (again this also depends on the ability to do an "index only retrieval").

I don't know of any BMS which can not use more than one index in a query. Think a join on a PK and a FK column - depending on the data distribution the DBMS might use the index to find the parent rows (PK lookup) and the child rows (FK lookup).

But not all DBMS can use more than one index for the same table in a single query.

After all whether or not an index is being used or not depends on a lot of things.

I can highly recommend http://use-the-index-luke.com/ which is a very good introduction on indexing across all major DBMS.

DBMS specific information:

Oracle: http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm
PostgreSQL: http://www.postgresql.org/docs/current/static/indexes.html


Update values in one table based on contents of another

Looks like this should do it for Oracle:

UPDATE Products
SET Products.Unit_In_Stock = Products.Unit_In_Stock -
(
    SELECT Order_Details.Quantity
    FROM Orders
        INNER JOIN Order_Details
        ON Orders.Order_ID = Order_Details.Order_ID
    WHERE Products.Product_ID = Order_Details.Product_ID
    AND (Orders.Shipped_Date IS NOT NULL)
    AND (Orders.This_Order_Was_Shipped = "YES")
)
WHERE EXISTS
(
    SELECT Order_Details.Quantity
    FROM Orders
        INNER JOIN Order_Details
        ON Orders.Order_ID = Order_Details.Order_ID
    WHERE Products.Product_ID = Order_Details.Product_ID
    AND (Orders.Shipped_Date IS NOT NULL)
    AND (Orders.This_Order_Was_Shipped = "YES")
);


Cannot login to SQL Server from Visual Studio, same credentials work on SSRS page

I solved my own problem.  I needed to grant data reader rights to that database for the user.  I do that by right clicking the user name under Security\Logins and choosing properties.  In there I go to User Mapping.  Make sure the user is mapped to the database being queried and under the Database Role Membership make sure db_datareader is checked.


log_reuse_Wait_desc always return LOG_BACKUP even right after log backup

When I queried sys.databases to see the log_reuse_Wait_desc it is always LOG_BACKUP- even right after log backup! how do I find why the log_reuse_desc is returning LOG_BACKUP always.

This is nicely explained by Paul Randal:

  Imagine a database where there’s very little insert/update/delete/DDL
  activity, so in between your regular log backups there are only a few
  log records generated, and they’re all in the same VLF. The next log
  backup runs, backing up those few log records, but it can’t clear the
  current VLF, so can’t clear log_reuse_wait_desc. As soon as there are
  enough changes in the database that the current VLF fills up and the
  next VLF is activated, the next log backup should be able to clear the
  previous VLF and then the log_reuse_wait_desc will revert to NOTHING.
  Until the next log backup occurs and isn’t able to clear the current
  VLF, in which case it will go back to LOG_BACKUP again.

  So LOG_BACKUP really means “either you need to take a log backup, or
  the log records that were backed up were all in the current VLF and so
  it could not be cleared.”

Full article: Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?

I would like to explore some options to reduce the log file size on the disk.

Before you shrink the log files it is also important for you to understand how you end up with too many or too less number of VLF's. I suggest you read the following posts. These will give you a better idea about VLF and transaction log management.

A Busy/Accidental DBA’s Guide to Managing VLFs by David Levy
How do you clear the SQL Server transaction log? by Aaron Bertrand
Aaron added 4 more links at the bottom of his answer, all those are very useful and related to your question.
Transaction Log VLFs – too many or too few? by Kimberly Tripp


SSMS 2016 CTP3.1 November Preview (v13.0.800.111) bug? properties error: missing column federated_service_account

Updating the VM's engine to CTP 3.1 resolved the issue. I no longer get the error on my laptop's SSMS.


Getting unique random looking numbers in a given interval

If you can install C code into your database, the permuteseq extension provides a ready-to-use solution to this problem. It encrypts consecutive numbers with format-preserving encryption techniques to get both the non-guessability, the unicity (it's collision-free), and the confinment in a given output range.

To auto-assign the values on insertion, the simplest way  is to set the column's DEFAULT clause.

Example

CREATE EXTENSION permuteseq;

CREATE SEQUENCE seqname MAXVALUE 9999999999; -- 10 digits

CREATE TABLE example(
 id serial,
 col text,
 random_number bigint default
     permute_nextval('seqname'::regclass, 8784354463) -- or any random number
);

INSERT INTO example(id,col) VALUES(1, 'foo');
INSERT INTO example(id,col) VALUES(2, 'bar');
INSERT INTO example(id,col) VALUES(3, 'baz');

SELECT * FROM example;

 id | col | random_number
----+-----+---------------
  1 | foo |    9737615019
  2 | bar |    7519592769
  3 | baz |    8112392939


DB2 DBM paramter NUMDB has value 8. Should I change it to 1 because I have only one database?

Information Center is pretty reliable, and I don't see any indication that it will divide memory for non-existent databases. Do you have another link that indicates that?

Otherwise, it is meant to be the maximum number of allowable activated/connected to databases. So there is no need to set it high (I believe by default it is set to something like 12).

Based on the page you have, it is recommended that you set it to the number of databases you have + 10% for room for growth. So if you have 1 database, you would want 1 database + 10% (which in this case would round up) so you would set it to 2.

The reason I believe IBM recommends this, is that you can sometimes get caught with NUMDB less than your number of databases. We have had this happen to us on several occasions in our development and QAT environments. We set the number of databases to something like 9 or 10. And then the developers decided they needed another database. We DBA's ran the CREATE DATABASE script like usual and it didn't fail. However, when the developers tried to access the new database, they got errors (I don't remember the exact SQLCODE off the top of my head). And it turned out we needed to up the value of NUMDB as we had reached the maximum number of activated/connected databases.

So I would follow IBM's recommendation, which in your case would be to set NUMDB to 2. This gives you what you need with some room for growth.

I think this also might not be a "hot" setting, thus requiring you to run db2stop and db2start for it to take affect.


How do I find the first row of the longest occurrences of repeating numbers?

For MySQL version 8+

WITH
cte1 AS
(
SELECT id,
       CASE WHEN     occupied  = LAG(occupied) OVER (ORDER BY id)
                 AND remaining = LAG(remaining) OVER (ORDER BY id)
            THEN 0
            ELSE 1
            END values_differs
FROM test
),
cte2 AS
(
SELECT id,
       SUM(values_differs) OVER (ORDER BY id) group_num
FROM cte1
ORDER BY id
)
SELECT MIN(id)
FROM cte2
GROUP BY group_num
ORDER BY COUNT(*) DESC LIMIT 1;

fiddle


What's the practice to generate long user id like those on Facebook?

Why not just set the auto_increment to 4000000 and let it count from there?

ALTER TABLE `table` AUTO_INCREMENT=4000000;

Also, make sure your id column can fit large numbers (bigint or similar)


Is it possible to compare timestamps between rows?

The problem is, when you GROUP on user,foo,bar you are not able to get a list of each of those times to compare.

Something like this might work, but no idea on the performance on a large table. You might want to only do this on a subset (ie tuples just from the last 30 days)

SELECT A.*,B.time FROM reqs A
INNER JOIN (SELECT user,foo,bar,time FROM reqs) B
 USING (user,foo,bar)
WHERE A.time!=B.time AND ABS(TIMESTAMPDIFF(SECOND, TIMESTAMP(A.time), TIMESTAMP(B.time))) < 60
GROUP BY A.user,A.foo,A.bar,A.time;


Assuming 2016-04-05 0:27:15 instead of 2016-04-05 1:27:15 in the underlying table, the question would make more sense to me:

CREATE TABLE tbl (created_at timestamp, status text);
INSERT INTO tbl VALUES
  ('2016-04-05 00:27:15', 'info')
, ('2016-04-05 03:27:15', 'info')
, ('2016-04-05 05:27:15', 'warn')
, ('2016-04-05 10:27:15', 'info')
, ('2016-04-05 11:27:15', 'warn');

The logic would be to count events that happened up to and excluding the next bound. This fits the often overlooked function width_bucket() perfectly. To be precise, it requires the variant with arbitrary bounds (since there is no regular pattern in the OP's bounds) introduced with Postgres 9.5. Explanation straight from the manual:

width_bucket(operand anyelement, thresholds anyarray)

  return the bucket number to which operand would be assigned given an
  array listing the lower bounds of the buckets; returns 0 for an input
  less than the first lower bound; the thresholds array must be sorted,
  smallest first, or unexpected results will be obtained

For regular buckets you can use another variant that's available in Postgres 9.1 as well.
Combine it with crosstab() re-using the same bounds as column names (the rest of the query works with Postgres 9.1):

SELECT * FROM crosstab(
 $$SELECT status
        , width_bucket(created_at, '{2016-04-05 01:00
                                   , 2016-04-05 04:00
                                   , 2016-04-05 08:00
                                   , 2016-04-05 12:00}'::timestamp[])
        , count(*)::int
   FROM   tbl
   WHERE  created_at < '2016-04-05 12:00'  -- exclude later rows
   GROUP  BY 1, 2
   ORDER  BY 1, 2$$
, 'SELECT generate_series(0,3)'
   ) AS t(status text, "2016-04-05 01:00" int
                     , "2016-04-05 04:00" int
                     , "2016-04-05 08:00" int
                     , "2016-04-05 12:00" int);

Result:

 status | 2016-04-05 01:00 | 2016-04-05 04:00 | 2016-04-05 08:00 | 2016-04-05 12:00
--------+------------------+------------------+------------------+------------------
 info   |                1 |                1 |                  |  1
 warn   |                  |                  |                1 |  1

The second crosstab parameter ('SELECT generate_series(0,3)') is a query string when executed returning one row for every target column. Every value not found on either side - not in the raw data or not generated by the 2nd parameter - is simply ignored.

Basics for crosstab():

PostgreSQL Crosstab Query

Replace NULL with 0

If you need 0 instead of NULL in the result, fix with COALESCE(), but that's merely a cosmetic problem:

SELECT status
     , COALESCE(t0, 0) AS "2016-04-05 01:00"
     , COALESCE(t1, 0) AS "2016-04-05 04:00"
     , COALESCE(t2, 0) AS "2016-04-05 08:00"
     , COALESCE(t3, 0) AS "2016-04-05 12:00"
FROM   crosstab(
 $$SELECT status
        , width_bucket(created_at, '{2016-04-05 01:00
                                   , 2016-04-05 04:00
                                   , 2016-04-05 08:00
                                   , 2016-04-05 12:00}'::timestamp[])
        , count(*)::int
   FROM   tbl
   WHERE  created_at < '2016-04-05 12:00'
   GROUP  BY 1, 2
   ORDER  BY 1, 2$$
, 'SELECT generate_series(0,3)'
   ) AS t(status text, t0 int, t1 int, t2 int, t3 int);

Result:

 status | 2016-04-05 01:00 | 2016-04-05 04:00 | 2016-04-05 08:00 | 2016-04-05 12:00
--------+------------------+------------------+------------------+------------------
 info   |                1 |                1 |                0 |  1
 warn   |                0 |                0 |                1 |  1

Adding totals

To add totals per status use the new GROUPING SETS in Postgres 9.5+

SELECT status
     , COALESCE(t0, 0) AS "2016-04-05 01:00"
     , COALESCE(t1, 0) AS "2016-04-05 04:00"
     , COALESCE(t2, 0) AS "2016-04-05 08:00"
     , COALESCE(t3, 0) AS "2016-04-05 12:00"
     , COALESCE(t4, 0) AS total
FROM   crosstab(
 $$SELECT status, COALESCE(slot, -1), ct  -- special slot for totals
   FROM  (
      SELECT status
           , width_bucket(created_at, '{2016-04-05 01:00
                                      , 2016-04-05 04:00
                                      , 2016-04-05 08:00
                                      , 2016-04-05 12:00}'::timestamp[]) AS slot
           , count(*)::int AS ct
      FROM   tbl
      WHERE  created_at < '2016-04-05 12:00'
      GROUP  BY GROUPING SETS ((1, 2), 1)  -- add totals per status
      ORDER  BY 1, 2
      ) sub$$
 , 'VALUES (0), (1), (2), (3), (-1)'  -- switched to VALUES for more sophisticated series
   ) AS t(status text, t0 int, t1 int, t2 int, t3 int, t4 int);

Result like above, plus:

...  | total
... -+-------
...  |     3
...  |     2

Note that total includes all rows not excluded before aggregation, even if filtered by crosstab().

This is in reply to @Vérace's request in the comments rather than to the unclear question.


Shoudn't DBCC SHIRNKFILE(Data_File, 0) command shrink data file to the last allocated extent?

Without having access to the source code for SQL Server, we can only guess. So,
Edit: 0 means shrink to initial size as per page header. My speculation that it means dummy, below, is incorrect.
SET SPECULATION MODE ON
0 makes no sense. If you want to get rid of the file, you have the EMPTYFILE option. I.e., why would you want to have a file which is 0 MB in size.
I just did a test, and indeed 0 means it doesn't do anything. We could argue that an error messages would be better: "0 MB is not valid file size.", or something like that. But apparently MS decided to leave 0 as a valid valid for the command and implementing it as a dummy.
SET SPECULATION MODE OFF
I suggest you file a request with MS to either document that 0 means "dummy", or behavior change so we'll get an error message (less likely to happen).


Is "--one-database" safe to use for importing from a dump?

The only risk is time and missing out on trigger stored procedures.

Normally, mysqldumps are done in alphabetical order.

When loading a dump, expect databases aardvark, alligator and antelope to load early. Any database like zoo and zebra would require time because --one-database does not force a mysqldump to load the specified database. The entire dump must be read until the specified database is in use.

For example, mysql --one-database zebra will read the entire mysqldump but start loading when it gets to the line the dump that says

USE zebra

Then, all INSERT INTO commands are processed until the EOF or until it see another line like

USE zimbabwe

Some developers I know would wait until the database they chose is loaded. Then, hit Ctrl-C. I would not risk that. You should make sure that all triggers and stored procedures are loaded as well.

If you cannot wait and want to Ctrl-C, login to mysql while the dump is still loading and make sure all tables, views, triggers, and stored procedures have been loaded.


Log Shipping Backup and Destination Folder Location

Recommendations are accurate. Reason 1 being never keep you backups on your database server or disk. Just good practice to separate them in case of disaster. Ideally you want you backups on different hardware as well, or even different location/data center. Approach it this way, if disaster struck, would you be able to recover? if not, what was your point of failure, server, disk, infrastructure.

The scenario you reference to on MS page explains multiple copy and restore jobs to multiple secondary locations. Copying to each secondary and restore keeps unique history on the jobs for that instance. As MS states "The primary and secondary server instances send their own history and status to the monitor server instance."