Query shows SELECT 0 I'm looking through the queries being executed by a third party application. The application was flagged as being slow by some of our end-users.

It seems to INSERT records into a HEAP, then DELETE them again after the session ends (without any selects happening on them?)
But at the end of each INSERT statement there is a SELECT 0.

Am I seeing some kind of artifact from the query plan cache? Is it possible there is some parameter that's missing? All other parameters are clearly indicated as for example @P1.

There are 350 INSERTactions per minute and a DELETE of everything in the table ever hour. Which seems to be an OK case to use a HEAP, however if this SELECT 0 is actually selecting something from this heap, I'm wondering if I should ad a clustered index.

Update: Added a screenshot to show the select:

An execution plan is only going to have the code that was last compiled and used, to my knowledge. In troubleshooting applications myself I have come across where a statement I was looking at through the execution plan was actually written with sp_executesql and so was dynamic T-SQL that contained a bit more logic than the end result.

You don't specify the version you are working with but I would suggest using a server-side trace or Extended Event session to map out the sequence of commands being passed so you fully understand what is being done in your situation.

My first thought is the application (or the developer) wrote the insert statement and in some form or fashion is doing a check on something (i.e. maybe errors). In that check if it is good they are simply passing what ends up looking like SELECT 0, where if an issue occurred it might return SELECT 3910 that would mean something to that application. You are only going to see the full logic (if any) behind all of it by doing a trace on the commands.


execution sequence of parallel execution plan I am reading an article about Understanding Parallel Execution

There is a paragraph which is as follows:

But for my understanding of how to read an execution plan, this parallel execution plan starts from operation 7 not operation 14.

Why this article emphasize that the execution plan starts at operation 14?? It upside down my knowledge of execution plan.

According to the quoted text Oracle will only have two parallel active per data flow operation. Take a look at the TQ column of the EXPLAIN PLAN. There is only one data flow operation for this query (Q1). There are five different parallel slave sets for Q1:

Q1,00
Q1,01
Q1,02
Q1,03
Q1,04

Q1,00 and Q1,01 will be processed first. The operation on line 7 (FTS on T3) has a slave set of Q1,02 so it can only start after Q1,00 or Q1,01 finish and free up a slave set.


Select into query, insert fails, but the table is created I am using SQL Server 2016 and I tried to the following query.

SELECT CONVERT(BIGINT, 'A') col1 INTO #tmp

This query is obviously in error. Because it does not convert. However, the temporary table (#tmp) is created even if the query fails.

Why? I think this is by design, but I want to know why.

PDW (parallel datawarehouse) does not create temporary table.

In autocommit mode, SELECT...INTO executes 2 separate transactions. The first creates the table and the second loads the table. The table create will be committed even if the subsequent insert portion of the statement fails.

In old versions of SQL Server, both operations were done in a single atomic transaction but this was notorious for locking system tables and causing excessive blocking.


Take a "slice" of a SQL Server database: for all tables, get only the rows that join (directly or indirectly) to a specific row For audit purposes, I'm trying to obtain a copy/export of a DB but including only the rows from each table that have relevance to a small collection of rows in some table. So every row of every table that these rows point to; every row of every table that points to these rows; and so forth. (In algebra terms this is the transitive-symmetric-reflexive closure of those rows with respect to the foreign key relation.)
I'm wondering if there exists some kind of tool to do this, or if this is scriptable somehow. I have ~400 tables with a tight network of foreign keys so I'd rather not do this by hand.

You could certainly script it with a nested stored procedure:

To be called as something like EXEC recursive_shaped_query @StartTable='SomeTable', @KeyColumn='Id', @ValuesSQL='SELECT Id FROM (VALUES (1),(6),(4576),(42)) AS Ids(Id)'
This then runs via ad-hoc SQL SELECT * FROM SomeTable WHERE Id IN (SELECT Id FROM (VALUES (1),(6),(4576),(42)) AS Ids(Id))
It then cursors through sys.foreign_keys to find any tables that reference this table and…
… for each such table found calls itself like EXEC recursive_shaped_query @StartTable='ReferencingTable', @KeyColumn='Id', @ValuesSQL='SELECT Id FROM SomeTable WHERE Id IN (SELECT Id FROM (VALUES (1),(6),(4576),(42)) AS Ids(Id))'

Of course there would be a number of complications to consider:

You will need to decide what to do if your data structure allows for circular links, like a table containing state machine graphs, or places where two entities might refer to each other, otherwise this will loop to infinity (well, until you hit the "procedure calls nested too deep" error).
You may need to deal with foreign keys acting on multiple fields instead of just one.
The SELECT * FROM t1 WHERE Id IN (SELECT Id FROM t2 WHERE Id IN (SELECT Id FROM Tt3 WHERE ... ))) nesting may get silly long and be queries that take an age to run due to table scans if useful indexes are not in place.

Other things to note:

You might find using joins instead of sub-selects produces more efficient queries (though I think the query planner will do a decent job of optimising the two syntax options to be equivalent).
You might prefer the procedure just emits the ad-hoc SQL needed instead of running it, so you can run it later (and rerun it again elsewhere).
If just emitting the ad-hoc SQL and not running it, a recursive CTE over sys.foreign_keys may be noticeably cleaner than a nested stored procedure - in fact doing that might be better anyway: generate the SQL in on go then run it in one go, perhaps explicitly dropping the data to temp tables then ending the current transaction to release locks before pushing the data out to the reader.
This is all of course assuming that you have foreign keys defined everywhere they should be. In too many databases out there some or all of these constraints are missing.

For our systems in [Day Job] I have a set of such queries manually created to pull out nested data for given entities as these procedures are useful for diagnostics, but I've never needed nearly enough of them to warrant trying to create a generator or other automated solution. I've thought about it as a play project, but not actually done it.


Truncate with where clause Can I use truncate command with a where clause?  I need to remove specific rows from several tables.

How can I delete specific data from the entire database?

SELECT DimEmployee.[FirstName], DimEmployee.[LastName], [SalesOrderNumber], [ShipDateKey]
    FROM DimEmployee
    JOIN [FactResellerSales]
        ON DimEmployee.[EmployeeKey] = [FactResellerSales].[ProductKey]
    WHERE DimEmployee.[FirstName] like 'kevin%' <--have to truncate this specific name from entire DB

Is there any other method to remove a specific data from entire DB?

In my database there are 172 tables. I wanted to delete a specific name and its corresponding columns from the entire database. The name is spread across entire database, hence I want to remove it in a single shot instead of going to each table and deleting it individually.

No, Truncate can't be used with a WHERE clause. Truncate simply deallocates all the pages belonging to a table (or a partition) and its indexes.

From BOL:

-- Syntax for SQL Server and Azure SQL Database

TRUNCATE TABLE
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
    [ , ...n ] ) ) ]
[ ; ]

If you're looking for a more efficient way to delete data, I'd start here.


IF and CASE in insert query INSERT INTO TARGET_TBL (KPIName ,KPILabel)
SELECT  `d`.`si_kpi_name` AS `KPIName`,
    IF(`t`.`kpi_val` = 0,
        WHEN (`v`.`TOIOS` >= `t`.`kpi1`) THEN 'Bad'
        WHEN (`v`.`TOIOS` >= `t`.`kpi2`) THEN 'Poor'
        WHEN (`v`.`TOIOS` >= `t`.`kpi3`) THEN 'Fair'
        ELSE 'GOOD',

        WHEN (`v`.`TOIOS` <= `t`.`kpi1`) THEN 'Bad'
        WHEN (`v`.`TOIOS` <= `t`.`kpi2`) THEN 'Poor'
        WHEN (`v`.`TOIOS` <= `t`.`kpi3`) THEN 'Fair'
        ELSE 'GOOD' )

        AS `KPILabel`
FROM (
    `voom_min` `v` LEFT OUTER JOIN `denver_config` `d` ON (`v`.`si_id` = `d`.`si_id`)
    LEFT OUTER JOIN gen_config g ON (d.si_name = g.si_name)
    JOIN `kpi_downstream` `t` ON ((`t`.`kpi` = 'market analysis'))
    )
WHERE (`v`.`TOIOS` IS NOT NULL)

MYSQL - Please concentrate on the part of where KPILABEL is calculated. What I am trying to do is:

If T.KPI_VALUE == O, Check set of conditions ... If not check set of conditions ...

I see in some websites the syntax of IF while using in insert query as

if(condition, if condition yes statements, ifnot statments)

I used this syntax for if syntax ...
for "if condition yes statements I used CASE statements"

Please help me to fix the syntax.

Don't mix

CASE value WHEN [compare_value] THEN result
          [WHEN [compare_value] THEN result ...]
          [ELSE result] END

and

IF(expr1,expr2,expr3)

syntaxes.

See the manual


Invalid use of a side-effecting operator 'change_tracking_min_valid_version' within a function I am trying to create a function wrapping change_tracking_min_valid_version(object_id), to simplify queries when using synonyms instead of table names. Something like this:

CREATE FUNCTION MY_CHANGE_TRACKING_MIN_VALID_VERSION(@synonym VARCHAR(100))
RETURNS BIGINT
BEGIN
    RETURN (SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(base_object_name))
    FROM sys.synonyms WHERE name = @synonym);
END

I have tried getting the base object name separately, to be able to drop the select part of the return statement, i.e.

set @retval = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(@table_name))

MS SQL Server Management Studio produces this error:

Msg 443, Level 16, State 1, Procedure MY_CHANGE_TRACKING_MIN_VALID_VERSION, Line 5
Invalid use of a side-effecting operator 'change_tracking_min_valid_version' within a function.

I do understand that I can't use operations with side-effects in functions, but since I can't seem to find any information about (or understand why there would be any) side-effects of this specific function, my question is:

What are the side-effects of CHANGE_TRACKING_MIN_VALID_VERSION?

(I am using SQL Server 2012.)

As Aaron mentions, this logic could be done in a view rather than a function. If you do need to use a function, however, an alternative might be to use sys.change_tracking_tables rather than CHANGE_TRACKING_MIN_VALID_VERSION. It has a min_valid_version column, and as far as I can tell from the documentation it provides the same information you are looking for.

