CS-SQLServer

Persistence is a corner stone of all computer applications. The approach to persistence in an application **should** carry lot more weight than the frameworks that are normally used to display or manipulate that data.

Persistence and Databases takes a new precedence with web where a variety of databases can all of a sudden open up new alleys to programability over the web.

This trend is evident in a number of multi-tenanted cloud based systems such as Salesforce or Microsoft Dynamics or Parse.

As a web developer these database are important to me because I salivate what can be done with each the strengths that each database bring to the table to push the programmable web by end users.

The goal is no longer where one database rules it all. Bring the diversity and exploit each for their strengths through the cloud.

A sample sql server view template

Basic DB2 Syntax: SQL, Stored Procedures etc.

Can I create a unique index on a column that allows nulls?

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/en/html/dbugen9/00000332.htm

Common table expressions are temporary views that are known only within the scope of a single SELECT statement. They permit you to write queries more easily, and to write queries that could not otherwise be expressed.

Common table expressions are useful or may be necessary if a query involves multiple aggregate functions or defines a view within a stored procedure that references program variables. Common table expressions also provide a convenient means to temporarily store sets of values.

Recursive common table expressions permit you to query tables that represent hierarchical information, such as reporting relationships within a company. They can also be used to solve parts explosion problems and least distance problems.

5) composite keys

3-Jan-20

composite keys

6) Database questions

5-Jan-20

Database questions

Guidelines for data modelling

Here is what sql manager does to alter the identify of a column

How can i backup and restore a SQL server database?

use query analyzer

How can I generate SQL script to regenerate/document tables in sql server?

How can I use outerjoins in SQL Server? Text borrowed from online library

How do I define a column to be a primary key in tsql?

How do I pass an exit code from sqlcmd to the batch file that is calling it? It turned out this is not a simple issue. The research will take you into understanding the sqlcmd.exe. Understanding transact-sql. Knowing how the options provided by sqlcmd.exe are confusing and unspecific. See how one TINY opening is left with raiserror function and you have to pretty much go for the precise bulls eye and a very specific parameters supplied to the raiserror to make this work.

How do I schedule a backup database program on windows servers? I have SQL Server 2008 Express on my azure server. It doesn't have any tasking or scheduling tools as it is an express version. Recommendation is to write a SQL server script and invoke it through sqlcmd.exe. Then have a windows scheduler schedule it at regular intervals. By the end of this research I will know how to schedule full backups and also differential backups using command line tools.

Search Google for this


select st.statement as masterPageTemplate \
from reports r, sql_statements st \
where 1=1 \
and r.report_content_id = st.statement_id \
and r.report_short_name = 'print_master_page' \
and r.owner_user_id = {ownerUserId.quote} \
and {ownerUserId.quote} in ( \
	select owner_user_id from reports r2 \
	where r2.report_id = {reportId} )

or better yet, the following


select st.statement as masterPageTemplate \
from reports r, sql_statements st \
where 1=1 \
and r.report_content_id = st.statement_id \
and r.report_short_name = 'print_master_page' \
and r.owner_user_id = (select owner_user_id from reports r2 \
	where r2.report_id = {reportId} )

SQLServer


select top 10 * from tabl1

Db2


select * from sysibm.syscolumns
fetch first 10 rows only

18) How to use sp_help?

11-Jun-03

Allows you to look into stored procedures, tables etc

in clause in stored procedures

jdbc drivers and specs for SQL Server

http://www.oreilly.com/catalog/mastorasql/toc.html

The draw back is that it is dedicated to Oracle. Hopefully one can extrapolate it for other databases.

23) MYSQL Notes

18-Nov-06

Notes
Questions
Links
Resources
I am hoping this article will be a good companion to non-relational programming languages such as Java, C#, and Perl. The provided examples use SQL Server syntax but should be applicable to all databases.
It is difficult to understand to see what is happening by looking at a complext join in an SQL statement. By adpating a symbols for joins it is easier to get a bird's eyeview of what is happening. This article takes a first swap at the problem.

Open sourced web based database client software

Please declare all your strings as nvarchar in SQL Server, including stored procedure params

Preparing SQLServer for UTF-16

Sample code for an SQL Server function

Sample code snippet for a SqlServer View

Sample code: A few sample stored procs

32) sample sql code

25-May-20

sample sql code

33) SampleStoredProc1

6-Feb-20

This proc demonstrates

1. left Outer joins in SQLServer
2. left join by default is a left outer join
3. You can use a sub-select as a column value
4. You can use top 1 to picke the top row for case 3
5. You can use a function to get the value for a column
6. For 4 and 5, you can use the values of other columns as inputs
7. Use of inner selects for outer joining multiple tables
8. Demonstrates how to pass input arguments and use them

This is a quick research article on what are NoSQL databases and largely when to (and less when not to) use them. These are becoming the staple databases cloud based multi-tenanted systems. This will identify what these databases are and who is using them and what are their core concepts.

35) SQL Examples XRef

30-Aug-20

SQL Examples XRef

36) sql quick journal

24-Mar-20

sql quick journal

SQL Server 2019 installation

SQL Server and SSDs: The nasty dependency

sql server in clause and null set

SQLServer user permissions to execute Stored procedures

SSMS: Sql Server Management Studio notes

t-sql code for altering/creatign indexes

Understanding star schemas

What are key SQL Server Resources?

What does dbo stand for in sql server?

What is SQL Server Browser service?