Storing last N samples in a table coming from multiple clients I want to store some last N (say fixed 50) samples in a table indexed by uuid. They're coming from multiple clients. The requirements of which last N are weak (the use case is to show some recent samples for debugging a stream of messages). But I want them to be relatively current, so my clients will update them often. So ideally I want to keep replacing the same rows.

My current plan is to have a schema like such:

CREATE TABLE samples (
  id     uuid     NOT NULL,
  index  integer  NOT NULL,
  text   text     NOT NULL,

  PRIMARY KEY(id, index)
);

The clients will then pick a random N between 0 and 50 and do upserts like such:

INSERT INTO samples
  (
    id,
    index,
    text
  )
VALUES
  (
    'f85c6f06-ae0b-48de-8c36-bfc86208495c',
    0,
    'sample 0'
  ),
  (
    'f85c6f06-ae0b-48de-8c36-bfc86208495c',
    2,
    'sample 2'
  )
ON CONFLICT (id, index)
DO UPDATE SET text = EXCLUDED.text;

The downside is that I'll be replacing them at random, which is probably going to work out but not guaranteed to. It also means multiple clients will trample on each other.

Is there a better way to do this? Some sort of round-robin style replace would be ideal, but I'm not sure how to get that effect. Is there a different strategy all together I could use here?

My idea is to use new table to store next index to update for each user:

CREATE TABLE next_sample_index (
  id     uuid     NOT NULL,
  index  integer  NOT NULL,

  PRIMARY KEY(id)
);

Just fill it with 0s for each user. I would use it as a kind of serial like this:

UPDATE
    next_sample_index
SET
    index = index + 1
WHERE
    id = XX
RETURNING
    index;

You will get next index and increase it at the same time.

Then you calculate modulus index % 50 so it acts as a round-robin queue.

To insert new sample:

DELETE FROM
    samples
WHERE
    id = XX AND
    index = --index-just-calculated--;

This DELETE command will do nothing if no such an index existed before.

INSERT INTO
    samples(
        id,
        index,
        text
    ) VALUES (
        XX,
        --index-just-calculated--,
        'f85c6f06-ae0b-48de-8c36-bfc86208495c'
    );

You just need to make sure that next_sample_index table is properly initialized or make a function to get next index that checks IF NOT FOUND condition (and makes INSERT if necessary).

XX in the examples is user id.

Hope this helps.


Efficient SELECT for array of tuples in PostgreSQL Assume I have a table records with the following structure

id (unique int)
updated (timestamp)

I have an input_array with values [[id_1, timestamp_1], [id_4, timestamp_4], ...]. I'll refer to each element as tuple_1, tuple_4, etc.

I'm looking for the most efficient query (in PostgreSQL v11.2+) to select [id_1, id_4, ...] from records, but only where tuple_{n}.updated > row{n}.updated. Assume input_array may contains thousands of tuples, and records upwards of a million rows.

I don't even know where to begin with this. Lateral join comes to mind, as does unnest, and where in, but everything I've tried so far fails miserably

Update I'm open to input_array being in any format (tuples, two separate arrays, whatever), and updated being an int

If you aren't fixed on the array input, you can use a tuple comparison.

select *
from records
where (id, updated) in ( (1, timestamp '2019-01-01 00:00:00'),
                         (2, timestamp '2019-01-02 00:00:00') )

That can make use of a regular btree index on (id, updated)

Note that this uses = for both values and is equivalent to

where (id = 1 and updated = timestamp '2019-01-01 00:00:00')
   or (id = 2 and updated = timestamp '2019-01-02 00:00:00')

But you want to compare the timestamps using >. You can do that if you join against a values clause:

select r.*
from records r
  join (
    values
       (1, timestamp '2019-01-01 00:00:00'),
       (2, timestamp '2019-01-02 00:00:00')
  ) as t(id,upd) on  r.id = t.id
where r.updated > t.upd;

Online example: https://rextester.com/GGC83046


Clustered Index Update on Nonclustered Columns Using: SQL Server 2008 R2

I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table.  The issue here is that the columns that are being updated are NOT part of the clustered index.

Table:

<table>
    id INT IDENTITY(1,1) -- Clustered Index
    , name VARCHAR(20) -- Nonclustered Index
    , status CHAR(1)
    , quantity INT
    , price FLOAT

Update statement:

UPDATE a
SET a.status = @status
    , a.quantity = @quantity
    , a.price = @price
FROM <table> a
WHERE a.name = @name

The execution plan shows an Eager Spool at 36% cost, a Clustered Index Update at 55% cost, and an Index Seek on the name index at 9%, among Compute Scalar and Top items at 0% cost.

Why is the plan showing a clustered index update?  What could I do to prevent this, and prevent the eager spool?

When you have a clustered index on a table, the clustered index IS the table!

Mentally  you can substitute "table" for "clustered index" in this instance and it will make sense.

The data for every field in every row is in your clustered index.  The clustered index just sets the order of the physical pages in the database to be organized by your clustering key(s).

You can always fall back on the phone book analogy for these things, too:  in  your classic phone book, the data is clustered on Last Name, First Name.  Each entry still has PhoneNum, Address at the leaf level but you don't order by that.  The pages are in physical order by the the keys.

I can't advise on optimizing the query unless you show us the table and query you are running, but basically this cost will be paid one way or another.  If you don't update the clustered index it will be a table update and a table scan.


How to delete mdf file in use but not attached? I have a mdf file that is not attached but when I try to delete it Windows Explorer fails due to the file being in use. SQL Manager also says it's in use when I try to attach the MDF so that I can delete it. Is there a way to kill the connection and delete the file without stopping the SQL service?  Thanks

Try to set the database (returned by the query in sys.master_files) Offline with

Alter database [YourDB] set offline;