CREATE FUNCTION MY_CHANGE_TRACKING_MIN_VALID_VERSION(@synonym VARCHAR(100))
RETURNS BIGINT
BEGIN
    RETURN (SELECT c.min_valid_version
    FROM sys.synonyms s
    JOIN sys.change_tracking_tables c
        ON c.object_id = OBJECT_ID(s.base_object_name)
    WHERE s.name = @synonym);
END
GO

What are the side-effects of CHANGE_TRACKING_MIN_VALID_VERSION?

As far as I can tell from the documentation, there aren't any "side-effects" that will meaningfully impact you. As Aaron mentions, you'll get the same error if you try to use NEWID in a function. In both cases, I would suspect (but am not sure) that NEWID and CHANGE_TRACKING_MIN_VALID_VERSION are accessing and/or modifying an internal data structure (e.g., a random generator in the case of NEWID) and SQL Server is biasing towards caution in rejecting their usage in a function.

-- This generates the same error
CREATE FUNCTION testNewIdInFunction()
RETURNS UNIQUEIDENTIFIER
BEGIN
    RETURN (SELECT NEWID());
END


Empty Strings, NULLs and For XML Path I have no control over the source table.
There is a mixture of NULLs and Empty strings.
What I need is to get all users on 1 line and semicolon delimit a list of all their entitlements. I can strip the leading semicolon in later code but the double semicolons and empty string semicolon are killing me.

My table:

EMPLOYEEID | USERNAME | ISPRIMARY | ENTITLEMENTS
123456     | BSmith   |   Y       | ac-les-all-full;ac-haw-all-full
123456     | BSmith   |   N       | ac-sco-all-full
987654     | ZJones   |   N       | NULL
987654     | ZJones   |   Y       | ac-trn-std-full
987654     | ZJones   |   N       |
456789     | RTaylor  |   Y       |

I want to return:

USERNAME | ENTITLEMENTS
BSmith   | ;ac-les-all-full;ac-haw-all-full;ac-sco-all-full
ZJones   | ;ac-trn-std-full
RTaylor  |

My Current SQL:

SELECT
    USERNAME
    ,ENTITLEMENTS = (
        SELECT DISTINCT ';' + ENTITLEMENTS
        FROM [staff_user_data] STS
        WHERE STS.[EMPLOYEEID] = STP.[EMPLOYEEID]
        FOR XML PATH(''))
FROM [staff_user_data] STP
WHERE ISPRIMARY = 'Y'

What I am getting is

USERNAME | ENTITLEMENTS
BSmith   | ;ac-les-all-full;ac-haw-all-full;ac-sco-all-full
ZJones   | ;;ac-trn-std-full
RTaylor  | ;

This appears to give you what you want.  I added logic where the ENTITLEMENTS are being generated to eliminate rows that are null or blank and wrapped that section in an isnull.

--demo setup
drop table if exists staff_user_data
go
CREATE TABLE staff_user_data (
  EMPLOYEEID INTEGER,
  USERNAME VARCHAR(7),
  ISPRIMARY VARCHAR(1),
  ENTITLEMENTS VARCHAR(31)
);

INSERT INTO staff_user_data
  (EMPLOYEEID, USERNAME, ISPRIMARY, ENTITLEMENTS)
VALUES
  ('123456', 'BSmith', 'Y', 'ac-les-all-full;ac-haw-all-full'),
  ('123456', 'BSmith', 'N', 'ac-sco-all-full'),
  ('987654', 'ZJones', 'N', NULL),
  ('987654', 'ZJones', 'Y', 'ac-trn-std-full'),
  ('987654', 'ZJones', 'N', ''),
  ('456789', 'RTaylor', 'Y', '');

--solution
SELECT
    USERNAME
    ,ENTITLEMENTS = isnull((
        SELECT DISTINCT ';' + ENTITLEMENTS
        FROM [staff_user_data] STS
        WHERE STS.[EMPLOYEEID] = STP.[EMPLOYEEID]
        and ENTITLEMENTS is not null and ENTITLEMENTS <> ''
        FOR XML PATH('')),'')
FROM [staff_user_data] STP
WHERE ISPRIMARY = 'Y'

 USERNAME | ENTITLEMENTS                                     |
|----------|--------------------------------------------------|
| BSmith   | ;ac-les-all-full;ac-haw-all-full;ac-sco-all-full |
| ZJones   | ;ac-trn-std-full                                 |
| RTaylor  |                                                  |


Searching 500 million records of binary data I will inserting into a database the signatures of 500,000,000 pictures. The signatures will be generated using libpuzzle. Each signature is 338 bytes. (so 160 GB) plus a table for searching (read more below). I would prefer to keep the main database on one VPS server with a standard HDD (No SSD because of cost issues).

The most important aspect is search time, Insertion time does not matter.

In the past I have attempted this all within MySQL (with way less records) and had one database for everything, the main searching happened with a scheme like:

--
-- Table structure for table `signatures`
--

CREATE TABLE IF NOT EXISTS `signatures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `compressed_signature` varchar(338) NOT NULL,
  `picture_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `picture_id` (`picture_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1107725 ;

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

--
-- Table structure for table `stored_pictures`
--

CREATE TABLE IF NOT EXISTS `stored_pictures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  `pid` bigint(20) unsigned NOT NULL,
  `num` int(11) NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `picture_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_url` (`url`),
  KEY `idx_picture_id` (`picture_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2773867 ;

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

--
-- Table structure for table `words`
--

CREATE TABLE IF NOT EXISTS `words` (
  `pos_and_word` char(5) NOT NULL,
  `signature_id` int(11) NOT NULL,
  KEY `idx_pos_and_word` (`pos_and_word`),
  KEY `signature_id` (`signature_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--

Where by nature of the libpizzle, you would search the words table for many signature_id and then get all the compressed_signature from the signatures table, do some math and spit back a score for each signature how similar it was for the search. Then for each similiarity that was above a threshold I would get the data I needed from stored_pictures by looking up the picture_id

It took about 5 minutes for 1 search to search 40,000,000 pictures - so I think there is room for improvement. Especially because I want this to be fast up to 500 million records.

Should I seperate all the non-essential data (only about 1% the size, which is everything that is related to the specific picture) in a seperate database? On a seperate server?

Because it's just doing a massive search for pos_and_word and spitting back all the signature_id's that could be a match, I assume that not having any type of relation with the data can help me pick a specific technology that will maximize my speed. Which technology is best for this?

You should consider using a real indexed search engine such as elasticsearch with runs on Java, uses a REST interface with json and so is exceedingly easy to program to, is free and open source, with a good community behind it.

www.elasticsearch.org

You could have it running on the same server as your application as long as you have a decent enough machine and it's built specifically for searching hundreds of millions of documents in near real time.

Pretty easy to install and setup and extremely easy to customize.  It also has the added benefit of being built to run in the cloud (though not a requirement) and can cluster out very easily when your application begins to grow in popularity.


in place updates causes forwarded records I am well aware what a forwarded record within a heap is. Since I want to keep forwarded records at 0, we decided to update only on columns that could not be extended.

Recently on my system I encountered forwarded records.
Table design is like this:

CREATE TABLE dbo.test (
 HashValue BINARY(16) NOT NULL,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
[other columns that never get updates]
) WITH(DATA_COMPRESSION=PAGE);

The insert statements ALWAYS bring HashValue AND LoadTime. I checked the query logs.
I insert a value of '9999-12-31'.

Now system performs an update on LoadTime like this:

;WITH CTE AS (
SELECT *, COALESCE(LEAD(LoadTime) OVER(PARTITION BY HashValue ORDER BY LoadTime) ,'9999-12-31') as EndTimeStamp
)
UPDATE CTE SET LoadEndTime = EndTimeStamp;

Since the LoadEndTime column is always filled, there should be no extension of that column within the row when the update is executed. It should be an in place update. Still I get forwarded records always after that process... It doesn't make sense to me.

Insert Statement is like this:

INSERT INTO dbo.test (HashValue, LoadTime,LoadEndTime)
SELECT HASHBYTES(...), GETDATE(), '1900-01-01'

So there is already a dummy value. Even with compression the fixed and variable representation of datetime '1900-01-01 00:00:00' should be 8 bytes.

This is due to compression.

Compressed table has no more FixedVar row format, so even if you use fixed-length columns they aren't stored in Fixedvar way, no more fixed space reserved for (even NULL) values.

When you use page compression, frist ROW COMPRESSION is applied.
And here what we have:Row Compression Implementation

  It stores fixed character strings by using variable-length format by
  not storing the blank characters. Note

  NULL and 0 values across all data types are optimized and take no
  bytes.

Here is my repro where I use dbo.Nums, table of 1000000 natural numbers, to fill my two tables: dbo.test_comp with page compression enabled and dbo.test_no_comp with no compression:

CREATE TABLE dbo.test_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
)
WITH(DATA_COMPRESSION=PAGE);

insert into dbo.test_comp(id,  LoadTime)
select n, getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 0   11,7117117117117    18519

UPDATE dbo.test_comp SET LoadEndTime = LoadTime;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 54949   11,1783696529459    19688

CREATE TABLE dbo.test_no_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
)

insert into dbo.test_no_comp(id,  LoadTime)
select n, getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_no_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_no_comp  0   6,22905027932961    28572

UPDATE dbo.test_no_comp SET LoadEndTime = LoadTime;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_no_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_no_comp  0   6,22905027932961    28572

UPDATE

  Even with compression the fixed and variable representation of
  datetime '1900-01-01 00:00:00' should be 8 bytes

This is completely untrue, because you use PAGE compression that creates a dictionary and substitute repeating values:

  Dictionary compression

  After prefix compression is applied to every
  column individually, the second phase of page compression looks at
  all values on the page to find duplicates in any column of any row,
  even if they have  been encoded to reflect prefix usage. The
  process of  detecting duplicate values is data type–agnostic, so
  values in completely different columns could  be the same in their
  binary representation. For example, a 1-byte character is represented
  in hex as  0x54, and it would be seen as a duplicate of the 1-byte
  integer 84, which is also represented in hex  as 0x54. The dictionary
  is stored as a set of symbols, each of which corresponds to a
  duplicated value  on the data page. After the symbols and data values
  are determined, each occurrence of one of the  duplicated values is
  replaced by the symbol. SQL Server recognizes that the value actually
  stored in  the column is a symbol and not a data value by examining
  the encoding in the CD array. Values which  have been replaced by
  symbols have a CD array value of 0xc.

