Should developers be allowed to use LocalDB vs a "development" instance?

To me all these arguments are particularly invalid

Ok. But they aren't to the rest of us. Why?

Patch level consistency between development servers and production

the patching would be proven out when an application progressed through Test

Patching can fix stability and data corruption issues which would otherwise plague developers. It should be done on development machines regardless.

Data security; only data on the development servers gets used for development

It's useful to separate "what should be" from "what is". Developers end up with sensitive (not necessarily PII, but not free either) data on their databases. It happens.

Recoverability; data is recoverable and still backed up

Super important.

Collation differences that can cause problems when moved to production

Only SharePoint and SCCM have issues around this

Anything which uses a temp table will have this issue. It's extremely common. You never notice it because most people go for a standard collation in the first place.

assuming it's ONLY for development, and the database will not be "moved" to production

Why would we assume that? Things often go from development to production. How else do you get production populated for the very first time? Pure scripts? Not necessarily when the application has been in pre-production for some time.

I guess one of the biggest concerns would be the ability for local db instances coming out of date, but thats a software management issue, not a DBA one IMO.

You simply need to issue a statement about what you do and don't support and why.
LocalDB is a core part of SSDT now and unavoidable. However it's not remotely accessible and doesn't have a scheduling component (Express has similar issues). Hence it's not generally supported by DBAs in regards to backups, maintenance, and integrity checks.
But consolidating to centralised development servers still makes sense. And now that Developer Edition is free, it's even easier to justify having more of them.


How are Postgres partitions counted for the table size limit?