If the database is still seen in the dmv, it may still be possible to set it as offline (and then you'll be able to delete the file).

But if you want to delete the file, you could also just try to drop the database

Drop database [YourDB];


How to use the 'select benchmark' command on a procedure I am trying to check which of my procedures is slowest and thus needs optimization.

I currently have around 160 procedures and about 30 functions.

I am using the select benchmark() to check the procedure speed, but all the commands I try end in the error "Check your syntax".

select BENCHMARK(1000, CALL testProc()); gives me an error,
select BENCHMARK(1000, testProc()); also gives me an error.

QUESTION

 Can Benchmark work on procedures, the examples I see only use functions.
 Is there a better, faster automated way to test execution speeds of procedures.

The BENCKMARK function only works on expressions.
What qualifies as an expression?

Stored Function (has a return value)
ENCODE function on a String
1 + 2 (that expression gives an integer)

ALTERNATIVE
What you need is to simulate the BENCHMARK function yourself.
Here is some sample code for you to try
drop database if exists mybmark;
create database mybmark;
use mybmark
DELIMITER $$
DROP PROCEDURE IF EXISTS `mybmark`.`testproc` $$
CREATE PROCEDURE `mybmark`.`testproc` ()
BEGIN

    DECLARE answer INT;

    SELECT 1+2 INTO answer;

END $$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `mybmark`.`mybenchmark` $$
CREATE PROCEDURE `mybmark`.`mybenchmark` (loop_count INT,expr varchar(128))
BEGIN
    DECLARE dt1,dt2,dtdiff,ndx INT;

    SET dt1 = UNIX_TIMESTAMP();
    SET ndx = loop_count;
    SET @sql = expr;
    PREPARE stmt FROM @sql;
    WHILE ndx > 0 DO
        EXECUTE stmt;
        SET ndx = ndx - 1;
    END WHILE;
    DEALLOCATE PREPARE stmt;
    SET dt2 = UNIX_TIMESTAMP();
    SET dtdiff = dt2 - dt1;

    SELECT dt1,dt2,dtdiff;

END $$
DELIMITER ;

To benchmark the testproc procedure, just pass the call to the testproc procedure as a parameter. For example to call the testproc procedure 1,000,000 times, do this:
mysql> call mybmark.mybenchmark(1000000,'CALL mybmark.testproc()');
+------------+------------+--------+
| dt1        | dt2        | dtdiff |
+------------+------------+--------+
| 1333474085 | 1333474102 |     17 |
+------------+------------+--------+
1 row in set (16.80 sec)

Query OK, 0 rows affected (16.80 sec)

mysql>

Give it a Try !!!


I only want one maximum value but it returns all the rows I need help with this query. I'm trying to get a value for a customer that spends the most amount every month in 2018 but it doesn't work:

PROMPT 'Customer that has Spent the Most on Specific Month'
    ACCEPT MonthInput Varchar PROMPT 'Month Value:'

SELECT  E.cust_name,
        E.cust_ID,
        MAX(P.total_amount)
FROM    customer E,
        room_booking RB,
        payment P,
        booking_details B
WHERE   P.booking_id    = RB.booking_id
AND     RB.cust_id      = E.cust_id
AND     B.booking_id    = RB.booking_id
AND     B.check_in_date LIKE'%-&MonthInput-2018%'
GROUP BY E.cust_name, E.cust_ID

My tables :

Customers (
    cust_name,
    cust_ID
    )

room_booking (
    booking_ID,
    cust_ID*
    )

booking_details (
    booking_ID*
    )

payment (
    booking_ID*
    )

It returns many rows, because the MAX-function goes through every group(each customer, as you have grouped the result pr customer), and for each customer, it finds the MAX P.total_amount.
I don't know how your data looks, but if each customer really just have one P.total_amount, it just prints each customers P.total_amount.

What you probably want, is something like this:

SELECT TOP 1
    E.cust_name,
    E.cust_ID,
    MAX(P.total_amount)
FROM    customer E,
    room_booking RB,
    payment P,
    booking_details B
WHERE   P.booking_id    = RB.booking_id
AND     RB.cust_id      = E.cust_id
AND     B.booking_id    = RB.booking_id
AND     B.check_in_date LIKE'%-&MonthInput-2018%'
GROUP BY E.cust_name, E.cust_ID
ORDER BY MAX(P.total_amount) DESC;

What we do here is the same as your original query, but we sort the list by the total amount, and selects just the first row, which must be the customer with the highest value.

If, continuing my assumption, that each customer only have one P.total_amount per month, then the grouping might not even be needed:

SELECT TOP 1
        E.cust_name,
        E.cust_ID,
        P.total_amount
FROM    customer E,
        room_booking RB,
        payment P,
        booking_details B
WHERE   P.booking_id    = RB.booking_id
AND     RB.cust_id      = E.cust_id
AND     B.booking_id    = RB.booking_id
AND     B.check_in_date LIKE'%-&MonthInput-2018%'
ORDER BY P.total_amount DESC;

Note that not all DBMS-systems support the TOP syntax.
SQL Server does. Other systems like Postgres or MYSQL use LIMIT to a similar effect. Some DBMS also support the standard syntax OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY (SQL Server 2012+, Postgres, Oracle 12)

See wikipedia: SELECT (SQL): Limiting result rows


How do I troubleshoot loss of connection with SQL server I have an application that intermittently has a break in the connection between it and the SQL Server instance it requires to function. What is a good way to go about troubleshooting this type of problem. Is there some kind of log where SQL server logs something if it drops a connection on purpose.

I understand that just about anything in between the application and the database could be causing the connection to be lost. I don't think its a network connectivity issue between the client and the server because this application is being served via Citrix XenApp and all the other instances running on that same host are not having trouble at, or around that point in time when I saw the problem.

If it matters, this is SQL Server 2005 - Enterprise Edition. My application is a Visual FoxPro based application being served to client machines via Citrix XenApp. This problem is rare and intermittent (1-2 times a day over hundreds of clients).

Also, SQL Server and Citrix are running on a virtualized server infrastructure.

TL;DR: How do I troubleshoot intermittent database connectivity loss?

Update: This is the message in the application error log when the problem happens:

Connectivity error: [Microsoft][ODBC SQL Server Driver]Communication link failure

The only time I've seen that message before is when SQL Server was completely overloaded and when the actual network connection itself was having problems (disconnected). I don't think either of those two things are the case in this instance.

Definitely have a look at the NT and SQL logs... and if you were running on SQL Server 2008 or later, check the ring buffers DMV for a few extra clues. Do you have any monitoring tools that you can increase the sample rate with, to add evidence the problem is maybe with the Citrix Xenapp delivery?

Ring Buffers:
http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

Kendra Little did a good introduction video on troubleshooting query timeouts, not quite the same as connection timeouts.
http://www.brentozar.com/archive/2011/07/mystery-of-query-timeouts-video/


Can’t run a function against EXEC My SQL Server book says you can't run a function inside EXEC and shows an example:

DECLARE @NumberOfLetters int = 15;
EXEC(‘SELECT LEFT(Name,’ + CAST(@NumberOfLetters AS varchar)
 + ‘) AS ShortName FROM Production.Product’);

This doesn't work as the CAST() function needs to be fully resolved prior to the EXEC line, so we can modify the code as:

DECLARE @NumberOfLetters AS int = 15;
DECLARE @str AS varchar(255);
SET @str = ‘SELECT LEFT(Name,’ + CAST(@NumberOfLetters AS varchar) + ‘) AS ShortName FROM Production.Product’;
EXEC(@str);

This time it works because, as mentioned above, the CAST() function needs to be fully resolved prior to the EXEC line.

But I have one more question, what about the LEFT() function, why it can be run against EXEC?

The LEFT() function in your example is not part of the EXEC statement. It is inside the string representing part of the dynamic SQL to be executed by your EXEC statement.

In contrast, the CAST() function is used to build that string. If you use the correct quotation marks – ' instead of ‘ & ’ – then perhaps you can also see the difference in the syntax highlighting:

DECLARE @NumberOfLetters int = 15;
EXEC('SELECT LEFT(Name,' + CAST(@NumberOfLetters AS varchar)
 + ') AS ShortName FROM Production.Product');

If syntax highlighting shows correctly on your monitor, then you should be able to see that the LEFT keyword is shown in red as part of a string, whereas the name CAST is shown in a different colour, and that is because it is not part of a string, rather it is part of an expression used to construct a string. That is precisely what the problem is: EXEC (...) does not accept an expression. It only accepts either a (single) string literal or a string variable.

Because you have to build your dynamic SQL script based on some values (the @NumberOfLetters value, in this case), you have to use a variable. You store the built script in it and then you pass it to the EXEC statement to run the dynamic script. That is what the modified code does:

DECLARE @NumberOfLetters AS int = 15;
DECLARE @str AS varchar(255);

-- you build the dynamic script first and store it in a variable
SET @str = 'SELECT LEFT(Name,' + CAST(@NumberOfLetters AS varchar)
         + ') AS ShortName FROM Production.Product';

-- then you just pass the variable to EXEC to run the dynamic script
EXEC(@str);


How to get space used by In-Memory table in SqlServer I'm trying to understand how much storage In-Memory table consumes in SqlServer:

SELECT is_memory_optimized,durability_desc FROM sys.tables WHERE name='TBL_HSD_AD_OR'
select count(*) [#Rows] from TBL_HSD_AD_OR with(nolock)

Now when I'm using EXEC sp_spaceused I get:

EXEC sp_spaceused 'TBL_HSD_AD_OR'

Is there a different way to get the space used for Memory-Optimized tables?

I guess this helps you..

With the help of 'Cached Page count' we can calculate the Total space occupied.

        set nocount on;
        set transaction isolation level read uncommitted;
        select
           count(*)as cached_pages_count,
           (COUNT(*) * 8.0) / 1024 AS Total_MB_Occupied, -- convert pages into MB -  the page size is 8 KB for sql server
           obj.name as objectname,
           ind.name as indexname,
           obj.index_id as indexid
        from sys.dm_os_buffer_descriptors as bd
        inner join
        (
            select       object_id as objectid,
                               object_name(object_id) as name,
                               index_id,allocation_unit_id
            from sys.allocation_units as au
                inner join sys.partitions as p
                    on au.container_id = p.hobt_id
                        and (au.type = 1 or au.type = 3)
            union all
            select       object_id as objectid,
                               object_name(object_id) as name,
                               index_id,allocation_unit_id
            from sys.allocation_units as au
                inner join sys.partitions as p
                    on au.container_id = p.partition_id
                        and au.type = 2
        ) as obj
            on bd.allocation_unit_id = obj.allocation_unit_id
        left outer join sys.indexes ind
        on  obj.objectid = ind.object_id
        and  obj.index_id = ind.index_id
        where bd.database_id = db_id()
        and bd.page_type in ('data_page', 'index_page')
        and obj.name like '%TABEL NAME%'  --- **Provide your table name**
        group by obj.name, ind.name, obj.index_id
        order by cached_pages_count desc


MySQL (on Windows) general log too large to rotate properly? First off, I'm not sure if this should go to Server Fault or Database Administrators.  It could be a Windows Server 2008R2 problem.  But I am asking here first in the chance that someone else running MySQL on Windows might have had the same problem.

We are using MySQL Community Version 5.5.34 with the general log turned on.  On Windows, there was no built-in log rotation script, so I wrote one, following the documentation on the MySQL website.  The information there says to rename the log file, then flush.  This is supposedly because it's quicker (almost instant) to rename the file than it is to make a copy of it in Windows.  Then presumably the log can be flushed right afterward (instead of waiting some arbitrary length of time for the log file to be copied).  The script renames the log to (date-time-stamp).log, then flushes the log using mysqladmin.  It then compresses the log as a zip file and moves it to an archive folder.

Anyway, I created a Windows Scheduled Task to run the script.  Everything works when I run the script either directly or through the Scheduled Task manager.  But when I leave it to run on its schedule, the log is not rotated properly.  It looks like the log is flushed, because I see the new log.  But the renamed file is not there.  Our log does get fairly large, the first day it got to about 400 MB.  When I ran tests on the script, the log was smaller (10 MB, etc).  I'm wondering if having a large quickly updating log might be too much for Windows/MySQL to handle.  Is there any other recommended way to rotate logs in Windows?

I will try running the rotation script without the zip compression and moving the file to the archive folder to see if that affects anything.  But like I said, everything works when I test the script both from the command line or through the Scheduled Task Manager (so presumably permissions and working directory and stuff like that should be ok).  The only difference I can think of is the size of the log file when the script is run.

Any ideas?

First of all, I'd like to commend you for using the general log. If this were Linux, log rotation could all be done with shell scripting and without manipulating mysql in any way. In your case, it is Windows.
Weel, I have some good news and bad news for you, in that order.
GOOD NEWS
You can enable the general log to record events in a table rather than a text file.
On Feb 11, 2012, I wrote a post on how to make the general log a MyISAM table, how to index it, how to enable it, and how to rotate it : MySQL general log. Don't worry, this works for Linux and Windows.
The beauty of having the general log as a table is the fact that you can query it for specific text and specific datetime.
BAD NEWS
You cannot take the text file version of the general log and convert. you will have to stop mysql  with net stop mysql. You can copy the text file or delete it.
GIVE IT A TRY !!!
CAVEAT : It can be a headache to work with the general log as a text file in Windows since Windows places a a strict lock of the table so that you cannot truncate it with MySQL running as you could in Linux.


How do you remove fragmentation from InnoDB tables? I have a Database having number of tables.

I want to delete some records from the tables say the no of records are more than 20K or 50K.

The All the Tables are InnoDB. And file_per_table is off.

When I will delete the records from a number of tables there will be fragmentation in the tables.

Is there any way to remove the fragmentation.?

Update On 17th April

mysql> select TABLE_NAME, TABLE_SCHEMA, Data_free from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema', 'mysql') and Data_Free >0;
+-----------------+--------------+-----------+
| TABLE_NAME      | TABLE_SCHEMA | Data_free |
+-----------------+--------------+-----------+
| City            | world_innodb |   5242880 |
| City_Copy       | world_innodb |   5242880 |
| Country         | world_innodb |   5242880 |
| CountryLanguage | world_innodb |   5242880 |
| a               | world_innodb |   5242880 |
| t1              | world_innodb |   5242880 |
| t2              | world_innodb |   5242880 |
+-----------------+--------------+-----------+
7 rows in set (0.00 sec)

So Now my Question is that how i will decide that my tables are fragmented or not.

I have addressed this in StackOverflow back in October 2010.

Keep in mind the busiest file in the InnoDB infrastructure : /var/lib/mysql/ibdata1

This file normally houses four types of information

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
Table Metadata (List of tablespace IDs)

Running OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:

Makes the table's data and indexes contiguous inside ibdata1, thus faster to access
It makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1

While you can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table, the big gaping whole of diskspace in ibdata1 simply won't disaapear and cannot be reclaimed. You must do more.

To shrink ibdata1 once and for all you must do the following:

1) MySQLDump all databases into a SQL text file (call it /root/SQLData.sql)

2) Drop all databases (except mysql schema)

