Anybody ever tried to search in ntext or text fields on MS SQL Server?
Common experience is to fail.
Solution: cast your search-term to text type, and try again.
Example, using EA-Database:
SELECT * FROM t_xref x
WHERE x.Description LIKE CAST(‘%GUID={761F0C9E-92E4-42d1-8F0A-D3740A2BFF60}%’ AS TEXT)
This is a very simple scenario, but is taken from a very complex one.
A Customer may have more than 1 address, some may have none. You only want each customer to appear only once. So what can you do?
SELECT C.Name, A.Address
FROM Customer AS C
OUTER APPLY (SELECT TOP 1 *
FROM Addresses
WHERE CID = C.ID) AS A
Note, that this will never work on SQL CE, but on SQL Server 2005 or 2008! And it’s not part of the ANSI standard!
In some cases the user might install the SQL Server Express Edition, before a “real” SQL Server (e.g. Developer Edition).
This can result in a basic version of Management Studio.
Keep in mind, that with SSMSE (e = Express), you cannot manage Analysis Services, Integration Services, or Reporting Services.
The more, you cannot update the full-text catalog by using SSMSE and it does not support scheduling administrative tasks by using SQL Server Agent.
If you later install a Developer Edition of a SQL Server on your system, you also cannot upgrade from the basic version SSMSE. Only possible thing is, to uninstall SQL Express, and do a “Upgrade” with the Developer Edition SQL Setup to the full version SSMS.
Easiest way to discover SSMSE: try to connect to a SQL server: if your only choice is “database engine”, that’s express .
Im Rahmen eines OCF basierten Projektes brauchten wir die Möglichkeit komplexe Sql Abfragen abbilden zu können. Eine QueryBuilder musste her, dabei sollte die Anwendung aber möglichst einfach sein.
Auf der Codeproject Seite “SelectQueryBuilder: Building complex and flexible SQL queries/commands from C#” findet sich eine sehr gute Implementierung.
Damit lassen sich so nette Sachen wie Join Bildung und dgl. ganz einfach umsetzen:
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Orders");
query.AddJoin(JoinType.InnerJoin,
"Customers", "CustomerID",
Comparison.Equals,
"Orders", "CustomerID");
query.AddWhere("Customers.City",
Comparison.Equals, "London");
Die leicht abgeänderte Version des SelectQueryBuilders ist jetzt Teil der OCF assembly Ocf.Common.
There are a lot of scripts out there promising to create insert scripts. You pass in the name of the table, and the scripts create an insert statement for each record contained. Only problem they have: the more columns there are, the greater the chance of lines being cut off in the middle.
The only working & free solution I found up to now is called SSMS Tools. You can get it here: LINK.
It integrates into SQL Server Management Studio and offers a couple of functions. The most important: the creation of insert scripts, based on the result of any sql statement.
This is standard functionality in Oracle clients for years now. So MS, please tell me, why this wasn’t implemented into SQL Server 2008?
How to use an Update Cursor on MS SQL Server.
Requirements: If the update-table does not have a Primary Key, the cursor is read-only. So I advise to create at least a temporary Primary Key, and delete it afterwards.
Usage:
DECLARE curs as CURSOR
FOR SELECT * FROM t_image
FOR UPDATEOPEN curs
FETCH curs INTO @lastValueWHILE (@@FETCH_STATUS = 0) BEGIN
UPDATE [t_image]
SET [CALC_VALUE] = @lastValue
WHERE current of cursFETCH curs INTO @lastValue
ENDCLOSE calc
DEALLOCATE calc
if you replace “WHERE current of curs” by any “WHERE x = y” statement, it would decrease performance dramtically.
During a test with 80000 records, execution time without update cursor takes 20 minutes. Using an update cursor speeds up to a total time of 20 seconds!
Im Folgenden habe ich zwei Varianten einer sich ziemlich ändelnden SQL Abfrage:
SELECT Top 1 Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
and "Order Subtotals".Subtotal > 2490.50
WHERE Employees.EmployeeID > 1
SELECT Top 1 Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Employees.EmployeeID > 1
and "Order Subtotals".Subtotal > 2490.50
Einmal ist die Subtotal Abfrage im FROM/JOIN Bereich und einmal im WHERE. In diesem Fall liefert der SQL Server tatsächlich das gleiche Ergebnis. Nun ist mir aber bei einem Kunden folgendes passiert: In einer Kundentabelle, werden die Kundendatensätze auch noch versioniert. Es musste auf eine bestimmte Version eines Satzes zugegriffen werden und dazu wurde leider weder ein ORDER BY DESC oder ein zielgerichtetes Abfragen auf die richtige Version des Datensatzes zurückgegriffen, sondern – einfach mit TOP 1 gearbeitet. Im FROM/JOIN Bereich hatte die Abfrage alle Versionen geliefert und daher bei TOP 1 immer die älteste Version. Durch Kopieren in den WHERE Bereich war das Problem dann gelöst. Ich werde versuchen auf Basis der Northwind Datenbank dieses Phenomen nachvollziehbarer zu machen. Aber es war mir jetzt schon wichtig genug das niederzuschreiben.