But I wanted point out another thing.
Did you test if your PAGE compression make sense at all in your case?
You have a heap and it's not static, i.e. you update it.

  Compression of heap pages

  Pages in a heap are checked for possible compression only during
  rebuild and shrink operations. Also, if you drop a clustered index on
  a table so that it becomes a heap, SQL Server runs compression
  analysis on any full pages. To make sure that the RowID values stay
  the same, heaps aren’t recompressed during typical data modification
  operations. Although the Page-ModCount value is maintained, SQL Server
  never tries to recompress a page based on the PageMod-Count value

So in your case even if you can achieve compression on INSERT (only if you use bulk insert with tablock), any update will only destroy your compression.

Look at my new test.

I realized that in my first test when i did INSERT without tablock, only row compression was applied, I inserted 1000000 rows and I get 19688 pages.
Now I insert with tablock into the same table:

CREATE TABLE dbo.test_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME not NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
)
WITH(DATA_COMPRESSION=PAGE);

insert into dbo.test_comp with(tablock) (id,  LoadTime, LoadEndTime)
select n, getdate(), getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 0   0   1387

Now I have only 1387 pages.
1387 vs 19688 only because in first case page compression, even if defined, was not applied since my INSERT was without tablock

And now I update my nice compressed table:

UPDATE dbo.test_comp SET LoadEndTime = getdate();

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 990247  8,43971631205674    22442

Wow, after update I have 22422 pages vs 1387 original pages!

After this test I really NEVER will use page compression on a heap that is not static. The final table size is not much less compared with its non-compressed analog but as a bonus I have 990247 forwarded records out of 1000000


SQL Server Transaction log Percent Used in Perfmon I am doing some research into monitoring transaction log use using windows perfmon.

In the AdventureWorks database I have created the following statement

SET IDENTITY_INSERT Person.BusinessEntity ON

WHILE 1 = 1
BEGIN

    DECLARE @id INT
    SELECT @id = MAX(BusinessEntityID) FROM Person.BusinessEntity
    SET @id += 1

    INSERT INTO Person.BusinessEntity(BusinessEntityID, rowguid, ModifiedDate) VALUES (@id,NEWID(), GETDATE())

    INSERT INTO [Person].[Person]
               ([BusinessEntityID]
               ,[PersonType]
               ,[NameStyle]
               ,[Title]
               ,[FirstName]
               ,[MiddleName]
               ,[LastName]
               ,[Suffix]
               ,[EmailPromotion]
               ,[AdditionalContactInfo]
               ,[Demographics]
               ,[rowguid]
               ,[ModifiedDate])
         VALUES
               (@id,
               'EM',
               0,
               NULL,
               'Ken',
               'J',
               'Sánchez',
               NULL,
               0,
               NULL,
               NULL,
               NEWID(),
               '2

009-01-07 00:00:00.000')
END

The query will indefinitely enter rows into Person.BusinessEntity and Person.Person until I click stop

This should cause the transaction log to grow quickly which I can monitor in perfmon

I have added the perfmon counter for percent log used and run the statement and I get the graph as follows

I am wondering why the graph goes up and down again?

I assume it goes up when the log is written to and back down as the log resizes again?

If this is true then disabling autogrowth on the transaction log in the Adventure Works database and running the statement whilst monitoring again should see the graph go up and up and then reach the top and the query terminate because it is unable to resize the log?

However, I get similar behaviour again.

Can someone explain to me why am not seeing the transaction log start to fill after turning off autogrowth? Is there something else happening here?

The database is in full recovery mode

My suspicion is that your database, while showing it's in FULL recovery mode, is actually in pseudo-simple mode where the database still behaves like it is still in SIMPLE recovery mode until a full database backup is taken.

Paul Randal, CEO of SQLSkills.com, wrote a blog post about this recovery model called pseudo-simple. This is a behavior of the database that, while it may be configured to be in FULL recovery model, still behaves as if it was still in SIMPLE recovery model. You can easily test this by creating a database and setting it to FULL recovery model. While you can perform a lot of transactions in the database, the transaction log will get truncated, as it is with databases in SIMPLE recovery model.

Paul wrote a TSQL function that can determine if a database is truly in FULL recovery.  Here's the script from the above post.

USE [msdb];
GO

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'SQLskillsIsReallyInFullRecovery')
    DROP FUNCTION [SQLskillsIsReallyInFullRecovery];
GO

CREATE FUNCTION [SQLskillsIsReallyInFullRecovery] (
    @DBName sysname)
RETURNS BIT
AS
BEGIN
    DECLARE @IsReallyFull BIT;
    DECLARE @LastLogBackupLSN NUMERIC (25,0);
    DECLARE @RecoveryModel TINYINT;

    SELECT
        @LastLogBackupLSN = [last_log_backup_lsn]
    FROM
        sys.database_recovery_status
    WHERE
        [database_id] = DB_ID (@DBName);

    SELECT
        @RecoveryModel = [recovery_model]
    FROM
        sys.databases
    WHERE
        [database_id] = DB_ID (@DBName);

    IF (@RecoveryModel = 1 AND @LastLogBackupLSN IS NOT NULL)
        SELECT @IsReallyFull = 1
    ELSE
        SELECT @IsReallyFull = 0;

    RETURN (@IsReallyFull);
END;
GO

And here is an example of utilizing that function (replacing 'test' with your database name).

select MSDB.DBO.SQLskillsIsReallyInFullRecovery ('test')

There is also a Powershell script written by Edwin Sarmiento that provides the same information:

Check If a SQL Server Database Is In Pseudo-Simple Recovery Model Using Windows PowerShell

My suggestion would be to take a FULL backup of the database and retry your tests.


One user for all database applications We are in a process of upgrading our SQL Server database. We are also ditching the Oracle databases that we had and are migrating all the databases to SQL Server 2016.

The internal db structure is something like, we have two Master databases, which have all the organization and product information. The rest of the web applications access these two databases frequently (mostly read-only).

For web applications, we have one user per application, which is also added in the Master databases (to ensure access of information).

Over the years, many of the web applications are decommissioned and some new ones have been created, (some are in the process of development), so we are also deleting their users and checking others as well.

What I want to inquire is that, what are the drawbacks of having a common user for all web applications? Does it affect performance of the web applications or the DB server? Which one will give me optimum performance and will be better both for the applications and the DB?

1 User for all web apps.
1 User for each web app added to the master databases as well.

The surrogate ID on sys.database_principals is an integer. This suggests a maximum user count of a billion-ish. The Maximum Capacity Specifications for SQL Server mentions no limit on logins or users that I can see. It does say there is a maximum of 32,767 connections at any one time. From any practical point-of-view there's nothing to stop you creating one user per application.

From a performance perspective, I've never seen an issue that resolves to too many user being defined. This is different too many being active. Each active connection consumes a small but finite resource. From what I remember this resource is not shared if different connections have the same user, so nothing to gain there.

(There is such a thing as connection pooling, however. To improve performance all connection requests must be constructed identically, however, including user names. This could work per application (with application-specific users) or per instance (with a shared user). To be effective developers must be meticulous with their connections.)

User credentials and permissions have to be stored somewhere. So, yes, having a ludicrous number of users will consume measurable resource. Since SQL Server is built to handle terabytes of data I doubt this will ever be a limiting factor. Considering how often authorisation is checked I'd imagine the storage and processing code paths have had a lot of optimization attention through the years.

This is just a long way of saying many users equals more resource but you won't be able to measure the downside.

The overwhelming considerations for me are the benefits achieved in security and monitoring by having separate credentials.

A single user, used by all applications, must by definition have access to all the data. If there's a piece of data it doesn't need that data is not used by any application and shouldn't be in this database. That's a security risk. One breach by one bit of one application opens the whole data infrastructure to exploitation. In contrast having separate users which are granted only the SELECT or EXECUTE rights they explicitly require limits the damage any one application failure can expose.

Secondly - monitoring: something, somewhere will consume too much resource. Knowing it's running as user_generic is not helpful. Knowing it's running as user_nightly_product_load will tell you exactly which code to examine. The complement of this is Resource Governor which allows limiting a connection's access to CPU and memory, if that connection can be distinguished from others. The easiest way is to have separate user credentials.

Just for giggles I started a VM with SQL Server 2017 RTM, default installation. I set it to CREATE USER <user_name> WITHOUT LOGIN. The user names were created from a SEQUENCE.  It took a while, and the creation rate decreased over time, but it created over 270,000 users before I gave up. If there is a limit, it's greater than that.


alter table which is under the load (sql server) we have table that being used for sending sms and other notification for confirmation as second factor.

Some changes in business logic requires us to alter table to make some columns NOT NULL.

Our stumbling-stone is that table should be accessed during 24/7, application writes to and reads from it pretty frequently and table has tens of millions rows. And when command like

alter table NOTIFICATION_TABLE
alter column C1 int null

starting to execute it obviously tries to lock table and seemingly cannot do it because table is being used by other requests. That situation end up with that alter command hangs out and nothing happened during 5 minutes. After that timeout we stop it because do not want occasionally crush our system.

Any thoughts about how we can implement this modification without stopping  our system?

Announce that over the weekend there will be a planned maintenance event where new notifications will be delayed for X hours.
Make a copy of the table. Redirect all reads there.
Disable any writes from the application or queue them up if that's unacceptable.
Alter the table.
Apply any queued up writes.
Set everything back.


What's the best database if i want save data temporally? I want to create a datalog system so I need to store some int data temporally. What would be the best database (Relational, NO-SQL or some specific) for that purpose? And what would be the best way to do that?

I'd look at SQLite if I were you but if you are you going to perform many analyses on this data - i.e. windowing functions -or CTEs (common table expressions), in that case, I'd go with PostgreSQL.


postgres 9.1: both md5 and ident on the same connection type? In pg_hba.conf, is there a way to enable both 'ident' and 'md5' for local connections?