3) Shutdown mysql

4) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

5) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

6) Restart mysql

This will recreate ibdata1 at 10 or 18MB (depending onthe version of MySQL), ib_logfile0 and ib_logfile1 at 1G each

7) Reload /root/SQLData.sql into mysql

ibdata1 will grow but only contain table metadata. In fact, it will grow very slowly over the years. The only way ibdata1 growth quickly is if you have one or more of the following:

A lot of DDL (CREATE TABLE, DROP TABLE, ALTER TABLE)
A lot of transactions
A lot of changes to commit per transaction

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable; and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul !!!

UPDATE 2012-04-19 09:23 EDT

After running the above steps, how can you determine what tables need to be defragmentated? It is possible to find out, but you will have script it.

Here is an example: Suppose you have the table mydb.mytable. With innodb_file_per_table enabled, you have the file /var/lib/mysql/mydb/mytable.ibd

You will have to retrieve two numbers

FILESIZE FROM OS : You can ascertain the filesize from the OS like this

ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'

FILESIZE FROM INFORMATION_SCHEMA : You can ascertain the filesize from information_schema.tables like this:

SELECT (data_length+index_length) tblsize FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

Just subtract INFORMATION_SCHEMA value from OS value and divide the difference by the INFORMATION_SCHEMA value.

From there you would decide what percentage deems it necessary to defrag that table. Of course, you defrag it using one of the following commands:

OPTIMIZE TABLE mydb.mytable;

or

ALTER TABLE mydb.mytable ENGINE=InnoDB;


Why won't Oracle use the spatial index when JOINing tables? (Or: Why is my query with a spatial JOIN so slow in Oracle?) I have the following table:

CREATE TABLE MY_DATA (
  MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
  GEOM SDO_GEOMETRY
);

I've set its SDO metadata and created a spatial index:

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)
VALUES ('MY_DATA', 'GEOM', 4326, MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180.0000,180.0000,0.00000000001),MDSYS.SDO_DIM_ELEMENT('Y',-90.0000,90,0.00000000001)));