So just to be clear, a table with two partitions would effectively be a maximum 64T table? This aligns with my understanding of the underlying mechanism (same as Oracle's) but I wondered if there was something I missed. – Gaius Jun 18 '16 at 17:08

Yes, that's true. Just to be clear. As @a_horse_with_no_name said partitions are implemented as different tables.

Though if you have more than 32T in a single table, even if partitioned, I would think something is funky.


how to connect SQL server from oracle in SQL developer

The last time I looked (and that's quite a number of years ago), you needed Oracle Gateway to establish such a link.

Just checked, the product is still around: Oracle Database Gateway for SQL Server.


Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server?

There is nothing built-in for Postgres.

But two very popular backup tools support differential backups:

barman
pgbackrest


how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters?

An nvarchar(max) value will be stored "in-row" if it is short enough.

The default behaviour can be modified using sp_tableoption, "large value types out of row" option. I wouldn't bother. The DB engine will manage this efficiently by itself.

As for design, there are several ways of doing this based on your model:

Will you always have both English and Arabic?
Can one be optional? If so, will one always be mandatory?
Do you expect more languages later?

1. Separate tables

That is, you can split off the separate languages into different tables. This allows table level collations rather than column level ones

It allows allows more rows per page and more chance of in-row LOB storage

PageParent

PageID int,
PageOtherInfo...

PageEnglish (note varchar may be OK here)

PageID int,
PageTitleEnglish varchar(200),
PageDescEnglish varchar(500),
PageBodyEnglish varchar(max)

PageArabic

PageID int,
PageTitleArabic nvarchar(200),
PageDescArabic nvarchar(500),
PageBodyArabic nvarchar(max)

2. Separate rows

Or have a languageID column to support several languages.This has the drawback that collation will be fixed for all languages which means poor sorting/filtering

PageParent

PageID int,
PageOtherInfo..

Page

PageID int,
LanguageCode,
PageTitle nvarchar(200),
PageDesc nvarchar(500),
PageBody nvarchar(max)


Does SQL Server's is_nullable mean anything at all?

The answer to your question is yes.
That column specifies the default nullability of a data type, which can be important if the user doesn't express a preference e.g. in a CREATE TABLE statement with an explicit NULL or NOT NULL specification. Sadly, that information is only present in the documentation for the old compatibility view sys.systypes:

allownulls (bit)
Indicates the default nullability for this data type. This default value is overridden by if nullability is specified by using CREATE TABLE or ALTER TABLE.|

For example, one can override the default NOT NULL behaviour of the sysname type:
DECLARE @T table (s sysname NULL);
INSERT @T (s) VALUES (NULL);
SELECT s FROM @T AS T;

s

NULL

The timestamp type (preferred name rowversion) has additional special behaviours due to its intended use to detect row changes. See this other answer for some more details. You can still override its nullability in the same way, but it is not possible to actually store a NULL for a timestamp/rowversion attribute.
There are several complex layers to determining attribute nullability. Rather than repeat some of that detail here, I refer you to Why you Should Always Specify Whether a Column Accepts Nulls by Phil Factor for background information and additional reading.


When you delete from heaps, allocated space may not be unallocated unless you use a table lock.
See "Deleting Rows from a Heap" from DELETE on MSDN

This is a separate problem to fragmentation (which happens of course)

Just in case, have you tried this to update usage info?

EXEC sp_spaceused 'MySchema.MyTable', 'true'

With no indexes or keys, you can't defragment it normally.
You can add/drop a clustered index, or do something like this

SELECT * INTO [MySchema].[MyTableNew]
FROM [MySchema].[MyTable]
-- ORDER BY something

-- if you like
EXEC sp_spaceused 'MySchema.MyTableNew', 'true'

DROP TABLE [MySchema].[MyTable];

EXEC sp_rename 'MySchema.MyTableNew', 'MyTable';

oops: just seen updates, my Internets has been broken on and off...


Right now we don't know the specification of your "Server" so we can't tell if space might become an issue.

Apart from that, the answers you are searching for are just a case of interpolating the available data along a time line.

Answering your questions:

From a (MySQL) database perspective: It depends. If the InnoDB page size parameter is set to 4KB then the maximum tablespace size is 16TB. For 8KB page size the tablespace can grow to 32TB. For more information on the limitations of the tables / tablespace / OS limitations visit these links:

15.8.1.7 Limits on InnoDB Tables (MySQL 8.0 documentation)
C.10.3 Limits on Table Size (MySQL 8.0 documentation)

No. Other than observing the limitations of the MySQL specifications. Others before you have stored more data for less interesting cases.
No. See above.

So space might become an issue depending on your "server" hardware.

Maintenance

As for maintaining your database, you might want to consider reading the following articles from the MySQL Documents site:

Chapter 7 Backup and Recovery

7.3 Example Backup and Recovery Strategy
7.4 Using mysqldump for Backups

Chapter 8 Optimization

8.5 Optimizing for InnoDB Tables

I guess the worst case would be if you were to lose all your data. Consider performing a simple mysqldump to have a backup of your data. Then consider implementing a backup strategy.


Optimize PostgreSQL query to avoid two time DB call for same operation

You can try to use such query:

WITH t as (
SELECT row_to_json(z)::jsonb as record FROM
(
    SELECT  t1.A, t2.B, t1.C
          ,COUNT(1) OVER () AS total_record_count
 FROM Table1 t1
 JOIN Table2 t2 ON t1.id = t2.id) z)

 SELECT record FROM t
 {=filterParam} ORDER BY {=orderParam} LIMIT(250) OFFSET({=offsetParam});

but your original query doesn't look like the best approach for pagination implementation. Among other factors depending on amount of data you have it may be more efficient to use separate queries. Because server would need to read all rows to calculate total_record_count for any offset and limit.


check when last password changed in my user db/table

I think you should be doing something like this.

Maintain username,password in separate table like 'password_change_history'
Compare the separate table password with  rm_users password for each user

2.1 if the password is not same -  changed - perform the following task.

  2.1.1 - send sms
  2.1.2 - update password_change_history with latest password for that user.

You can even add date_time column for last password change, counter for how many times changed...extra , so the new table will act as extra meta information table.

You also need to check if any new user is added or any user is deleted.


What is the most effective way to discover all running instances of SQL Server using PowerShell?

If you want something that will be useful for the future I would probably steer clear of trying to search the registry. The hives for SQL Server have changed a bit over the years and it can be troublesome to keep up with.

The method with the SqlDataSourceEnumerator is flaky at times and although I will use it, not concrete evidence that instances are on the network. I believe it depends on SQL Browser Service as well, which most of the time I find disabled.

I will utilize the WMI class win32_Service. I use this because it offers up more information about the service than Get-Service cmdlet does.

I write everything as functions generally because you can use this to actually just do daily check or verification of the service for troubleshooting.

function Get-ServiceStatus ([string[]]$server)
{
 foreach ($s in $server)
 {
   if(Test-Connection $s -Count 2 -Quiet)
   {
    Get-WmiObject win32_Service -Computer $s |
     where {$_.DisplayName -match "SQL Server"} |
     select SystemName, DisplayName, Name, State, Status, StartMode, StartName
   }
 }
}

This is a bit more than what I usually use but in case someone else comes across and wants to use it. The Test-Connection equates to ping myserver in a DOS prompt and the -Quiet flag simply just has it return true or false. This will default to 4 pings so setting -Count 2 just makes it do it twice instead.

The variable [string[]]$server is a method used to state that $server will accept an array of server names. So an example call of this function could look something like:

Get-ServiceStatus -server (Get-Content C:\temp\MyServerList.txt)

or

$servers = 'MyServer1','MyServer2','MyServer3'
Get-ServiceStatus -server $servers

EDIT

A noted comment is the above does depend on a list of servers being provided. In cases where I am not provided that list you do have a few other options.

If I am in an Active Directory environment I can use the ActiveDirectory module in PowerShell to pull a list of all the servers on the domain with Get-ADComputer cmdlet. A word of warning though make sure you use a good -Filter on large domains.
I have also simply done an IP scan (with approval) of a network that gives me the IP addresses where port 1433 was found open. I will take that IP list and utilize Get-ADComputer to find the domain computer names, then pass that into the function above

Example:

Import-Module ActiveDirectory
$sList = $ipList | Select -ExpandProperty IP
$results = foreach ($i in $sList) {
 Get-ADComputer -Filter 'IPv4Address -eq $i' -Properties * | Select Name}
Get-ServiceStatus -server $results

EDIT

The suggested edit to utilize Write-Verbose and also add in try/catch block, while that may be useful, and in most cases a code practice, I will leave that up to the person that wants to use this function to add that additional code or functionality. Just trying to provide a basic example to go on. I did add the SystemName property to the output to include the actual server name returning information, do this on other functions just generally don't use this for more than one server at a time so it slipped my mind.


Why am I getting Table scan on a partitioned table query?

The query did do partition elimination.

If you look at the tooltip, the Actual Partition Count says 3, which means it touched 3 partitions. (The Seek Predicate section also confirm this.)

The reason why it appears as a table scan is because it's a table scan with partition elimination. I'm not sure how else to explain that.

No indexes were used for this. Table partitioning is implemented deeper down in the database internal structures than an index, so partition elimination is very efficient by nature (given a suitable predicate, which you provided).

That said, the predicate you've given is suitable only for partition elimination, and (very likely) not much else because it's not very selective. If all the rows from those partitions are required, this is already close to an optimal solution -- creating a useful clustered index is usually recommended.


Enrolling for Microsoft SQL Server related Newsletters from Microsoft

Follow this feed for notifications of SQL Server updates https://blogs.msdn.microsoft.com/sqlreleaseservices/


The problem is the locking in the SGA that effectively cause the the concurrency is going down. This kind of apps make the database a close to single user platform instead of a highly scalable and concurrent accessible database. The cause of this is that the connections have to be maintained in a shared memory table where inserting and deleting means locking and causes serialization. There is a nice video series about this kind of real world performance problems connection pooling Implementing connection pooling in the demo app in the video causes a 10 fold performance upgrade ...

The spawning of the processes does cause a little overhead but that is handled well by the OS.

Next to that, the listener log wil grow quickly, causing every next connection to be slower than the previous one.

If session auditing is enabled - a default - the sys.aud$ table will explode and make your system tablespace grow very rapidly. I see many databases where this is the cause of an enormous system tablespace and sometimes even bigger than the rest of the database, because of this connection problem.


How can I list all user-owned functions?

The answer you are probably looking for is

select * from user_objects where object_type = 'FUNCTION';

However this won't return a list of packaged functions. I'm not aware of anyway of returning these other than by querying user_objects for objects of type 'PACKAGE' and then describing each in turn. Someone with a developer hat on might be able to answer the packaged function part of the question.


You are using 32-bit OS and 32-bit builds do not support the WiredTiger storage engine.

You need to use either 64-bit or change your storage engine to mmapv1 as suggested in screenshot by passing parameter explicitly. --storageEngine=mmapv1

check here https://docs.mongodb.org/manual/administration/production-notes/


how can I see the arguments passed to a stored proc on Azure?

Today, I think you would have to add logging to the stored procedures (or to the calling application). Trace / profiler are not supported on Azure SQL Database, and never will be.

At some point in the future, you should be able to use database-scoped Extended Event sessions. Bob Beauchemin talked about this here and here, and there is an official marketing statement from Microsoft's Tiffany Wissner here (but nothing technical):

Preview Available for Latest Version of Azure SQL Database

If you have an Azure database and you happen to be on the most recent public version (13.0.15 as far as I can tell), you can start to see some evidence of her statements about Extended Events. For example, I have a context menu item that I can't do anything with yet:

So, it's coming, just not yet. Until then I can't really think of a decent way to obtain runtime parameter values for called procedures unless you change the application to retrieve actual, post-execution plans and log the XML output somewhere. And if you're going to do that, well, you may as well just add the logging of the actual values - much easier to consume.

This answer, of course, is subject to change as it gets older.


psql has a flag that controls the way null is outputted -P 'null=\N'


Why does CURRENT_LOAD_SOURCE() return "Batch No. 1" instead of the actual source file name?

This certainly looks like a bug, whether you want to characterize it as an oversight, unfinished feature, or something else.

Vertica support tells me this is a known issue and a fix is being tracked as part of VER-36735. (Unfortunately, their issue tracker is not visible to the public.)

The problem appears to be related to the LOCAL clause, which is used to load files that are on the client machine as opposed to the server.

If you remove the LOCAL clause (and put the files you are loading on the server), then CURRENT_LOAD_SOURCE() will return the file name of the file being loaded, as expected.

COPY table1 (
    col1,
    col2,
    file_name AS CURRENT_LOAD_SOURCE()
)
FROM :src_file  -- no LOCAL!
REJECTED DATA :rejected_file
EXCEPTIONS :exceptions_file
SKIP 1;

If you're stuck with the LOCAL, then your best bet is to simply pass in a new variable (e.g. :file_name) that you explicitly set with the name of the file you are loading. You'll just need to be sure to provide the base name of the file (as opposed to the full path), to match the intended behavior of CURRENT_LOAD_SOURCE().


I've found answer around Key/Value pairs terms, and the capacity involved is the inner-join:

SELECT DISTINCT
   Prospects.id,
   Prospects.prenom,
   Prospects.nom,
   Prospects.date,
   Prospects.departement
FROM
    Prospects
    INNER JOIN Pairs AS p1 ON Prospects.id = p1.id
    INNER JOIN Pairs AS p2 ON Prospects.id = p2.id
    INNER JOIN Pairs AS p3 ON Prospects.id = p3.id
    INNER JOIN Pairs AS p4 ON Prospects.id = p4.id
    INNER JOIN Pairs AS p5 ON Prospects.id = p5.id
    INNER JOIN Pairs AS p6 ON Prospects.id = p6.id
WHERE TRUE
AND (( p1.name like 'name%'    ) AND ( p1.value LIKE '%XXX%'))
AND (( p2.name like 'forname%' ) AND ( p2.value LIKE '%CAMILLE%'))
AND (( p3.name = 'codePostal'  ) AND ( p3.value = 'XXXXX'))
AND (( p4.name = 'email'       ) AND ( p4.value LIKE '%XXXX%' ))
AND (( p5.name like 'tel_%'    ) AND ( p5.value LIKE '%XXXXXX%'))
AND (( p6.name = 'projet'      ) AND ( p6.value LIKE '%gociat%' ))
AND ( etape IN (-1,3,8 ))

The lines INNER JOIN...id and AND (( p... )) are generated by analysis of the request dynamically made by the GUI. The keyword TRUE is added to make all AND... lines identical, the first isn't different from the others.


Do you have SQL Server Profiler installed? Try running a trace to capture any queries that may be causing excessive locks or resource problems throughout the day.


You got the error from line 5. This is line 5:

DBMS_DATAPUMP.ADD_FILE(
  handle => hdnl, filename => 'tab1.dmp',
  directory => 'DATA_PUMP_DIR',
  filetype => dbms_datapump.ku$_file_type_dump_file
);

If the specified dumpfile already exists, this line throws the above error. You can add reusefile => 1 to overwrite the existing file and avoid this error.

DBMS_DATAPUMP.ADD_FILE(
  handle => hdnl, filename => 'tab1.dmp',
  directory => 'DATA_PUMP_DIR',
  filetype => dbms_datapump.ku$_file_type_dump_file,
  reusefile => 1
);


If it fits within the rules of normalization, then 1:1 relationships can be normalized (by definition!) - In other words, there is nothing about 1:1 relationships that make it impossible for them to obey the normal forms.

To answer your question about the practicality of 1:1 relationships, there are times when this is a perfectly useful construct, such as when you have subtypes with distinct predicates (columns).

The reasons you would use 1:1 relationships depend on your point of view.  DBAs tend to think of everything as being a performance decision.  Data modelers and programmers tend to think of these decisions as being design or model oriented.  In fact, there is a lot of overlap between these points of view.  It depends on what your perspectives and priorities are.  Here are some examples of motivations for 1:1 relationships:

You have some subset of columns that are very wide and you want to
segregate them physically in your storage for performance reasons.
You have some subset of columns that are not read or updated
frequently and you want to keep them apart from the frequently used
columns for performance reasons.
You have some columns that are optional in general but they are
mandatory when you know that the record is of a certain type.
You have some columns that logically belong together for a subtype
and you want to model them to fit well with your code's object model.
You have some columns that can only apply to some subtype(s) of an
entity super-type, and you want your schema to enforce the absence of
this data for other subtypes.
You have some columns that belong to an entity but you need to protect
these particular columns using more restrictive access rules (e.g. salary
on an employee table).

So you can see, sometimes the driver is performance, sometimes it is model purity, or just a desire to take full advantage of declarative schema rules.


I wouldn't worry so much about the fragmentation over 10% but over 90% fragmentation on a clustered index is a lot...but are you seeing any performance issues with that specific table when it has that much fragmentation? (If it's not a big table, it may not even be a concern.)
If you are experiencing performance issues with higher fragmentation then you can look into rebuilding (or oftentimes more recommended reorganizing because of its similar benefits while being less heavy of an operation) the index.
For a point of reference, if you had a table with only 10 rows in it, each row on its own data page, then you'd have 90% fragmentation  but the performance difference would be negligible compared to if you rebuilt it and it had 0% fragmentation. (This is a very basic example, and holds true for much larger tables to a point.)
By the way, I find this Brent Ozar Article by Kendra Little a good read on rebuilding vs reorganizing indexes.


http://support.oracle.com

There are at least 3 notes about these errors.

"The issue has been fixed in 11.1.0.7 and 11.2."


Could an update on all statistics separately cause bad performance?

Question: I am not too familiar with stat maintenance, so I am wondering, is it really necessary to run a weekly job separately to update all statistics?

Yes, you need to maintain your statistics if you have large tables. Read here how Auto update statistics works:

  Auto Update Statistics Option Statistics are checked before query
  compilation or before executing a cached query plan. Statistics are
  considered out-of-date when:

  There was a data change on an empty table. The number of rows in the
  table was 500 or less at the time of statistics creation and the
  column modification counter of the leading column of the statistics
  object has changed by more than 500 since then.

  The table had more than 500 rows when the statistics were gathered,
  and the column modification counter of the leading column of the
  statistics object has changed by more than 500 + 20% of the number of
  rows in the table when the statistics were gathered.

  A table in TempDB with less than 6 rows had at least 6 row
  modifications.

Question:  Don't statistics get updated after a certain amount of changes or during an index rebuild anyway?

  Rebuilding an index, for example by using ALTER INDEX … REBUILD will
  also update index statistics with the equivalent of using WITH
  FULLSCAN unless the table is partitioned, in which case the statistics
  are only sampled* (applies to SQL Server 2012 and later). Rebuilding
  indexes does not update column statistics.

  Reorganizing an index, for

    example using ALTER INDEX … REORGANIZE does not update any statistics.

Question: Why would recalculating them separately cause an issue?

Might be because your sample rate is to low and the statistics is not accurate. Use FULLSCAN to see if that helps. It might also be that the Cardinality Estimation misinterprets the statistics (can happen in SQL Server 2014), if this is the case try trace flag 9481 to disable cardinal estimator in SQL Server 2014.

The StatisticsSample parameter of Ola Hallengrens script give you this:

  Indicate, as a percentage, how much of a table is gathered when
  updating statistics. A value of 100 is equivalent to a full scan. If
  no value is specified, then SQL Server automatically computes the
  required sample.


Include non-inserted rows as result of INSERT INTO ... RETURNING

I would simply separate the query logic and the insert logic:

with vals (a,b) as (
  VALUES (1, null), (null, 2), (2, 3)
), new_rows as (
  insert into all_sums (sum)
  select a + b
  from vals
  where a + b is not null
)
select a + b
from vals;

Online example


Equivalent functions in MySQL that exist in SQL Server

I believe SIGNAL (introduced in MySQL 5.5) is what you want, for automatic logging.

However, the current implementation cannot use the various conditions such as SCHEMA_NAME after the SIGNAL executes:

  Other condition information items can be set, but currently have no effect, in the sense that they are not accessible from error returns. For example, you can set CLASS_ORIGIN in a SIGNAL statement, but cannot see it after SIGNAL executes

The only available information is the MYSQL_ERRNO and MESSAGE_TEXT, so if you want the schema_name, you'll have to include that in your MESSAGE_TEXT.

Update From the SIGNAL/RESIGNAL worklog

  Since /include/mysqld_error.h says "#define ER_NO_DB_ERROR 1046"
     MESSAGE_TEXT will be 'No database selected'.
     Or, if language = German, 'Keine Datenbank ausgewhlt'.
     Things get trickier if the default message has '%s' in it.
     We won't support the above.

So you can set a signal to:
SIGNAL SQLSTATE '22012' SET MYSQL_ERRNO=1365, MESSAGE_TEXT='Division by 0' explicitly, but it won't automatically inherit the text and errornumber just from the sqlstate.

Caveat:
 I was able to get some ERROR information using RESIGNAL. Consider this:

DROP PROCEDURE IF EXISTS p;
DELIMITER |
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  DROP TABLE xx;
END|
DELIMITER ;
CALL p();

Query OK, 0 rows affected (0.00 sec)

However with this:

DROP PROCEDURE IF EXISTS p;
DELIMITER |
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL;
  END;
  DROP TABLE xx;
END|
DELIMITER ;
CALL p();

ERROR 1051 (42S02): Unknown table 'xx'

Example (slightly modified) found from RESIGNAL documentation

Update2

Alas, it's not yet possible to create a generic exception handler. Here is a 6-year-old feature request for it: http://bugs.mysql.com/bug.php?id=11660

The (relatively) good news is, the fix is to create the ability to view the diagnostics stack. This is good because the pre-requisite for this feature is the SIGNAL/RESIGNAL mentioned above. The bad part about it is the worklog for the diagnostics stack is not publicly visible yet.

So for now you'd have to create an exception handler for each state your procedure would come across to log into a table, and only log the error number (hard-coded, as it's not available by the handler) and maybe the hard-coded message text.