Concretely, I am happy to have 'ident' turned on for humans to interact with the database. However, if I set up an httpd instance (e.g.), I don't like having to add a full linux user so that it can connect to the db via ident. I'd like to just assign a password inside postgres.

My experiments in having two lines in pg_hba.conf for 'local' didn't work, but the doc is a bit ambiguous, and I thought perhaps that I'd missed something.

Only if you can differentiate by user or database. You can't have both ident and md5 enabled for the same (user,database,connection-origin) tuple.

I usually use unix sockets with ident for my admin and casual use. Many apps only work over tcp, so I use tcp with md5 for them. For apps that can use a unix socket I specify their database as md5 and just use a password when I connect to it.

It'd be really nice if Pg supported fallback auth where you could try several protocols or where the client could pick, but right now AFAIK it doesn't.


Connected to MySQL database but cannot print the data I connected successfully to my database and the I tried to print my data but I can only see the "Connected succesfully" line in the browser. Here is my code:

<?php

$db=@mysqli_connect("host","root","pass");
if (!db) { die('Cannot connect with the database.');}

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

$sql = "SELECT username FROM students";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "username: " . $row["username"].  "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();

?>

You definitely have to check your query execution response, as clearly suggested by the mysqli::query documentation, by adding the following code fragment just after the query call.

if (!$result) {
    printf("Error: [%s] %s<br/>", $conn->errno, $conn->error);
}

Hope it helps.


Equivalent of DB2 function "decimal(numeric expression, precision, scale)" in PostgreSQL Is there an equivalent of the DB2 function decimal(numeric expression, precision, scale) in PostgreSQL? Or how would I do this functionality in PostgreSQL?

The DECIMAL function returns a decimal representation of a character-string. The value of this second argument specifies the precision of the result. The scale value specifies the scale of the result (not sure what that means?).


PostGIS - indexing - speed up query I have this query (called from PHP, hence the $ variables):

SELECT a.name,
      St_AsText(a.geom),
      b.name AS name_b,
      b.id
FROM a
LEFT JOIN b ON b.id = a.b_id
WHERE UNACCENT(LOWER(a.name)) LIKE UNACCENT('$searched%')
ORDER BY a.geom <-> 'SRID=4326;POINT($lat $lon)'::geometry
LIMIT 2;

There is lower(a.name) BTree index on a.name, GIST index on a.geom and b.id is primary key

It runs about 40ms for $searched = "Maria%". Now, if I set $searched = "Mariasomething some%", time goes up to 2s.

Why? Can it be speed up or perform in a more stable manner?

Explain
We would have to see table definition, cardinalities and the EXPLAIN output to be certain, but the reason is most likely this:
Only your spatial GiST index on a.geom can be used. The btree index is not applicable. Postgres walks through the "closest" rows until it finds the first two matching your predicate.
Normally, more restrictive conditions make queries faster, since fewer rows have to be fetched (and sorted). But not in this constellation:
Fewer qualifying rows don't help a nearest neighbour search with a small LIMIT - au contrair. There are two options:

The "nearest neighbour" search based on the GiST index - which is fast as long as some of the closest rows qualify - fastest if you remove the WHERE clause altogether, try it! It gets increasingly expensive if many rows have to be filtered. Basically: the longer your pattern the more expensive the query.

A sequential scan, which has to solve the same problem for selective conditions: filter many rows until finding some candidates. The method is faster, but the whole table has to be read, and if more than a few rows are found, the final sort by distance gets expensive.

Basically, Postgres is stuck between a rock and a hard place with this wicked query.
Fix
You would need an index on unaccent(lower(name)) which is not possible, because unaccent() is only STABLE, not IMMUTABLE. But you can work around this limitation with an IMMUTABLE function wrapper - done right:

Does PostgreSQL support “accent insensitive” collations?

The best index still seems tricky and depends on your complete situation.
Since you match left-anchored patterns, I would try a separate btree index with the text_pattern_ops operator class, using the function f_unaccent() outlined in the linked answer above:
CREATE INDEX a_name_pattern_idx ON a (f_unaccent(lower(name)) text_pattern_ops);

More:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Then:

Rewrite the WHERE condition to match the indexed expression
Make the wildcard to the right of the expression explicit to make it work for prepared statements.
Don't concatenation text parameters into your query to begin with, that's an invitation for sneaky errors and SQL injection. Use a prepared statement:

$sql = 'SELECT a.name, ST_AsText(a.geom), b.name AS name_b, b.id
        FROM   a
        LEFT   JOIN b ON b.id = a.b_id
        WHERE  f_unaccent(lower(a.name)) LIKE (f_unaccent(lower($1)) || $$%$$)
        ORDER  BY a.geom <-> ST_GeomFromText($$POINT($2 $3)$$, 4326)
        LIMIT  2';

$result = pg_query_params($sql, array($searched, $lat, $lon));

I use dollar-quoting to avoid escaping single quotes.
For repeated calls use pg_prepare and pg_execute. Details:

Problems with my attempt to implement an UPSERT

Or you could try a GiST index - a multicolumn, functional GiST index combining a PostGis spatial GiST index and a functional trigram GiST index. I didn't test that combination, yet.
You need to install pg_trgm first. Instructions:

How is LIKE implemented?

CREATE INDEX a_special_gist_idx ON a
USING gist (f_unaccent(lower(name)) gist_trgm_ops, geom);

I put f_unaccent(lower(name)) first because, per documentation:

A multicolumn GiST index can be used with query conditions that
involve any subset of the index's columns. Conditions on additional
columns restrict the entries returned by the index, but the condition
on the first column is the most important one for determining how much
of the index needs to be scanned. A GiST index will be relatively
ineffective if its first column has only a few distinct values, even
if there are many distinct values in additional columns.


Oracle query get schema name, size and creation date I'm having trouble making a query with the following output, Schema Name, Size and Creation Date.
I'm new to Oracle and after more than 4 hours of searching I decided to create a question here.

I found the following query that returns name and size.

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments
group by owner;

select u.username,
       u.created,
       round(nvl(sum(s.bytes)/1024/1024,0),2) size_mb
  from dba_users u
  left outer join dba_segments s on (s.owner = u.username)
 group by u.username, u.created
 order by u.username;


Should database be denormalized if I need an index across multiple tables I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like:

SELECT * FROM song s
JOIN album a ON a.id = s.album_id
WHERE LOWER(CONCAT_WS(' ', album.name, song.name) = LOWER('Meteora Breaking The Habit')

I wish to create an index on the above type of search terms but it looks like I can't have an index across multiple tables. In this case, would it make sense to de-normalize the table and put the song & album name in a single table or is there a better approach?

Reference Table:

CREATE TABLE album (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE song (
   id SERIAL PRIMARY KEY,
   name text NOT NULL,
   album_id integer NOT NULL,
   CONSTRAINT album_id_fk FOREIGN KEY (album_id) REFERENCES album (id)
);

P.S. For the sake of understanding, I'm ignoring the fact that query can be song name first & album name later. Also ignoring that query can contain band name too. The query sent by the client is of the form album.name + ' ' + song.name.

If you are getting the LOWER('Meteora Breaking The Habit') portion of the query from a source that it outside your control, then I would recommend keeping your song/album table structure (although remove album_id from song table), but include a 3rd table:

CREATE TABLE ALBUM_SONG (
  album_id,
  song_id,
  album_song_name -- this would be album.name || song.name
)

This is essentially a join table between the two for M-M relationships ('cause hey - the same song could be on multiple albums)..

Then you can index and query the join table on LOWER(album_song_name)


Selecting row multiple times, as specified by a field in that row As part of an redesign, there's need for an different schema in one of the main tables in our database. The data in one of the existing tables will be kept as legacy data.

To make the changeover easier, I'm trying to create a view on the existing data, which can be union'ed with new data, to allow both to be used until the final change.

The scenario is that there's a table which stores the following:

WorkID
User
HoursWorked
Project
Number of reports written

It's being modified to become two tables, the first being

WorkID
User
HoursWorked
Project
Flag Legacy

And the second, with one row for every report as specified in the number of reports field in the original table.

ReportID,
WorkID [fk to first table]
.. (extra data about the report)

My issue is deriving the second table as a view, that can then be union'ed with data from the new version's table.

So, my question is, how could I go about selecting a row multiple times as specified by a field within that row as a query that can be created as a view - infact is it possible?

The DB is in Sql Server 2008r2

Thanks for your help!

If you have a numbers table, you can use that. If you don't have a numbers table, just create one:

CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY CLUSTERED);

INSERT dbo.Numbers WITH (TABLOCKX) (Number)
SELECT TOP (1000000) Number = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1
  CROSS JOIN sys.all_objects AS s2;

(You probably don't need 1 million rows in your numbers table, but it's a decent ballpark that handles most scenarios. You can also reduce space by applying compression if you have an appropriate edition.)

Then you can do this:

SELECT t.columns
FROM dbo.[a table] AS t
CROSS JOIN dbo.Numbers AS n
WHERE n.Number >= 1 -- in case you have a 0-based numbers table
  AND t.[Number of reports written] >= n.Number;

More info on the power of Numbers tables:

Why are numbers tables "invaluable"?
Generate a Set - Part 1 | Part 2 | Part 3
Why should I consider using an auxiliary numbers table?


List of All Privileges Table on MySQL I’m trying to connect MySQL to an identity management (IDM) platform - goal is, to be able to create roles from the IDM site out of the MySQL privileges and be able to provision it to new users, which should create as well in the MySQL.

To do this, I need to connect MySQL to the IDM platform, which I have already done. I’m stuck on the next step though: I need the list of all privileges that can be granted to a user in MySQL then map it as entitlements on the IDM site. I have checked the information_schema.user_privileges/mysql.user but found that it only contains the current privileges of existing users. The privileges were on columns, I think if there is a way I can get it as records then I may consider using it.

Can anyone please help? Or if someone knows any methodology to do this better please let me know.

The intervals for MySQL grants comes with different levels.

You simply extract the grants from the mysql schema.

Problem is: From which level do you need the extraction ???

You will need the following query to show you global, database, and table grants

SELECT
    IF(table_name='user','Global',IF(table_name='db','Database','Table')) grant_level,
    REPLACE(LEFT(column_name,LENGTH(column_name) - 5),'_',' ') grant_type
FROM (SELECT table_name,column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name IN ('user','db','tables_priv')
AND column_name LIKE '%\_priv') AAA
ORDER BY table_name='user' DESC,table_name,column_name;

For MySQL 5.7.12, you will see this:

mysql> SELECT
    ->     IF(table_name='user','Global',IF(table_name='db','Database','Table')) priv_level,
    ->     REPLACE(LEFT(column_name,LENGTH(column_name) - 5),'_',' ') priv_type
    -> FROM (SELECT table_name,column_name FROM information_schema.columns
    -> WHERE table_schema='mysql' AND table_name IN ('user','db','tables_priv')
    -> AND column_name LIKE '%\_priv') AAA
    -> ORDER BY table_name='user' DESC,table_name,column_name;
+------------+-------------------+
| priv_level | priv_type         |
+------------+-------------------+
| Global     | Alter             |
| Global     | Alter routine     |
| Global     | Create            |
| Global     | Create routine    |
| Global     | Create tablespace |
| Global     | Create tmp table  |
| Global     | Create user       |
| Global     | Create view       |
| Global     | Delete            |
| Global     | Drop              |
| Global     | Event             |
| Global     | Execute           |
| Global     | File              |
| Global     | Grant             |
| Global     | Index             |
| Global     | Insert            |
| Global     | Lock tables       |
| Global     | Process           |
| Global     | References        |
| Global     | Reload            |
| Global     | Repl client       |
| Global     | Repl slave        |
| Global     | Select            |
| Global     | Show db           |
| Global     | Show view         |
| Global     | Shutdown          |
| Global     | Super             |
| Global     | Trigger           |
| Global     | Update            |
| Database   | Alter             |
| Database   | Alter routine     |
| Database   | Create            |
| Database   | Create routine    |
| Database   | Create tmp table  |
| Database   | Create view       |
| Database   | Delete            |
| Database   | Drop              |
| Database   | Event             |
| Database   | Execute           |
| Database   | Grant             |
| Database   | Index             |
| Database   | Insert            |
| Database   | Lock tables       |
| Database   | References        |
| Database   | Select            |
| Database   | Show view         |
| Database   | Trigger           |
| Database   | Update            |
| Table      | Column            |
| Table      | Table             |
+------------+-------------------+
50 rows in set (0.02 sec)

This should give you a good starting point.

You could probably create it as a table with something like this

CREATE TABLE grant_records ENGINE=MyISAM AS
SELECT
    IF(table_name='user','Global',IF(table_name='db','Database','Table')) grant_level,
    REPLACE(LEFT(column_name,LENGTH(column_name) - 5),'_',' ') grant_type
FROM (SELECT table_name,column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name IN ('user','db','tables_priv')
AND column_name LIKE '%\_priv') AAA
ORDER BY table_name='user' DESC,table_name,column_name;


Single sql query to select a record from parent table only if all it's records in child table has values I've Table1 and Table2, Table1's PK is the FK of Table2. Now i need to perform a select from Table1 if and only if all the records in Table2 corresponding to a Table1 record has values (if any field value is null then skip).

ie, for an id in Table1 there have multiple sub ids in Table2. Out of those multiple ids if a particular field corresponds to any one id is null then select should be skipped.

Any one please help me out to write a single sql query for this requirement.

This is called an "anti-join" (or anti-semijoin). One way to write it in SQL is to use the NOT EXISTS construction:

SELECT t1.*
FROM Table1 AS t1
WHERE NOT EXISTS
      ( SELECT *
        FROM Table2 AS t2
        WHERE t2.FKcolumn = t1.PKcolumn
          AND t2.columnX IS NULL
      )

If - as your comment - you want a parent row (at Table1) not related to any child row (at Table2), to NOT be shown, add this (or join Table2 if you also want columns from the second table shown):

  AND EXISTS
      ( SELECT *
        FROM Table2 AS t2
        WHERE t2.FKcolumn = t1.PKcolumn
      )


Database mirroring - Witness only In SQL Server 2008 R2 Express the only mirroring alternative is "Witness only", however I can't find a good explenation to what this is.

I would like to have a database on a virtual server and then have a local SQL Server that mirrors this database, in case the internet connection goes down, I want to fail-over on the local server.

Any ideas on what SQL Server editions I need to be running on both ends?

For SQL 2008 you can run either Enterprise or Standard for mirroring: http://msdn.microsoft.com/en-us/library/ms188712.aspx

The witness server itself can be Standard, Enterprise, Workgroup, or Express.

The witness server is needed for automatic failover: http://technet.microsoft.com/en-us/library/ms175191.aspx


Why does MS SQL Server return a result for empty string check when Unicode string is not empty select * from (select N'ግዜ ' as t) as t2 where t= ''

the string 'ግዜ ' matches the above check, why is this?

While I am not sure of the exact reason for those specific characters, t The issue has to do with the older collations (please see UPDATE section at the end). And it is not just empty string that they equate to, but also to just one of those characters:

SELECT * FROM (SELECT N'ግዜ') tab(col) WHERE tab.col = N'ግ';

And if you try a case-sensitive collation, even with multiple characters, they still equate:

SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግግግግ' COLLATE SQL_Latin1_General_CP1_CS_AS;
SELECT * FROM (SELECT N'ግ') t(c) WHERE t.c = N'ዜዜዜዜ' COLLATE SQL_Latin1_General_CP1_CS_AS;
SELECT * FROM (SELECT N'ዜ') t(c) WHERE t.c = N'ግግግግ' COLLATE SQL_Latin1_General_CP1_CS_AS;

Even the "equivalent" Windows Collations have the same issue:

SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_CS_AS;
SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_CS_AS_KS_WS;

BUT, it seems that newer versions of the Windows Collations (i.e. the 100 series or newer) "fixes" the issue and these no longer equate:

SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_100_CI_AI;
SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_100_CI_AS;

And, of course, the binary Windows Collations (both older and newer series) work just fine as the following do not report a match:

SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_BIN;
SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_BIN2;
SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_100_BIN;
SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_100_BIN2;

UPDATE (2015-08-20)
After 6 hours of pouring through documentation on http://www.unicode.org/, http://site.icu-project.org/, and a couple of other Unicode-related sites, I gave up trying to find evidence of a "weighting" change that might have occurred just prior to 2008 (the new 100 series of collations were introduced in SQL Server 2008). I did, however, find the following info at www.fileformat.info for the two characters being tested here:

ግ = Unicode Character 'ETHIOPIC SYLLABLE GE' (U+130D)
ዜ = Unicode Character 'ETHIOPIC SYLLABLE ZEE' (U+12DC)

So, I moved on to the next project and moments later came across the following on the SQL Server 2008 MSDN page for Collation and Unicode Support:

  SQL Server 2008 has introduced new collations that are in full alignment with collations that Windows Server 2008 provides. These 80 new collations are denoted by *_100 version references. They provide users with the most up-to-date and linguistically accurate cultural sorting conventions. Support includes the following:

  ...
  Weighting has been added to previously non-weighted characters that would have compared equally.

No sort weight for a character means that it is effectively invisible.

Moral of the story: don't try so hard; give up sooner ;-)

UPDATE (2018-09-20)

For a more visual indication of what is going on, the query below compares each BMP character (Code Points 0 - 65535 / U+0000 - U+FFFF) to an empty string. The comparison is repeated using different collations: BIN2, a SQL Server Collations, Latin1_General that started with SQL Server 2000, Latin1_General that started with SQL Server 2008, Japanese_XJIS that started with SQL Server 2008, and Japanese_XJIS that started with SQL Server 2017. The two Collations starting in SQL Server 2008 are showing that both return the same number of matches, yet the newer Japanese_XJIS Collation returns a different number (the only Collations updated in SQL Server 2017 are the Japanese Collations). This is done to show how many characters are missing sort weights across the various Collation versions.

;WITH nums AS
(
  SELECT TOP (65536) (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1) AS [CodePoint]
  FROM   [master].[sys].[columns] col
  CROSS JOIN [master].[sys].[objects] obj
)
SELECT nums.[CodePoint],
       COALESCE(NCHAR(nums.[CodePoint]), N'TOTALS:') AS [Character],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE Latin1_General_BIN2) THEN 1 END) AS [BIN2],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE SQL_Latin1_General_CP1_CS_AS) THEN 1 END) AS [SQL Collations],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE Latin1_General_CS_AS_KS_WS) THEN 1 END) AS [SQL2000 Latin1],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE Latin1_General_100_CS_AS_KS_WS) THEN 1 END) AS [SQL2008 Latin1],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE Japanese_XJIS_100_CS_AS_KS_WS) THEN 1 END) AS [SQL2008 Japanese],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE Japanese_XJIS_140_CS_AS_KS_WS) THEN 1 END) AS [SQL2017 Japanese]
FROM   nums
GROUP BY ROLLUP ((nums.[CodePoint], NCHAR(nums.[CodePoint])));

To see the details for all rows, execute the query above. But for just the summary, that is:

BIN2  SQL Collations SQL2000 Latin1  SQL2008 Latin1  SQL2008 Japanese  SQL2017 Japanese
1     21230          21229           5840            5840              3375


How to provide the key value in a join when a match is not found My DB2 database has the following tables:

CustomerLog

customer  version  userid  description
--------  -------  ------  ----------------------------
Orange    101      2       New customer
Orange    102      2       Bespoke widgets added
Orange    103      4       Fix errors after review
Orange    104      3       Priority bugfix
Peach     101      1       New customer
Peach     102      3       Custom screens for Peach
...

Users

userid  name
------  -------------------
1       Andrew
2       Belinda
3       Charles

With the select statement:

select c.customer, c.version, u.name, c.description
from customerlog c, users u
where c.customer='Orange'
and c.userid = u.userid

I get the following result:

customer  version  name        description
--------  -------  ----------  ----------------------------
Orange    101      Belinda     New customer
Orange    102      Belinda     Bespoke widgets added
Orange    104      Charles     Priority bugfix