CREATE INDEX SPIX_MY_DATA ON MY_DATA (GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

The table has about 150,000 rows.

I need to find rows where the geometries intersect one specific row. This seems like a simple enough task. All it takes is a JOIN and a WHERE clause:

SELECT /*+ INDEX(MD2 SPIX_MY_DATA)*/
    MD2.MY_DATA_ID
FROM MY_DATA MD1
JOIN MY_DATA MD2 ON SDO_RELATE(MD1.GEOM, MD2.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

But this query is very slow. It takes over 85 seconds to fetch only 8 related rows. We can see from the query plan that it's not using the spatial index, in spite of the index hint:

Plan hash value: 4008712617

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   379 |  9854 |  1390   (1)| 00:00:17 |
|   1 |  NESTED LOOPS                |               |   379 |  9854 |  1390   (1)| 00:00:17 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C00755898 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | MY_DATA       |   379 |  4927 |  1388   (1)| 00:00:17 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MD1"."MY_DATA_ID"=143668)
   4 - filter("MD2"."MY_DATA_ID"<>143668 AND
              "MDSYS"."SDO_RTREE_RELATE"("MD1"."GEOM","MD2"."GEOM",'mask=ANYINTERACT
              querytype=window  ')='TRUE')

(Row 2 is MD1, and row 4 is MD2.)

I tried changing the hint to /*+ INDEX(MY_DATA SPIX_MY_DATA)*/, but this also had no effect. I've made sure my statistics are up to date.

How can I speed up this query? How can I get it to use the spatial index?

I'm currently using Oracle 11.2.

Behavior of SDO_RELATE and the query planner

SDO_RELATE will only use the spatial index of the first parameter. The documentation doesn't really say this explicitly, but it can be gleaned from some details there.

The documentation for the two geometry arguments say:

  geometry1: Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.

  geometry2 Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

(emphasis mine)

This suggests very strongly that SDO_RELATE is designed specifically to use the spatial index on its first argument. However, since you have a primary key filter on MD1 in your WHERE clause, the planner rightly concludes that using the primary key index will be faster that using the spatial index on MD1. It never considers reversing the arguments to the function, and since SDO_RELATE uses the index on the first argument instead of the second, it never considers actually using the spatial index.

SDO_FILTER behaves the same way, as far as I can tell.

The solution

The answer is as simple as it is unintuitive. Just switch the order of the arguments to SDO_RELATE:

SELECT
    MD2.MY_DATA_ID
FROM MY_DATA MD1
JOIN MY_DATA MD2 ON SDO_RELATE(MD2.GEOM, MD1.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

This causes it to use the spatial index now:

Plan hash value: 3780744499

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |  1487 | 38662 |   534   (1)| 00:00:07 |
|   1 |  NESTED LOOPS                |               |  1487 | 38662 |   534   (1)| 00:00:07 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C00755898 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |  1487 | 19331 |   534   (1)| 00:00:07 |
|*  5 |    DOMAIN INDEX              | SPIX_MY_DATA  |       |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MD1"."MY_DATA_ID"=143668)
   4 - filter("MD2"."MY_DATA_ID"<>143668)
   5 - access("MDSYS"."SDO_RTREE_RELATE"("MD2"."GEOM","MD1"."GEOM",'mask=ANYINTERACT
              querytype=window  ')='TRUE')

The query is now nearly instantaneous, running in about 50 milliseconds.

Ignore the ORDERED hint part in the docs for cases like this

The documentation goes on to say later:

  geometry2 can come from a table or be a transient SDO_GEOMETRY object, such as a bind variable or SDO_GEOMETRY constructor.

  If the geometry2 column is not spatially indexed, the operator indexes the query window in memory and performance is very good.
  If two or more geometries from geometry2 are passed to the operator, the ORDERED optimizer hint must be specified, and the table in geometry2 must be specified first in the FROM clause.

From this, we might think we could also force the planner to use the index by putting MD2 first in the list and specifying the ORDERED hint:

SELECT /*+ ORDERED INDEX(MD2 SPIX_MY_DATA)*/
    MD2.MY_DATA_ID
FROM MY_DATA MD2
JOIN MY_DATA MD1 ON SDO_RELATE(MD1.GEOM, MD2.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

which gives us a different execution plan:

Plan hash value: 3307523706

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |  1487 | 38662 | 14806   (1)| 00:02:58 |
|   1 |  NESTED LOOPS                    |               |  1487 | 38662 | 14806   (1)| 00:02:58 |
|*  2 |   TABLE ACCESS FULL              | MY_DATA       |   148K|  1887K|  4752   (1)| 00:00:58 |
|   3 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|   4 |    BITMAP AND                    |               |       |       |            |          |
|   5 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|   6 |      SORT ORDER BY               |               |       |       |            |          |
|*  7 |       DOMAIN INDEX               | SPIX_MY_DATA  |     1 |       |     0   (0)| 00:00:01 |
|   8 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|*  9 |      INDEX RANGE SCAN            | SYS_C00755898 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MD2"."MY_DATA_ID"<>143668)
   7 - access("MDSYS"."SDO_RTREE_RELATE"("MD1"."GEOM","MD2"."GEOM",'mask=ANYINTERACT
              querytype=window  ')='TRUE')
   9 - access("MD1"."MY_DATA_ID"=143668)

This does force the index to be used, but it's used in the wrong way. It's still doing a full table scan on MD2. Instead of filtering MD2 by the lone geometry from MD1's result set, it grabs almost all the rows in the table from MD2 (note the 148K count on row 2 of the plan), performs two searches on MD1 (one by the primary key and the other comparing all the rows in MD2 using the spatial index), and bitmap-ands the result. This actually makes performance worse, doing a completely unnecessary scan on the spatial index and extra work comparing that scan to the primary key one.

As a general rule, you want to put the table with the smallest set of geometries (after other filters) first in your FROM clause and let it use the spatial index on the second table.

Just specifying the ORDERED hint on the original query does not change the plan from the original.


How to ensure database is cataloged I am planing to upgrade my database. I am referring to this website:
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.qb.upgrade.doc/doc/t0007193.html

On the Before you begin part:
The second statement is Ensure that all the local databases that you want to upgrade are cataloged.

As my understanding, database is auto cataloged once its created. I run db2 list database directory and get some info, but not sure its consider cataloged or not:

[db2inst1@localhost bin]$ db2 list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = MYDB
 Database name                        = MYDB
 Local database directory             = /home/db2inst1
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Kindly advise.

There are 2 types of database directories in Db2 for LUW: system and local.
When you create a database on some path /some_path, the following directory structure is created for this local database directory (if it doesn't exist): /some_path/${DB2INSTANCE}/NODE0000/sqldbdir and the database is cataloged (or registered) in the system database directory automatically.
You may have many local directories in the system, but only one system directory.
You have an ability to uncatalog ("deregister") a database manually afterwards, which removes the corresponding entry from the system directory, but not from the local one. The only way to remove a database from its local directory is to drop it.

So, the problem described in the doc is about such uncataloged databases, which Db2 instance is unaware of. If you uncataloged your databases previously, and forgot to catalog them again, you should find such databases and catalog them. If you don't remember their local database directory paths, you may use unix find /some_path -type d -name sqldbdir command, and db2 list db directory on /some_path, if you found the directory structure like /some_path/${DB2INSTANCE}/NODE0000/sqldbdir.


MySQL Statement not displaying some values I have a MySQL Statement that runs perfectly but does not display some of the values from the table that it is supposed to display. My table has got a column called 'token' which has values that are generated from another table. The values are numeric and 20 characters long so its something like '57683913499284962398' which I transfer to the working table.

I would like to be able to select the token which is already generated but on running my query it is the only column that is not displayed here is my query:

SELECT `Group Transaction No`,`Receipt No`, `Transaction No`, `Actual Cost`,
`CErr`,`Resource Units`, `Meter Serial No`, `Token`, `Transaction Timestamp`,
ROUND(SUM(`Transaction Amount`),0) as Amount FROM `efulusi_acc`
GROUP BY `Group Transaction No`
ORDER BY `efulusi_acc`.`Token` limit 5

Your query is just plain invalid, as you have included values in the column list that are not part of the GROUP BY.  MySQL lets you do this (unfortunately), but the results will be indeterminate.  Consider setting sql_mode=TRADITIONAL in my.cnf (and SET GLOBAL sql_mode=TRADITIONAL)

A valid query would like like this, though I'm not sure it will get you the results that you want.

SELECT `Group Transaction No`,`Receipt No`, `Transaction No`, `Actual Cost`,
`CErr`,`Resource Units`, `Meter Serial No`, `Token`, `Transaction Timestamp`,
ROUND(SUM(`Transaction Amount`),0) as Amount FROM `efulusi_acc`
GROUP BY 1,2,3,4,5,6,7,8,9
ORDER BY 8
LIMIT 5

Also, it's a really bad idea to include spaces in your column names.  It's just plain difficult to work with and can cause a lot of edge case bugs.


Max redo speed on replica side in Availability Group I heard about maximum 50 Mb/sec for receive and 50Mb/sec for redo.
But really, I have only 20Mb/sec redo speed. This is speed from Perfmon, so I know about the following issue:

AlwaysOn Availability Groups log_send_rate

As I know redo operation depends on CPU and for SQL Server 2012 it's run use only one core of CPU. I think it’s awful for situation when I can generate a lot of logs. For ex. when reorganize a lot of indexes in big tables.

Do you know about any KB or advise for improving performance for this case?

"Heard about"? From where? As far as I know this is dependent on local factors such as raw disk subsystem capability, controller capacity, concurrency, type of data being logged, etc. etc.

Redoing large index maintenance operations on AGs is definitely a known issue. This post certainly admits as much and suggests ways to mitigate the issues:

Recommendations for Index Maintenance with AlwaysOn Availability Groups

But I don't think you can expect any predictable x MB/sec never mind a guarantee, you will have to observe what your system is capable of, and troubleshoot issues when it falls below your threshold range.


Combine Multiple primary databases to single AlwaysOn AG Can i combine 2 different databases like db01 database from server1 and db02 database from server2 to single AG group on DR ?

Version : SQL server 2016 enterprise edition

No, you cannot. This is because a database can belong to one and only one availability group.

You can, however, create an availability group from Server1 to DR and Server2 to DR which would have the DR server as a secondary to both availability groups and a copy of both databases would reside there.


Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint? I am trying to work out ways to improve the performance on a very slow query. It has more obvious problems but one thing I noticed is that one of the conditions in the WHERE clause is 'AND t.data IS NOT NULL' while the 'data' column of table t has no entries that are NULL and has indeed a NOT NULL contraint.
So I wondered whether the query optimizer would be able to ignore the condition or not. My thinking is that it cannot do that just because of the constraint (since it is not guaranteed if it was created with NOVALIDATE) but might be 'clever' enough to use statistics about the number of NULL fields in the column.
My own testing was inconclusive and I was unable to find any further informations on this topic.

My limited testing shows that the "IS NOT NULL" predicate can be eliminated if:

the column is declared as NOT NULL in the table definition, or
the column is protected from nulls by an active, trusted check constraint

Here's a simple test table:
CREATE TABLE dbo.Test
(
    Id int IDENTITY(1,1) NOT NULL,
    DeclareNotNull int NOT NULL,
    DeclaredNull int NULL,

    CONSTRAINT PK_Test PRIMARY KEY (Id),
    CONSTRAINT CK_DeclaredNull CHECK (DeclaredNull IS NOT NULL)
);
GO

INSERT INTO dbo.Test
    (DeclareNotNull, DeclaredNull)
SELECT
    v.[number],
    v.[number]
FROM master.dbo.spt_values v
WHERE
    v.[number] IS NOT NULL;
GO

It has two columns: one is declared to be NOT NULL, the other is declared NULL but has a check constraint.  Neither column has any rows with NULL values.
We can validate the check constraint like this:
SELECT
    cs.[name],
    cs.[type_desc],
    cs.is_disabled,
    cs.is_not_trusted
FROM sys.check_constraints cs
WHERE cs.parent_object_id = OBJECT_ID(N'dbo.Test');

We can then get estimated plans on these two queries:
SELECT * FROM dbo.Test WHERE DeclareNotNull IS NOT NULL;
SELECT * FROM dbo.Test WHERE DeclaredNull IS NOT NULL;

Notice there are no "Predicate" or "Seek Predicate" sections in the scan, or other filter operators in the execution plan.  The null check has been removed in both cases.
If we disable the check constraint:
ALTER TABLE dbo.Test
NOCHECK CONSTRAINT CK_DeclaredNull;
GO

SELECT
    cs.[name],
    cs.[type_desc],
    cs.is_disabled,
    cs.is_not_trusted
FROM sys.check_constraints cs
WHERE cs.parent_object_id = OBJECT_ID(N'dbo.Test');

And we get the estimated plan for that second query:

The clustered index scan operator now includes a "Predicate" section, since the check constraint is not trusted.


Linked server to remote sql server failed I have tried to create linked server to the remote sql server but when it failed with following error message.

  The test connection to the linked server failed.

  ------------------------------ ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Login failed. The login is from an untrusted domain and cannot be used
  with Windows authentication. (Microsoft SQL Server, Error: 18452)

I have followed the instructions from How do I specify a linked server to a remote database over tcp/ip?.

My config settings are

EXEC master.dbo.sp_addlinkedserver
@server     = N'TESTSERVER',
@srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
@provider   = N'SQLNCLI',
@datasrc    = N'172.x.x.x';
-- Pair local and remote logins
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname  = N'TESTSERVER',
@useself     = N'False',
@locallogin  = N'Local_user_name',
@rmtuser     = N'Remote_user_name',
@rmtpassword = N'Remote_password';

I am able to connect the remote server instance. But it failed when I used that as a linked server in my Local instance.

Please help me resolve the error.

Thanks.

  Update

I am able to connect by changing the setting from Be made using the login’s current security context to  Be made using this security context

Please let me know why the latter works.

As I can see from the your error messages you are connecting to the server from different domain. So your cuurent windows credentials cannot be used as Be made using the login’s current security context implies.

Option Be made using this security context implies that during authentication SQl Server will use the credentials specified in the text boxes (SQL Server authentication).

So the answer is - you can use the method Be made using this security context with the credentials you specified, or, alternatively you can specify how every local user will authenticate on the remote server (specifying that no authentication is made for any login not in the list above):


Redshift - Optimize Expensive Query I have two tables in Redshift that I am trying to do a join on to get zip code demographics based on a users normalized ip address. By normalized address, I mean that it is concerted to a uniform length string that has the periods stripped out and can be directly compared to one another. For example, this is applied to all ips before any joins are done and is stored in the tables:

lpad(split_part(ip, '.', 1), 3, '0') ||
lpad(split_part(ip, '.', 2), 3, '0') ||
lpad(split_part(ip, '.', 3), 3, '0') ||
lpad(split_part(ip, '.', 4), 3, '0')

so 209.170.151.71 would be transformed into 209170151071.

I have two tables. This first is visitor_details which contains the following:

-----------------------------
| visitor_id |      ip      |
-----------------------------
|      1     | 209170151071 |
|      2     | 123170167071 |
      ...           ...
| 50000000   | 001213020341 |
-----------------------------

The I have a table called geo_ip which has the following structure:

----------------------------------------
|    start_ip |    end_ip      |  zip  |
----------------------------------------
|209170151071 | 209170151071   | 11101 |
|309170151071 | 409170151071   | 11102 |
      ...           ...           ...
|509170151071 | 609170151071   | 11103 |
----------------------------------------

I'm trying to run the following query:

WITH vd AS (
  SELECT visitor_id,
         ip_address as c_ip
  FROM dev.visitor_details
)
SELECT
  visitor_id,
  c_ip,
  g.*
FROM
  vd
JOIN
  dev.geo_ip g
  ON vd.c_ip BETWEEN g.startip and g.endip
LIMIT 500;

The sort keys on geo ip are an interleaved sort key using both startip and endip. The table also doesn't seem to be skewed. However, running the query results in a very long execution time (never completed). Looking at the explain I see the following:

XN Limit  (cost=0.00..245.17 rows=500 width=238)
   ->  XN Nested Loop DS_BCAST_INNER  (cost=0.00..18442148764959.20 rows=37610983146614 width=238)
         Join Filter: ((("inner".startip)::text <= ("outer".ip_address)::text) AND (("inner".endip)::text >= ("outer".ip_address)::text))
         ->  XN Seq Scan on visitor_details  (cost=0.00..596971.20 rows=59697120 width=72)
         ->  XN Seq Scan on geo_ip g  (cost=0.00..56702.71 rows=5670271 width=166)
 ----- Nested Loop Join in the query plan - review the join predicates to avoid Cartesian products -----

Can anyone make any suggestions on how to either optimize the query of the table setup to make it run efficiently?

The main thing is to avoid the nested loop join that is caused by the "between" in the join condition.

In your example specifically, I would start by rewriting this as

SELECT
  visitor.id,
  visitor.ip,
  LAST_VALUE(zip IGNORE NULLS) OVER (
    ORDER BY COALESCE(geo_ip.start_ip, visitor.ip)
    ROWS UNBOUNDED PRECEDING
  ) as zip
FROM geo_ip
FULL OUTER JOIN visitor
ON visitor.ip=geo_ip.start_ip

Note that Redshift will only do a full outer join if considers it a merge joinable condition, which means you should set your distribution and sort key for both tables to be on visitor.ip and geo_ip.start_ip, respectively. If this is not an option, or too much of a bother, you can do this instead as a UNION ALL since you don't actually need the geo and visitor records to be on the same row:

SELECT
  visitor_id,
  visitor_ip,
  LAST_VALUE(zip IGNORE NULLS) OVER (
    ORDER BY ip, CASE WHEN zip IS NULL THEN 0 ELSE 1 END
    ROWS UNBOUNDED PRECEDING
  ) as zip
FROM (
  SELECT null as visitor_id, start_ip as ip, zip
  FROM geo_ip
  UNION ALL
  SELECT id as visitor_id, ip, null as zip
  FROM visitor
)

These would get rid of the nested loop join. You can still improve upon this for larger clusters by allowing the operation to be distributable.

For example, if you can partition your IP address table by the first octet (i.e, no row in the table has a start and end of the range with different first octets), you could add a partition to your window function, which should let the sorting be processed on separate nodes in your cluster:

SELECT
  visitor.id,
  visitor.ip,
  LAST_VALUE(zip IGNORE NULLS) OVER (
    PARTITION BY (COALESCE(geo_ip.start_ip, visitor.ip) / 1000000000 )::int
    ORDER BY COALESCE(geo_ip.start_ip, visitor.ip)
    ROWS UNBOUNDED PRECEDING
  ) as zip
FROM geo_ip
FULL OUTER JOIN visitor
ON visitor.ip=geo_ip.start_ip


How do I set the isolation level for SELECT statement? Using PostgreSQL 9.5 I would like to make sure that when I run a SELECT statement I get consistent results, i.e. if the table is modified while the SELECT is running, I want to get the state that was valid when the SELECT started.

Basically, it's like: Take a snapshot of a table, and don't allow updates to the table while the snapshot is being taken, and then deliver the snapshot, while updates are allowed again.

If I understood isolation levels correctly, REPEATABLE READ is what I'm looking for, isn't it?

Now my question is: How can I run a SELECT with REPEATABLE READ isolation level? Or am I missing something, and my approach is wrong? How would I solve this?

You don't need to do anything to get statement level consistency

A query always sees a consistent state of the database regardless of the isolation level you use.

Quote from the manual:

  This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.

See the chapter about Multi Version Concurrency Control in the manual


Cluster Size Configurations for SQL Server at RAID Level I was reading the below two articles (one of which I posted) and one recommends to use a RAID cluster stripe size of 256k while the other recommends to use 64k. There is a big difference between the two and was wondering if anyone could shed some light on this.

RAID 10 Cluster Size for SQL Dynamics AX Database

SAN block size for SQL Server on 4KB NTFS

Think of the right answer in terms of the COALESCE command, which returns the first non-null value from a list. The prioritized list of recommendations are:

Your tested and proven settings. Some folks have enough time to do repeated benchmarks of their own applications on their own hardware. That's fairly unusual, though.
Microsoft's Fast Track Reference Architectures. These are very specific documented setups done in partnership with hardware vendors. They include everything from firmware versions to SQL config settings. Unfortunately, they're not available for all combinations of hardware. (Don't worry too much about the SQL Server version - SQL IO hasn't changed much since SQL 2000, with the exception of SQL 2014's new in-memory OLTP.)
Your storage vendor's SQL-specific guidelines. Major storage vendors like Dell, EMC, and HP provide detailed documentation for SQL Server as well as other database platforms. Make sure to get the docs for your exact make/model of storage - different models can have totally different recommendations.
General advice from some bozo on the web. Myself included - my work was quoted in one of the other answers. General advice just doesn't hold true for all combinations of storage hardware. This is a last resort.


Oracle returns an error ORA-01031: insufficient privileges Oracle returns an error "ORA-01031: insufficient privileges"
When I run sqlplus / as sysdba but sqlplus sys/oracle123 as sysdba doesn't, why?

Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Linux version: SUSE Linux Enterprise Server 11 (x86_64)

I know that Oracle uses OS authentication when running sqlplus / as sysdba, I also checked sqlnet.ora carefully and it contains SQLNET.AUTHENTICATION_SERVICES = (NTS).

I also changed the oracle user's group, oracle user absolutely belongs to dba group because when I run command id the following information returns:

oracle@linux-mage:~> id
uid=103(oracle) gid=106(dba) groups=105(oinstall),106(dba)

Another information is I can logged on through sqlplus sys/oracle123 as sysdba, but I can not shutdown the instance:

oracle@linux-mage:~> sqlplus sys/oracle123 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 5 16:29:57 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected.
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-27140: attach to post/wait facility failed
SQL> shutdown abort
ORA-01031: insufficient privileges
SQL>

i ran strace -o trace.out sqlplus "/ as sysdba" and get the following output message.
the strace message is too big. so i paste it into pastebin. please visit this link

http://pastebin.com/YpjVeG8u

ipcs -s and ipcs -ls information:
    oracle@linux-mage:~> ipcs -s

------ Semaphore Arrays --------
key        semid      owner      perms      nsems

oracle@linux-mage:~> ipcs -ls

------ Semaphore Limits --------
max number of arrays = 200
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767

Something must have gone wrong at the O/S level - check this page for some steps to track down the problem


Is ORDER BY on two indexed columns will be slow? I have multiple-field index on columns a,b,c,d and another unique index on column id. Is my query like below after adding id to ORDER BY slows large or i don't need to worry ? Query plan shows that sort get only 1%.

SELECT *
FROM
  (SELECT *,
          row_number() over (
                             ORDER BY a,b,c,d,id ) AS ROW
   FROM table1
   WHERE /* ... */) a
WHERE ROW > 0
  AND ROW <= 20000;

If ID is the clustered index, on SQL Server this index will have ID added as the last index key.

For a non-unique non-clustered index on a clustered index table the clustered index keys that aren't explicitly listed as non-clustered index keys will be added as additional non-clustered index keys. And the non-clustered index will actually be stored as a unique index.  EG your index on (a,b,c,d) is implemented as a unique index on (a,b,c,d,id).

If the non-clustered index is unique, the clustered index keys will be present on the leaf level of the index, but will not be index keys.


How to import data from a pair of .mdf/.ldf files into a MySQL database I have two Microsoft SQL Server database files named "xxx.mdf" & "xxx.ldf" and I want to import that database into MySQL so I can run queries against it using phpMyAdmin. How would I go about doing this?

The first thing to note is that an .sql file is not a MySQL database. It is a script file containing SQL statements. You would run such a script against the database platform for which it was written (using the SQL dialect for a specific SQL database engine) to create the actual database.

Assuming that you are planning to do this all on a desktop machine with no software already installed, here is an overview of the steps to accomplish what you stated in your question. To describe every step in detail would make for a very long answer, so take this as a "roadmap" and start working through the steps. If you run into any particular difficulties that create a new question for that specific issue.

(Also, see the "Notes" at the bottom for a couple of possible alternatives.)

Install Microsoft SQL Server

If you are reasonably certain that the database in question is no larger that 10 GB and uses no Enterprise-level features (thanks, Jon!) then download SQL Server 2008 Express from here (thanks, Ashian!) and install it. Otherwise, download and install the (180-day trial) SQL Server Enterprise Evaluation Edition.

Attach the SQL Server database

Once SQL Server is installed, you need to "Attach" the existing database (.mdf/.ldf files) to the instance of SQL Server that you just created.

Install MySQL Server

To create and manipulate a MySQL database you need to have the MySQL Server software installed.

Install Internet Information Services (IIS)

phpMyAdmin is a web-based administration tool for MySQL, so it needs a web server environment in which to operate.

Install PHP

phpMyAdmin runs under PHP, so your IIS install must have that available, too.

Install phpMyAdmin

You need to install it if you're going to use it.

Install MySQL Workbench

MySQL Workbench will do the actual conversion from Microsoft SQL Server to MySQL.

Notes:

Of course, once you have SQL Server installed and the database attached, you could always "make multiple quer[ies]" against the database in SQL Server itself.
If the database really needs to be in MySQL (e.g., if you already have MySQL queries written or provided to you) but you only need to query the database locally, then a tool like HeidiSQL might allow you to avoid installing IIS, PHP, and phpMyAdmin. In fact, HeidiSQL might be able to do the conversion for you (e.g., by doing an export from SQL Server and an import into MySQL), in which case you could use it instead of MySQL Workbench.


Select TOP @N where @N is a variable from another Query I am building a report on server hardware projections, and I'm getting stuck here.

I have a query that correctly returns the number of HDD's installed on the server, and am storing that number into a variable called @OtherHDDs, and it is an INT.

I need to now do this...

Select top @OtherHDDs....
from ...

How do I do this correctly?

You need to enclose the variable in parenthesis:

select top (@OtherHDDs) -- your column list here
from YourTable

MSDN Reference on SELECT


innodb_file_format Barracuda I have a couple questions for those more familiar.  Most of my instances have been running Antelope despite having support for Barracuda.

I was looking to play around with some compresses innodb tables.  My understanding is this is only available under the Barracuda format.

I see innodb_file_format is dynamic so I can just switch over with out a bounce.  Are there any implications of doing this I should be aware of.  All I can tell is that means new tables or subsequently altered will be created with that format.  Is this all correct?
I was hoping to have to not go through and convert all my tables.  Is is kosher to have antelope and barracude tables coexisting in the same tablespace? Even if it works are there any gotcha's to look out for?

From what I've read and gathered from my tests the answers are: Yes. Yes. I'm not sure.

Update

I've been running w/ some Dynamic and some Compressed tables in various instances since this post with out issue.  Further I neglected to read http://dev.mysql.com/doc/refman/5.5/en/innodb-file-format-identifying.html  at the time.

  After you enable a given innodb_file_format, this change applies only
  to newly created tables rather than existing ones. If you do create a
  new table, the tablespace containing the table is tagged with the
  “earliest” or “simplest” file format that is required for the table's
  features. For example, if you enable file format Barracuda, and create
  a new table that is not compressed and does not use
  ROW_FORMAT=DYNAMIC, the new tablespace that contains the table is
  tagged as using file format Antelope.

So tables will be created as Antelope even if you allow Barracuda.  The mixing is unavoidable unless you specify every table as row_format dynamic or a compressed table.

There is no indication you should do a complete dump and reload when introducing your first Barracuda table (such as is recommended when upgrading major versions of mysql)

So I'm answering this question almost 4 years late:

InnoDB file formats were conceived at a time when InnoDB was independent of the MySQL Server (for example: MySQL 5.1 could run two different versions of InnoDB).
The reason why you would not want to run Barracuda (in 2012) is that it could reduce flexibility in downgrading MySQL (i.e. after a failed upgrade, you want to move back to a version that can not read a newer format).  i.e. there should be no technical reasons why Antelope is better.
In MySQL 5.7 the innodb_file_format option was deprecated.  Since MySQL and InnoDB are no longer independent, and thus InnoDB can use the MySQL rules of upgrades and what backwards compatibility is required.  No confusing setting required!
In MySQL 5.7, the default switched to Barracuda/DYNAMIC.  Since all currently supported releases of MySQL can read this format, it is safe to move away from Antelope and still offer downgrade.
On a MySQL 5.7 server, Antelope tables will be upgraded to Barracuda/DYNAMIC on the next table rebuild (OPTIMIZE TABLE etc).  That is unless they were specifically created with ROW_FORMAT=oldrowformat.
In MySQL 8.0, the option innodb_file_format is removed.

MySQL 5.7 also introduces the option innodb_default_row_format, which defaults to DYNAMIC.  This addresses the point in your update.


SQL Server: Grant access to all views in schema, but not tables I have tables and views in a single database under a specific schema.  For simplicity, here is an example:

[DBXYZ].[SCHEMAXYZ].[TABLEWITHRESTRICTEDDATA]
[DBXYZ].[SCHEMAXYZ].[SAFEVIEW1]
[DBXYZ].[SCHEMAXYZ].[SAFEVIEW2]

I would like to grant SELECT access to a certain set of users to the views in schema "SCHEMAXYZ" but not the tables.  I prefer not to grant access to specific views because it means more administrative maintenance in the future when new views are added.

I realize that one option is simply to use a different schema name for the views (e.g. "SCHEMAXYZ_VIEWS") but that is not my preference.

Is this possible?

You'll need to move the views to a different schema to utilize this security tom foolery.

Basically you can GRANT SELECT permissions on a Schema, but you are unable to limit the SELECT privileges to views only.

The workaround, as you identified, is to create a schema that only owns the views in question.  This schema is also owned by the same principal that owns the base schema where the tables reside so your permissions are a little easier to manage.

Below is an example script breaking it down:

-- Create Test Table
CREATE TABLE dbo.Test
(
      ID INT IDENTITY(1,1)
    , VALUE NVARCHAR(50)
)
GO

-- Test Data
INSERT INTO dbo.Test (VALUE)
VALUES
    ('blah'), ('blah'), ('blah'), ('yackity'), ('schmackity')
GO

-- Create Special Schema that will "Own" all the Views
CREATE SCHEMA viewSchema AUTHORIZATION dbo
GO

-- Create a new DB Role
CREATE ROLE db_viewreader
GO

-- Grant SELECT to all objects in the viewSchema schema to the new DB role
GRANT SELECT ON SCHEMA::viewSchema TO db_viewreader
GO

-- Create a View on top of the Test Table AFTER the role was
--  defined/created to show this applies to any new views made under this schema
CREATE VIEW viewSchema.testView
AS
SELECT    ID
        , VALUE
  FROM dbo.Test
GO

-- Create a new Test User
CREATE USER testuser WITHOUT LOGIN
GO
ALTER ROLE db_viewreader ADD MEMBER testuser
GO

-- Execute the following block of code as the testuser account
EXECUTE AS USER = 'testuser'

    SELECT *
    FROM dbo.Test

    SELECT *
    FROM viewSchema.testView

-- Revert security context back to sa
REVERT;

-- Cleanup
ALTER ROLE [db_viewreader] DROP MEMBER [testuser]
DROP ROLE db_viewreader
DROP VIEW viewSchema.testView
DROP SCHEMA viewSchema
DROP TABLE dbo.Test
DROP USER [testuser]
GO

Your output should look as follows, confirming you are unable to select from the base table while having no issues selecting from the view that references it:

(5 row(s) affected)
Msg 229, Level 14, State 5, Line 58
The SELECT permission was denied on the object 'Test', database 'TestDB', schema 'dbo'.

(5 row(s) affected)

The beauty here is any user that is part of the db_viewreader role will have SELECT privileges to any new view you place into the viewSchema schema automagically which is what I assume you're looking for.


Running each SQL Server service under different Windows accounts Besides the Database Engine, SQL Server has some additional Services, namely:

SQL Server Integration Services
SQL Server Analysis Services
SQL Server Reporting Services
SQL Server Agent

Should each one of them have separate Windows accounts?

If so, what are the benefits of using this more finegrained account setup? Is it practical?

If not, what would be a good, recommended account setup for the SQL Server and its additional services?

Typically they aren't run under a different account, but all under the same Windows account.  This makes the management much simpler as there is only one account's password to change.  The downside to this is that if the password needs to be changed for the account all the servers need to be restarted at once.

As for permissions, just set the SQL account as a normal domain user, and let the SQL installer modify the rights on the machine that are needed.  It will give the account the rights that it needs to run SQL.

The benefit to having one account per service is that you get a more fine grained control of what objects on the domain each account gets rights to.  While more secure in larger environments it often isn't practical to control things at this level as you'll quickly end up with hundreds or thousands of accounts just for running SQL Services.


How to do a ranked search based on number of multiple columns matched Context
I am trying to create a ranked-customer-search that will order results based on "most likely correct". We have several factors we search by, but to keep it simple I will stick with just name, phone number, & email. The goal is that if the customer has an existing account, we use that instead of creating a new account.
It is also worth noting that for this system, a customer account is US state-specific. So it is technically possible for a single person to have 49 existing accounts and still need to have a "new account" created, so there are often many duplicate accounts we can copy information from.
My Attempt
The query below uses binary values to determine the rank of a result. So a match based on a phone number (0100) scores higher than a match based on name (0001) or email (0010). This works pretty well but isn't quite ideal. For example, an account that matches both email & name will rank lower than an account that matches only a phone number. Unfortunately, this is pretty much my limit when it comes to creating queries. (see SQL Fiddle)
(The UNIONs below are required because they allow better indexes to be used)
SELECT
    results.id,
    SUM(results.score) AS total_score
FROM (
    (
        SELECT
            c1.id,
            4 AS score
        FROM customers c1
        WHERE
            c1.phone = :phone
    ) UNION ALL (
        SELECT
            c2.id,
            2 AS score
        FROM customers c2
        WHERE
            c2.email = :email
    ) UNION ALL (
        SELECT
            c3.id,
            1 AS score
        FROM customers c3
        WHERE
            c3.first_name = :first_name
            AND c3.last_name = :last_name
    )
) results
GROUP BY
    results.id
ORDER BY
    total_score DESC
LIMIT 10;

My Question
I am not sure how to change it so that matching multiple less-important factors ranks higher than a single important factor?
Also, since this is my first time creating a search query like this, there is likely a better and/or more standard way of doing this; so any resources you can share related to this would also be greatly appreciated!

Example of the current result set:

first_name
last_name
phone
email

john
doe
5556667777
johnd@example.com

john
doe
5556667777

5556667777

john
doe

johnd@example.com

Example of the desired result set: (last 2 rows are switched because the last 1 had more matching data)

first_name
last_name
phone
email

john
doe
5556667777
johnd@example.com

john
doe
5556667777

john
doe

johnd@example.com

5556667777

In general:
SELECT *, (  (phone = :phone) * 4
           + (email = :email) * 2
           + (first_name = :first_name AND last_name = :last_name)) AS score
FROM customers
-- HAVING score
ORDER BY score DESC limit 10;

fiddle


T-SQL SELECT use multiple indexes for no reason I have a question about indexer and execution plans in T-SQL.

My database is SQL Server 2008.

I have a simple three-table db schema:

The InverterData table is very large and partitioned (28,880,436 rows).

The Date column is calculated like this:

[Date] AS (ISNULL(CONVERT([date], [TimeStamp]), CONVERT([DATE], '19000101', (112)))) PERSISTED NOT NULL,

There is also a index for this column:

CREATE NONCLUSTERED INDEX [NonClusteredIndex]
ON [InverterData] ([Date] DESC, [InverterID] ASC)

Select query #1:

I now want to make a simple select that include all three tables and the 'Date' column in a where clause:

SELECT
    [TimeStamp], [ACPower], [DCPower]
FROM
    [InverterData]
JOIN
    [Inverter] ON [InverterData].[InverterID] = [Inverter].[ID]
JOIN
    [DataLogger] ON [Inverter].[DataLoggerID] = [DataLogger].[ID]
WHERE
    [InverterData].[Date] = '05.01.2016'
    AND [DataLogger].[ProjectID] = 20686

It took round about 19 seconds on me current database (result ~80 rows).

This is the execution plan:

Select query #2:

In the first select I detected that there is a long duration index seek for the 'Date' column. So I run a second select that only include the primary key column 'TimeStamp'.

This is the second select:

SELECT
    [TimeStamp], [ACPower], [DCPower]
FROM
    [InverterData]
JOIN
    [Inverter] ON [InverterData].[InverterID] = [Inverter].[ID]
JOIN
    [DataLogger] ON [Inverter].[DataLoggerID] = [DataLogger].[ID]
WHERE
    [TimeStamp] >= '05.01.2016' AND [TimeStamp] < '06.01.2016'
    AND [DataLogger].[ProjectID] = 20686

It took only about 2 seconds on me current database.

This is the execution plan:

Question:

Why are there two index seeks? I included all used columns from select 1 in one index. Why did it took so much longer?

Update 1:

If someone needed the complete schema I will add it to a SQL fiddle and post it.

Update 2:

Tooltip of index seek

In your first query the index is used to find rows matching [InverterData].[Date] = '05.01.2016' then it needs to lookup the rest of the row data to satisfy being able to return ACPower and DCPower - if you remove these columns from the output you'll see the extra lookup go away.

You could include the extra columns in the index with:

CREATE NONCLUSTERED INDEX [NonClusteredIndex]
ON [InverterData] ([Date] DESC, [InverterID] ASC) INCLUDE ([ACPower], [DCPower])

This removes the extra lookup via the clustered index at the expense of making the non-clustered index consume more space on disk (and in memory). This query speed and used space trade-off is something you'll have to decide upon by running benchmarks on the bits of the application that use that table.

Note that you could also do:

CREATE NONCLUSTERED INDEX [NonClusteredIndex]
ON [InverterData] ([Date] DESC, [InverterID] ASC, [ACPower], [DCPower])

which uses the extra columns as part of the key rather than just INCLUDING them. The former example is likely to be more efficient as the values are unlikely to be filtered/sorted upon so it saves page splits caused by the engine trying to keep the (effectively random) values stored in order.


update statements shows update count but not updating the table I am facing a strange issue which I have never faced before. I have a update query like below

Update A
Set A.Col10 = E.Col10, A.Col11 = E.Col11
FROM TableA A
INNER JOIN TableB B on A.Col1 = B.Col1
INNER JOIN TableC C on C.Col1 = B.Col2
INNER JOIN TableD D on D.Col1 = C.Col2
INNER JOIN TableE E on E.Col1 = D.Col2
WHERE D.Col3 BETWEEN @StartDate AND @ENDDate
AND E.Col10 <> A.Col10

When i am running above statement it is giving message that 810 rows are updated. but when I am selecting based on the same criteria I am still getting those 810 rows.

SELECT A.Col10 , E.Col10
FROM TableA A
INNER JOIN TableB B on A.Col1 = B.Col1
INNER JOIN TableC C on C.Col1 = B.Col2
INNER JOIN TableD D on D.Col1 = C.Col2
INNER JOIN TableE E on E.Col1 = D.Col2
WHERE D.Col3 BETWEEN @StartDate AND @ENDDate
AND E.Col10 <> A.Col10

no matter how many times I update the table it is still showing the same count. now, my question is where I am going wrong and what are those records which got updated when actually no records are updated.

If the result of those joins is not unique, sql server will update A.Col10 with every E.Col10 value that matches your query, so at the end there might be the same amount of rows in the second query as in the first statement
For example - there are 81 A values and 11 E values, one A value match one E value, so sql server will update each A value 10 times = 810. The final result is the same(one A value match one E value), so when you select those values you still get 810 results.


Passwordless mysqldump via shell script in /etc/cron.daily I'm aware that there are dozens of questions similar to this, but it seems none has a definitive answer to my problem, so that's why I'm posting this... I hope in the right place.

The problem:

I have a script placed in /etc/cron.daily that performs a daily database backup among the other things. It works fine as long as there is a password hardcoded into the script for the mysqldump command.

#!/bin/sh
$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql

However, not wanting to have the password in the script, I've set up ~/.my.cnf file (chmod 600) with my user's password stored there so the mysqldump command in the script would be passwordless.

~/.my.cnf
[mysqldump]
password="pass"

#!/bin/sh
$ mysqldump -u [uname] db_name > db_backup.sql

When I run this new script manually from the command line as root it works like a charm.

sudo sh /etc/cron.daily/daily-backup-script

But when cron wants to run it it's unable to dump the database giving the following error:

mysqldump: Got error: 1045: Access denied for user 'user'@'localhost' (using password: NO) when trying to connect.

So, I assume cron doesn't have appropriate privilege to perform the passwordless mysqldymp command in the script, with password placed in ~/.my.cnf, however the script and the passwordless mysqldump command IN IT are working flawlessly from the command line with sudo.

Effort so far:

I've tried sudo in front of the mysqldump command in the script.
I've tried sudo -u user in front of the mysqldump command in the script.
I've chown-ed the ~/.my.cnf file as root:root.

Solved with the help of this answer.
cron doesn't know the path to ~/.my.cnf, so as per the MYSQL documentation you need to specify the path to the .my.cnf file.
The proper passwordless mysqldump command in the shell script triggered via cron should be:
mysqldump --defaults-extra-file=/path/to/.my.cnf -u [uname] db_name > db_backup.sql

Works like a charm.


Trying to use SSRS rsconfig.exe in silent mode to create the ReportServer and ReportServerTemp databases on a local instance I have installed a brand new SQL 2017 Enterprise instance on a Windows 2019 Datacentre VM.

I have then installed SQL Server Reporting Services via the command line (/quiet, /norestart, etc) and all has installed fine.

I am able to use rsconfigtool.exe to manually create the ReportServer and ReportServerTemp databases via the GUI, but I would like to be able to complete this task via automation (if possibly silently and via the command line).

I would like to have the ReportServer and ReportServerTemp databases installed on the instance I have created and not a new SSRS instance.

rsconfigtool.exe does not appear to except any parameters in order to make this change.

Any advice/help would be gratefully received.

Rather than using the rsconfigtool.exe Gui application you can use rsconfig.exe from the command line. It is located in the same Shared Tools directory. rsconfig.exe /? allows you to see the available command line arguments.


Undo SQL UPDATE Azure DB I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table.

I've read around and found ApexSQL Log but when I connect I try to connect I see "SQL server Azure is not supported"

I understand that this tool examines logs to reproduce the data, is there a manual technique to do is, or an alternative compatible tool?

I also found this tutorial but after running it on Azure I see 'Invalid object name 'fn_dblog'

Really grateful for you support.

Thanks.

I have accidentally updated over 3,000 records in one of my tables hosted on Azure.

You are out of luck (since you do not have any backups).

Also, it is not possible to use the undocumented command fn_dblog in Azure. It is only supported in SQL Server.

Also Transaction logs are no longer managed by the DBA in SQL Azure; this is automatically managed by SQL Azure's infrastructure.

check out : Supported Transact-SQL Statements (Azure SQL Database)


PL/SQL function to receive a number and return its binary format I'm trying to write a function to receive a number and return its binary format. This is what I've developed so far and it's ok for inputs : 4,8,16 but it does not return correct binary format for other numbers.
I could not find the problem and I was wondering if you could find the problem?

create or replace function Show_Binary(i_Number in Number) Return Varchar2
  AS

     V_Binary varchar2(50) := '';
     i_Number2 Number := i_Number;
       Begin
         While i_Number2>=2 LOOP

          V_Binary := V_Binary || Remainder(i_Number2, 2);
          i_Number2 := i_Number2 / 2;

       End LOOP;
          V_Binary := V_Binary || TO_CHAR(i_Number2);
          select reverse (V_Binary) into V_Binary from dual;
      return (V_Binary);
   End;

This is not an Oracle or PL/SQL issue, but a matter of implementing the proper algorithm.

Here is an example:

https://www.orafaq.com/wiki/Binary

CREATE OR REPLACE FUNCTION dec2bin (N in number) RETURN varchar2 IS
  binval varchar2(64);
  N2     number := N;
BEGIN
  while ( N2 > 0 ) loop
     binval := mod(N2, 2) || binval;
     N2 := trunc( N2 / 2 );
  end loop;
  return binval;
END dec2bin;
/

SQL> SELECT dec2bin(22) FROM dual;
DEC2BIN(22)
----------------
10110

The overhead of LISTAGG + hierarchical query + other SQL functions is bigger than the overhead of a simple PL/SQL function.

SQL> with g as (select * from dual connect by level <= 1000) select count(distinct dec2bin(rownum)) as bincd from g,g;

     BINCD
----------
   1000000

Elapsed: 00:00:13.75

with g as (select * from dual connect by level <= 1000),
g2 as (select rownum as r from g, g)
select count(distinct bin) as bincd from (
select (SELECT LISTAGG(SIGN(BITAND(r, POWER(2,LEVEL-1))),'')
       WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=r
) as bin
from g2
);

     BINCD
----------
   1000000

Elapsed: 00:00:35.53

And this is without the UDF Pragma. On 12c and above the usage of the function gets slightly faster with the addition of PRAGMA_UDF.

CREATE OR REPLACE FUNCTION dec2bin (N in number) RETURN varchar2 IS
  PRAGMA UDF; -- <==================================================== MAGIC
  binval varchar2(64);
  N2     number := N;
BEGIN
  while ( N2 > 0 ) loop
     binval := mod(N2, 2) || binval;
     N2 := trunc( N2 / 2 );
  end loop;
  return binval;
END dec2bin;
/

SQL> with g as (select * from dual connect by level <= 1000) select count(distinct dec2bin(rownum)) as bincd from g,g;

     BINCD
----------
   1000000

Elapsed: 00:00:12.01


PowerShell to get all servers with SQL Server or Oracle installed Overview:

Trying to get all servers with SQL Server or Oracle installed.

Troubleshooting:

get-service -computername "" works fine when run by itself.

But when used in script block of Foreach statement it results in this error:

  Get-Service : Cannot open Service Control Manager on computer
  '@{Name=}'. This operation might require other privileges.

Code Used:

CLS
Import-Module ActiveDirectory

$servers = Get-ADComputer -Filter {OperatingSystem -like "*Server*"} -Property Name | Select Name

Foreach ($server in $servers)
{
    get-service -computername $server |where{$_.name -like "OracleService*" -or $_.name -like "*SQL*"}| select @{N='Sever';E={$_.name}}, Name, DisplayName | Format-Table -A
}

What to do?

This is an easy way to find the SQL Servers with the dbatools module.

Install-Module dbatools
Find-DbaInstance -DiscoveryType All

Reference:
https://docs.dbatools.io/#Find-DbaInstance


Add constraint to ensure flag column is <= sum of column in other table I have a column RSEFlag that contains a flag value that is the sum of one or more of 1, 2, 4. I have separate table that contains the description of each of those flag values:

RSECode RSEDescription
------- --------------
   4    Environment
   1    Reliability
   2    Safety

I would like to constrain my RSEFlag column so that it is less than or equal to the sum of values from the RSE table.

Is this a worthwhile exercise and what is the best approach?

You can use a CHECK constraint defined against a UDF to enforce this, but this comes with many caveats.
You can also define INSERT and UPDATE triggers to do this same work.
In Oracle, you could create a materialized view that joined the two tables together and placed your RSEFlag column next to the appropriate sum.  Then you would apply a CHECK constraint to enforce that RSEFlag <= RSECode_SUM.  This Ask Tom discussion has an example of how to do this. (Search for alter table dept_tot_mvw add constraint and note that dept_tot_mvw is a materialized view.)

Unfortunately, you cannot use this approach since constraints are not allowed on indexed views in SQL Server.


reindex database in sql server 2008 The best way to reindex 500GB database in SQL Server 2008

Is it by using Maintenance plan?
Would I face performance problems while reindexing?

Actually - reindexing WILL lead you to to performance issues.
It very depends what you want to reindex and which server edition you have.

If you have an Enterprise+ edition - you can reindex all the non-clustered indexes as ONLINE operation

DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql = @sql + N'ALTER INDEX ['+i.name+N'] ON ['+s.name+N'].['+t.name+N'] REBUILD WITH (ONLINE = ON);'+NCHAR(13)+NCHAR(10)
FROM sys.indexes i
JOIN sys.tables t
    ON i.object_id = t.object_id
JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE i.type = 2
PRINT @sql

same you can do with the clustered indexes when them does not contain BLOB columns - just replace i.type = 2 to i.type = 1

for reindexing specially clustered index of Very Big Tables with BLOB fields -
you may choose three options -

or rebuild clustered index offline
or by moving all the table's data into the new table (painful)
using 'large values out of row' option ON, make sure that all the LOB IS off-row (update table set LOB=LOB --do not use this - it is just an examlpe), rebuild the clust. index offline relatively fast and after it - reorganize it with lob compaction - which is an online operation by default


Verify day falls in month I have a table in BigQuery with Xday, Xmonth, and Xyear columns but the data was user generated and apparently done without adequate input validation so some of it is nonsense.  It's easy enough to do things like Xday between 1 and 31, but I still get an error when doing date(Xyear, Xmonth, Xday) if for example someone put in February 30th.
Is there some way I can either validate the date prior to doing the conversion to the date type or a way to just toss out the rows causing errors instead of throwing the exception?

You can add the prefix SAFE so the DATE function returns NULL instead of throwing an error.
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators


Load Balancing PG Bouncer With HA Proxy I have a server with two instances of PG Bouncer running on different ports. Both are using session mode to pool. I set up HA Proxy to load balance incoming connections between these two processes to distribute the load. I get "server closed the connection unexpectedly" intermittently when connecting through HA Proxy. If I connect directly to one of the PG Bouncer instances I do not receive these errors. Has anyone setup something similar to this?

I fail to see why you are loadbalancing between pgbouncers on the same server, I assume you have a single DB server instance, if they run on the same node you're not really balancing anything.

Than being said, this sounds like timeout issues, since pgsql connections tend to live longer than the standard haproxy timeouts I suggest your raise them: (for example in defaults section add : )

timeout queue           1m
timeout connect         1s
timeout client          3600s
timeout server          3600s
timeout check           2s

This will keep any connection that the client hasn't closed open for an hour. queries that take longer to run will be cut.

see https://github.com/gplv2/haproxy-postgresql/blob/master/haproxy-example.cfg for more inspiration from a working haproxy config.


Design Pattern to Reduce Number Of Columns I have a table with 49 columns, which is already normalized.  It seems kind of excessive, but since every field is part of a form and the main entity is the form there are no relations I can refactor to delegate columns to another table, so hence my question "Are the any patterns that target the reduction of the number of columns in a table?"

I've tried to do something like a Question - Answer schema, but since the Answer value can be of many different Data Types (Enums, Strings, Numbers), there is no generic way to parse everything.

UPDATE:

I hadn't considered but the data will be eventually serialized on JSON to be served from a WEBApi so having a lot of nested fields could mean trouble.

Look at your table from the feature usage side:

If you always access a single record by id, or work with data sequentially (example - answer for all questions) - continue to use a single table
If you plan to make any group by, filter or search operation over a range of records - main table PK + columns for these operations, second (and more) - other tables. Example - select query for specific topic.
Have TEXT/BLOB - move to second table
If number of columns unpredictable - look for JSON type of column

Real practice shows that 3-5, max 10 parameters are used for search and group by.
These columns have indexes, and if indexes are created properly (including for foreign keys) - all operations will be fast.


Temporary Table error when executing entirety of script I'm having a problem with running the following script in it's entirety - That is, if I run it line-by-line separately it appears to work, but if I run everything at once, I get the error Message "There is already an object named '#customers' in the database." Any ideas as to why this happens?

if object_id('tempdb..#customers') is not null DROP TABLE #customers
if object_id('tempdb..#temporary_storage') is not null DROP TABLE #temporary_storage

SELECT [customer_number]
  INTO #customers
  FROM [source_placeholder]

SELECT DISTINCT([customer_number]) AS 'customer_number'
  INTO #temporary_storage
  FROM #customers

    IF object_id('tempdb..#customers') is not null DROP TABLE #customers

SELECT *
 INTO #customers
 FROM #temporary_storage

if object_id('tempdb..#customers') is not null DROP TABLE #customers
GO
if object_id('tempdb..#temporary_storage') is not null DROP TABLE #temporary_storage
GO

SELECT [customer_number]
  INTO #customers
  FROM [source_placeholder]

SELECT DISTINCT([customer_number]) AS 'customer_number'
  INTO #temporary_storage
  FROM #customers

    IF object_id('tempdb..#customers') is not null DROP TABLE #customers

GO --- End Batch here

SELECT *
 INTO #customers
 FROM #temporary_storage

You can't put them in one batch, you have to put a GO between the drop statements.


Exception when calling Postgresql function returning a local numeric from C# I wrote a Postgresql function to insert a row and return the value generated from the identity column.  I receive the following exception when trying to call it from C#.
Npgsql.PostgresException: '42601: query has no destination for result data'
I have looked around for an answer, and it seems ExecuteScalar has worked for others, but in all the examples I've seen, it's usually when using RETURN QUERY, not a local variable.  What am I missing?
Here is the function:
CREATE OR REPLACE FUNCTION public.func_insert_item(_name character varying)
 RETURNS BIGINT
 LANGUAGE plpgsql
AS $function$
DECLARE
    _item_id BIGINT;
BEGIN

    INSERT INTO public.items
    (
        name
    )
    VALUES
    (
        _name
    )
    RETURNING _item_id;

    RETURN _item_id;
END;
$function$

Here is the C#:

        static NpgsqlParameter CreateParameter(string name, ParameterDirection direction, string value)
        {
            var parameter = new NpgsqlParameter(name, NpgsqlTypes.NpgsqlDbType.Varchar, value.Length);
            parameter.Direction = direction;
            parameter.Value = value;

            return parameter;
        }

        static void Main(string[] args)
        {
            using var connection = new NpgsqlConnection(connectionString.ToString());
            connection.Open();

            using var command = new NpgsqlCommand("func_insert_item", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(CreateParameter("_name", ParameterDirection.Input, name));

            object itemId = command.ExecuteScalar();
        }

It seems like you misunderstood the RETURNING clause. It should list the columns the statement should return, not the names of the variables the returned values should go into. You need an additional INTO clause for the latter.
CREATE
 OR REPLACE FUNCTION public.func_insert_item(_name character varying)
                     RETURNS bigint
                     LANGUAGE plpgsql
AS
$$
DECLARE
    _item_id bigint;
BEGIN
    INSERT INTO public.items
                (name)
                VALUES (_name)
                RETURNING <name of the id column>
                          INTO _item_id;

    RETURN _item_id;
END;
$$

Unfortunately you didn't provide the table's DDL, so I don't know what column could be the ID you want to return. Replace <name of the id column> with its name.


The key of the relation resulting from a full outer join What is the key of the relation resulting from a full outer join? If there is no key, how is it said that the relational algebra is closed under its operators and the result of each operator is also a relation?

Short Answer
Closure in relational algebra has nothing to do with the concept of a key.  A relational variable holds a relation so long as that relation has the following properties:

Tuples are not ordered - order holds no implicit meaning
Tuples are distinct - there are no duplicates
Attributes are not ordered - order holds no implicit meaning
Attributes are single valued - only one of whatever type it is

The basic operators of the relational algebra are indeed closed and thus when applied on relations always yield relations.  Keys are irrelevant as by definition each relation has no duplicate tuples.  Any operation that would result in duplicate tuples (for example the projection of a single attribute over the relation) by definition eliminate those duplicate tuples when providing the result.  As you know SQL does not properly implement this.
Long Answer
In order to really understand the short answer, and to understand the addtional nuances that "outer join" throws into the equation, we need to delve into the foundations of relational algebra in number algebra.
Number Algebra
Algebra is "the part of mathematics in which letters and other general symbols are used to represent numbers and quantities in formulae and equations" as defined by google.  One algebraic property is operation, meaning there are actions that work to change numbers.  For numbers these are addition, subtraction, multiplication, division, raising to a power, and taking of a root.   There are many other algebraic properties as well such as the associative property which states that you can group numbers in any way without changing the result of the operations.  These properties give algebra its unique power, in that with these properties we can use letters to represent numbers, manipulate them via the defined operators in equations, and know the transformation will be absolutely correct regardless of what actual number is eventually substituted for the letters.
Closure is another of the algebraic properties which states that if an operator which works on numbers always produces numbers then numbers are closed under that operation.  Real numbers we know are closed under addition for example.  Add any two real numbers and the result will always be a real number.  Real numbers are not closed under the square root operation as the square root of -1 is not a real number.  All of this comes straight out of the property glossary of the Math Forum @ Drexel.
Relational Algebra
Chris Date says that Ted Codd was a genius.  Codd's unique genius was the realization that mathematical relations, combined with set theory and logic, would be very practical when applied to data management.  Fabian Pascal's Practical Database Foundation Series provides an excellent explanation of why this is so, and I'll briefly review a few of his insights.
One of the primary database problem of the 1960's was one where a program had to be written for each "question" you would want to ask of the DBMS managing the data.  Codd showed that by taking the concept of a relation and adapting it for databases a logical model of data input and output could be developed completely distinct from how data is physically stored.  Part of that logical model was the set of operators that would apply to relations - the relational algebra.  Just like number algebra allows us to exploit the algebraic properties to manipulate massively complex equations where letters stand for any potential number and guarantee correct results, the relational algebra can do the same for relations.
Why is this important?  Because with algebra, operations and their results do not depend of what the relations and their attributes actually mean.  Then, by adapting logic, which like algebra proscribes valid forms which can be used to evaluate an argument to be true or false regardless of what the propositions mean, a generalized query language can be implemented that enables a user of the DBMS to ask a true question of the data - one of arbitrary complexity - and the DBMS can be programmed using the rules of relational algebra to give a provably correct answer!
Now just like with number algebra, closure is the essential property that gives the algebra its power.  Relations are closed under the basic operators and as such you can arbitrarily nest these operations at will, in any order, to declare practically any "question" you could ever think of.  Once the DBMS is created that implements the relational algebra, unique programs no longer have to be written to find the answer to each question.  The DBMS user simply declares the question using the relational algebra.
Outer Join
Thus far I have been saying basic operators.  That is because the theory does not account for missing data.  Each relation by definition contains tuples with a value for every attribute.  But to adapt the mathematics to the practical realities of missing data some solution has to be developed for when an attribute value is unknown.  SQL implemented the NULL and 3VL.  Codd later proposed marks and 4VL.  The join operator had to be extended to preserve un-matched tuples, and SQL implemented the extension by producing NULLs instead of values for the attributes of un-matched tuples.  Date shows that by doing this outer join is:

a kind of shotgun marriage: It forces tables into a kind of union — yes, I do mean union, not join — even when the tables in question fail to conform to the usual requirements for union (see Chapter 6). It does this, in effect, by padding one or both of the tables with nulls before doing the union, thereby making them conform to those usual requirements after all. But there’s no reason why that padding shouldn’t be done with proper values instead of nulls. (Date, C. J. (2011-12-16). SQL and Relational Theory: How to Write Accurate SQL Code (Kindle Locations 2601-2602). O'Reilly Media. Kindle Edition.)

Conclusion
So are relations closed under the operation of outer join (full, left, or right)?  I guess it depends on how that operator is implemented.  I would say yes if you do consider a relation to still be a relation even with markers for the missing values of the attributes of un-matched tuples.  Or yes if you don't consider a relation to still be a relation even with markers for missing values but can substitute default values for the missing attributes values.  But if you don't consider a relation to still be a relation even with markers for missing values, and your DBMS implements SQL NULLs, and you can't or don't substitute values for the SQL supplied NULLs in the outer join, then no, relations are not closed under that outer join.  Two relations went in, but a relation did not come out.