How do I reduce SharePoint 2010 SQL server log files?

If your databases are in full recovery mode you will need to backup the transaction logs, when that is done you can shrink the log files and setup a regular log backup to minimize growth and keep good backups. I would reccomend that you read this: https://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx but first things first.

The first step is to find the largest log files which you can shrink without backups - So you have to find databases which are in simple recovery mode:

select d.name, d.recovery_model_desc as recovery,d.log_reuse_wait_desc as wait_desc
,f.name as [LogicalName]
,CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB]
from sys.databases d
 inner join sys.master_files f on d.database_id = f.database_id
 where f.type = 1
 order by [Total Size in MB] desc

This will give you an output similar to this:

name    recovery    wait_desc   LogicalName Total Size in MB
dba     FULL        NOTHING     dba_log     4693.50
dbb     SIMPLE      NOTHING     dbb_Log     1024.00

For each file which is in SIMPLE recovery and not having wait_desc = "active transaction" you can simply do

use database [NAMEFromQuery];
GO
DBCC SHRINKFILE (N'LogicalNameFromQuery' , Truncateonly);

And then figure out why the log is growing.

Now for the databases in FULL Recovery mode you can do

  ALTER DATABASE [NAMEFromQuery] SET RECOVERY SIMPLE WITH NO_WAIT

and then truncate the log as above to get out of trouble but remember which databases were in full recovery model.

Now read the article linked above and start making good backups.


Is this a good strategy for importing a large amount of data and decomposing as an ETL?

If you are confident in the integrity of the data being imported, it may be a good idea to disable all the constraints to your database before beginning your inserts and then re-enabling them after the fact.

See this helpful stack overflow answer from awhile back: Can foreign key constraints be temporarily disabled using T-SQL?

This will save you the head ache of having to worry about layering the inserts in order to respect the existing constraints of the database you are loading into.

In terms of the actual inserts themselves, I'd be on the side of not using cursors.  Not only is the process slow but they take up a large amount off memory and create db locks.  If you are cursor-ing through a very large amount of rows you also run the risk of very quickly escalating the size of the database logs.  If the server is only an average one then, space may eventually be a concern.  Try to consider a more set based approach when doing the additional inserts needed for your process.

example, if you can do this:

insert into t1 (col1)
SELECT col1 FROM t2

instead of this:

...
insert into t1 (col1) values ('foo');
insert into t1 (col1) values ('bar');
insert into t1 (col1 values
...


How to design tables for dynamic payroll items

You could try to build a fully table-driven rules engine, sometimes also called an inference engine, but you would be much better off buying one than trying to build your own, because they are very complex.

If the cost and/or complexity of a fully table-driven rules engine is too high for your organization, then you can build a compromise solution.  The compromise involves mixing tables and procedural (code based) rules.  The objective is to optimize the balance between flexibility and maintainability.

The way to achieve this is to carefully examine all of your current rules, taking into consideration as much as possible any potential rules that aren't used now, but which might come into effect in the future.  This analysis should give you a set of rules for doing your payroll calculations.

You then need to break these rules down into two categories: (i) rules to be based in procedural code and (ii) rules to be table-driven.  The best way to do this is to keep scalars in tables and more complex rules in code.  For example the fact that one amount can be based on another amount is a good candidate for a code-based rule.  The fact that the derived amount is 20% of the base amount is a good candidate for a table-driven rule.

For practical purposes, your rule tables will have a structure that includes the following:

Rule Type: This is a value that tells your procedural code which function/code-based rule uses the scalar data in a particular record.
Scalar Value: This is a numeric value that has a meaning which is understood by the procedural code indicated by the Rule Type.
Business Identifier: This is an indicator of which business element the rule applies to.  Like the Scalar Value the Rule Type indicates which procedural code uses the rule record and that procedural code knows what to make of the Business Identifier.  It might be a department number, an employee ID, a job code or whatever else your rules require to link payroll amounts to employees.  Your rules table may need more than one Business Identifier, depending on how your rules work, or your requirements might dictate that business identifiers are normalized out into an intersection table.
Effective Date: One thing you can bet on is that your payroll scalars will change over time.  Your system is going to need a record of what the rules used to be so you can make corrections to your payroll.  Also, for the sake of the sanity of your HR and IT staff, you need to be able to record new rules before they go into effect.
Expiry Date: This completes the effective date range of the rule.  I'd recommend using a max collating value so that every record has a fully qualified effective range.  That will make your queries much simpler.

One of the realities of a solution like this is that you will have to come back to build new code-based rules from time to time as your organization's payroll policies change.  Don't be discouraged by this.  Even if you had a fully table-driven rules engine, changing payroll policies would entail making changes to the rules data.  Business changes entail system changes - that's a fact of life.


Determining SQL Server memory requirements

I'm not sure I believe there was ever an actual issue. Just because max server memory was set to 2 petabytes doesn't mean it would ever use that much. This is the functional equivalent of setting it to infinity, and just means you trust SQL Server to use as much memory as it needs.

Setting it to 500 MB seems artificially low to me. While your database is currently 128 MB on disk, you are probably not running any query patterns that lead to any performance issues even with that limit on memory. But when SQL Server needs more than 500 MB of memory, you're going to start noticing. SQL Server is designed to behave as if it is the only server on the machine, so it will take memory as it needs it, and won't release it unless the operating system needs it and applies pressure to SQL Server (discussions of LPIM aside). Restricting SQL Server's memory this way (or taking it away) will just cause performance degradation as it is much more expensive to page data in and out of memory, onto disk, etc. which will most certainly be required as your database grows. Think about trying to put a pig into a tube sock - eventually you're going to need a bigger sock. Why wait until the current sock rips?

I suggest a higher limit, but I can't tell you what that would be. You didn't disclose how much memory is on the server, whether it is x86 or x64, or what else is going to be running there. If the server is dedicated to SQL Server, I see absolutely no reason to save memory - what are you saving it for?

For a bit of an anecdotal observation, I don't think I've ever seen any instance of SQL Server on any kind of physical or virtual machine limited to 500 MB, and I have very serious doubts that this could possibly be the optimal setting for you in the long run.

I'd also suggest doing research into SQL Server settings and then changing them, instead of the other way around. This situation sounds a lot like saying "What does this button do?" after pressing it...


Can we restrict Maximum Job history rows for particular job/jobs in SQL Server 2012?

Not through the UI, but since the sysjobhistory table is not a system table, you could manage this manually with a simple scheduled job that cleans up. Assuming you always want to keep fewer rows for the exceptions than the rest.

First, let's assume you have some query against sysjobs that will return the jobs you want to be more restrictive:

SELECT job_id FROM dbo.sysjobs WHERE <...>;

Next, for simplicity, let's say that for all the jobs in that list, you only want to keep the 10 most recent rows.

DECLARE @rows_to_keep int = 10;

;WITH history AS
(
  SELECT rn = ROW_NUMBER() OVER
    (
      PARTITION BY job_id
      ORDER BY run_date DESC, run_time DESC
    ),
    job_id, run_date, run_time
    FROM msdb.dbo.sysjobhistory
    WHERE job_id IN
    (
      -- query from above
      SELECT job_id FROM dbo.sysjobs WHERE <...>
    )
)
DELETE history
WHERE rn > @rows_to_keep;

It gets a little more complex depending on the answers to these comments:

  If you have two jobs where you want to keep only the last 10 rows, and one job has 2 steps and the other has 5 steps,  are you keeping 10 rows each (so 5 executions of the first job, and 2 executions of the second), or 10 total executions each (so 20 rows for the first job, and 50 rows for the second)? Well, disregarding step 0 for simplicity.

Also if you want different rules for different jobs, you can just create your own table:

CREATE TABLE dbo.CustomJobRetentions
(
  job_id uniqueidentifier,
  rows_to_keep int
);

Then you can incorporate that into your query:

;WITH history AS
(
  SELECT rn = ROW_NUMBER() OVER
    (
      PARTITION BY h.job_id
      ORDER BY h.run_date DESC, h.run_time DESC
    ),
    h.job_id, h.run_date, run_time, c.rows_to_keep
    FROM msdb.dbo.sysjobhistory AS h
    INNER JOIN msdb.dbo.CustomJobRetentions AS c
    ON c.job_id = h.job_id
)
DELETE history
WHERE rn > rows_to_keep;

Please try this out on a test system first.


Msg 227, Level 15, State 1, Line 20 "Modify" is not a valid function,
  property, or field.

XML is case sensitive and in SQL Server so are the functions used on the XML datatype. Change Modify(... to modify(....

If you have untyped XML you will still get an error.

  Msg 2356, Level 16, State 1, Line 21 XQuery
  [AllUserData.tp_ColumnSet.modify()]: The target of 'replace value of'
  must be a non-metadata attribute or an element with simple typed
  content, found 'element(nvarchar1,xdt:untyped) ?'

To fix that you need to specify the text() element of the node you want to update.

You could also make use of the exist() function in the where clause instead of extracting the value.

update dbo.AllUserData
set tp_ColumnSet.modify('replace value of (/nvarchar1/text())[1] with "1996"')
where tp_ColumnSet.exist('/nvarchar13/text()[. = "623"]') = 1;

From comment:

  The XQuery modify method is not allowed on sparse column sets.

You need to update the value using the regular column instead of the column set.

update dbo.AllUserData
set nvarchar1 = N'1996'
where nvarchar13 = N'623';


Is it possible to display this restriction in an ER diagram?

The short answer is "no."

Entity relationship diagrams can be used to show constraints based on the relationships between entities.  However, the particular constraint you want to show (that bookings do not overlap in time) is a constraint between rows in the same table, not between two or more tables.

The only single table constraint that can be illustrated in most common entity relationship diagrams is uniqueness, since ER diagrams can illustrate a primary key.


Assuming that R means Read and W means Write, then the work you're proposing is like this:

W1(x) C

That's it. Reads  are irrelevant for recovery. And a crash denotes the end, you can't have a write after crash. So the task of recovery is to redo the one write and then undo it, since there is no Commit.

The algorithm is very simple:

Redo everything, in forward order
Undo anything not committed, in reverse order, generating a compensating write for every action being undone.

A good explanation is the ARIES paper.

See also How to read and interpret the SQL Server log on my blog.


Oracle 12 Grid installaton falsly detecting pre-existing ASM installation

I think I managed to figure out the problem.

There was a registry entry under hkey_local_machine/software/oracle which needed to be removed manually. Once that was done, the install was able to proceed.


Duplicate UNIQUE KEY error after ROLLBACK in MySQL

In one of your comments to your own question, you're saying you're using MyISAM.

However, MyISAM does not support transactions (see ref. table). Therefore, it will always autocommit, whether you try to turn it off or not.

If you want to use transactions, you need to use an engine that supports it, such as InnoDB.

EDIT (following comments and additional information -- I must admit I hadn't realised you were also using a second table until @RolandoMySQLDBA pointed it out):

As the documentation says: "If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode."

Since a second table (seudonimos_consulta, using MyISAM) is involved in the transaction, via a trigger, what's inserted after the first INSERT in that table isn't rolled back. Hence, #1062 - Duplicate entry 'Agatha Christie' for key 'seudonimo' would refer to the second table, seudonimos_consulta.seudonimo, not seudonimos.seudonimo.


Your question may be a candidate for closing as "Primarily opinion-based" but yes, I'd follow your proposed pattern, to a point. What I tend toward is identifying the most frequent parameter combinations and coding discrete procedures for them.

It becomes unfeasible with a large number of combinations of parameters to code and maintain for each, although you could code-gen them. Instead, deal with the lesser combinations with dynamic SQL, or static SQL with OPTION RECOMPILE:

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
  AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CompanyName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (c.Country = @country OR @country IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER  BY o.OrderID
OPTION (RECOMPILE)

  The effect of all the @x IS NULL clauses is that if that input
  parameter is NULL, then that AND-condition is always true. Thus, the
  only conditions that are in effect are those where the search
  parameter has a non-NULL value.

  As far as maintainability goes, it's difficult to think of a better
  solution for the search conditions at hand. It's compact, easy to read
  and to extend. And performance? Very good as long as you include the
  query hint OPTION (RECOMPILE). This hint forces the query to be
  recompiled each time, in which case SQL Server will use the actual
  variable values as if they were constants.

Query and quote courtesy of Erland Sommarskog's reference article on the topic, Dynamic Search Conditions.

By coding specifically for the common cases you reduce the overhead of RECOMPILE incurred on the less frequent cases. That said, I confess I'm getting good value from more liberal application of RECOMPILE recently. In the era of more cores & hyper-threading, the recompilation overhead can (but certainly not always!) be preferable to one-size-fits-all execution plans.


Is it possible to refresh PgAdmin 4 query without restarting a new query after disconnect?

Re execute the query without closing it. It will give the result. it is working for me


To get the list of columns in default order, without the bad column:
SELECT string_agg(quote_ident(attname), ', ' ORDER BY attnum)
FROM   pg_attribute
WHERE  attrelid = 'myschema.mytable'::regclass
AND    NOT attisdropped  -- no dropped (dead) columns
AND    attnum > 0        -- no system columns
AND    attname <> 'bad_column'  -- case sensitive!

Or just WHERE  attrelid = 'mytable'::regclass if you trust the search path to resolve to the right schema.
quote_ident() adds double-quotes where necessary.
I asked the same question in 2007 on pgsql-general. It was Postgres 8.2 back then. Sweet memories ...
Related:

How to check if a table exists in a given schema


Run a SQL statement on all databases monitored by OEM


Using SQL Server 2012 Standard with Bitlocker

You need to use the account that the SQL Server runs as to enable Bitlocker.


Storing a Shipping Address Best Practice

The Shipping Address is a property of each and every order, not of the customer. Even if it was a property of the Customer, it would be necessary to know where past orders were shipped to if a customer relocated.

Therefore all that can be stored as a Customer property are the Default Shipping Address (and Default Billing Address), for pre-populating each order and invoice as they are created.  This is not a denormalization, because it directly reflects and captures the required business rules.

Now that the historical accounting records are taken care of,  there should be a CustomerLocation table with FK's to three Address records in an Address table: Physical Address; Default Billing Address; and Default Shipping Address. The first of these records should be associated with an EffectiveDate, so that again a historical record exists as changes occur.


Filtering by minimum value per group problem

To get the desired result from your sample table,

select tgroup, name, id
from
(
    select tgroup, name, id, row_number() over (partition by tgroup order by id) rn
    from (<<your subselect>>)
 )
 where rn = 1

I've renamed group to tgroup as group is a reserved word in SQL.

SQLFiddle

As for your real query, please first make it readable.


How to remove Windows NTFS Compression from Oracle Database datafiles?

As Paparazzi said for MSSQL, just shutdown the database and then decompress the files and startup again.  If your architecture allows, you could offline a tablespace at a time and then just decompress the datafiles for that tablespace.  Eventually though you will need do do the system tablespace and will need to shutdown the whole system.


MariaDB Replication - Replicate only specific tables and views

On the master host you have to add to the [mysqld] section of the my.cnf:

. . . . .
binlog_do_db = mydatabase
. . . . .

Master service should be restarted then.
You can rename the replica's database but you can't rename tables, views and other objects. If you want to avoid collisions between local and replicated objects you have to replicate all the objects into the different database.
On the slave host you have to add to the same section:

. . . . .
# DB name mapping (if you need that)
slave.replicate_rewrite_db = mydatabase->mydb
# DB objects filtering - only listed objects will be replicated
slave.replicate_do_table    = mydb.mytable
slave.replicate_do_table    = mydb.myview
. . . . .

Slave service should be restarted too.
Next you have to make an initial copy of the data (remotely):

mysqldump --host 11.22.33.44 -u username -ppassword --master-data=2 \
--quick --hex-blob --add-drop-table --create-options --extended-insert \
--disable-keys --allow-keywords mydatabase mytable myview > mdump.sql

Next you have to create an empty target DB on the slave and fill it by dumped data:

mysql -u username -ppassword -e 'CREATE DATABASE mydb;'
mysql -u username -ppassword mydb < mdump.sql

Then you have to create the slave process on the slave host. mdump.sql file contains binlog filename and position in the header so you can copy and paste them:

mysql -u username -ppassword -e 'CHANGE MASTER "slave" TO \
                                 master_host="11.22.33.44",
                                 master_user="ruser",
                                 master_password="rpass",
                                 master_log_file="filename",
                                 master_log_pos="position";'

Now you can start the slave and check its state:

mysql -p username -ppassword -e 'START SLAVE "slave"; SHOW ALL SLAVES STATUS\G'


"Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060" while altering the table

If you have previously removed or modified columns on this table, you may need to reclaim space before this operation will succeed. SQL Server does not always/usually immediately reclaim space for dropped or altered columns.

If the previous operations were all removals (or alterations) of variable-length columns, issuing DBCC CLEANTABLE ought to be sufficient. Otherwise, you will need to rebuild the table. You can do this by rebuilding the clustered index:

ALTER INDEX PK_table
ON dbo.table
REBUILD
WITH (ONLINE = ON); -- Enterprise only, optional


MySQL Replication - Accidentally Dropped Database on Slave

Yes, you should be fine.

You can DROP DATABASE IF EXISTS mydata; on the Master should replicate to the Slave, execute the IF EXISTS part on the Slave, do nothing, and be successful without erroring out.

I have done this before.

As an alternative, you could also run this on the Master:

SET sql_log_bin = 0;
DROP DATABASE mydata;
SET sql_log_bin = 1;

or

SET sql_log_bin = 0;
DROP DATABASE IF EXISTS mydata;
SET sql_log_bin = 1;

I have done this before as well.


Using transactional replication to roll back from SQL server 2014 to 2008R2

I have recently implemented your scenario for one of my company's critical - ordering & sales system's migration from 2008 to 2014.

You have 2 options for rollback depending on the downtime that you are allowed -

Upgrade :

Follow the pre-migration steps
setup DB mirroring from 2008r2 to 2014 in async mode.
Before cutover, change the mirroring to sync mode and make sure that mirroring queues are caught up before doing failover.
Perform mirroring failover. This is one way . You cannot failover to lower version.
disable all the application logins on both the servers (script out disable and enable). This is very important since you want to make sure that both the copies (2008r2 and 2014) are exactly same while you are configuring rollback options.

Rollback plan (Going backwards comes with a price that you need to agree with the business and application stake holders) :

Option 1 : Use transactional replication if you can accept downtime. The downtime will be the amount of time it takes to take a full backup of your 10TB database.
You can setup distribution database on 2014 ahead of time.
During backup, no data changes should be done on either side.
Once the backup is done, the replication will just work fine without doing snapshot as the LSNs are same on both sides.
I did not go with this !

-

Option 2 : Use Peer-to-peer replication if you need very minimal downtime.
The downtime here is equal to the time it takes to create the peer and related jobs which depending on the size of tables (article) will vary. In my case for a 700GB database, it took 4 mins. I scripted entire replication and used .bat scripts to fire replication scripts on both sides 2014 and 2008.
P2P needs to have both dbs exactly same. Since you disable all the logins and not activity is happening, this is my preferred (and now a defacto) option wherein I have to provide rollback option.

Once you configure rollback option (either T-rep or P2P), if you run into major issues, you can just point your application to the 2008R2. What we did is we use Failover Partner, this way we just shutdown the 2014 server and application will automatically redirect to 2008 server.

Important Notes:

I used P2P replication as downtime had to be less than 15 mins.
Above both options requires that all the tables have primary keys defined. Tables without PKs e.g. staging tables needs to be discussed with stake holders and excluded.
We kept replication for only 1 day as fallback/rollback plan. If we did not see any issues after migration (done during weekend) and survive 1 business day (monday) we tear down replication and setup DBs into AG in 2014.
We did not change the compatibility level to 2014. We kept it same as 2008. I would highly recommend to do that unless you have thoroughly tested your application on 2014 in downstream environments. Even if you change the   compatibility level to 2014 and you see query regressions you can enable TF 9481 to tell optimizer to use legacy CE.

Fair Warning: I have implemented above in production with full success - but I did extensive testing and invested time in automating the entire process and hence I can confidently give the process and timings. Highly suggest you to death test the process & document it before you implement it in PROD.


High CPU for SQL server running on VmWare

thats seems to be a lot of CPU being burned and should be approached IMHO.
Do you have any performance issue?
I would try sp_blitz https://www.brentozar.com/blitz/
and if you have querystore enabled, take a look at queries that eat up the CPU.


Clarification on SQL Server 2016 Always Encrypted

AE is intended to protect sensitive data end-to-end. That's why keys are held by the client, not server. In SQL Server 2016 granularity is at the column level.

If you want to AE the entire database, you'd have to do it one column at a time. That doesn't really make sense and doesn't fit the AE scenario unless all data in your database is sensitive. Theoretically possible but pretty rare in reality. You've also made your database extremely limited in query capabilities and perf is pretty much out the window.


Why this query returns rows that I don't want to be there

Yes @jon-seigel you're right and thanks for your suggestions didn't see it before :( I add some parentheses and now it works see the right query below:

SELECT * FROM (default_users) LEFT JOIN default_profiles ON
    default_profiles.user_id = default_users.id WHERE
    (default_users.group_id = '3' OR default_users.group_id = '4' OR
    default_users.group_id = '5') AND (default_users.email LIKE '%polar%'
    OR default_users.username LIKE '%polar%') ORDER BY default_users.id DESC


Error when start to attach mdf - : parameter name: ncolindex actual value was -1 (Microsoft.Sql Server.Grid Control)

Try to login with Admin account (not sql account)


How can I resolve SSIS Excel Connection Manager Error 0xC0209303?

There were 2 issues that were preventing the package from running on the server. Below are the 2 issues and the solutions I found.

The package is executed by an application that uses the 64-bit DTexec utility by default but the package needs to be run using the 32 bit version of the utility to be able to properly access the Excel file through the Excel connection manager.

I created a "wrapper" SSIS package that uses an Execute Process Task that calls the 32-bit (instead of 64 bit) DTExec utility and passes the command to open the original  package.

I also needed to install the 32-bit version of the Microsoft Access Database Engine 2010 Redistributable.

Further Reading:

Microsoft.ACE.OLEDB.12.0 is not registered (Stack Overflow)


Is it correct, order of where clause doesn't matter when it is used with join?

The order of items in the where clause should not make a difference, especially if you use the preferred join syntax as follows:

select a.col1,
       b.col2
 from table1 a
 join table2 b on b.col1 = a.col1
where a.col3 = 10

This keeps the join conditions separated from the filters.


Repeating rows of date intervals Between dates Firebird

Your question is not 100% clear, and I'm proposing a tentative answer using PostgreSQL (I don't have FireBird installed at the moment). The concepts are the same regardless of database make.

1st: I would represent data in an easier way:

CREATE TABLE expenses
(
    description varchar,
    amount numeric(10,2),
    dateadd date,
    times_per_month numeric(10, 0)
) ;

INSERT INTO expenses
    (description, amount, dateadd, times_per_month)
VALUES
    ('SALARY',    1000.00, '2017-03-26', 1),
    ('DINNER',      50.00, '2017-03-26', 4),
    ('BUSTICK',     75.00, '2017-03-26', 2) ;

From this point on, and assuming you would like this data to appear under the heading MARCH (and not APRIL, MAY, ... forever), you would just use something very simple: a multiplication.

SELECT
  to_char(dateadd, 'MON') AS month,
  description,
  (amount * times_per_month) AS subtotal
FROM
  expenses ;

And you'd get:

| month | description | subtotal |
|-------|-------------|----------|
|   MAR |      SALARY |     1000 |
|   MAR |      DINNER |      200 |
|   MAR |     BUSTICK |      150 |

Check it at: http://sqlfiddle.com/#!15/34efd/1/0


How to connect external database to AppEngine?

Roughly speaking, no.

The big demand of GAE is the integrated, scalable, cloud backend. Without the backend hardware, it's functionally Django on bigtables. If the customers (quite rightly) don't like vendor lockin, GAE is not for them.

What features of GAE would you keep, if not for the backend?


Surprising results for data types with type modifier

This is due to relation attributes (defined in pg_class and pg_attribute, or defined dynamically from a select statement) supporting modifiers (via pg_attribute.atttypmod), whilst function parameters do not. Modifiers are lost when processed through functions, and since all operators are handled via functions, modifiers are lost when processed by operators as well.

Functions with output values, or that return sets of record, or the equivalent returns table(...) are also unable to retain any modifiers included in the definition. However, tables that return setof <type> will retain (actually, probably typecast to) any modifiers defined for type in pg_attribute.


I get this effect only when applying a query.

A table, view, or other direct structure seems to export into SQL fine.

The SQL option seems is not available to a query result set, very sadly.


Postgres: difference between CTE and temporary table

To add to what others have said. Temporary tables can also have primary keys, constraints, indexes, etc. whereas CTEs cannot.

On the flip side, you can do some pretty neat tricks with CTEs that would be harder, I think, if done with temporary tables-- such as chaining them to perform deletes, inserts, and selects all in one statement. There are some nice examples of "cool-CTE-tricks" here.


Setting the column width of a column in a table created by logparser

Its possible to get somewhat close to this. First precreate the table

IF object_id('[LogparserTest].[dbo].[ZipCodes]') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[ZipCodes]
END

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [varchar](3) NOT NULL,
    [Zip] [varchar](6) NOT NULL
);

Note that you have to use varchar columns one character wider than your input.

Now run this command, note the lack of -createtable, but you can keep that there if you desire. It does't recreate an existing table:

  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT SUBSTR(Field1, 0, 1) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -cleartable -transactionRowCount:0  -ignoreMinWarns:OFF

If you create the correct length columns as char or varchar you get the following error for every row:

An error occurred while uploading data to SQL table
  SQL State:     22001
  Native Error:  0
  Error Message: [Microsoft][ODBC SQL Server Driver]String data, right
  truncation

However, note that SELECT MAX(LEN([CountryCode])), MAX(LEN(ZIP)) FROM [LogparserTest].[dbo].[ZipCodes]; Returns 2 and 5. Tracing the bulk insert with extended events shows that things are being paramaterized as follows so I don't know what's going on:

(@Param000004 char(2),@Param000005 char(5))INSERT [LogparserTest].[dbo].ZipCodes VALUES(@Param000004,@Param000005)

So at this point you still need to perform an ALTER TABLE ALTER COLUMN command to alter each column, so it doesn't actually buy you much.


Repairing buggy query server-side – without changes in the app

You are facing the error Ambiguous column name… because the app is quering your database this way:
SELECT
[Name],
[Salary],
[Name],
[Age]
FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.EmployeeID = B.EmployeeID

As you can see there are two [Name] in the SELECT. You will resolve the issue by recreating the query this way:
SELECT
A.[Name],
[Salary],
B.[Name],
[Age]
FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.EmployeeID = B.EmployeeID

Yes, you are facing this because, as you said, you are adding some columns in your DB. You have 2 choices:

Contact the vendor and make it aware of the Ambiguous column name… error
Recreate the structure of your database and avoid using column names that were already used.


Choosing the right storage block size for sql server

The method is the one used in the article that you linked in your question: test it. You can capture a representative workload for your application/database and replay that workload on servers that have different cluster sizes. What you will find out is that SQL Sever works best with 64KB clusters, because of the way it reads data from disk (read-ahead).

However, you don't have to do that: setting a 64KB cluster size is an established best practice, as clearly stated in the article that you refer to. From the article:

  The file allocation unit size (cluster size) recommended for SQL
  Server is 64 KB; this is reflected in Figure 4.

If you decided to test the performance under different cluster sizes, you would probably discover that the difference is negligible, especially on high-end storage systems.


SQL Server Replication: "ALTER TABLE ALTER COLUMN" is not propagated to subscribers

If anyone wants to increase the column width of the replicated table in SQL Server 2008, then no need to change the property of replicate_ddl=1. Simply follow the steps as given.

Open SSMS
Connect to Publisher database
run command

ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] varchar(22)

It will increase the column width from varchar(x) to varchar(22) and same change you can see on subscriber (transaction got replicated). So no need to re-initialize the replication

Hope this will help all who are looking for it.


First thing you have to separate here are the words query, transaction and connection.

Clue: your query gets executed - it is in the active state. After that the query ends but the connection stays on - the idle state. There is no transaction (it has been committed) so the xact_start is null. So you have to close the connection after the query is succesful.
Clue: Before the autocommit was on the query was left in the middle of the transaction so first you would have to commit and then close connection.


Enabling/disabling/changing Oracle auditing without a shutdown?

AUDIT_TRAIL is an initialisation parameters and needs a database restart. Anyway by default AUDIT_TRAIL is set to DB. This could be enough.

With this AUDIT_TRAIL you have just to issue:

AUDIT SELECT TABLE

to start the audit on any table for any select. As you stated this activity has an overhead from the database point of view. you have to pay attention to you SYSAUX tablespace growth where by default resides the sys.aud$ table. To move sys.aud$ you have to do:

SQL> BEGIN
 DBMS_AUDIT_MGMT.set_audit_trail_location(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
 audit_trail_location_value => 'AUDIT_TBS');
END;
/

FGA is much more than AUDIT and lets you specify the conditions necessary for an audit record to be generated. Furthermore FGA policies are programatically bound to the object (table, view).

Apart from this you should consider a different solution like an third party tool for DAM (Direct Access Monitoring). Oracle has its own solution today called Oracle Firewall. Otherwise you can check for other products like IBM Guardium, DBProtect, etc.


Have a look at MS Docs about it:

Limitations and Restrictions
When TOP is used with INSERT, UPDATE, MERGE, or DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in these statements. If you need to use TOP to insert, delete, or modify rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause that is specified in a subselect statement. See the Examples section that follows in this topic.

UPDATE TOP(500) instances
SET thing = 'new thing'
WHERE Something = 'SomethingElse';

Or
UPDATE instances
SET thing = 'new thing'
FROM (SELECT TOP (500) Id
      FROM instances
      ORDER BY Something) AS t1
WHERE instances.Id = t1.Id;

Or you can update the derived table directly without a join:
UPDATE t1
SET thing = 'new thing'
FROM (SELECT TOP (500) *
      FROM instances
      ORDER BY Something) AS t1;

An example of this approach can be found at dbfiddle.


For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

This does the job:
CREATE MATERIALIZED VIEW speedy_materialized_view AS
WITH RECURSIVE tree AS (
   SELECT id, parent_id, ARRAY[id] AS path
   FROM   gear_category
   WHERE  parent_id IS NULL

   UNION ALL
   SELECT c.id, c.parent_id, path || c.id
   FROM   tree t
   JOIN   gear_category c ON c.parent_id = t.id
   )
, tree_ct AS (
   SELECT t.id, t.path, COALESCE(i.item_ct, 0) AS item_ct
   FROM   tree t
   LEFT   JOIN  (
      SELECT category_id AS id, count(*) AS item_ct
      FROM   gear_item
      GROUP  BY 1
      ) i USING (id)
   )
SELECT t.id
     , t.item_ct       AS count_direct_child_items
     , sum(t1.item_ct) AS count_recursive_child_items
FROM   tree_ct t
LEFT   JOIN tree_ct t1 ON t1.path[1:array_upper(t.path, 1)] = t.path
GROUP  BY t.id, t.item_ct;

count_recursive_child_items is counted separately for each category, so I am not convinced it's the fastest possible way for deep trees.
However, aggregate functions are not allowed in recursive CTEs.
Recursive Function
Strictly speaking, it's really iterative - but so is a "recursive" CTE.
You could build a function working with temp tables. You need to know your way around plpgsql or there is too much to explain.
CREATE OR REPLACE FUNCTION f_tree_ct()
  RETURNS TABLE (id int, count_direct_child_items int, count_recursive_child_items int) AS
$func$
DECLARE
   _lvl int;
BEGIN
   -- basic table with added path and count
   CREATE TEMP TABLE t1 AS
   WITH RECURSIVE tree AS (
      SELECT c.id, c.parent_id, '{}'::int[] AS path, 0 AS lvl
      FROM   gear_category c
      WHERE  c.parent_id IS NULL

      UNION ALL
      SELECT c.id, c.parent_id, path || c.parent_id, lvl + 1
      FROM   tree t
      JOIN   gear_category c ON c.parent_id = t.id
      )
   , tree_ct AS (
      SELECT t.id, t.parent_id, t.path, t.lvl, COALESCE(i.item_ct, 0) AS item_ct
      FROM   tree t
      LEFT   JOIN  (
         SELECT i.category_id AS id, count(*)::int AS item_ct
         FROM   gear_item i
         GROUP  BY 1
         ) i USING (id)
      )
   TABLE tree_ct;

   -- CREATE INDEX ON t1 (lvl); -- only for very deep trees

   SELECT INTO _lvl max(lvl) FROM t1;  -- identify max lvl to start bottom up

   -- recursively aggregate each level in 2nd temp table
   CREATE TEMP TABLE t2 AS
   SELECT t1.id, t1.parent_id, t1.lvl
        , t1.item_ct
        , t1.item_ct AS sum_ct
   FROM   t1
   WHERE  t1.lvl = _lvl;

   IF _lvl > 0 THEN
      FOR i IN REVERSE _lvl .. 1 LOOP
         INSERT INTO t2
         SELECT t1.id, t1.parent_id, t1.lvl, t1.item_ct
              , CASE WHEN t2.sum_ct IS NULL THEN t1.item_ct ELSE t1.item_ct + t2.sum_ct END
         FROM   t1
         LEFT   JOIN (
            SELECT t2.parent_id AS id, sum(t2.sum_ct) AS sum_ct
            FROM   t2
            WHERE  t2.lvl = i
            GROUP  BY 1
            ) t2 USING (id)
         WHERE  t1.lvl = i - 1;
      END LOOP;
   END IF;

   RETURN QUERY  -- only requested columns, unsorted
   SELECT t2.id, t2.item_ct, t2.sum_ct FROM t2;

   DROP TABLE t1, t2; -- to allow repeated execution in one transaction
   RETURN;
END
$func$  LANGUAGE plpgsql;

This cannot be included in a CREATE MATERIALIZED VIEW statement because of the use of temporary tables. You could just create another (temp) table with it, acting as a manually maintained "materialized view":
CREATE TABLE speedy_materialized_view AS
SELECT * FROM f_tree_ct();

Alternatively you could TRUNCATE speedy_materialized_view in the function and write to it directly. The function would RETURNS void instead or you could return some meta-information like a row count ...
SQL Fiddle.
Aside:
Column aliases in the recursive term of a CTE are just for documentation since output column names are determined by the non-recursive term only.


Best practice: Unions or a derived table?

In your example query, neither way is particularly better, because the inner queries have their own where clauses, are generating a very small result-set, and are able to do this very efficiently presumably because the invoice column in pending and done are indexed.

If you were doing something more complex, and especially without such highly-selective where clauses in the inter queries, the union would likely perform better... because in the derived table scenario, the derived table is materialized before the joins to the outer tables and indexes on the tables used in the inner query can't be used to handle the join... on the other hand, with UNION, the optimizer could use indexes on pending/done if they were appropriate.

Personally, I'd be inclined to solve this one by writing one or more stored procedures to encapsulate the union queries and return the answer as a result set using an unbounded SELECT.

  MySQL supports a very useful extension that enables the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client.

  http://dev.mysql.com/doc/refman/5.5/en/stored-routines-syntax.html

These are called "unbounded."  So, a stored procedure that takes the invoice id as an input parameter, does the SELECT ... UNION ALL ... SELECT can encapsulate everything and give you a very clean way to ask the database for what you need and get a record-set in return.

CALL get_invoice_customer_location_detail_by_invoice_id(123456); # long, but much more appealing

If you subsequently decide union or derived really was The One True Way after all, then you only have to update that in one place, in the procedure definition.

Free tip: UNION means UNION DISTINCT in non-dinosaur versions of MySQL, while some older versions will interpret it as UNION ALL.  It's probably good practice to explicitly use the one you actually want, so you always get the behavior you expect.  It's also generally good from a performance standpoint to use UNION ALL unless you need UNION DISTINCT because it's one less thing for the optimizer to have to deal with, but this is much more the case with large result sets, where a lot of time could be spent de-duplicating a set of data that is already free of duplicates.


How can I get LOAD DATA LOCAL INFILE to work for MySQL 8.0.21 on a Windows machine?

Windows restricts the acces for that folder.
Best is to create a separate folder and give it the right access rights.
I tried a lot but i can't replicate your message, when the file exists it can open it and insert the data.
so the basic steps
Open your my.ini file
and find, so put there the new created folder

"c:/my_folder/"

in double quotes
# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/"

restart your server
place there your fff.txt
Which should look like this
test
test
test
test
test
test

and then run
LOAD DATA INFILE 'c:/my_folder/fff.txt' INTO TABLE fff
LINES TERMINATED BY '\r\n';

and sex rows are inserted.


Sounds like you've already implemented some form of Slowly Changing Dimension, dealing with times, but need to also deal with user preferences as well?  For ease, I'm going to use  Type 2 SCDs and assume temporal changes, but you could obviously swap those out for a more appropriate approach as your specific solution requires.  I'm also going to imagine the relevant parts of your structure completely abstractly in MS SQL so I can also refer to this SQL Fiddle later:

The example of an abstract system without any form of SCD involves the following structures and forms our base case:

CREATE TABLE dbo.DimFoo
(
    DimFoo_PK       TINYINT IDENTITY( 1, 1 ) NOT NULL,
                        PRIMARY KEY ( DimFoo_PK ),
    FooName         NVARCHAR( 5 )
);

CREATE TABLE dbo.FctBar
(
    FctBar_PK       SMALLINT IDENTITY( 1, 1 ) NOT NULL,
                        PRIMARY KEY ( FctBar_PK ),
    DateCreated     DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    DimFoo_FK       TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                        REFERENCES dbo.DimFoo ( DimFoo_PK )
);

In this example, the fact table contains two entries, each keyed to the dimension table.  When queried, both entries show the dimension value to be "Sprocket".  The requirement handed down is that in 2010, the definition of "Sprocket" changed to "Schprocket," giving way to the need for an SCD to be implemented.  In the academic example, often these SCDs emerge through a replacement of the dimension table, but in practice, I've found it largely preferable to simply add SCDs to the structure and re-key the necessary fact tables.  This allows use of the original dimension table as form of master look-up and a continued entry point for new dimension values which can then propagated into the SCD and fact table.

CREATE TABLE dbo.ScdFoo
(
    ScdFoo_PK       TINYINT IDENTITY( 1, 1 ) NOT NULL,
                        PRIMARY KEY ( ScdFoo_PK ),
    DimFoo_FK       TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                        REFERENCES dbo.DimFoo ( DimFoo_PK ),
    FooName         NVARCHAR( 16 ),
    DateStart       DATE NOT NULL,
    DateEnd         DATE
);

With this structure in place, we can run an update as outlined in the simple example of a Type 2 SCD, making sure to then drop the old foreign key reference from the fact table for the purpose of retaining normalization levels.  The new query pulls the expected name for each entry in the fact table, now reflecting changes in time.

At this point, I believe your question is largely based around the idea that a number of specific users are also interested in seeing this same fact data keyed differently.  In the vein of how this has been illustrated thus far, I assume the existence of another table for storing user profile information, which I will define as follows:

CREATE TABLE dbo.UserProfile
(
    UserProfile_PK   TINYINT IDENTITY( 1, 1 ) NOT NULL,
                         PRIMARY KEY ( UserProfile_PK ),
    Code             NVARCHAR( 8 )
);

An additional requirement detail is required at this point, either way - do the users which are interested in seeing their own definitions wish to do so per time slice ( as already expressed in the SCD ) or by value ( as originally stored in the dimension from the base case )?  In either solution, this can be accomplished in a simple manner through a one-to-many relationship between the user profile table and the original dimension table, then queried as shown in the original SQL Fiddle, or alternatively by simply swapping the reference from the original dimension to the slowly changing dimension instead.

CREATE TABLE dbo.DimUserProfileFoo
(
    DimUserProfileFoo_PK
                     TINYINT IDENTITY( 1, 1 ) NOT NULL,
                         PRIMARY KEY ( DimUserProfileFoo_PK ),
    UserProfile_FK   TINYINT NOT NULL, FOREIGN KEY ( UserProfile_FK )
                         REFERENCES dbo.UserProfile ( UserProfile_PK ),
    DimFoo_FK        TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                         REFERENCES dbo.DimFoo ( DimFoo_PK ),
                         UNIQUE( UserProfile_FK, DimFoo_FK ),
    FooName          NVARCHAR( 16 ) NOT NULL
);

If your users are particularly crusty, they may wish to see their own definitions of dimensions based on their own time slices.  This would simply call for the new user-keyed dimension to also be a SCD, which, sticking to the Type 2 SCDs we've used, could be queried as expressed in this SQL Fiddle.

CREATE TABLE dbo.ScdUserProfileFoo
(
    ScdUserProfileFoo_PK
                     TINYINT IDENTITY( 1, 1 ) NOT NULL,
                         PRIMARY KEY ( ScdUserProfileFoo_PK),
    UserProfile_FK   TINYINT NOT NULL, FOREIGN KEY ( UserProfile_FK )
                         REFERENCES dbo.UserProfile ( UserProfile_PK ),
    DimFoo_FK        TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                         REFERENCES dbo.DimFoo ( DimFoo_PK ),
    DateStart        DATE NOT NULL,
                         UNIQUE( UserProfile_FK, DimFoo_FK, DateStart ),
    DateEnd          DATE,
    FooName          NVARCHAR( 16 ) NOT NULL
);


Unexpected Implicit conversion in a procedure

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.


database normalisation on object that is attached to another object that can change

Since Make and Model is a hierarchical relationship they can exist in the same table.

You'll need a junction table to handle the many to many relationship between machine, equipment, and sub-location.

Your junction table will need to contain four columns; [SLID], [MID], [EID], [datetimestamp].

You can handle tasks in several ways - if one task always follows one piece of equipment then the link between Task and Equipment is fine, however, if the same task can use multiple pieces of equipment on multiple machines then you would want include a task id [TID] field in your junction table.

The junction table will grow quite large so you might want to partition it by month and year, and also experiment with the indexes to make sure that they cover the reporting requirements.

Also, since you're using an ORM I'm going to make an assumption that the idea of using GUIDs as IDs has come up, or will at some point - don't create a clustered index (primary key) on a guid column, if you must use a guid to make it easier for the application to do its thing, then do create an integer id column as your primary keys.

Example ERD - This is what I might start with given the requirements, and modify it from there as needed.


How to find which edition the SQL Server installation media belongs to

Per this thread on MSDN:

  SQL 2008 onwards SQL server setup allows you to chose the option which edition you want to install. That means one media has the files of all edition.

and

  ..., the FULL media is good to install all editions, it depends on the PID you provide during setup.

The media layout is found in \MediaInfo.xml, and the default product key is in \<platform>\DefaultSetup.ini. Note that the product key is the same, regardless of platform.

Unfortunately, I haven't found a way to determine the available editions without running Setup and looking at the list after entering the product key. (I assume this is by design for security reasons.)

If you have a lot of these to validate, it's probably faster to grab a list of the product keys and check against the keys available in your MSDN subscription. The corollary to that is to always replace the default product key during Setup with one of the keys on MSDN.


This looks to me that an employee works in at department at a location since a point in time.  I don’t recognise the diagram type as an ERD so I can’t tell what type of relationships they are but I expect that they are one to many. i.e. the works_in3 table is a link table to facilitate a many to many relationship of employee and department. As for location I expect this is where the employee worked in this department, again representing a many to many relationships, as one location could house many employees performing many roles for departments. But that’s just my opinion.


Select row with more of one type than another in a separate table

I want to get all customers that have purchased more apples than pears but have bought at least one apple.

SELECT c.id
FROM customers as c
JOIN products p ON c.id = p.customer_id
WHERE p.type IN ('apple', 'pear')
GROUP BY c.id
HAVING SUM(p.type='apple') > SUM(p.type='pear')


Is there a way to use sysconv() built-in function?

The view sys.columns is a system view. This view is provided to you by the sys user in the master database, which is a database user without login.

The sys user has its own schema sys which is then used to link all the sys.* objects.

Permissions to select from the sys.* objects are granted to you via membership in the public SQL Server role. The SQL Server role public has SELECT permissions on all sys.* schema objects.

This is how you are granted the permission to select * from sys.columns.

If you are not member of the public server role, then you don't have access to any of the sys.* objects.

sys user (script)

USE [master]
GO

/****** Object:  User [sys]    Script Date: 16.12.2016 15:57:08 ******/
CREATE USER [sys]
GO

sys schema (script)

USE [master]
GO

/****** Object:  Schema [sys]    Script Date: 16.12.2016 15:57:35 ******/
CREATE SCHEMA [sys]
GO

public server role (script)

USE [master]
GO

/****** Object:  ServerRole [public]    Script Date: 16.12.2016 16:05:05 ******/
CREATE SERVER ROLE [public]
GO

Public database role

There is also a public database role, which is somehow linked to the server role. If you query the sys.database_principals (view) in the master database, then you will see that there is a public principal with the same id as the server role public which is 0. I am assuming that this is the missing link, between the database_role public and the server_role public.

Edit: Added some information regarding the database role "public"

The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default. Grant public only the permissions you want all users to have.

Quote from Server and Database Roles in SQL Server, Section: "The public Role"

If you retrieve the permissions for the database role public in the master database you will get the following listing:

Query:

SELECT
   OBJECT_NAME(dp.major_id) AS OBJECT ,
   USER_NAME(dp.grantee_principal_id) AS grantee,
   USER_NAME(dp.grantor_principal_id) AS grantor,
  dp.permission_name
FROM sys.database_permissions AS dp
WHERE dp.grantee_principal_id = 0

Results:

sp_MSalreadyhavegeneration  public dbo EXECUTE
sp_MSwritemergeperfcounter  public dbo EXECUTE
TABLE_PRIVILEGES            public dbo SELECT
sp_replsetsyncstatus        public dbo EXECUTE
sp_replshowcmds             public dbo EXECUTE
sp_publishdb                public dbo EXECUTE
dm_pdw_nodes_os_tasks       public dbo SELECT
...
[truncated]

To come back to your example with the sys.colums view: You have been granted the permissions to SELECT from the view, but you do not have permissions to actually execute the sysconv function directly, which is the definiton of a column in the result set. It has been hidden from your prying eyes.


CPU usage constantly high after upgrading from Postgres 9.5 to 9.6 to 10.6

ANALYZE VERBOSE;

You need to run an ANALYZE query on the whole database using the above command.

The issue is that the query plans postgres has generated are optimised for the previous version of postgres, when you do an RDS update it does not implicity regenerate these plans, this needs to be done manually (I'm sure there's a reason why AWS doesn't do this manually but I've really no idea why).

In my case I saw roughly a week of extremely high CPU usage, just as in your case, then after running ANALYZE my cpu dropped back to it's previous baseline. As you can see in the image below the upgrade (in my case from 9.4 - 9.5) was run on 11/27, the analyze query was run on 12/02.

(The VERBOSE is not strictly necessary but it's useful to be able to watch the progress of the command)


microsoft webmatrix deleted my wamp databases?

Here is the only thing I can advise for now:

Open a DOS Window and run the following

C:\> dir mysql.user /s

If you see two mysql.user files, then you may have two DB setups.

You would just have to point datadir in my.cnf back to the old setup.

Please find out and let me know ...


Use something like

SELECT result.text, result.date, sample.ID
FROM       ( SELECT sample, MAX(date) date
             FROM result
             GROUP BY sample) maxtime
INNER JOIN result  ON result.sample=maxtime.sample
                  AND result.date=maxtime.date
LEFT JOIN  test ON test.ID = result.test
LEFT JOIN  sample ON sample.ID = result.sample
LEFT JOIN  batch ON batch.ID = sample.batch
WHERE batch.ID = 12759
  AND test.ID  = 2144


SSIS - This error is thrown by Connections collection when the specific connection element is not found

The problem lies in the referencing. You might have copied the package from an older one and are making the changes or you might have edited the connection managers. The connection managers are internally used using the GUID that you have mentioned. You might have changed most of the references from the UI, but still there are some objects and tasks, that are referencing the deleted or older connection managers. The probable one would be the SQL Server Logging in the package.

Solution is: Open the code of the package, by right clicking the package in the solution explorer and clicking on View Code which shows the package code in XML format. Now you are able to see, which tasks are referencing the old connection manager. Do the needful changes and this should do it!

Let me know if the problem persists after this!!


Find changed columns for near-identical rows across tables

Clarifications
Your comment needs addressing first:

numeric data almost always takes 0 (and text types take '')

The key word here is "almost". As long as it's not "never" (like in "never ever!"), you need to take NULL into account anyway.

no risk of testing NULL=NULL, which would return 1 inappropriately

No it wouldn't. Anything compared to NULL is always NULL even NULL=NULL. Try it. You need to understand NULL comparison.

I think I just need to change sum(col1) to sum(col1::int) to get the number of rows where col1 changed.

If you want to count every case of a.col1 IS DISTINCT FROM b.col1, then you need to work with NULL-safe comparison to begin with. Apart from that, your expression would work. There are many alternatives, depending on the situation:

For absolute performance, is SUM faster or COUNT?

You use select a.* into vm201512 ... in your 1st query. Don't. SELECT INTO .. is discouraged. Use the superior CREATE TABLE AS ... like in your 2nd query.

Creating temporary tables in SQL

Also, Postgres provides pivot functionality in the tablefunc module, but this is not a "pivot" problem at all. Nothing is pivoted here.
The core problem is the dynamic nature of the query due to varying input tables.
Solution
Assuming no NULL values. Where NULL values are possible, use IS NOT DISTINCT FROM instead of =.
Tested in Postgres 9.5. Should work for Postgres 9.1 or later.
You can build your queries like this:
CREATE OR REPLACE FUNCTION f_build_query(_t1 regclass
                                       , _t2 regclass
                                       , _join_col text = 'pfi')
  RETURNS text AS
$func$
SELECT format('SELECT %I, %s FROM %s a JOIN %s b USING (%1$I);'
            , _join_col
            , string_agg(format ('a.%1$I = b.%1$I AS %1$I', attname), ', ' ORDER BY attnum)
            , _t1, _t2)
FROM   pg_attribute
WHERE  attrelid = _t1        -- compare all columns from 1st table
AND    NOT attisdropped      -- no dropped (dead) columns
AND    attnum > 0            -- no system columns
AND    attname <> _join_col  -- exclude "pfi"
$func$  LANGUAGE sql;

Call:
SELECT f_build_query('vm201512.property_d', 'vm201412.property');

Returns a query like this (which you can execute in turn):
SELECT pfi, a.a = b.a AS a, a."weird NaMe" = b."weird NaMe" AS "weird NaMe" -- more ...
FROM vm201512.property_d a JOIN vm201412.property b USING (pfi);

Result:
 pfi | a | b | weird NaMe
-----+---+---+------------
   1 | t | f | t
   2 | f | t | f

Works for arbitrary input tables, and deals with identifiers safely. You can provide table names schema-qualified or not, as you like.

Table name as a PostgreSQL function parameter

Simple dynamic solution
The difficulty is to return varying row types. SQL demands to know the return type at call time. To avoid difficulties, you could return a simple array instead. You get values in the original order of columns, but you don't get column names like in the first query:
CREATE OR REPLACE FUNCTION f_diff_matrix(_t1 regclass
                                       , _t2 regclass
                                       , _join_col text = 'pfi')
  RETURNS TABLE (pfi int, change_matrix bool[]) AS   -- Adapt data type of pfi to your needs!
$func$
BEGIN
   RETURN QUERY EXECUTE (
   SELECT format('SELECT %I, ARRAY[%s] FROM %s a JOIN %s b USING (%1$I)'
               , _join_col
               , string_agg(format ('a.%1$I = b.%1$I', attname), ', ' ORDER BY attnum)
               , _t1, _t2)
   FROM   pg_attribute
   WHERE  attrelid = _t1        -- compare all columns from 1st table
   AND    NOT attisdropped      -- no dropped (dead) columns
   AND    attnum > 0            -- no system columns
   AND    attname <> _join_col  -- exclude "pfi"
   );
END
$func$  LANGUAGE plpgsql;

Call (note the difference!):
SELECT * FROM f_diff_matrix('vm201512.property_d', 'vm201412.property');

Result:
 pfi | change_matrix
-----+---------------
   1 | {t,f,t}  -- one element per column
   2 | {f,t,f}

SQL Fiddle.

You could even make the same function return a dynamic result set for various tables, but I doubt it's worth the complication:

Refactor a PL/pgSQL function to return the output of various SELECT queries

If your really need dynamic pivot functionality (not in this case):

Dynamic alternative to pivot with CASE and GROUP BY


Enabling page compression on 1.5TB table

Your plan sounds well except for this one:

  Also, instead of enabling page compression. After Step #3, Truncate
  the largest table, enable page compression and then do a SELECT * INTO
  ReplicaDB.dbo.ReplicaTbl ?

If you truncate a table and enable a page compression, you must do INSERT and not SELECT INTO and at this point you should use TABLOCK to get page compression and not row compression only.

Besides this, rebuilding with compression is much faster that an insert into a compressed table, we have a difference in 3-5 times on large tables.

Have a look here: The Data Loading Performance Guide, section Data Compression and Bulk Load:

  When data is bulk loaded into a compressed table or partition, both
  page-level and row-level compression are generally done at bulk load
  time. However, you should be aware of an exception: When bulk loading
  into a page compressed heap, you must use the TABLOCK hint to achieve
  page compression. If the TABLOCK hint is not used, only row level
  compression is done on the heap. Using page compression on a bulk
  target will generally slow down bulk load speeds – especially if the
  I/O system is able to supply enough speed to saturate the CPUs.


Does RSS Profile have an effect on SQL Server with NUMA setup?

Receive side scaling (RSS) improves the system performance when handling network data on multiprocessor systems.  This should result in better scaling for receiving data from the network.

One place to find details is at: https://technet.microsoft.com/en-us/library/hh997036.aspx

From this I would infer that the Receive side scaling primarily benefits the receipt of data from the network.

There is a link that you might find interesting for an SAP installation and, although posted in 2012, seems to be using pre-Windows 2012 Operating Systems.

Nonetheless, you may benefit from some of the details. One of the charts shows that only 1 processor is consuming the reads, while other processors are idling along doing not so much.  See the charts at:

http://blogs.msdn.com/b/saponsqlserver/archive/2012/01/12/network-settings-network-teaming-receive-side-scaling-rss-amp-unbalanced-cpu-load.aspx

Therefore, it seems that RSS is primarily affecting how quickly Windows can receive the data and make it available to other processes, such as the SQL Server.  This depends on the RSS Profile you choose.

For example:

The Default profile which disables the new RSS features, leaving behavior very similar to the Windows 2008 R2 behavior.
The NUMA scalability profile enables RSS CPUs to be assigned per NUMA node on a round robin basis. This results in "RSS assigns at least one RSS CPU per NUMA node before assigning the second RSS CPU for each NUMA node." This round robin setup spreads the task across more CPUs, thus likely improving the data throughput.

This suggests that if the server is a heavy consumer of the data network, the NUMA scalability profile could significantly increase the consumption of this data.  But once that is accomplished and the data is in cache, I cannot see  that this would carry over into other SQL Server operations.

Is your environment consuming very heavy levels of data, such that this would benefit your work? Then you might consider the various profiles available to you.

Other Low latency support includes such options as Parallel Receive Processing, Preserving in-order packet delivery, and several other options that may be a benefit to you.
Also the latest RSS "enables Windows Server 2012 to scale optimally on large servers that have more than 64 processors."

So, RSS enables Windows with better options for receiving data, but it does not seem to indicate that the SQL Server will behave in some better way.  But, I cannot find any clear direction on this and most of the papers are about a decade old.


dbcc shrinkfile not reducing size of log file in sql server 2008

First do a DBCC LOGINFO(); then go to the very last row.  Is the status 2?  That means it's active and it cannot be shrunk until that VLF has released that portion of the log file so next checkpoint it could flush it to disk (assuming it has resources).

If it is active you can do a select name, log_reuse_wait_desc from sys.databases to see what is using it.  Sometimes it's mirroring, another HA solution, a long transaction, etc.

You can then do a transaction log backup and checkpoint; to force the log file to roll over to the first VLF.  Actually you're in SIMPLE recovery mode so a backup is not needed, that's for FULL and BULK LOGGED only.

Once it's rolled over and it is no longer an active VLF, you can remove it.  Note that it will remove all the non active VLFs until it hits an active one.  At that point it will stop.  You'll have to roll over again.

Now would be a great time to brush up on VLF fragmentation and management so you optimize your performance and time to recovery.  While were at it, here's some other great info.


Aggregate function that picks any/first/last/random value

I believe I found a more efficient SQL for what I am trying to achieve

WITH src AS (
    ...
)
, identical_field1 (
    SELECT field1 FROM src LIMIT 1
)
SELECT (SELECT field1 FROM identical_field1), array_agg(field2)
FROM src

Sub-select on field1 is evaluated only once and only requests one single row from src.

I am not marking it as an answer for now 'cause someone may still suggest a more efficient way.


SQL Connector for Azure Key Vault - Cannot use more than one key vault

Stupidly enough, opening a new tab in SQL Studio fixed the issue. I guess when creating a credential, it seems to "stick" to the tab on which it was created, even if creating and binding a second credential within the same tab. Running the same commands in a new tab worked.


Why would SequelPro only import 23k rows out of 130k?

This may depend on where you generated the CSV file. If the CSV file was generated on a Windows machine, there could be some character set issues

See https://code.google.com/p/sequel-pro/issues/detail?id=1629

See the following URLs as SequelPro's character set problems are not new

https://stackoverflow.com/questions/8910528/understanding-mysql-charset-and-collation
https://stackoverflow.com/questions/11185324/special-characters-get-lost-in-mysql-export-import
http://forums.mysql.com/read.php?28,284657,284657

If the CSV file was generated on another Mac OSx server, you should not be having this issue.

You may have to resort to setting the default character set to match that CSV file. Sounds weird to here it goes:

Please run this query and you will see something like this:

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql>

You can also see the character set of the database

mysql> show create database mydb\G
*************************** 1. row ***************************
       Database: mydb
Create Database: CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

mysql>

Perhaps you should load another table that has the matching character set:

CREATE TABLE anothertable LIKE mytable;

Change the whole table's character set

ALTER TABLE anothertable CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

or change a column's character set

ALTER TABLE anothertable MODIFY col1 CHAR(50) CHARACTER SET utf8;

Then, have SequalPro load anothertable.

I guess to be less aggressive, just change the column's character set.