If I instead used a left join, I could get the following row selected as well:

Orange    103      null        Fix errors after review

... but is there a way to get the userid in place of the null?  ie:

Orange    103      4           Fix errors after review

Use COALESCE

Purpose
COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

select c.customer, c.version, coalesce(u.name, to_char(c.userid)) as user c.description
from customerlog c
     left join users u on u.userid = c.userid
where c.customer='Orange'


Are there reasons why I should not set my db owner to [sa]? Yesterday I asked this question regarding changing the dbo of multiple databases that I have.  The change makes sense, but I want to be clear.

Is there any, good reason or circumstance why I should not set the dbo of a database to [sa]?

Making SA the owner of a database actually simplifies and/or solves a number of things, but can have some security implications.

In particular, remember that if SA is the owner of a database, then dbo = 'SA'.  This means that, among other things, any procedures in the [dbo] schema (which is the default) that have "EXECUTE As Owner" in them, are actually executing as SA.  That's not quite as bad as it sounds, because unless you've marked the database as TRUSTWORTHY, SQL Server will not let a session or task out of the database with an impersonated server-level principal like that.

Which brings up the next point: never mark such databases as TRUSTWORTHY, unless you're really, really sure that it is secure.  Because anyone with the ability to create procedures in the [dbo] schema can execute as SA, on the entire server, if they want to.

Another issue can come up because many products and applications that have their own SQL Server database, often specify that their application login has to be the DBO of the database.  Obviously you could resolve that by making their application login be 'SA'.  Hopefully, it's also obvious that you should never do that, unless that SQL Server Instance is not used for anything else (even then, I would recommend against it).


SQL Server: Rows over 8KB: Where are the docs? I need to properly understand if and how I can get 10KB or 20KB per row in SQL Server 2016, Standard Edition.

I am reading the SQL Server Tech specs:

https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server#Engine

Specifically "Bytes per row"

It refers to something in BOL, that I cannot find.

Where is the section "Row-Overflow Data Exceeding 8 KB" topic in SQL Server Books Online. ?

Table and Index Organization

  Variable length data stored in varchar, nvarchar, varbinary, or
  sql_variant columns that exceed the 8,060 byte row size limit.

  For every partition used by a table (heap or clustered table), index,
  or indexed view, there is one ROW_OVERFLOW_DATA allocation unit. This
  allocation unit contains zero (0) pages until a data row with variable
  length columns (varchar, nvarchar, varbinary, or sql_variant) in the
  IN_ROW_DATA allocation unit exceeds the 8 KB row size limit. When the
  size limitation is reached, SQL Server moves the column with the
  largest width from that row to a page in the ROW_OVERFLOW_DATA
  allocation unit. A 24-byte pointer to this off-row data is maintained
  on the original page.

Row-Overflow Data Exceeding 8 KB

  The length of individual columns must still fall within the limit of
  8,000 bytes for varchar, nvarchar, varbinary, sql_variant, and CLR
  user-defined type columns. Only their combined lengths can exceed the
  8,060-byte row limit of a table.

Another good answer here:
Work around SQL Server maximum columns limit 1024 and 8kb record size


SQL Server Connection Timeout Issue I have a application, which runs for the entire hour, every hour of every day. It basically has 400ish 'projects' to execute within each hour, and loops over the scripts, which execute stored procedures, to complete the task.

Just as an overview, the application is querying the database for data, extracting that data, and writing to a file. The final stage of that is doing at the application layer.

For whatever reason, today, the database has been suffering from constant connection timeout issues. The time in which they happen varies, but it's often 8 minutes in - so after some 'projects' have completed their cycle. It always fails when the application moves on to find the information relevant to the next 'Project' - which is a basic script which says "what haven't I executed yet?".

I've tried extending the connection timeout from 20 seconds to 60 - this hasn't helped.

I have noticed the Virtual Machine is running low on memory, due to the host running out of physical memory. I'm not sure if memory can cause an issue regarding connections - I have given the VM a little more by taking it away from another VM.

This system has been running for months without issue (apart from maybe the odd connection issue every now and then).

Other than the above, is there anything else I should be looking at and/or thinking of?

Ignoring both physical and VM memory issues for now.

Queries that take longer and longer to run can be cause by several things, but
I will guess that you perhaps do you have regular index and stats maintenance.


how to script server side permissions - specifically EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER? I could not script the following server side permissions:

GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]

but thanks to the question below I found a solution to that:

How to script out the grant view on login permission?

Now I have a similar problem:

I have a process that grant the following server side permission to a login:

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [my_company\my_login]

that is working fine, however, I need to script all the permissions of the my_company\my_login but I cannot find a way to script this permission.

How can I do that?

Unlike sys.server_principals and sys.database_permissions holds information about server level access/permissions, information similar to that sys.database_principals' andsys.database_permission` holds information about database level access/permissions.

Joining these two objects with UNION of sys.objects and sys.sysobjects will give you desired information about user defined and system objects, which can be converted into script.

Note: To generate script for object level permission we need to execute the script in respective database. Like sys.xp_prop_oledb_provide is an object of master database, so we'll execute the SELECT statement in master database.

Example:-

CREATE LOGIN Radhe WITH PASSWORD='HareKrishna001!', DEFAULT_DATABASE=master;
GO

CREATE USER Radhe FOR LOGIN Radhe;
GO

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [Radhe];
GO

USE master;
GO
select
        case when DBPerm.state_desc='GRANT_WITH_GRANT_OPTION' then 'GRANT' else DBPerm.state_desc end COLLATE SQL_Latin1_General_CP1_CI_AS+
        ' '+DBPerm.permission_name  COLLATE SQL_Latin1_General_CP1_CI_AS+' ON '+o.objectschema+'.'+o.name+' TO ['+DBPrin.name+']'+
         case when DBPerm.state_desc='GRANT_WITH_GRANT_OPTION' then 'WITH GRANT OPTION;' else ';' end COLLATE SQL_Latin1_General_CP1_CI_AS AS [Script]
from
            sys.database_permissions DBPerm
inner join  (
            select name,object_id,schema_name(schema_id) objectschema from sys.objects
            union all
            select name,id,'sys' as objectschema from sys.sysobjects
            ) o
on          DBPerm.major_id=o.object_id
inner join  sys.database_principals DBPrin
on          DBPerm.grantee_principal_id=DBPrin.principal_id
where
            DBPrin.name NOT LIKE '##%##'
        AND DBPrin.name NOT LIKE 'NT AUTHORITY%'
        AND DBPrin.name NOT LIKE 'NT SERVICE%'
        AND DBPrin.name not in ('sa','public');

Results to:

Script
-------------------------------------------------------------
GRANT EXECUTE ON sys.xp_prop_oledb_provider TO [Radhe]

Hare Krishna!


Activity Monitor % Processor Time grayed out on SQL Server The % Processor Time graph at Activity Monitor for remote connections stopped working and it grayed out after the physical SQL Server restart (image below), but when I get in the server with the same Domain User it works fine. It was also working fine on remote connections before the restart and I have noticed that it takes significantly more time to load the Processes than before.

The problem is happening to all users that try to see the Processor Time remotely including sa and server admins so I would think it´s not a permission issue. The server runs Windows Server 2012 and SQL Server 2012.

What could be happening? or How can I solve this?

Thanks in advance! and sorry about my English.

I know it is not a solution but we had a programmed restart and after that everything works fine, it is a production server so we couldn't restart before but that solved it.


cannot connect my oracle server to developer I get the following error: Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12505, TNS: listener does not currently know of SID given in connect descriptor.  My services are below and my connection window.  I believe the installation of oracle 12c was a success.  Do you guys know what could be wrong, please.

enter image description here

First Logged in SQLPlus and Unlock the user

Run SQLPlus, connect as SYS user (use administrator's password you have entered during installation) . Suppose that here For your test connection unlock HR user and give him a new password:

C:\>sqlplus  sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 24 12:15:51 2018

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter user hr identified by new_password;

User altered.

SQL> alter user hr account unlock;

User altered.

For Creating a Database Connection

The first step to managing database objects using Oracle SQL Developer is to create a database connection.Perform the following steps:

Open Oracle SQL Developer.
In the Connections navigator, right-click Connections and select New Connection.
Enter test for the Connection Name (or any other name that identifies your connection), hr for the Username and Password, specify your localhost for the Hostname and enter ORCL10(in your case, as it showing in your attached picture) for the SID. Click Test.
The status of the connection was tested successfully. The connection was not saved however. Click Save to save the connection, and then click Connect.

For further your ref Here , Here & Here


deleting a table referenced by many foreign keys I have a table that I want to drop and create again, because I want to add the IDENTITY property to a column, wich is not possible directly. The problem is that I can't drop it, because my column is being referenced by many foreign keys in other tables. So, what are the strategies adopted for droping a table referenced by many foreign keys from other tables ?

Thank you for help in advance :)

Assuming the column you want to change is the one the FKs point to, you need to drop all of the foreign key constraints - it's not enough to simply disable them. You can't drop the table, anyway; and you can't drop the column if it's actually referenced, and the only way to add the IDENTITY property is to drop/re-create the column, or drop/re-create the table.

Here is a script that generates the commands to drop and later re-create all of the foreign keys that point at dbo.yourtable. It is based on this answer. Drop is easy, just build a simple concatenated list from sys.foreign_keys:

DECLARE @drop NVARCHAR(MAX) = N'';

SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
WHERE fk.referenced_object_id = OBJECT_ID(N'dbo.yourtable');

PRINT @drop;

Create is a little more complex. We need to generate the list of columns on both sides of the constraint, even though in most cases there is only one column:

DECLARE @create NVARCHAR(MAX) = N'';

SELECT @create += N'
ALTER TABLE '
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c
    INNER JOIN sys.foreign_key_columns AS fkc
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c
    INNER JOIN sys.foreign_key_columns AS fkc
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0
AND rt.[object_id] = OBJECT_ID(N'dbo.yourtable');

PRINT @create;

When you're happy with the output (keep in mind that PRINT is limited to 8K, so it may look like the command is truncated), add this to the end and run it again:

EXEC sp_executesql @drop

-- drop and re-create the table here, whatever that needs to entail

EXEC sp_executesql @create;

This should be a pretty simple exercise because, based on information you've added later, these tables are empty.

If this is not the column the FKs point to, you may be able to drop just the column and re-add it with the IDENTITY property. But if you had data, this would mean you would have to accept whatever arbitrary row numbers SQL Server would assign, or be prepared to completely gut the table (and all the FK tables, or at least the FK column(s) pointing to this table) and re-populate. You would also have to accept that the column may now be in a different "physical" location within the table (you shouldn't care about this at all, but I mention it because many do for reasons unknown).

In SQL Server 2012, as a workaround, you could implement a SEQUENCE instead of using the IDENTITY property. Since you're only on SQL Server 2008, you could instead generate the increment using your own serializable MAX+1 construct. But that's a different question altogether. (I just ran a search, I have not looked at how good those answers are; also see some ideas here.) Or move to a more modern version with more options.


DB2 GLOBAL VARIABLES Refresh for datapool managed sessionis I've recently discovered the feature of defining global variables for DB2. I wanted to utilize it. My use case is to create a variable containing the value for Previous Banking day.

 CREATE VARIABLE SCHEMA.GV_PREV_PROC_DATE DATE
 DEFAULT ((SELECT SCHEMA.DATE_UTIL.PREVIOUS_BANKING_DAY(PROC_DATE)
           FROM SCHEMA.TBL_PARAMETERS));

This particular date is usually referenced throughout various programs. Instead of querying everytime, I thought of placing it in the variable. Based on the DB2 specification, the value is session scoped. Since we utilize datsource pooling, the value is not updated once the date is incremented by the end of day batch program, unless i reset my connection pool.

Is there a way to propagate the updates to existing sessions, or is my use case totally not appropriate for this global variables? By the way I'm using DB2 10.1 LUW

You can set variables to a new value in a session but you would need your application to spot the date change and update the variable for the session accordingly. Or alternatively, kill all sessions when the date changes!


How to import table's data in MySql from Sql Server? I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error.

Here this error may be occurring because table in Sql Server has a column with data type image and table in MySql this column's data type is LONGBLOB.

Please provide your expert answers. If not possible through SQL Server Import and Export Wizard than please suggest any other method for data transfer.

You could try changing the datatype in SQL Server to VARBINARY(MAX) :

http://wiki.ispirer.org/sqlways/mysql/data-types/longblob

Even if this is just to get your data into SQL Server. It may also be worth noting that IMAGE is being removed in future editions of SQL Server.


How to filter out the records with the latest date? I have a table with id, task_id, created_taskaction (= rec.cr.date).

I would like to get the latest record for e.g. task_id 0001.

In the wordpress framework I tried this:

> $last_task_action2=$wpdb->get_row("SELECT * FROM $wpdb->task_actions
> NATURAL JOIN ( SELECT id_task, MAX(created_taskaction) AS datetime
> FROM $wpdb->task_actions GROUP BY id_task) mostrecent ON
> $wpdb->task_actions.id_task = $wpdb->mostrecent.id_task  AND
> $wpdb->task_actions.datetime = $wpdb->mostrecent.datetime");

But it doesn't work. I tried copying from this thread, but it's still confusing:
https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql

Even these two don't show ANY data...

$last_task_actiond = $wpdb->get_row("SELECT MAX (created_taskaction) AS 'Max' FROM $wpdb->task_actions");
echo $last_task_actiond;

and

$last_task_actiond = $wpdb->get_row("SELECT MAX (created_taskaction) AS 'Max' FROM wp_task_actions");
echo $last_task_actiond;

Nothing shows up...

It worked I found 2 bugs:

Line 5:

  $wpdb->task_actions.datetime = $wpdb->mostrecent.datetime");
  Had to be: task_actions.created_taskaction instead of task_actions.datetime

Also the $wpdb-> sometimes doesn't work properly, so I changed all of them with wp_


Updating Always On Availability Group server connected to Windows SQL Cluster I'm working with a unique configuration.  Our organization prefers Windows Failover Clusters for our main database servers.  In this case my primary replica is a WFSC.  This is a reporting server where the data is extracted to each night.  We have a large database that we duplicate to another server we call shadow.  The shadow server supports the secondary replica.

I don't have a lot of experience adding updates/service packs to this configuration because of the business requirements to keep it running.  That said, I'm wondering if anyone has experience with updating WFSC & Always On availability groups and can give me some pointers.  I'm concerned with how the AG will react to different configurations when we roll the WFSC from one node to the other.

It will not react any different, it is the same way as you upgrade a cluster server (if you did that before). To minimize the downtime of your server make sure to set it up a manual failover. Then start with patching your secondary node after you finish that, do database consistency check, on your secondary and then failover the primary node to your secondary server (the updated server) and do the same exact process on the then primary, now secondary node. Once you finish the upgrade make sure to check the health status of the availability group and if fix if any suspended or not synchronized database on from the list of your availability database.


Multi-valued range subtraction I have a table (actually a query, but I think it's the same thing) with two
range columns. I need to perform a 'subtraction' on the two ranges. Precisely,
I need Intersection(a, Complement(b)). Since this is not representable with a single
range value, there could be 0, 1, or 2 rows in the output for each input row.
Here's an example:

CREATE TABLE test (some_data VARCHAR, a INT4RANGE, b INT4RANGE);
INSERT INTO test VALUES ('a', '[0, 5)', '[1, 2)');
INSERT INTO test VALUES ('b', '[0, 5)', '[1, 5)');
INSERT INTO test VALUES ('c', '[0, 5)', '[0, 5)');
INSERT INTO test VALUES ('d', '[1, 3)', '[4, 5)');

SELECT * FROM test;
 some_data |   a   |   b
-----------+-------+-------
 a         | [0,5) | [1,2)
 b         | [0,5) | [1,5)
 c         | [0,5) | [0,5)
 d         | [1,3) | [4,5)

And this is the result I want:

SELECT somequery... FROM test;

 some_data |  a-b  |
-----------+-------+
 a         | [0,1) |
 a         | [2,5) |
 b         | [0,1) |
 d         | [1,3) |

Looks like simple math. Assuming that all the ranges are of the same inclusive-exclusive type [):

SELECT some_data, int4range(lower(a), LEAST(upper(a),lower(b))) AS ab
FROM test
WHERE lower(a) < lower(b)

UNION ALL

SELECT some_data, int4range(GREATEST(lower(a),upper(b)), upper(a))
FROM test
WHERE upper(b) < upper(a)

UNION ALL

SELECT some_data, a
FROM test
WHERE b = int4range(0,0)
   OR a = int4range(0,0) ;

Tested at SQL-Fiddle.


Is there a way to find out if value exists with addToSet? addToSet adds values to an array if the value doesn't exist.

If the value already exists MongoDB will not return anything saying that it exsits.

Is there a way to find out if the value exists? (other than going through the array)

MongoDB does not return the results of field-level updates like an $addToSet operator; the overall document update either succeeds or fails.

However, assuming you are using MongoDB 2.6 or newer, you can infer whether the update was a no-op by inspecting the WriteResult returned on update/upsert.

Example below using MongoDB 3.2.

Test data:

db.fruit.insert([
    { _id: 'a', tasty: ['apple', 'banana', 'pear']},
    { _id: 'b', tasty: ['kumquat', 'banana', 'mango']},
    { _id: 'c', tasty: ['plum'] }
])

Value was added to array

Add "mango" to the tasty array in the a document:

db.fruit.update(
    { _id: 'a' },
    { $addToSet: { 'tasty': 'mango' }}
)

WriteResult({
  "nMatched": 1,
  "nUpserted": 0,
  "nModified": 1
})

Since the only possible update was to the array, nModified:1 indicates that "mango" was added.

Value already exists in the array

Add "mango" to the tasty array in the b document:

db.fruit.update(
    { _id: 'b' },
    { $addToSet: { 'tasty': 'mango' }}
)

WriteResult({
  "nMatched": 1,
  "nUpserted": 0,
  "nModified": 0
})

Since the only possible update was to the array, nModified:0 indicates that "mango" already existed.

Updating with more fields

If you combine multiple field updates, you won't know which field was actually updated. In this case, you could specifically match documents where the array value isn't set already:

db.fruit.update(
    // query
     {
         _id: 'c',
         tasty: { $nin: ['mango'] }
     },

     // update
    {
        $set: { fruitBasket: true },
        $addToSet: { 'tasty': 'mango' }
    }
)

WriteResult({
  "nMatched": 1,
  "nUpserted": 0,
  "nModified": 1
})


How can I select the OS User in MySQL? I need to select the OS user during trigger execution to insert into a table. How can I select the OS User as it may be different from the MySQL user?

Some RDBMS (SQL Server) allow you to get this information easily. Especially for a direct DB connection from code running in the user's session on Windows, using Windows Auth to connect to SQL Server.

It can be done if the user connects to a website, which then connects to SQL Server too.

However, you're on MySQL.

Your simplest option is to have the client code send it in, derived from the OS. Ypu can then pass this in to the trigger as per this SO answer https://stackoverflow.com/questions/324605/mysql-trigger-storing-a-select-in-a-variable


Restore the latest backup on the database We have a stored proc which takes the backup from a database to the specified location. Sometimes the backup process runs more than once. It causes having more than one backup set on the same file name. When we write T-SQL to restore the target db using this backup file, how can we tell SQL to restore the latest db backup set on the target database (using the T-SQL command)?

When we restore the backup using MS SQL management studio, we can simply select the back up set we want to restore. But I need to run the restore using T-SQL and wondering how can determine SQL uses the latest db backup set to restore. For example from the image below, I want to restore the last backup set (highlighted in yellow):

I checked this website, but couldn't find the answer. I appreciate it if you could help.

Thanks.

If you don't have access to the original server and all you have is the backup file, then you'll need to restore the headers from the backup file first to see what's inside it.

You can do this by:

create table #headers
(
BackupName              nvarchar(128),
BackupDescription       nvarchar(255),
BackupType              smallint,
ExpirationDate          datetime,
Compressed              bit,
Position                smallint,
DeviceType              tinyint,
UserName                nvarchar(128),
ServerName              nvarchar(128),
DatabaseName            nvarchar(128),
DatabaseVersion         int,
DatabaseCreationDate    datetime,
BackupSize              numeric(20,0),
FirstLSN                numeric(25,0),
LastLSN                 numeric(25,0),
CheckpointLSN           numeric(25,0),
DatabaseBackupLSN       numeric(25,0),
BackupStartDate         datetime,
BackupFinishDate        datetime,
SortOrder               smallint,
[CodePage]              smallint,
UnicodeLocaleId         int,
UnicodeComparisonStyle  int,
CompatibilityLevel      tinyint,
SoftwareVendorId        int,
SoftwareVersionMajor    int,
SoftwareVersionMinor    int,
SoftwareVersionBuild    int,
MachineName             nvarchar(128),
Flags                   int,
BindingID               uniqueidentifier,
RecoveryForkID          uniqueidentifier,
Collation               nvarchar(128),
FamilyGUID              uniqueidentifier,
HasBulkLoggedData       bit,
IsSnapshot              bit,
IsReadOnly              bit,
IsSingleUser            bit,
HasBackupChecksums      bit,
IsDamaged               bit,
BeginsLogChain          bit,
HasIncompleteMetaData   bit,
IsForceOffline          bit,
IsCopyOnly              bit,
FirstRecoveryForkID     uniqueidentifier,
ForkPointLSN            numeric(25,0),
RecoveryModel           nvarchar(60),
DifferentialBaseLSN     numeric(25,0),
DifferentialBaseGUID    uniqueidentifier,
BackupTypeDescription   nvarchar(60),
BackupSetGUID           uniqueidentifier,
CompressedBackupSize    bigint,
Containment             tinyint
);
go

insert into #headers exec('restore headeronly from disk=''c:\DEV\UnitTest.bak''');
go

You now have a temporary table(#headers) which contains details of all the backups contained within that device file.

To find the most recent full database backup we filter for only full db backups (backupType=1) and then look for the most recent backup in the device, which will be the one with the largest LSN. This gives us the position value, which is used in the RESTORE command to specify to SQL Server which backup we want to restore:

declare @filepos int;
select top 1 @filepos=Position from #headers where BackupType=1 order by lastlsn desc;
restore database unittest from Disk=N'c:\DEV\UnitTest.bak' with file=@filepos, recovery, replace;

As you say you will still have access to the original server, we can then query the tables in msdb that SQL Server uses to store this information.

The following queries are assuming you are running them in a SQL session on the original server. If you're not allowed to do that, then you'll need to look into using a linked server and 4 part naming.

There are 2 ways you can specify the set of backups you want to look at. One is via the path to the backup file, the other is by referencing the name on the backup device. I've included both in this query, with some notes about which lines to comment out depend on how you're doing it.

This will produce a RESTORE statement which you can run in a seperate SSMS session. I've also included an example at the bottom of how you could automate this, though you'd want to do some testing beforehand to make sure it's doing what you want.

declare @filepos int, @dbname varchar(50), @devname varchar(50)

/*
dbname is the name of the database you want to restore. Need this, as it's possible for backups of more than one database to be present in the same backup file or device

devname is either the name of the backup device, or the path to the .bak file
*/

select @dbname='Unittest', @devname='c:\DEV\unittest.bak'

select @filepos=max(c.position)
from
msdb.dbo.backupmediafamily a
inner join msdb.dbo.backupmediaset b on a.media_set_id=b.media_set_id
inner join msdb.dbo.backupset c on b.media_set_id=c.media_set_id
where
a.physical_device_name=@devname  --use this line if you know the path to the backup
--a.logical_device_name=@devname --this line if you only know a device name.
and c.type='D' and c.database_name=@dbname

--This will build the restore statement if you know the device name
select 'restore database ['+@dbname+'] from '+@devname+' with file='+cast(@filepos as varchar(4))+', recovery, replace'

--This will build the restore statement if you know the backup file path
select 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace'

/*
This section only if feeling brave and confident, and you've checked the output previously, as it will execute whatever the generated restore command is, so this could cause a major problem if not checked first, or even overwrite production if run on the wrong box.
*/

declare @restore_cmd varchar(250)
select @restore = 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace'
exec(@restore)


DROP TABLE ... CASCADE does not drop any dependent table I am still relatively new to psql and I was just finishing an assignment when I decided to try something which was to drop a table which other tables depend on due to a foreign key relationship. My expectation was that any table that had a foreign key reference to the table I am dropping will have (at least) all their rows deleted and the table dropped as well.
This was not the case. The tables are still there and even though I specified CASCADE, nothing happened to the rows of those tables.

However using TRUNCATE ... CASCADE on the referenced table does in fact remove the rows in the other tables

EDIT:
Ok, I understand it does this because this is the way it has been specified by the documentation, but my question now is, when is this ever expected behaviour?

DROPing a domian with CASCADE removes every column in any table that uses that domain.
DROPing a table with a view attached to it will delete the view.

Why does it make sense that dropping a table which is referenced by other tables does not delete the tables that reference this one as well? Convienience?

The idea behind DROP CASCADE is to automatically remove the dependent objects. This is documented on the same manual page that the other answer is referring to:

CASCADE
Automatically drop objects that depend on the table (such as views).

(Emphasis mine.)
When you are dropping a table that is referenced by another table, the object that immediately depends on the table being dropped is not the other table itself but the foreign key constraint defined on it.
So, the behaviour you are observing should be expected as it is consistent with the other cases you have mentioned:

DROP TABLE ... CASCADE drops the views that directly depend on the table you are dropping.

DROP DOMAIN ... CASCADE drops the columns that directly depend on the domain you are dropping.

TRUNCATE ... CASCADE is also consistent with the explanation above because it removes rows, and the objects dependent on rows can only be other rows, including other tables' rows – that is why the referencing tables are truncated as well1.

1My only issue with the other tables' truncation is that their foreign keys may be defined on nullable columns and yet the tables are still truncated completely even if some of their rows do not reference the table(s) specified in the TRUNCATE statement. Still, even if it is an inconsistency, it is how the behaviour is documented:

CASCADE
Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE.


Efficient pagination for big amount of data I need to be able to quickly (1-2 seconds) retrieve pages of 50,000 records from table that contains ~3,000,000 records. There's a UNIQUE index on a string primary key which contains values like '57133c4aea332cb17b001a28'.

string_id CHARACTER VARYING(255)

CREATE UNIQUE INDEX index_name ON users USING BTREE (string_id);

I've tried

SELECT
   customer_id,
   string_id
FROM users
WHERE string_id > '57133c4aea332cb17b001a28'
ORDER BY string_id
LIMIT 50000

but it has a very poor execution time:

Limit  (cost=0.11..48044.67 rows=50000 width=29)
  ->  Index Scan using index_name on users  (cost=0.11..2799915.87 rows=2913874 width=29)
    Index Cond: ((string_id)::text > '57133c4aea332cb17b001a28'::text)

Limit  (cost=0.11..48237.53 rows=50000 width=29) (actual time=0.561..25740.665 rows=50000 loops=1)
  ->  Index Scan using index_name on users  (cost=0.11..2792079.10 rows=2894101 width=29) (actual time=0.560..25730.852 rows=50000 loops=1)
    Index Cond: ((string_id)::text > '57133c4aea332cb17b001a28'::text)
Planning time: 0.364 ms
Execution time: 25747.091 ms

I'm using PostgreSQL 9.5.

Why is it so hard for DB to get 50,000 records using ordered index and what can I do to improve it?

If the 50,000 records are scattered all over the place, and it takes 4ms to fetch each one (15,000 rpm hard drive), it would take 200 seconds.  So 25 seconds is reasonable, since it is unlikely they will be scattered completely at random.

You can CLUSTER users on index_name which will reorder the table to be in the same order as the string_id.  But it won't automatically stay clustered as you do updates and more insertions.

You can add an index on (string_id, customer_id) and see if an index-only scan will solve your problem.


PL/SQL Loop Returning One Result I'm working with the following script:

create or replace PROCEDURE a_proc
is vi_get_emails varchar2(500);
cursor get_emails is
SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id)
FROM purch_authorize_group_line pagl join purchase_authorizer pa
ON pagl.authorize_id = pa.authorize_id
WHERE pagl.authorize_group_id = '30-PM-COM'
AND pa.notify_user = 'TRUE';

BEGIN
    OPEN  get_emails;
    FETCH get_emails  INTO vi_get_emails;
    CLOSE get_emails;

  FOR rec_ IN vi_get_emails LOOP
     command_sys.mail(from_user_name_ => 'IFSAPP',
                    to_user_name_ => vi_get_emails,
                    subject_ => 'Test subject',
                    text_ => 'Test message body');
    END LOOP;
END a_proc;

This part of my script returns two values:

SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id)
FROM purch_authorize_group_line pagl join purchase_authorizer pa
ON pagl.authorize_id = pa.authorize_id
WHERE pagl.authorize_group_id = '30-PM-COM'
AND pa.notify_user = 'TRUE';

Let's call these values Bob and Bill.

I would like the second part of my script to email both Bob and Bill. However, it's only emailing Bob since Bob is the first record returned by my query. It's not emailing the two users. It's only emailing the first user returned by the query.

You have to loop over the results from the first query. You're just fetching once from your cursor and then close it. So you only process the first line of the result.

Try something like:

CREATE OR REPLACE PROCEDURE a_proc
IS
BEGIN
  FOR email IN (SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id) email
                       FROM purch_authorize_group_line pagl
                            INNER JOIN purchase_authorizer pa
                                       ON pagl.authorize_id = pa.authorize_id
                       WHERE pagl.authorize_group_id = '30-PM-COM'
                            AND pa.notify_user = 'TRUE') LOOP
    command_sys.mail(from_user_name_ => 'IFSAPP',
                     to_user_name_ => email.email,
                     subject_ => 'Test subject',
                     text_ => 'Test message body');
  END LOOP;
END a_proc;

I use an implicit cursor here (FOR email IN (SELECT ...)) to loop over the results. Of course you can do that equivalently with an explicit cursor too.